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.
Illustration 48: Opening
the Cube Editor for the Warehouse Cube
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.
Illustration 49: Targeting
the Fact Table for Replacement
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.
Illustration 50: Selecting
the View as the Replacement Fact Table
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:
-
Year-to-Date
Total
-
Quarter-to-Date
Total
-
Month-to-Date
Total
-
Prior Year
Total
-
Prior Quarter
Total
-
Prior Month
Total
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.
Illustration 51: Select
Insert --> Dimension --> Existing ...
Dimension
Manager opens.
10. Scroll to, and select,
Relative Time in the Shared Dimension pane, as depicted in Illustration
52.
Illustration 52: Select
the Relative Time Shared Dimension
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.
Illustration 53: The
Relative Time Dimension Appears within the Cube
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.
Illustration 54: Select New
Calculated Member ...
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:
Year-to-Date Total
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.
Illustration 55: "Syntax
OK" Indication
20. Click OK to
close the message box.
The Calculated
Member Builder appears, with our
input, as depicted in Illustration 56.
Illustration 56: The
Calculated Member Builder with Our Input (Compressed View)
The Calculated
Member Builder closes. We see the
new Calculated Member within the Calculated Members folder of the
tree, as shown in Illustration 57.
Illustration 57: The Year-to-Date
Total Calculated Member Appears ...
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.
» 
Comment and Contribute
