Handling Time-based Accumulation at Numerous Levels
In this article, we will
focus on time-based accumulation. By "accumulation," I mean the
calculation of a period-to-date
total, through summing or other aggregation options over a range of members of
the Time dimension. As in our last article, we will manage this over general levels of the time
hierarchy, and not strictly within the domain of years, although most of us
probably see "Year-to-Date" accumulations most frequently in
business - largely as a means of showing "where the business is at this
point in the year," and quite often as a means of providing comparison to "where
the business was at this point last year," or some other year. The "period"
in "period-to-date" can obviously be days, weeks, months,
quarters, years, and other levels that occur between, and externally to, these.
An important objective to consider within our coding efforts is to make our
solution work at multiple levels, so as to take advantage of the general power
of OLAP.
With the foregoing in
mind, we will pursue a solution that handles a requirement to accumulate
periods, but which does so in a way that the accumulation occurs at all
relevant levels. We will do this within the frame of an exercise where we
build the MDX from scratch to meet a hypothetical business requirement, as we
do in most of our MDX articles. This way, we can assimilate the concepts in a
logical fashion and see the working components in meaningful increments. This
avenue also allows us to avoid the "distraction of dissection" that
often accompanies the "documentation" and procedural articles that we
sadly find to be the norm these days.
We will start with a hypothetical
scenario that illustrates a need for a period-to-date accumulation. Let's say that a group of FoodMart information
consumers have come to us with a business need that they wish to meet using Warehouse
(one of the sample cubes that install with a typical MSAS installation) data.
The consumers want to be able to report upon the "to-date" warehouse
profit, certainly within a "year-to-date" context, but also from
the perspective of quarters and months, for year 1998.
We will approach this in
steps, again using the Sample Application (which also accompanied our
MSAS installation), as a platform from which to perform our practice exercise.
1.
Start the MDX
Sample Application.
We are
initially greeted by the Connect dialog.
2.
Click OK,
after ensuring that appropriate settings are in place for the local
environment.
The MDX
Sample Application window appears.
A
blank Query pane appears.
4.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
5.
Select the Warehouse
cube in the Cube drop-down list box.
The MDX
Sample Application window should resemble that depicted in Illustration 1,
complete with the information from the Warehouse cube displaying in the Metadata
tree (left section of the Metadata pane).
Illustration 1: The MDX Sample Application Window
(Compressed View)
As we
did in the previous session, we will begin creating our query with a focus on
building the Time hierarchy we need in the row axis, only this time with
the single Warehouse Profit measure, initially returning the "direct
pulls" that require no calculated members. Once the core query is in
place, we will enhance it, in the next phase, to add in a calculated member for
what we will call "Profit-to-Date," meaning "accumulated Warehouse
Profit through the current period," which will reflect the appropriate
accumulated value at any level of the hierarchy.
1.
Create the
following new query:
-- MXAS16-1: Simple Core Query (Time Dim Axis and Regular Measure)
SELECT
{ [Measures].[Warehouse Profit]} ON COLUMNS,
{ [Time].[Year].[1997].[Q1].Children,
[Time].[Year].[1997].[Q1],
[Time].[Year].[1997].[Q2].Children,
[Time].[Year].[1997].[Q2],
[Time].[Year].[1997].[Q3].Children,
[Time].[Year].[1997].[Q3],
[Time].[Year].[1997].[Q4].Children,
[Time].[Year].[1997].[Q4],
[Time].[Year].[1997]} ON ROWS
FROM
[Warehouse]
2.
Execute the
query using the Run Query button.
The
results dataset appears as shown in Illustration 2.
Illustration 2:
The Results Dataset - Simple Time Members in Row Axis and Basic Measure
3.
Save the query
as MXAS16-1.
The core
query allows us to get the Time levels aligned in the row axis. We are
now free to focus on the relative time concepts involved with meeting
the information consumer's stated business need. The enhancements we graft in
next will add the Profit-to-Date calculated member on the column axis,
to the right of the Warehouse Profit measure,
4.
Within the
query we have saved as MXAS16-1, replace the top comment line of the
query with the following:
-- MXAS16-2: Complete Query (Adding Profit-To-Date Calculated Measure on Column Axis)
5.
Save the query
as MDX16-2, to preserve MDX16-1.
6.
Under the
comment line we have adjusted, and before the SELECT statement, add the
following expression:
WITH
MEMBER [Measures].[Profit-to-Date]
AS
'SUM(
PERIODSTODATE([Time].[Year], [Time].CurrentMember),
[Measures].[Warehouse Profit]
)'
The
purpose here is to create a Year-to-Date aggregation, using a summed PeriodsToDate()
function to generate a set that delivers what we need. Now, let's add the
calculated member we have defined above to the existing SELECT statement,
in the appropriate position to present it to the right of the Warehouse
Profit measure, by taking the following step:
7.
Within the
first line of the SELECT statement, which currently appears as follows:
{ [Measures].[Warehouse Profit]} ON COLUMNS,
Insert a comma between the right-most bracket in the line
(the "]" just to the right of Warehouse Profit) and the
right-most curly brace ("}").
8.
Insert a
space, then the following expression, between the comma just inserted, and the
right-most curly brace:
[Measures].[Profit-to-Date]
We
are simply adding the new calculated member to the right of the existing [Measures].[Warehouse
Profit] measure, within the brackets of the ON COLUMNS line of the SELECT
statement. The modified line will appear as shown:
{ [Measures].[Warehouse Profit], [Measures].[Profit-to-Date]} ON COLUMNS,
The
Query pane
appears as shown in Illustration 3, with the newly inserted coding
circled in red.
Illustration 3: The Query
with Modifications Circled
9.
Execute the
query using the Run Query button.
The
results dataset appears, with new calculated member circled in red, as shown in
Illustration 4.
Illustration 4:
The Results Dataset - New Calculated Member Circled
10.
Save the query
as MXAS16-2.
A review
of the dataset that is returned reveals that our Profit-to-Date
calculated member meets the expectations of our information consumers The
mechanics behind the calculation reflect a combination of the Sum()
function and the PeriodsToDate() function, together with .CurrentMember,
used in a manner similar to the way we used it in our last article. The nucleus
of the calculation is as follows:
'SUM(
PERIODSTODATE([Time].[Year], [Time].CurrentMember),
[Measures].[Warehouse Profit])'
NOTE: For a detailed introduction to
the PeriodsToDate() function, please see MDX
Time Series Functions, Part I: PeriodsToDate() and Kindred Functions, and for more details surrounding
the .CurrentMember function, see MDX
Member Functions: "Relative" Member Functions, in the Database Journal MDX Essentials collection.
As we
have observed numerous times, within this and other series, it is often
necessary, in building MDX statements, to relate a current member value
to others in the cube hierarchy, just as we have done in the above expression.
Although MDX has numerous methods that can be applied to a member to traverse
hierarchies, the most commonly used ones certainly include .CurrentMember
and .PrevMember. We have used .CurrentMember, in this instance,
to obtain precisely the value we seek, the accumulated "to-date"
total of Warehouse Profits, at the current period (composed of
the intersect of a given point in the time hierarchy and [Measures].[Warehouse
Profit].
11.
Exit the MDX
Sample Application and Analysis Manager when ready.
Summary and Conclusion ...
In
this article, we continued in the direction in which we began in our previous
article, focusing on the Time dimension from the perspective of MDX
queries. We examined "period-to-date" aggregations, initially
with a discussion of their pervasiveness as a common business requirement. We
mentioned that, although Year-to-Date totals are, perhaps, the most
common manifestation of period-to-date constructs, the same concept of
accumulation over a period range applies at subordinate time levels, as well. We
illustrated this point in the practice example that we undertook next.
We
activated the concepts that we explored in a hands-on example, which illustrated
the satisfaction of a hypothetical business requirement we received from a
group of information consumers. Within the steps of the example, we used MDX to
establish a core result set, then to generate the period-to-date
accumulation at every level of the Time hierarchy, discussing our
objectives with each step, as well as the results we obtained with each.
»
See All Articles by Columnist William E. Pearson, III
» 