Snowflake schemas normalize dimensions to eliminate redundancy; that is, the dimension data has been grouped into multiple tables instead of one large table.
For example, a product dimension table in a star schema might be EDW 3NF into a products table, a product_category table, and a product_manufacturer table in a snowflake schema.
While this saves space, it increases the number of dimension tables and requires more foreign key joins.
The result is more complex queries and reduced query performance.

Creating a Snowflake

A snowflake dimensional structure is supported by WhereScape RED.
A snowflake can be created for EDW 3NF or partially EDW 3NF dimension tables, by including the surrogate key of the parent dimension in the child dimension.
In the example below, the dim_state table represents the parent dimension.
The column dim_state_key is added to the child dimension dim_customer. Any fact tables that include the dim_customer dimension will inherently have a link to the dim_state dimension.
The process for creating a snowflake is as follows:

  1. Build both dimensions (see previous sections).
  2. Expand dimensions in the left pane.
  3. Click on the child dimension table in the left pane to display its columns in the middle pane.
  4. Browse the data warehouse connection in the right pane.
  5. Expand the parent dimension table in the right pane.
  6. Drag the surrogate key of the parent dimension table from the right pane to the child dimension's column list in the middle pane.
  7. Create/Recreate the child dimension.
  8. Rebuild the child dimension's update procedure.
  9. A screen appears asking for the business key column(s) in the child dimension that matches the business key for the parent dimension:
  10. Add the business key column(s) and then click OK.

The WhereScape RED screen should look like this:

  • No labels