Snowflaking - BI tool requirement

[angelos_T]

Hi all,
Our organisation has recently invested in the Micro strategy bi software stack.
I have yet to use the tool but from early discussions the consultant is recommending that we normalise our dimension tables in order to enhance performance with this tool.

I'd like to see how the tool performs on current structure before we make any changes but I am curious to see whether anyone has gone down this road before?

A

[nick_white]

What does the consultant mean by denormalising your dimension tables? Dimension tables aren't normalised in the first place.
Can you give an example of an existing Dimension table structure and how the consultant would expect it to look once it had been denormalised?

Thanks,

[angelos_T]

Sorry edited the original message - meant normalise.
A simple example would a simple Product dimension:
Before:
dim_product
product_key
product_id
product_description
category_id
category_description

After
The recommendation is to snowflake the category data.
dim_product
product_key
product_id
category_id

dim_product_category
category_id
category_description

Thanks,
A

[nick_white]

Hi - Dimensions are denormalised specifically to improve query performance by reducing the number of joins. A quick scan of the Microstrategy website found the statement: MicroStrategy can access all of the data in the enterprise: terabyte-sized relational data warehouses to the smallest data marts; multidimensional or cube databases; ...

Given this I would be very suspicious of any suggestion that normalising the tables is going to improve performance (significantly) - though not knowing your specific set-up I can't categorically state that it won't.
Also, even if it does work for Microstrategy, changing your data structures like this will adversely impact any other tool you might have that uses this data.

BTW - the Kimball methodology does support 'aggregated' dimensions. In your case you would keep the Product Dim in its current, denormalised, state and also create a Category Dim that just contains the category attributes from the Product Dim. Fact tables with a grain of Product would join to the Product Dim and fact tables at the Category grain would join to the Category Dim.
Don't know if this helps in the situation you are facing?

Regards,

[BoxesAndLines]

This is a common 'performance' enhancement recommended by the MS folks. I typically ignore this request and keep the dimensions flattened and I've yet to encounter detrimental performance problems. I like your idea of using current structure before snowflaking all the dimensions.

[angelos_T]

Thanks for the feedback guys.
I think would try the aggregated dimension approach first should it come down to it.

[Sponsored content]