August 18, 2013 at 1:10 pm
Hi,
I have an Operational Datastore (ODS) instance consisting of several relational databases that are populated daily from exports from the OLTP databases. I use SSIS 2012 for this.
The databases in the ODS are replicates of the databases in the OLTP.
I also hold web analytics etc in the ODS and I will be combining data from all these sources for MI reporting and also for data analysis and visualization through a BI dashboard.
There won't be much data to start off with, but I want to build the system for future use when there will be more data.
Do I build a datawarehouse from the ODS databases, or do I build datamarts, or do I create logical views on top of the ODS databases that can be used by MI reporting and BI tools?
I think performance at this point in time will be met by simple logical views. Is this wise or do I invest my time in designing a proper datawarehouse from the ODS now?
Also, should I import data from the ODS into the datawarehouse using Analysis Services? Is this the correct use of it?
Thanks for any help offered.
August 20, 2013 at 10:32 am
Hi Lise_Synnove,
Sounds like you've got a lot of reading to do. I'd start with the Kimball methodology to get more of an idea of how to attack. Have a look at this:
Depending on how big/complex/distributed your database is will depend on whether you choose a warehouse or a number of marts, especially if your data is not really related.
If I were you I'd build a Staging database layer on top of your ODS and do most of your ETL there. Then I'd feed from staging in to your data warehouse in a star schema.
Then I'd build an Analysis Services cube/number of cubes from your warehouse. Many people are advocates of using views to enable quick changes to the cube without changing your ETL layer. Basically if you do the lion's share of the work now you will end up with a better solution later.
Cheers,
Jim.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply