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.
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 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).
Illustration 1:
Designating a Time Dimension in the Analysis Services Dimension 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 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.
Illustration 2: Relative
Time Structures in Cognos PowerPlay Transformer
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.