Snowflaking - BI tool requirement

View previous topic View next topic Go down

Snowflaking - BI tool requirement

Post  angelos_T on Wed Oct 22, 2014 1:44 am

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


Last edited by angelos_T on Wed Oct 22, 2014 2:34 am; edited 1 time in total

angelos_T

Posts : 5
Join date : 2014-07-23

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  nick_white on Wed Oct 22, 2014 2:28 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  angelos_T on Wed Oct 22, 2014 2:40 am

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

angelos_T

Posts : 5
Join date : 2014-07-23

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  nick_white on Wed Oct 22, 2014 8:34 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  BoxesAndLines on Wed Oct 22, 2014 11:06 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  angelos_T on Thu Oct 23, 2014 1:25 am

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




angelos_T

Posts : 5
Join date : 2014-07-23

View user profile

Back to top Go down

Re: Snowflaking - BI tool requirement

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum