Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How do I use Analysis Services? Expand / Collapse
Posted Sunday, August 18, 2013 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 3:34 AM
Points: 3, Visits: 20
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.

Post #1485615
Posted Tuesday, August 20, 2013 10:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 14, 2016 4:29 AM
Points: 350, Visits: 713
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.



SQL SERVER Central Forum Etiquette
Post #1486360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse