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.
» 
Comment and Contribute
