Database Reference
In-Depth Information
A sample scenario for choosing the
Snowflake schema
Here's an example of a design decision process that would lead you to a Snowflake
dimension. Start by assuming that all the dimensions in the Data Mart (versus the
Data Warehouse, where we may have different ideas) will be modeled as Stars.
We start in our first design with a single dimension, Geography, containing the follow-
ing columns:
•
skGeography
(surrogate key)
•
PostalCode
(business key)
•
CityID
•
CityName
•
StateID
•
StateName
•
CountryID
•
CountryName
We have one fact source table containing, say, population data with the following
columns:
•
CensusDate
•
PostalCode
•
PopulationCount
In ETL, we would join this source table to the dimension table on the business key
PostalCode
to retrieve the surrogate key and use this to load the data mart fact
table:
•
CensusDate
•
skGeography
•
PopulationCount
Now, let's introduce a second fact source table containing projected population data,
but with a different grain. Let's assume this data comes in, not at the Postal Code
Search WWH ::
Custom Search