MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions - Page 3

July 8, 2003

Practice

Let's begin the hands-on portion of the lesson by creating a calculated member to reinforce our understanding of the basics we have covered so far, and by using the PeriodsToDate() function in a manner that assists our ends. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will, as usual, be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.             Start the MDX Sample Application.

2.             Clear the top area (the Query pane) of any queries or remnants that might appear.

3.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5.             Select the HR cube in the Cube drop-down list box.

We will begin with an illustration that creates a calculated member and uses the PeriodsToDate() function in a manner similar to the example we cited earlier, but with a difference in time levels to enrich the example: our query will focus on quarter-to-date organization salaries, presented on a monthly basis, to present a result set comprising the two years stored in the HR sample cube.

First, let's run a simple query to gain an understanding of our data. We will ascertain the values for monthly total salaries values first, and build an inherent explanation into our ultimate objective by approaching the logic we reviewed above, this time from a perspective of construction.

5.             Type the following query into the Query pane:

```-- MDX09-1:  Tutorial Query No. 1
SELECT
{[Measures].[Org Salary]} ON COLUMNS,
[Time].[Month].Members ON ROWS
FROM HR
```

Analysis Services fills the Results pane, presenting the dataset depicted in Illustration 3.

Illustration 3: Result Dataset - Monthly Total Salaries

The query delivers the results that we requested; we see the total organization salary amounts returned for each month. Keep these amounts in mind as we progress.

We are asked to produce values for Quarter-to-Date Salaries for each month, accumulating the totals for each month from the beginning of each quarter. The information consumers requesting this information want the quarter-to-date cumulative salaries to appear in a column to the right of the month salary totals. Let's set about meeting this business need with a query containing the Sum statement and other components that we have already previewed.

6.             Type the following query into the Query pane:

```-- MDX09-2:  Tutorial Query No. 2
WITH
MEMBER [Measures].[QTD Salaries] AS
'Sum(PeriodsToDate([Time].[Quarter]),[Org Salary])'
SELECT
{[Measures].[Org Salary] , [Measures].[QTD Salaries]} ON COLUMNS,
[Time].[Month].Members ON ROWS
FROM HR
```

7.             Click the Run button on the toolbar atop the Sample Application, to execute the query.

As soon as the specified cells in the Results pane are filled by Analysis Services, we see the result dataset depicted in Illustration 4.

Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the total monthly salaries (Org Salary) amounts aligned side by side with the cumulative quarter-to-date totals (QTD Salaries). Note that the quarter-to-date totals "reset" every three months, beginning with the total of the first month and accumulating thereafter until the next quarter begins. The PeriodsToDate () function was designed to provide this highly popular result for business users.

To restate our request in words, we are asking, within the QTD Salaries calculated measure, for the sum of Org Salaries, returned within the scope of quarters, for the set of periods on the level of quarters. When we request that the results be presented in terms of months, as we do in the SELECT clause of the query, the result is an accumulation that states the total for each quarter (the scope of the PeriodsToDate() function) within the context of each succeeding month period. Since we specify a level in our function, ([Time].[Quarter]), the function returns the set of periods on the level of quarters, beginning with the first quarter Q1 (the first member of the quarter level) and ending with [Time].[Quarter].CurrentMember (because we didn't specify a member in the function), or the quarter in which the monthly total is displayed.

Note: For a discussion of the .CurrentMember function, see my earlier articles on the members functions within this series, as well as other series' articles I have published within the Database Journal library.

If it is desirable to save this or subsequent queries, we can do so by selecting File -> Save As, and give the file a meaningful name and location.

Now let's take a look at the "shortcut functions" that derive from the PeriodsToDate() function.