Introduction to SQL Server 2000 Analysis Services: Working with Dimensions - Page 3

September 10, 2002

Building and Managing a Single-Table Dimension

Before can create a cube, one or more dimension structures must be in place. While we have experienced dimension creation as integrated steps of the cube building cycle in Lesson One, we will be working without the Cube Wizard in coming lessons, and so will need to create our dimensions prior to building our cube. In Lesson One we discussed dimensions from a star-schema, otherwise known as single-table dimensions. We will begin this lesson by becoming familiar with the Dimension Editor in its simplest context: we will use the Dimension Editor to build a single-table dimension.

To gain exposure to working with dimensions and hierarchical levels, we will perform the following steps.

1. Right-click the Shared Dimensions folder in the MyCube2 Database tree.
2. Select New Dimension --> Editor, on the shortcut menu that appears, as pictured below.

Click to Enlarge
Illustration 8: Initializing the Dimension Editor via the Shortcut Menu

The Choose a Dimension Table dialog appears, listing tables from which we can choose to build our dimension.

3. Select Region from the Tables list, as shown in Illustration 9.

Illustration 9: The Region Table is selected in the Tables List

The Details pane of the Choose a Dimension Table dialog becomes populated with the Column names of the Region table, as shown above.

4. Click OK.

The Dimension Editor appears, as shown in Illustration 10.

Illustration 10: The Dimension Editor, with Keyed Sections

The sections of the Dimension Editor correspond to the keys shown in Illustration 10 above, as follows:

A. Dimension Tree
B. Schema Pane
C. Properties Pane
D. Schema Tab
E. Data Tab
5. Click the Name section of the Properties pane, Basic tab. Type in Region as the value.

This names the new dimension as Region, and places it in the Dimension tree, as shown below:

Illustration 11: The Region Dimension appears in the Dimension Tree

6. Drag the sales_region column from the Region table over the dimension name (the newly added "Region") in the Dimension tree, and drop to create a new dimension level as shown in Illustration 12.

Illustration 12: The Sales Region Level appears in the Dimension Tree

IMPORTANT: Note that while levels can be renamed via the Properties pane, dimension names, once saved, cannot be changed.

7. Rename the Sales Region level in the dimension tree, highlighting it first, then typing Region in as the new name. Press Enter to save.

We now have the Region table (the relational table, represented in the Schema pane, which is the actual data source), a Region dimension (within which the Region hierarchy will reside) and a Region level (which will contain the Region Sales members). To see the actual members, we can browse the dimension.

8. Click the Data tab (see Illustration 10 above). This retrieves a hierarchical representation of the Region dimension (the top level being named "All Region" by default). We can expand the All Region level of the Region dimension to see the eight regional members, as depicted below.

Illustration 13: A Browse of the Region Dimension Members

Page 4: Building a Single-Table Dimension (Continued)

Go to page: Prev 1 2 3 4 5 6 7 8 9 10 11 Next

Introduction to SQL Server 2000 Analysis Services: Working with Dimensions - Page 3

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos