Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube

Monday Jun 13th 2005 by William Pearson
Share:

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 Services cube.

About the Series ...

This 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.

Introduction

In 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:

  • Discuss a simple approach to meeting the requirement for relative time periods;
  • Highlight the differences between our approach and the Cognos PowerPlay Transformer capabilities;
  • Perform a practice exercise, whereby we add relative time capabilities with this straightforward method;
  • Verify 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 information consumers.

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.

Having 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.

The 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.

The 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" Business Intelligence.

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.

The 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).


Illustration 1: Designating a Time Dimension in the Analysis Services Date Wizard

In 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

Much 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.

The 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 measures.

The 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 world capacity.

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.

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>>.)

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>>]))

The Warehouse 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).

NOTE: For more information on the PeriodsToDate() function, see my article MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions in the MDX Essentials series at Database Journal. The article details "shorthand" versions of the PeriodsToDate() function, which include a "YTD" version.

The Calculated Member Builder appears with our input as depicted in Illustration 5.


Illustration 5: Calculated Member Builder with Complete MDX Expression (Compressed View)

12.  Click the Check button to perform a syntax check.

A message box appears, as shown in Illustration 6, informing us that the syntax is acceptable.


Illustration 6: Testing Positive for Syntax Correctness

13.  Click OK to close the Calculated Member Builder, and to save our new calculated member.

The 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 your environment.

As 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" state.

14.  Select Tools --> Process Cube to process the Sales cube.

15.  Click Yes on the Save the Cube dialog that appears next, as shown in Illustration 8.


Illustration 8: Click "Yes" to Save the Cube

16.  Click No 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

The Process 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 refreshed.


Illustration 10: Full Process Selected in the Process a Cube Dialog

17.  Ensure that the Full Process radio button is selected on the Process a Cube dialog.

18.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as depicted in Illustration 11.


Illustration 11: Indication of Successful Processing Appears (Compact View)

19.  Click Close to dismiss the viewer.

20.  Click the Data tab in the Cube Editor, if necessary.

Cube data is retrieved.

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.

Click for larger image

Illustration 12: The Data View with Specified Arrangements - Compressed View

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.

Conclusion

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved