To star or to snowflake, that is the question…Which of star schema and snowflake schema models perform better is an age old debate between database developers.

Both star schema and snowflake schema are relational models made up of fact and dimension tables. The difference is a snowflake dimension is made up of several highly normalized tables that remove redundant attributes; whereas, a star schema dimension is a signal denormalized contextual table.  Both have a place in data warehousing depending on the objective .  However, in most cases when the end objective is killer analytical query performance, the datamart/database should be modeled using star schema techniques.

When an end user imposes a question on a normalized database, the system must search through numerous tables and records before it can return the answer which murders performance. However, in a star schema model, denormalized contextual dimensions surround a single fact table. This allows the system to point to the dimensions that are contextually necessary and to ignore any table whose context is not referenced in the question.

Since Framework Manager does not store any data in memory and is only a pass-through metadata application, it is especially important that the data source be optimized for maximum query performance. Implementing Framework Manager using a poorly designed database will only produce poor results thus agitating your end users with subpar query speed and accuracy.

As stated above, there are a few instances when it makes sense to snowflake (outrigger) a dimension in a database designed for analytical querying. For instance, if a dimension has an extreme amount of attributes (about 20 or more) that are contextually related, then it makes since to build an outrigger table.

Unlike Hamlet, you do not have a choice whether your BI system lives a miserable existence because of poor query performance or dies a tragic death. The silver lining is a well-designed data warehouse using star schema dimensional modeling.