Top down vs bottom up (build OLAP schema first) and then build cubes?


  • Ok, so I am starting with nothing.  Every example I have been going through for SSAS, and Data warehouse topics in general, is using AdventureWorksDW and building the cubes based on a schema that is already in place.

    Myself and a partner, in the process of discussing what our needs are, have already come up with an OLAP schema to use.  So should I start there, build out those tables, then define my cubes, dimensions etc off of that?  Or should I start with the cube wizard first, define everything and let it generate my schema and then customize it?

    I guess Im wanting to hear from those that have done this a few times how you start from scratch.

    Wednesday, September 25, 2013 11:00 PM

All replies

  • HI 

    i think first you must study about Data warehouse (fact,dim) after that you can start SSAS.

    Thursday, September 26, 2013 4:27 AM
  • What do you mean by olap schema? A logical ER diagram?

    If you have the model ready i'd personally mock it up as a powerpivot model in excel using a handful of rows in the excel worksheets as the source. You could have a fully functioning cube within a few hours/days depending on complexity.

    Fire off powerview and some pivot tables/charts against it to see if your logical model lets you do what you need before starting a ground up build.

    Once you know that it works build your DW tables/source views for your cube & the ETL processes to populate them. Then either import your powerpivot workbook and point it at your DW views if you're sticking with tabular or rework it as multidimensional

    Adelaide, Australia

    Thursday, September 26, 2013 6:41 AM
  • jakubk,

    What Im referring to by OLAP schema, is the underlying DB schema that is used for the data ware house.  For example, if you start with an empty database, run through the cube wizards and have it generate a schema for you.  

    In my case, Im trying to choose between building the schema manually (only about 12 tables or so), and then build the cubes based on those tables - or- as above, run through the cube wizard and define the measures, dimensions etc...and let the wizard generate the schema.  Maybe its a moot point?

    • Edited by shiftbit Thursday, September 26, 2013 2:51 PM sdfsd
    Thursday, September 26, 2013 10:34 AM
  •  Hi shiftbit,

    Is this still an issue or has it been resolved?



    Thursday, February 06, 2014 9:23 AM