Database Reference
In-Depth Information
Star- or Snowflake-relational schema
There continues to be much discussion, and often debate, over the question of wheth-
er 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 Snow-
flake 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 de-
normalized 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.
Search WWH ::
Custom Search