Replicate the prefabricated relative time periods found
in Cognos PowerPlay Transformer and other OLAP Design and Reporting tools. In
this article, MSAS and Cognos Architect Bill Pearson shows how to provide
relative time reporting capabilities, such as year-to-date, from an Analysis
About the Series ...
article is a member of the series Introduction to MSSQL Server Analysis
Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each
installment progressively adding features and techniques designed to meet
specific real - world needs. For more information on the series, as well as
the hardware / software requirements to prepare for the exercises we
will undertake, please see my initial article, Creating Our First Cube.
Note: Current Service Pack updates are assumed for MSSQL Server 2000, MSSQL
Server 2000 Analysis Services, and the related Books Online
and Samples. Images are from a Windows 2003 Server
environment, upon which I have also implemented MS Office 2003, but the
steps performed in the articles, together with the views that result, will be
quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis
Services" or "MSAS"). The same is generally true,
except where differences are specifically noted, when MS Office 2000 and
above are used in the environment, in cases where MS Office components
are presented in the article.
this article, we will examine the design and creation, within Analysis
services, of relative time periods, a popular feature that can be
generated automatically or manually for reporting in the Cognos PowerPlay application,
within its cube design component, Cognos PowerPlay Transformer, as well
as other popular enterprise BI applications. A common request among scores of
e-mails and calls I receive, centering upon the replication, in Analysis
Services, of features found within popular enterprise BI applications, is for
assistance in setting up these time periods, examples of which include current "period,"
(meaning month, quarter, year, or other levels of the Time / Date
dimension), prior period, period to date, and others. In this article, we will
examine the creation such a time grouping in
Analysis Services, which we can later put to use in the reporting component of
the Microsoft integrated BI solution, Reporting Services, much as we
would report from a cube created in Cognos PowerPlay Transformer using Cognos
PowerPlay as the reporting application.
In this, the first half of a two-part discussion surrounding relative
time periods in an Analysis Services cube, we will overview the
capabilities found in Cognos PowerPlay Transformer and other enterprise
cube design applications, and then:
simple approach to meeting the requirement for relative time periods;
differences between our approach and the Cognos PowerPlay Transformer
practice exercise, whereby we add relative time capabilities with this
adequacy of our solution by demonstrating the use of the new capabilities from
the perspective of the Cube Browser in Analysis Manager;
Look forward to
a more sophisticated solution we will propose and build in Part II, to
more closely replicate the functionality in Cognos PowerPlay Transformer
and make the solution even more user friendly for reporting specialists and
About the Mastering Enterprise
BI Articles ...
Having worked with Cognos PowerPlay, together with its
cube design component, Cognos PowerPlay Transformer, for over ten years (along
with numerous other enterprise BI applications), I have come to appreciate the
leadership roles Cognos has played in the evolution of OLAP and enterprise
reporting. As I have stated repeatedly, however, I became convinced, from their
earliest appearance, that the components of the Microsoft integrated BI
solution (including MSSQL Server, Analysis Services, and Reporting Services)
will commoditize business intelligence. It is therefore easy to see why a
natural area of specialization for me has become the conversion of Cognos
enterprise BI to the Microsoft solution.
been impressed from my first exposure to each member of this exciting group of
integrated applications, my certainty in the destiny of the Microsoft BI
solution grows stronger by the day, as I convert formerly dominant enterprise
Business Intelligence systems, such as Cognos, Business Objects, Crystal, and
others, to the Reporting Services architecture. I receive constant requests to
conduct strategy sessions about these conversions with large organizations in a
diverse range of industries - the interest grows daily as awareness of the
solution becomes pervasive. Indeed, the five-to-six-plus figures that many can
shave from their annual IT budgets represent a compelling sweetener to
examining this incredible toolset.
purpose of the Mastering Enterprise BI subset of my Introduction to
MSSQL Server Analysis Services series is to focus on techniques for implementing features in
Analysis Services that parallel those found in the more "mature"
enterprise OLAP packages. In
many cases, which I try to outline in my articles at appropriate junctures, the
functionality of the OLAP solutions of well-established, but expensive,
packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay,
can be met - often exceeded - in most respects by Analysis Services / Reporting
Services - at a tiny fraction of the cost. To this
end, I welcome input from readers who are performing comparisons between the
two vendors, or are perhaps undertaking or considering evaluations of Analysis
Services / Reporting Services against their Cognos counterparts. I look
forward to opportunities to show, in future articles, ways that the same
capabilities can be accomplished in the Microsoft solution.
vacuum of documentation comparing components of the Microsoft BI solution to their
counterparts among the dominant enterprise BI vendors, to date, represents a
serious "undersell" of both Analysis Services and Reporting Services,
particularly from an OLAP reporting perspective. I hope to contribute to
making this arena more accessible to everyone, and to share my implementation
and conversion experiences as the series evolves. In the meantime, rest
assured that the ease of replicating popular enterprise BI features OLAP in
Analysis Services will be yet another reason that the Microsoft solution "commoditizes"
Relative Time Periods in an Analysis Services Cube
Overview and Discussion
Since my earliest work with Analysis Services, I have
received myriad e-mails and calls asking how to replicate features in existing
enterprise BI solutions (mostly Cognos, because of my history with its
products, and the simple fact of its huge BI market share, but also from the
perspective of many other solutions, including Business Objects, Crystal
Reports / Crystal Analysis, MicroStrategy, and others) within
the components of the integrated Microsoft solution, or within a given
component of that solution. One of the questions that I get constantly is "how
can I replicate the relative time periods that are automatically
created via PowerPlay Transformer's Date Wizard (and which can, of
course, be manually created as well)?"
As most of us in the BI (and peripheral) arenas - be it as an
architect, designer / developer, or information consumer - are aware, the Time
/ Date dimension in an OLAP solution consists of levels typically
made up of Year, Quarter and Month (less commonly
continuing, as well, to Week, Day, sometimes even Hour, Minute,
etc., levels - I've seen lots of possibilities). In Cognos PowerPlay
Transformer, the Date Wizard assists in the building of the Time /
Date dimension by prompting us for the levels we need, and then creating
those levels and their members from the date field we designate as the source
of the date information that we wish to capture within our cube structures. In
addition to creating this basic dimension structure, we can create alternative
time hierarchies, such as fiscal, etc., in addition to our standard (often referred
to as "calendar" time) hierarchy.
NOTE: In this article, we will focus
on a single, standard Time / Date dimension. For more
information on setting up an alternative Time / Date hierarchy
within Analysis Services, see my article Handling
Time Dimensions within this Database Journal series.
Date Wizard in Cognos PowerPlay Transformer creates the vast
majority of the Time dimension for us, with perhaps a little remaining
effort due upon us to modify the presentation of members at the various date
levels. This is similarly accomplished within Analysis Services when we
specify, within the Dimension Wizard that we are creating a Time
dimension, as depicted in Illustration 1).
Designating a Time Dimension in the Analysis Services Date Wizard
addition to creating the basic dimensional structure (the levels and "categories,"
or members of the levels, of the dimension) for the Time / Date
dimension, Cognos PowerPlay Transformer can go a step further and create
several "relative time categories." Just one of many advanced
multidimensional modeling options within Transformer, relative time
data, such as year over year, quarter-to-date and year-to-date, and others,
allow us to leverage today's data warehouses / marts and perform dramatically
powerful analysis. A sample of the relative time structures that are
easily generated in Transformer, as seen from within the Dimension
Diagram for the Time dimension of a sample cube, appears in Illustration 2.
Illustration 2: Relative
Time Structures in Cognos PowerPlay Transformer
ink has been spilt (to use a rather anachronistic cliché) in forums and
elsewhere regarding the fact that relative time periods do not
automatically appear in the Analysis Services environment - after all, we told
the wizard we wanted to create a time dimension! Why didn't it create the relative
time categories that it should have "known" we needed? One might
ask how the wizard is supposed to know what relative time periods might
be important to us, but aside from that, suffice it to say that we have to
create the structures manually. The good news is that we can have precisely
what we want (and nothing more) in this way, with the fringe benefit of
learning a bit more about the application in the process.
fact is that we can indeed create relative time periods in Analysis
Services. This can be accomplished in at least a couple of ways, depending
upon the need for which a given cube is being designed. The first approach,
which we will examine in this article, uses time-related MDX functions within calculated
members to meet needed relative time capabilities individually for a
given measure. As we shall see, this accomplishes the purpose, but differs
from the Cognos relative time structure in that it does not apply to all
second approach, which I will expose in my next article, involves the creation
of a special dimension to house the relative time calculations. While
this is a bit more involved from a development perspective, the end result is
that reporting specialists, and other "end users" of the cube, will
find that the relative time structures parallel those found in the Cognos
PowerPlay Transformer rendition.
Considerations and Comments
For purposes of this
exercise, we will be working with the Warehouse cube, within the FoodMart
2000 Analysis Services database. The Warehouse cube, as most of us
are aware, is a member of a group of working samples that accompanies a typical
installation of Analysis Services. If the samples are not installed in,
or have been removed from, your environment, they can be obtained from the
installation CD, from the Analysis Services section of the Microsoft
website, and perhaps elsewhere.
We will be discussing
MDX throughout the article, while performing most of our exercises within
Analysis Manager. The MDX functions we encounter in this article are explored
in depth in my MDX Essentials series, so we will limit our exploration
of the functions themselves to a brief overview, and to considerations that
exist within the immediate context in which the functions are used. I will try
to provide references to related articles where appropriate, and I encourage
you to pursue a deeper understanding of MDX, especially if you intend to work
with Analysis Services or report from Analysis Services cubes within any real
In this pair of
articles, we will create representative relative time structures -
enough to give a good understanding for the process involved. Every business
environment will be different, and your needs may require an elaborate set of
these structures. Our point here is to illustrate how Analysis Services can
match the capabilities that can be constructed within Cognos PowerPlay
Transformer to support, at least within this consideration, a more "apples
to apples" comparison of the products, as well as to help "jump start"
practitioners who, like the scores of readers worldwide who have E-mailed me
with questions regarding the setup of relative time periods, need a helping
hand in adding this functionality to their cubes.
Approach: Adding a Relative Time Calculated Member to an Analysis Services Cube
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.
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
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.
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).
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.
Expand the Analysis
Servers folder in the management console.
Expand the Analysis
Server with which you are working by clicking the "+" sign to its
Expand the FoodMart
Expand the Cubes
folder inside the FoodMart 2000 database.
the Warehouse cube
... from the context menu that appears, as depicted in Illustration 3.
Illustration 3: Select
Edit from the Context Menu
the Calculated Members folder within Cube Editor.
Calculated Member ... from the context menu that appears, as shown in Illustration
Illustration 4: Select
New Calculated Member from the Context Menu
Member Builder opens.
following into the Member name box:
YTD Warehouse Sales
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.
following MDX into the Value Expression section of the Calculated
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
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.
Table 1: Basic Examples of PeriodsToDate() Expressions
and the Respective Sets Returned
from the beginning of Quarter 4 to December 12, 1997.
- level specified, but no member specified
from the beginning of the year that is the ancestor of Time.CurrentMember,
PeriodsToDate() - no level or member specified
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
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>>.)
The PeriodsToDate() component of our calculated
member's expression, PeriodsToDate([Time].[Year]), is therefore
specifying the set of all periods from the beginning of the year to the
current period. This will allow the calculation to be applied at various
points in time, and, based upon the current period, to supply the set of
"all periods through that period."
The YTD Warehouse
Sales measure is being defined, via the Sum() function, as the "sum
of a numeric expression evaluated over a set." The syntax of the Sum()
function is relatively straightforward, and can be represented as follows:
Sum((<<Set>>[, <<Numeric Expression>>]))
Sales measure inhabits the Numeric Expression portion of the Sum()
function. The Set portion of the Sum() function consists
of PeriodsToDate([Time].[Year]) within the syntax we have constructed
inside the calculated member. Its effect is to ask for a set of members
to be returned - a set of members that we can use in the Sum() function
as a basis for accumulation. The end result is that we sum the Warehouse
Sales from each of the periods, from the beginning of the Year
to the current period (ergo Year-to-Date Warehouse Sales).
Member Builder appears with our input as depicted in Illustration 5.
Calculated Member Builder with Complete MDX Expression (Compressed View)
Click the Check
button to perform a syntax check.
message box appears, as shown in Illustration 6, informing us that the
syntax is acceptable.
Illustration 6: Testing
Positive for Syntax Correctness
Click OK to
close the Calculated Member Builder, and to save our new calculated
new Calculated Member appears in the tree within the Calculated
Members folder, as depicted in Illustration 7.
Illustration 7: The New
Calculated Member in the Calculated Members Folder
NOTE: This view may not be the same as your own, depending upon
differing activities that have been conducted with the Warehouse cube in
another matter, although the actions we have performed to this point alone do
not require it, let's process the cube to make sure we are all in a similar "processed"
--> Process Cube to process the Sales cube.
on the Save the Cube dialog that appears next, as shown in Illustration
Illustration 8: Click "Yes"
to Save the Cube
on the dialog that appears next, as depicted in Illustration 9; we will
not design aggregations at present.
Illustration 9: Click "No"
to Designing Aggregations
a Cube dialog appears, as shown in Illustration 10, where we want the processing
method set to Full Process. (It may be the only option, depending upon
the status of the cube at this stage). Full processing for the Warehouse
cube will be relatively quick, so we will perform it to ensure that all is
Illustration 10: Full
Process Selected in the Process a Cube Dialog
the Full Process radio button is selected on the Process a Cube
to begin processing.
begins. The Process viewer displays various logged events, then presents
a green Processing completed successfully message, as depicted in Illustration 11.
Indication of Successful Processing Appears (Compact View)
to dismiss the viewer.
Click the Data
tab in the Cube Editor, if necessary.
Verification and Use
With data appearing in the Data view, we will take this opportunity to verify the proper operation of our new calculated member, before handing it over with instructions for use to the intended audience.
1. Position Measures as the column axis, if necessary.
2. Position the Warehouse dimension as the row axis.
3. Drill down to display the U.S. Warehouse States.
The Data view, with our arrangements, appears as shown in Illustration 12.
We are able to see the values that appear for all measures, with the Warehouse Sales measure, and the YTD Warehouse Sales calculated member / measure circled.
4. Drag the Time dimension to the immediate right of the newly placed Warehouse dimension columns, to effect a "crossjoin." When the cursor appears as depicted in Illustration 13, drop the Time dimension.
Illustration 13: Cursor Indicates Appropriate Drop Point
The final arrangement should appear as partially shown in Illustration 14.
Illustration 14: Arrangement in the Data View - Partial Row Axis
5. Double-click the Year column heading to drill down to the member quarters of years 1997 and 1998.
6. Double-click the Quarter column heading to drill down to the member months of each.
7. Scroll to focus upon the Warehouse Sales measure and the new YTD Warehouse Sales calculated measure, juxtaposed against California warehouses (CA) and 1998 in the row axis, as depicted in Illustration 15.
NOTE: I have removed the measures columns, between the Warehouse Sales measure and the YTD Warehouse Sales calculated measure, to display the measures side-by-side in a more compact presentation.
Illustration 15: Arrangement in the Data View - Partial, Composite View
We can readily see that our year-to-date calculation appears to be effective. The YTD Warehouse Sales value is not only incremented for the Warehouse Sales each month, but the same effect is obtained at rollup levels within the Time dimension.
8. Right-click the Quarter column heading, once more.
9. Select Drill Up from the context menu that appears, as shown in Illustration 16.
Illustration 16: Drilling Up to the Quarter Level
The display contracts, hiding the member months. We can see, at the Quarter level, too, that the YTD Warehouse Sales value is incremented for the Warehouse Sales each quarter, just as it was for each month, as (compactly) depicted in Illustration 17.
Illustration 17: Arrangement in the Data View - Partial, Composite View
Thus, we verify that the YTD Warehouse Sales calculated member performs as intended. We can use the calculated member in a report or through any other mechanism for querying the cube, to generate a year-to-date total, at a given point in time, for the Warehouse Sales measure.
10. Select File --> Exit to close the Cube Editor, saving as requested.
11. Exit Analysis Services, as desired.
In our next article, we will evolve our examination of relative time periods a step further, and expose an approach to supply the same capabilities, except within a structure that allows us to juxtapose the calculation with any appropriate measure in our cube. This will contrast with the method we have examined in this article, where we presented a solution that would imply the creation of a "to date" calculated member for each measure, as well as for the "to -date" period (month, quarter, year, etc.) under examination. While our next approach will present slightly more complication in setup, it will afford us relative time capabilities that more closely resemble those that are created as a byproduct, as we have mentioned, of using the Date wizard in Cognos PowerPlay Transformer.
In this article, we introduced relative time periods, discussing their general importance in analysis and reporting. We described how dominant enterprise business intelligence vendor Cognos has provided easy-to-use relative time structures within the Cognos PowerPlay Transformer application for reporting in Cognos PowerPlay. Our primary focus, in response to a request that I receive on a recurring basis, was to address options afforded within Analysis Services for replicating similar capabilities.
In this article, we began with a straightforward approach to meeting the requirement for relative time periods, highlighting differences in operation inherent in the use of a calculated member to achieve the capabilities offered in Cognos PowerPlay Transformer. We then performed a practice exercise whereby we added an example relative time structure using this straightforward method. As a part of constructing a Year-to-Date calculated member for a given measure within our sample cube, we discussed the manner in which the combined PeriodsToDate() and Sum() MDX functions could be used to support our relative time period.
We verified the adequacy of our solution through the use of the Cube Browser in the Analysis Services Cube Editor, discussing the use of the new calculated member in browses of the cube, reports and other queries. We demonstrated that the calculated member operates in a "contextually sensitive" way, from the perspective of the time dimension levels / other dimensional intersects at which it is employed, leveraging the power of OLAP beyond the capabilities of a simple fixed calculation.
Finally, we looked forward to a more sophisticated solution we will propose and build in Part II, to more closely replicate the functionality in Cognos PowerPlay Transformer and to provide a solution even more user friendly for reporting specialists and information consumers.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.