Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube - Page 4

August 8, 2005


Create a New Aging Shared Dimension

We will create support for aging within our cube by adding a new shared dimension. It will be within this dimension that we construct the logic to derive our aging buckets.

1.  Right-click the Shared Dimensions folder within the new AgingDim Analysis Services database.

2.  Select New Dimension from the context menu that appears.

3.  Select Editor from the cascading menu that appears next, as depicted in Illustration 12.

Illustration 12: Select New Dimension --> Editor from the Context Menus

The Choose a Dimension Table dialog appears.

4.  Scroll down in the Tables pane (left half of the dialog), to locate the time_by_day table in the clone database.

5.  Select the time_by_day table, as shown in Illustration 13.

Illustration 13: Select the Time_by_day Table

6.  Click OK to accept the selection, and to progress to the Dimension Editor.

7.  From the Schema tab in the Dimension Editor, drag the field named the_date from within the time_by_day table to the left, dropping it onto the <New> dimension in the tree, as depicted in Illustration 14.

Illustration 14: Drag the Field Named The_date to the New Dimension

The Date appears as the first level under the <New> dimension.

8.  Click <New> in the Dimension tree to highlight it.

9.  Type Aged Period into the Name box, on the Basic tab of the Properties pane.

10.  Click the The Date level in the Dimension tree.

11.  Replace the Name on the Basic tab of the Properties pane with the following:


Our changes appear as shown in a partial view of the Dimension Editor in Illustration 15.

Illustration 15: Highlighted Changes in the Dimension Editor (Partial View)

We now need to insert a new level within the new dimension.

12.  Right-click the Transaction Date level in the Dimension tree.

13.  Select New Level, as depicted in Illustration 16.

Illustration 16: Select New Level ...

The Insert Level dialog appears.

14.  Select the_date as the member key column (it will be a temporary assignment, as we shall see), as shown in Illustration 17.

Illustration 17: Selecting The_date as a "Placeholder..."

15.  Click OK to accept the selection.