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

August 8, 2005

Add the New Aged Period Dimension, and Apply a Filter, to the Sales Cube

We will next make a couple of modifications to our cube to finalize construction of our Aged Period dimension. First, we will add the shared dimension to the Sales cube, and then we will put a fact table filter in place to limit our cube to the specified transactions – those that occurred in the last half of 1997.

1.  Right-click the Sales cube within the cloned AgingDim Analysis Services database.

2.  Select Edit from the context menu that appears, as shown in Illustration 23.

Illustration 23: Opening the Cube Editor

The Cube Editor opens.

3.  Right-click the Dimensions folder that appears underneath the Sales cube.

4.  Select Existing Dimensions ... on the context menu that appears, as depicted in Illustration 24.

Illustration 24: Preparing to Add the New Shared Dimension to the Sales Cube

The Dimension Manager appears.

5.  Select Aged Period in the Shared dimensions pane (left half of the Dimension Manager dialog).

6.  Click the single arrowhead button (">") to move Aged Period to the Cube dimensions pane (right half of the Dimension Manager dialog), as shown in Illustration 25.

Illustration 25: Adding the Aged Period Dimension to the Cube Dimensions

The Aged Period dimension appears in the Cube dimensions pane.

7.  Click OK.

The Dimension Manager closes, and we are returned to the Cube Editor. We see the Aged Period dimension appear in the cube tree pane, as depicted in Illustration 26.

Illustration 26: The Aged Period Shared Dimension Appears in the Cube Tree

We will now save the cube, and then apply the fact table filter to limit transactions to the time frame specified by the information consumers.

8.  Select File --> Save from the main menu of the Cube Editor.

9.  Click Yes at the Save the Cube dialog that next appears, prompting us to continue (with loss of storage settings as a result).

10.  Select File --> Exit to close the Cube Editor.

11.  Select No when prompted to design storage, as shown in Illustration 27.

Illustration 27: Decline Storage Design When Prompted ...

The Design Storage dialog disappears, and we return to Analysis Manager.