Star- or Snowflake-relational schema

There continues to be much discussion, and often debate, over the question of whether a Star or Snowflake schema is preferred and whether cubes or Tabular Models may be required.

Note

In the Star schema, each fact table is directly related to every dimension table; in the Snowflake schema, some dimension tables may be further normalized and connected to the fact table through other dimensions. You can use Star or Snowflake data models for building multidimensional as well as Tabular Models.

The answer is that either architecture is acceptable, and in most environments, the best choice is not one or the other but rather a mix of both.

Before making a decision on using a Star or Snowflake architecture for your relational scheme, it's important to understand the key characteristics of each. Stars are denormalized models, most typically seen in data marts. Though not optimal for data maintenance activities (as they are heavily data redundant), Stars are very fast to query and due to their far less complex schema, they are easier for business users to navigate. Snowflakes, on the other hand, are normalized models, most typically seen in data warehouses. Since they are normalized, Snowflakes are optimized for data maintenance, but the requirement of joining many tables to retrieve data mean a more complex overall schema and slower queries.

Given that our primary goal in BI is to provide access to data as quickly and intuitively as possible, Stars are generally considered to be the preferred "outer" data layer. Outer in this case implies that we may have (and often we do have) a normalized (Snowflake) data warehouse, which is the primary persistent managed data store. The denormalized (Star) Data Mart is populated from the Data Warehouse as a way of positioning data for optimal user, reporting, and application use. Cubes and Tabular Models, like all analysis tools, benefit greatly from this optimization but can consume the normalized data warehouse as well—usually not as efficiently.

However, this does not mean that my Data Mart must be entirely comprised of denormalized Star structures. You will find that as your environment matures, you will be faced with the fact data of differing grains along shared (conformed) dimensions. In such instances, you should consider normalizing (Snowflaking) those specific dimensions in order to accommodate those different facts. This is a good example of a Data Mart that is still considered a Star architecture but contains a small number of Snowflake dimensions.