Hands-On Procedure
Basic
Approach: Adding a Relative Time Calculated Member to an Analysis Services Cube
We will
begin our examination of relative time periods in Analysis Services with
a "periods to date" scenario, one of several relative time
structures we have cited as an example in the introduction. The PeriodsToDate()
function, according to the Analysis Services Books Online, "returns
a set of periods (members) from a specified level starting with the first
period and ending with a specified member." Within the scope of a level
that is specified within the function, the function returns a set of
periods that exist on the same level as the member that is also specified in
the expression. In other words, it returns the number of periods within the
specified level, up to and including the specified member.
The PeriodsToDate()
function allows us to meet very common business needs, including the
calculation of a year-to-date total. The calculation of this total
requires accumulation over a range of members of the Time dimension.
This set of Time members is easily assembled using the PeriodsToDate()
function, although other, less direct approaches exist to meet this
requirement.
To set
the scene for our practice session, let's say that a group of information
consumers in the Finance department at a client, the FoodMart organization, has
encountered a need wherein they are seeking our assistance. The company has
embarked on a conversion from an existing enterprise BI system to the
integrated Microsoft BI solution, a move that is expected to trim hundreds of
thousands of dollars from the annual IT budget. Moreover, moving to the
Analysis Services / Reporting Services combination will offer the added benefit
of allowing the organization to defer earlier plans to offshore approximately
ten percent of their development staff to accommodate budget considerations.
FoodMart has evaluated Analysis Services, and has engaged us for the
conversion, based upon our experience with the both Analysis Services and with
the system that it is replacing.
The
consumers have noted that the previous application provided "prefabricated"
relative time structures that do not automatically appear in the Warehouse
cube, their first Analysis Services development effort. Their requirement for
a relative time aggregation is straightforward: they wish to be able to
generate year-to-date totals for Warehouse Sales within the Cube
Browser, and ultimately within the report environment, (they have chosen Reporting
Services to replace the existing high-cost system they currently use).
Let's
jump right into creating a calculated member in Analysis Services to provide a Year-to-Date
value for the designated measure, Warehouse Sales, discussing the syntax
we employ along the way.
1.
Open Analysis
Manager.
2.
Expand the Analysis
Servers folder in the management console.
3.
Expand the Analysis
Server with which you are working by clicking the "+" sign to its
left.
4.
Expand the FoodMart
2000 database.
5.
Expand the Cubes
folder inside the FoodMart 2000 database.
6.
Right-click
the Warehouse cube
7.
Select Edit
... from the context menu that appears, as depicted in Illustration 3.
Illustration 3: Select
Edit from the Context Menu
The Cube
Editor opens.
8.
Right-click
the Calculated Members folder within Cube Editor.
9.
Select New
Calculated Member ... from the context menu that appears, as shown in Illustration
4.
Illustration 4: Select
New Calculated Member from the Context Menu
The Calculated
Member Builder opens.
10.
Type the
following into the Member name box:
YTD Warehouse Sales
While
the name of the calculated member can obviously be anything that is useful in
the environment in which we are creating it, we need to make the name intuitive
to the users. We used the term "YTD" here simply because that
was the name employed in the previous application.
11.
Type the
following MDX into the Value Expression section of the Calculated
Member Builder:
Sum(PeriodsToDate([Time].[Year]),[Measures].[Warehouse Sales])
Let's examine the syntax in the MDX PeriodsToDate()
function, which we employ in the Value expression section of the Calculated Member Builder above, together with, and within the
context of, the Sum statement, to get an understanding of how it helps us to aggregate a
measure over time.
The PeriodsToDate() function consists of the
following:
PeriodsToDate([<<Level>>[, <<Member>>]])
PeriodsToDate() returns a set of periods, which are members
within a hierarchical level in the Time dimension, beginning with the
first period within that level and ending with the member that we specify. Simple
examples of its use appear in Table 1.
|
Expression
|
Returned Set
|
PeriodsToDate([Time].[Quarter],
[Time].[Day].[12-Dec-1997])
|
The days
from the beginning of Quarter 4 to December 12, 1997.
|
PeriodsToDate([Time].[Year], [Time].[Month].[March])
|
January,
February, March
|
PeriodsToDate([Time].[Year])
- level specified, but no member specified
|
The members
from the beginning of the year that is the ancestor of Time.CurrentMember,
through Time.CurrentMember.
|
PeriodsToDate() - no level or member specified
|
The set
of members from the beginning of the level containing the period of Time.CurrentMember
to Time.CurrentMember. All the returned members are at the same level
as Time.CurrentMember.
|
Table 1: Basic Examples of PeriodsToDate() Expressions
and the Respective Sets Returned
The <<Level>> supplies the scope of the function (in
our example it is [Time].[Year], or the Year level of the Time
dimension). Because the function we constructed for our calculated member
above has a level specified, but no member (like the third
expression from the top in Table 1 above), <<Member>> becomes [Time].CurrentMember
(an unspecified member assumes the same dimension as that of <<Level>>.)
» 
Comment and Contribute
