Snowflake Design and Microstrategy

View previous topic View next topic Go down

Snowflake Design and Microstrategy

Post  turbotortuga on Tue Aug 11, 2015 8:21 am

Hello everyone!

It has been a while since I have posted something, well here it goes:
I am currently working in a company that uses Microstrategy as the reporting tool. The engineers have been developing Snowflake designs to accommodate data for the reporting tool.
I have never designed Snowflake.
What it is interesting to me is that according to their designs, a "Dimension" is a Type-1 table where the data is overwritten and no history is kept, they can also be "Lookup" tables where only inserts are done if no matches are found. These lookup tables can be for example (DimAssetStatus, DimAssetTaskStatus, DimRepairStatus, type, category dimensions etc...)

"Fact" tables are use to track changes (history), I see dimensions also containing measures such as Sale Price, Original Principal Balance etc. Instead of in the facts...

While these design patter is unique to me, given I'm new to Snowflake design....Is it correct? I'm used to Star Schemas where data is de-normalized rather than in 3NF, dimensions having either Type-1 or Type-2 attributes making them slowly changing dimensions (to preserve history) and fact containing the measurements at the lowest possible grain. Cardinality between fact and dimension being one-to-many and so on...

By the way, dimensions are connected to each other and not necessarily through facts.

Any thoughts on these?
For example would it be consider bad design if I combine: DimAssetStatus, DimAssetTaskStatus and DimRepairStatus to a single table called: DimAssetStatus?
Should facts be use the way they are using them? What are the dos and don'ts in Snowflaking?

Give me all you got!
RGC.
avatar
turbotortuga

Posts : 13
Join date : 2013-11-04

View user profile

Back to top Go down

Re: Snowflake Design and Microstrategy

Post  ngalemmo on Tue Aug 11, 2015 11:16 pm

Basically, all snow flaking means is dimensions contain foreign keys to other dimensions.

I have seen star schemas implemented against MS.  

One of the features MS provides is automatic drop-down lists of values a user can select from when filtering queries.  For performance reasons, you provide a bunch of code/description tables or views to support this feature.  It is easy enough to refresh these tables using a select distinct from an existing dimension.  You can do this with views if the dimension table is small.

Others choose to normalize the dimensions (snowflake).  This results in a more complex model.  

Many DBMS's today have optimizations specifically for star queries.  It will use them if it sees the classic query pattern: a large fact table with FK references to single dimension tables.  If you introduce additional joins from the dimensions, you break that pattern and do not have the ability to benefit from those optimizations.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Snowflake Design and Microstrategy

Post  zoom on Thu Aug 13, 2015 8:05 am

Snow Flaking is not recommended because it creates more table joins and because of the volume of the data. A dimension can be type 1, so there is nothing wrong having it. Sales price can be a Fact/measure or it can be attribute of a product dimension. Fact tables can track history and you can have type 2 slowly changing Fact table.

Having said that Microstrategy tool needs tables in a certain design so it can perform fast. You have a great opportunity to learn about Microstrategy and how to deal with SQL performance issue by tweaking dimensional modeling best practices.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Snowflake Design and Microstrategy

Post  turbotortuga on Thu Aug 13, 2015 8:28 am

Thank you guys!
Your input is much appreciated. I feel better now about the approach that has been taken by the engineers here.
I want to make sure I contribute in the right way to the team, and I am willing to follow their design patter and choice, Snowflake, I must say that I do agree with both of you, while Snowflaking is a valid design, it does add complexity and increases table joins, but on the plus side it does reduce redundancy and better adapts in Microstategy when it come to drop-down menus and such...

Now for the following question:
For example would it be consider bad design if I combine: DimAssetStatus, DimAssetTaskStatus and DimRepairStatus to a single table called: DimStatus?

In a SnowFlake design, can this be a single "Status" table (a conformed dimension) that can be referenced by other dimensions that have a "status" attribute, or is it better to keep it as separate entities (smaller dimensions) where each dimension containing a "status" attribute would contain the FK to a separate "Status" dimension such as: DimAssetStatus, DimAssetTaskStatus and DimRepairStatus?

Thanks again!
RGC
avatar
turbotortuga

Posts : 13
Join date : 2013-11-04

View user profile

Back to top Go down

Re: Snowflake Design and Microstrategy

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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