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
- create a db schema where I can store all my data in.
- write code to get the data from the datasources into classes
- get the data into entity framework and write the data into the
database (this should happen every 24 hours or at button click) - 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?
Best Answer
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.