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.
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.
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.
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.
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.
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.
15. Click OK to accept the selection.