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

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.

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

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.

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.