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 following 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 ...

Get SQL Server Analysis Services 2012 Cube Development Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial