Creating a Dimension from a Snowflake Schema: Multiple, Related Dimension Tables
Since the product_id key in our fact table is joined to a series of Product Dimension tables, and because each of these tables contains columns that are needed to define the respective levels of a multiple-tier hierarchy, we will need to approach this slightly more complex scenario from the perspective of a snowflake schema dimension. With the exception of defining joins, and a few additional steps, the Dimension Wizard certainly comes to our assistance in making the process straightforward and efficient.
We begin, once more, by clicking the New Dimension button in the Cube Wizard, where we left it in the previous example. We click Next at the Dimension Wizard's Welcome screen, and then select the Snowflake Schema radio button at the Choose How You Want to Create the Dimension dialog, as shown below in Illustration 22. We are given additional information about the choice we have made (in the Description area at the bottom of the dialog), and then we click Next.
Illustration 22: Choosing Snowflake Schema at the Choose How You Want to Create the Dimension Dialog
We will select the product and product_class tables, as shown below (Illustration 23), then click Next to continue to the Create and Edit Joins dialog, where we can see the joins (relationships) that exist between the tables we have selected.
Illustration 23: Selecting the product and product_class Tables to Define Dimensions
Illustration 24: The Create and Edit Joins Dialog, where we can Review / Edit Table Relationships
The join between the two tables we have chosen appears appropriate at the product_class_id field, as shown in Illustration 24 above. Data types and other considerations need to be reviewed, or problems will almost certainly result later, but in our simple scenario, the join depicted is as it should be. (For more information on deleting or creating new joins, or on the criteria that dictate correct definition and placement of these relationships, see the online documentation, as well as the Analysis Services volume of the MSSQL Server Reference Library, for starters). Click Next to move forward.
We again select levels for the dimension at the next dialog, with warnings again given if we seem to be placing the levels out of order. We appropriately order them as shown below (Illustration 25) from most summarized to most detailed, from top to bottom product_category, product_subcategory, and brand_name.
Illustration 25: Selecting Levels for the Product Dimension
We click Next, again skipping the Specify Member Key Columns for our present purposes, as well as passing over Advanced Options as beyond our immediate scope in this session.
Finally, we arrive at the Finish ... dialog, and enter Product in the Dimension Name box, and leave the other options with defaults selected, as shown in Illustration 26.
Illustration 26: Finishing the Dimension Design for the Product Dimension
After clicking Finish, we again arrive at the Cube Wizard dialog, where, as illustrated below (Illustration 27), we see the Product Dimension is now a member of the Cube Dimensions list.
Illustration 27: The Product Dimension now appears in the Cube Wizard Dimension List
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0