Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 10
July 11, 2005
Modify the Cube to Support Relative Time Periods
The objective behind our efforts in this article is to create relative time capabilities that perform similarly to those generated in Cognos PowerPlay Transformer, specifically, as well as to expose a more sophisticated way of adding these powerful structures to our existing model. A key difference exists between this approach to providing relative time structures and the standalone calculated member approach we examined in Relative Time Periods in an Analysis Services Cube, Part I: the more sophisticated approach of using a specially created dimension means far more flexibility in an OLAP environment. At the conceptual heart of the mechanism is the fact that an independent dimension means yet another point of convergence with the measures in the cube – a point of convergence that can be leveraged by any member of the dimension that houses it.
Because this intersection occurs with every measure, we need provide only a single calculation for each relative time period we wish to be able to report upon from our cubes. In contrast, using the method illustrated in our previous article, we constructed a calculated member for the year-to-date value of a single measure as an illustration. The same year-to-date scenario (as well as other cumulative and comparative relative time periods we will create), as we will discover using the dimensional approach, can be applied to any measure in our cube simply by juxtaposing the desired calculated member with the measure to which we wish to apply its logic.
1. Expand the Cubes folder within the new Relative Time Sample database.
The sample cubes appear.
2. Right-click on the Warehouse sample cube.
3. Select Edit from the context menu that appears, as depicted in Illustration 48.
The Cube Editor opens.
Before we go further, let's update the fact table to reflect our earlier changes within the relational and Analysis Services databases.
4. On the Schema tab, right-click the current fact table, inventory_fact_1997.
5. Select Replace ... from the context menu that appears, as shown in Illustration 49.
The Select table dialog appears. We will select the first view we created in the FoodMart 2000 MS Access database, vW_inventory_fact_1997.
6. In the Table pane of the dialog, scroll to, and select, the vW_inventory_fact_1997 view, appearing near the bottom, as depicted in Illustration 50.
The columns of the view appear in the Details pane, as shown above.
7. Click OK to accept selection.
The Select table dialog closes, and we see that the fact table reference swap has occurred in the Schema view.
Next, we will add the shared dimension, Relative Time, which we created earlier to the Warehouse cube, and then create six calculated members within the cube. The calculated members will contain the relative time logic we need to add the desired capabilities to the cube, as we shall see. For purposes of our practice example, we will create a handful, consisting of the following popular variations, to illustrate the concepts:
Keep in mind that the tiny set we will assemble might be expanded to meet virtually any relative time consideration. We can leverage a vast assortment of MDX functions (and combinations of functions) via the same concept. Our point here is to illustrate an approach to unleashing some of that power in an efficient approach to meeting common business requirements.
8. Select Insert --> Dimension from the main menu of the Cube Editor.
9. Select Existing ... from the cascading menu that appears, as shown in Illustration 51.
Dimension Manager opens.
10. Scroll to, and select, Relative Time in the Shared Dimension pane, as depicted in Illustration 52.
11. Click the Add a dimension (for a single selection) button, the top ">" button (circled in Illustration 52 above) between the panes of the Dimension Manager.
The Relative Time dimension appears in the Cube dimensions pane.
12. Click OK to accept the new addition.
The Dimension Manager closes, and we see the Relative Time dimension appear in the tree, within the Dimensions folder, as shown in Illustration 53.
13. Right-click the Calculated Members folder in the tree.
14. Select New Calculated Member ... on the context menu that appears, as depicted in Illustration 54.
The Calculated Member Builder opens.
15. Select Relative Time in the top Parent dimension selector box of the Builder.
16. Type the following into the Parent member box:
[All Relative Time]
17. Type the following into the Member name box:
18. Type the following MDX into the Value expression box:
SUM(PeriodsToDate([Time].[Year]), ([Relative Time].[Relative Time].[Current]) )
19. Click Check to perform syntactical verification.
An Analysis Manager message box appears, indicating "Syntax is OK, as shown in Illustration 55.
20. Click OK to close the message box.
The Calculated Member Builder appears, with our input, as depicted in Illustration 56.
21. Click OK.
The Calculated Member Builder closes. We see the new Calculated Member within the Calculated Members folder of the tree, as shown in Illustration 57.
NOTE: For more information on the PeriodsToDate() function, see my article MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions in the MDX Essentials series at Database Journal. The article details "shorthand" versions of the PeriodsToDate() function, which include a "YTD" version.
In addition, we discuss the use of the PeriodsToDate() function in a simpler approach for providing relative time capabilities individually for a given measure in our previous article, Relative Time Periods in an Analysis Services Cube, Part I. In that article, our practice exercise centers upon the creation of a standalone calculated member, which exploits a combination of the SUM() and PeriodsToDate() functions to accomplish a more basic objective than the need we have described in this article.