none
Data warehouse newbie question - how to generate my OLAP database starting from nothing

    Question

  • So I have been watching some videos on SSAS and general Data warehousing concepts.  Now I know just enough to be dangerous.  I have SQL skills, along with SSIS and SSRS, but have never messed with SSAS nor a data warehouse, cubes etc.

    One of the tutorials I was watching (Pluralsight) mentioned what they referred to as the "top down approach", meaning they were starting with nothing.  This is where I am.  I have several OLTP databases but no OLAP database. 

    (1) Correct me if I am wrong, but can I start by using the cube wizard, define my mesasures and dimensions, and then generate the schema from there.  Then, I will have an empty database, which I can then fill with SSIS packages. 

    That sound about right?

    (2) Another general idea, is where to start, or rather how.  I see it as starting with what the requirement is for the user(s).  In my case, we are starting small, probably with just one or a few reports.  From a report, I will know what the measures are, as well as the dimensions.  Then I can build these out with the cube wizard as mentioned above, then generate my schema and populate the fact/dimension tables with one or more SSIS packages.

    That sound reasonable?

    (3) Ok, say I have done he above and now Im on to the 2nd report and I decide I will need all new measures and new dimensions, and as such will need this schema in my OLAP db.  Will I use the cube wizard here also, and generate the schema for this new cube?  What if its just a new dimension?



    • Edited by shiftbit Tuesday, August 13, 2013 1:15 PM sdfgfd
    Tuesday, August 13, 2013 12:44 PM

Answers

  • (1) Yes, you are right. You can do this quite easily. Here is a tutorial, for example: http://knowledgeinside-jithesh.blogspot.com/2012/05/ssas-creation-of-cube-top-down.html

    Although, recommended is to start exactly the opposite, that is bottom up. First build the relational datawarehouse and build the SSAS cube on top of that. As far as I know, a great number of developers are using this approach

    (2) Yes, it does sound reasonable. The SSAS cube is a flexible construction which allows you to add additional dimensions, measure groups etc. Again, see the recommendation from the first point.

    (3) Here you will use the measure group and dimension wizards. You can see these links for more information

    http://technet.microsoft.com/en-us/library/ms365388(v=sql.105).aspx

    and 

    http://technet.microsoft.com/en-us/library/ms174792(v=sql.105).aspx

    Please let me know if you have any more questions and I will do my best to answer them.

    Razvan

    Per aspera ad astra!

    • Marked as answer by shiftbit Tuesday, August 13, 2013 4:23 PM
    Tuesday, August 13, 2013 2:39 PM