Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 2

A More Powerful Approach to Relative Time Periods in an Analysis Services Cube

Overview and Discussion

As we discussed in the last article of this series, one of the questions that I receive 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 working within 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., to supplement our standard (often referred to as "calendar" time) hierarchy.

The Date Wizard in Cognos PowerPlay Transformer creates the vast majority of the Time dimension for us, with perhaps a little remaining effort required to modify the presentation (as well as general behavior) 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).

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 further 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 "Powercube," appears in Illustration 2.

Much discussion has occurred in forums and elsewhere regarding the fact that relative time periods do not automatically appear in the Analysis Services environment. Moreover, as I have already noted, I receive direct questions regularly regarding a means of adding similar structures in Analysis Services. As we noted in our last article, 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. A straightforward approach, which we examined in Relative Time Periods in an Analysis Services Cube, Part I, used time-related MDX functions within calculated members to meet needed relative time capabilities individually for a given measure. As we emphasized in the article, this accomplishes the general purpose, but differs from the Cognos relative time structure in that it does not apply interchangeably to all measures.

The second approach, which we will explore in this article, involves the creation of a special dimension to house the relative time calculations. The end objective 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. This approach is a bit more involved, from a development perspective, than that which we undertook in our last article, where we examined a means of meeting a limited need for a relative time aggregation for a specific measure. The environment within which relative time structures are to be implemented, along with the specific business requirements and other factors, will dictate the appropriate choice between the "lightweight" approach of our previous article, and an approach similar to that which we will develop in this article, or perhaps a hybrid - or entirely different, altogether – approach.

Considerations and Comments

For purposes of this exercise, we will again 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.

As was the case in our last session, 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 exposure, appropriate within the immediate context in which the functions are used. I will provide references to related articles where appropriate, and, as I have done at points within my various series', I encourage any practitioner that intends to work with Analysis Services, or to report from Analysis Services cubes within any real world capacity, to pursue a deeper understanding of MDX.

In this article, we will create a representative set of relative time structures – enough to give a good understanding for the process involved. While this set of structures will meet some of the basic needs common to many organizations, every business environment will be different, and your needs may require a larger and / or more 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 existing Analysis Services cubes.