Building and Managing a Multi-table Dimension

As we saw in Lesson One, the Dimension Wizard provides a radio button setpoint, in its "Choose how you want to create the dimension" dialogue, where we can indicate that we need to create a multi-table dimension (a "Snowflake Schema"), as shown in the illustration below.

Click to Enlarge
Illustration 20: The Choose How You Want to Create the Dimension Dialog

While we can use the Dimension Wizard to add the tables we need to accomplish this, our objective in the next several lessons is to lessen our dependence on "wizardry" and, more importantly, to familiarize ourselves with the advanced features of the Dimension Editor. We will add the tables ourselves, with the objective of recreating the Product dimension that we created via the Wizard in our first lesson.

This will involve dealing with properties with which we were not confronted using the Dimension Wizard, but therein lay opportunities to more finely customize our dimensions, and to add a higher level of sophistication to our overall cube design. We will expose some of these properties, and discuss other relevant design considerations, as we build the multi-table dimension.

Building the Dimension

We begin creating a multi-table dimension by taking the following steps:

1. Select from the Dimension Drop-down List box in the upper right corner of the Schema pane (see Illustration 10 above for location of the Schema pane.)

The Dimension Drop-down List box always indicates the dimension in which we are currently working. Illustration 21 shows the Dimension Drop-down List box, with the option selected.

Illustration 21: The Dimension Drop-down List Box

We wish to select the product and product_class tables, as we did in Lesson One, to allow us to define the Product dimension. While it is always best to select multiple tables from the lowest to the highest, or the highest to the lowest, level in the hierarchy (to ensure that joins are properly placed), in our example (with two tables) this should not present too serious a problem.

2. Select the product table.

We see the Details pane populated with the columns of the product table, as shown in Illustration 22.

Illustration 22: The Select Table Dialog with product Table Selected

3. Click OK, and return to the Dimension Editor, with the product table now appearing on the right side.
4. Select Insert from the top menu, Tables ... option, as shown below.

Illustration 23: Selecting Insert --> Tables ... from the Top Menu

5. When the Select Table dialog appears, (shown below) select the product_class table, in accordance with our selection in Lesson One.

Illustration 24: The Select Table Dialog with product_class Table Selected

6. Click Add, and then Close.

We see the two tables appear together when the Dimension Editor reappears, as seen in Illustration 25 below.

Illustration 25: The Dimension Editor Dialog with product and product_class Tables

A join appears between the two tables at product_class_id. The join is appropriate, so we leave it undisturbed. NOTE: Had we needed to add a join, this would be done in a manner very similar to the process of creating joins in MS Access and other applications. The column to be joined from one table is simply dragged over onto its "partner" in the other table(s). To delete a join, again like MS Access, et. al., we simply click the join line to select it, then press Delete.

7. Click the Basic tab of the Properties pane.
8. Type in Product as the Name of the new Dimension.
9. Press Enter.

The Dimension tree reflects the new Product dimension, as shown in the illustration below.

Illustration 26: The New Product Dimension Appears in the Dimension Tree