Database Reference
In-Depth Information
This is known as a
snowflake
schema. If you imagine more tables like
DimGeography
appearing in the diagram, you will see that the structure resembles a snowflake more
than the previous star.
The snowflake schema is nothing but a star schema complicated by the
presence of intermediate tables and joins between dimensions. The problem
with snowflakes is that reading them at first glance is not so easy. Try to answer
the following two questions:
•
Can the
Geography
dimension be reached from
FactInternetSales
?
•
What does the
SalesTerritoryKey
in
FactInternetSales
mean?
° Is it a denormalization of the more complex relationship through
DimCustomer
?
° Is it a completely separate key added during ETL?
The answers in this case are:
•
DimGeography
is not used to create a new dimension, but is being used to
add geographic attributes to the
Customer
dimension
•
DimSalesTerritory
is not the territory of the customer, but the territory of
the order, added during the ETL phase
The problem is that, in order to answer these questions, we would have to search
through the documentation or the ETL code to discover the exact meaning of the fields.
So, the simplicity of the star schema is lost when we switch from a star schema to a
snowflake schema. Nevertheless, sometimes snowflakes are necessary, but it is very
important that—when a snowflake starts to appear in our project—we explain how
to read the relationships and what the fields mean.
It might be the case that a snowflake design is mandatory, due to the overall
structure of the data warehouse or due to the complexity of the database structure.
In this case, we have basically the following options to present a star schema to SSAS:
• We can use views to transform the underlying snowflake into a star schema.
Using views to join tables, it's possible to hide the snowflake structure,
persist our knowledge of how the tables in the snowflake schema should be
joined together, and present to Analysis Services a pure star schema. This
is—in our opinion—the best approach.
Search WWH ::
Custom Search