Post reply

Star Schema or Cube ... advantages to each?

  • October 20, 2010 at 1:14 pm

    #228123

    We have a star schema loaded via an etl package and a corresponding cube.

    My bosses seem to think we would be fine querying the star schema and never use the cube?!

    I'm fairly new to the BI world, but this seems crazy to me..Why not use the cube?

    Are there advantages to using the star schema and/or the cube?

    I'm having trouble convincing them to use the cube....help!

  • October 20, 2010 at 4:02 pm

    #1239986

    So the real question is direct DB queries versus cube queries.

    Well unless you are going to go through the effort of building accumulations and pre-calculated roll-ups the cube is the way to go.

    Now let me say why..

    The cube (if it is designed even slightly right) will contain pre-aggregated and pre-calculated values. These help come to monthly/weekly/quarterly/yearly summations. Otherwise you have to calculate them everytime they are calculated. Really the only place a cube WON'T help is for a detail report.

    What I am hearing you say is that your management slightly understands database and doesn't understand data warehouse fundamentals at all..

    Does this answer your question?

    CEWII

  • October 21, 2010 at 3:23 pm

    #1240624

    yeah it helps thank you..

  • October 22, 2010 at 3:14 am

    #1240777

    I'm not certain why your bosses allocated the resources to construct a cube and the ETL process to populate it. If all they needed was a server to query, a copy of the normal database could have been used.

    Using SQL queries to query the star schema denies the ability to work things in "think time." The cube allows you to find patterns to assist in decision support in a way that ordinary SQL queries could not do nearly so efficiently.

    You don't mention MDX, and since you said you are new to BI, I'd point out that the cubes can be queried using MDX, the cube equivalent to SQL. From my view it's more difficult to learn than SQL but provides some interesting possibilities.

  • October 23, 2010 at 12:23 pm

    #1241295

    To make it easy for your management:

    SQL query to database: find the needle in the haystack

    Cube: look at the whole haystack

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply