Featured Database Articles

Making the Cube Multi-Dimensional: Creating Meaningful Perspectives in our Cube

Each of the _id columns we saw in the last dialog box represented dimensions -- in the form of key columns that might be joined to dimension tables from the fact table in the MyFoodMart star schema. These joins provide a means of allowing us to pull in more details about the dimensions, and, in effect, act as the "link" between fact and dimensions. Much power lies in this concept, and a more in-depth look at the relational concepts surrounding, and the physical attributes and capabilities of, joins might be of interest at another time. Suffice it to say that we now have the opportunity to select existing dimensions -- or to even create new ones -- thus allowing us significant flexibility within the standard operations of the Cube Wizard -- or more accurately, within a second, "child" wizard, the Dimension Wizard.

From the "Select Dimensions" dialog that appeared when we clicked Next at the measures selection step shown above (Illustration 11), we will now click New Dimension at the dialog box we encounter next, shown below (Illustration 12). This results in a launch of the Dimension Wizard, as shown immediately next (Illustration 13), whose Welcome dialog can also be skipped going forward (with the check box), should we choose to short circuit the Welcome dialog later.

Illustration 12: Click New Dimension to Start the Dimension Wizard

Illustration 13: The Welcome Dialog for the Dimension Wizard

Creating a Dimension from a Star Schema: A Single Dimension Table

We click Next for now, and arrive at a dialog that asks how we want to create the current dimension. Important, contextually sensitive information is displayed for each of the five possible options. For purposes of our examination of the basics, we'll accept the default, Star Schema, as shown below in Illustration 14.

Clicking Next at this juncture brings us to the initial prompt to Select the Dimension Table, from which we will select region. Many attributes of the region dimension appear as details in the right pane, as shown below in Illustration 15, and, as with the Fact Table Selection dialog in earlier steps, we can verify our choices by reviewing a snapshot of actual data using the Browse Data... button.

Illustration 15: We Select the Region Table as a Source for our First Dimension

We click Next, and the Wizard prompts us to Select the Levels for ... the Dimension we have chosen, when such levels exist. We can see that various hierarchical levels might be of interest within the region dimension. We select five for this dimension, sales_country, sales_region, sales_state_province, sales_district, and sales_city, then we attempt to rank them in what we think is the logical hierarchy, for optimal reporting purposes, as shown below in Illustration 16.

Illustration 16: At the Prompt, we select sales_country, sales_region, sales_state_province, sales_district, and sales_city

Attempting to rank these in logical order, from summarized to detailed, might be a bit confusing. Knowing the data well is the best defense, but say we weren't sure and made an attempt to move district above province (there are fewer districts than provinces, as the former are, in reality, a subset of the latter). The Wizard warns us that the arrangement might not be the most logical, and provides a valuable tip regarding the best order for reporting purposes, as depicted in Illustration 17. We decide to follow the implied advice, and abandon the contemplated re-sort with a click of the Yes button. The Wizard places levels in the order that appears most logical (small to larger, from the top down, in this case) as a result.

Once we are returned to the Dimension Selection dialog, clicking Next takes us through the Specification of Member Keys dialog (see Illustration 18) -- which broaches topics beyond the scope of our simple overview -- then through the Advanced Options dialog (Illustration 19), a topic for discussion later in our series, which we will leave at default setpoints.

We next arrive at the Finish the Dimension Wizard dialog, a preview point, where we name the Dimension "Region", simply enough. Using the "+" sign to the left of the topmost level of the dimension tree to expand the levels listed, we can preview the hierarchies that will be created (and check our design, to some extent), as shown in the following picture (Illustration 20). We will leave the rest of the setpoints at default for now, and click Finish.

Illustration 20: The Finish the Dimension Wizard Dialog

We return to the Cube Wizard, where we see immediately that Region appears in the list of Cube Dimensions, as shown below (Illustration 21).

Illustration 21: Back at the Cube Wizard, we see Region as a Listed Dimension