1

At the moment I have three different sources where my data are. 1. A Dataprovider, 2. inhouse access DB and 3. Salesforce. Now I want to build a datawarehouse (using ms software) where I want to store the data and create reportings via e.g asp.net mvc or excel. Since we have really small datasets (all together is about 500mb saved in csv) I am working on a concept to avoid the huge sql server. My idea up till now is

  1. create a db schema where I can store all my data in.
  2. write code to get the data from the datasources into classes
  3. get the data into entity framework and write the data into the database (this should happen every 24 hours or at button click)
  4. set a reporting module that gets data from the entity framework and creates reports

my problem is that I am not sure if this works at all. Is it possible to update the data every 24 hours via the entity framework?

ruedi
  • 133
  • 10

2 Answers2

5

It would work but it is using the wrong tools for the job.

The whole process here is DB-centric, so use the DB-centric tools. SSIS will be able to reads your datasources and update your data warehouse. One advantage is not having to recompile a program that can perform this work if you use SSIS, but also if one day you hand off your work to a DBA, he will not have to learn C# (most DBAs do not want to work outside the DB). You will also find that using SSIS to load csv data is a 'solved problem' that does not require writing a new program using EF.

In short, use the right tool for the job, and that tool is the DB import utilities that comes with SQL Server: ie SSIS.

Community
  • 1
gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
  • Thanks for your answer, I think I will go the DB-centric way. One of my datasources is a Rest Service that delivers JSON I still would have to write C# code within the script task though to get the data isnt that right? So the C# Problem would still be there. – ruedi May 23 '16 at 16:15
  • Not necessarily (though it depends on the complexity of the data). You can [parse JSON in SSIS packages.](http://dennysjymbo.blogspot.co.uk/2013/05/using-json-feed-as-data-source-in-ssis.html) – gbjbaanb May 23 '16 at 17:39
0

Since you haven't started any work yet, have a look at Power BI: you can use it to gather data from Access, Database AND Salesforce. You'll have to clean the data, build the data model and the Reports, but all from the same tool. It can also deal with JSON. If you need to report mainly on numbers you can Excel as your front end. Since the data is small in its size it might be good enough (I've found Power BI pretty quick to learn).

Hila DG
  • 109
  • 2