Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 9
July 11, 2005
Create a New Relative Time Shared Dimension
We will begin the procedure for creating support for relative time periods by adding a new shared dimension, with which we will next associate calculated members that will perform the aggregations, as we shall see.
1. Right-click the Shared Dimensions folder within the new Relative Time Sample 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 41.
Click for larger image
The Choose a Dimension Table dialog appears.
4. Scroll down in the Tables pane (left half of the dialog), to locate the relativetime table in the clone database.
5. Select the relativetime table, which we created expressly to support the new relative time dimension, as shown in Illustration 42.
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 relativetime_id field from within the relativetime table to the left, dropping it onto the <New> dimension in the tree, as depicted in Illustration 43.
Relativetime_Id appears as the first level under the <New> dimension.
8. Click <New> in the Dimension tree to highlight it.
9. Type Relative Time into the Name box, on the Basic tab of the Properties pane.
10. Click the Relativetime_Id level in the Dimension tree.
11. Replace the Name on the Basic tab of the Properties pane with the following:
12. Replace the current text in the Member Name Column box, "relativetime"."relativetime_id", with the following:
Our changes appear highlighted in a partial view of the Dimension Editor in Illustration 44.
13. Select Tools --> Process Dimension from the main menu within the Dimension Editor, as depicted in Illustration 45.
14. Click Yes when prompted to save the dimension.
The Process a Dimension – Select the processing method dialog appears, with only the Rebuild the dimension structure option available (this is the first time the dimension has been processed), as shown in Illustration 46.
15. Click OK to proceed with processing.
Processing begins immediately, and the Process viewer appears, displaying various logged events. Processing completes, and the viewer presents a green Processing completed successfully message, as shown in Illustration 47.
16. Click Close to dismiss the Process viewer.
17. Select File --> Exit to close the Dimension Editor, and return to Analysis Manager.
We now have a Relative Time dimension within which we will concentrate the relative time capabilities requested by the information consumers. We will add the new dimension to the Warehouse cube in our next steps, where we will make other enhancements to the cube structure to complete our addition of relative time structures.