More Sophisticated Relative Time Periods to an Analysis Services Cube
continue 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. We leveraged
an MDX function for this purpose in our previous article to serve as the basis
for a simpler Year-to-Date relative time structure; the inherent
limitation of that structure, we noted, was the fact that it would render the year-to-date
total for only a single, designated measure, albeit at interchangeable points
use the PeriodsToDate() function again, but in a different way, so as
to support relative time structures that will generate aggregates across
both various measures and various points in time, as we shall
see. In addition to the PeriodsToDate() function, we will enlist the
assistance of another MDX function, ParallelPeriod(). My hope is that
exposure to these two functions, within a handful of relative time
period contexts, will impart enough of an understanding of the approach we develop
in this article to enable relatively inexperienced readers to extend the logic
into the creation of any additional relative time structures they might
need in their own environments. We will glimpse the operation of the underlying
functions at the appropriate junctures within our practice example, referencing
articles where I focus more upon the detailed syntax options and provide
examples of the uses for each.
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 which is expected to trim hundreds of
thousands of dollars from the annual IT budget. Because the savings is
expected to defer earlier plans to offshore approximately ten percent of their
development staff to accommodate budget considerations, FoodMart is anxious to
demonstrate that Analysis Services can provide all the functionality that
existed in the previous enterprise BI application. For this reason, among
others, Foodmart 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 initial
requirement for a relative time aggregation was straightforward: in our
previous article, we detailed their wish to be able to generate simple year-to-date
totals for Warehouse Sales. Having been presented with a simple solution
to the initial requirement, the information consumers have done what they often
do: they have returned with a more involved requirement. The consumers tell
us that they wish to have relative time structures to accumulate year-to-date,
quarter-to-date and month-to-date aggregates, but they want to be
able to apply the same structures to all relevant measures in the Warehouse
cube, as they are able to do within the BI application they are replacing.
Furthermore, they want to be able to report upon parallel periods (such
as Prior Year, Prior Quarter and Prior Month) to support
comparative analysis and reporting. As with the cumulative time structures,
they wish to be able to juxtapose the parallel period structures against all
relevant measures and generate accurate totals 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.)
prepare for our relative time periods development efforts by creating a
copy of the underlying data source, so that we can alter it to support
subsequent enhancements to our cube. We will also create a copy of the
existing Foodmart Analysis Services database, to leave the original
undisturbed and fully accessible to current users in the organization. Finally,
we will then create the necessary relative time structures, specifically
for the Warehouse cube, discussing the objects and the syntax we employ
along the way.
the FoodMart 2000 Sample Database
we proceed within Analysis Manager, we will need to create a small table in the
FoodMart 2000 database underlying the Warehouse sample cube, as
well as a "view," (which we will manage via a query), both of which
we will use to illustrate the support of a flexible relative time
dimension. While I typically work within MSSQL Server databases, along with
other large RDBMS', in developing enterprise BI solutions, we will use the FoodMart
sample here, so as to make the steps available to anyone who have installed Analysis
Services and all associated samples. Using existing samples for our exercise,
instead of creating an entire solution from scratch, will save a great deal of
time and effort, and allow us to focus upon the creation of the relative
time dimension that forms the subject of this exercise. The same concepts
apply in MSSQL Server and other enterprise RDBMS': I use them primarily within
these environments in my consulting practice.
we could certainly use the existing database (with the addition of the table
and "view" I have mentioned), I prefer to make these changes in a
copy for use within our practice session. This way, we prevent causing any
issues with other samples that rely upon the existing FoodMart 2000
schema, or disrupting the prospective use of the samples within the context of
procedures in the Books Online and other references that depend upon the
FoodMart 2000 database.
will be working with a copy of the Warehouse cube, within a clone of the
FoodMart 2000 Analysis Services database, when we get to the Analysis
Services level, as well, for much the same set of reasons. The result of using
the copies we make in preparation will be a freestanding, working example of
the technique we discuss to which you can refer later, without any regard for
realignment of original samples, or destruction of the model we create when
doing a restoration of the original structures, say to follow a later article
or procedure that is undertaken with samples in their original states.
the FoodMart 2000.mdb file, via Windows Explorer.
2000 database is typically installed in the Program directory for
Analysis Services, in the Samples folder. An example path, assuming
installation of Analysis Services on the C: drive, would appear as follows:
C:\Program Files\Microsoft Analysis Services\Samples
2000 database appears as shown in Illustration 3.
Illustration 3: Navigate
to FoodMart 2000.mdb in Windows Explorer
from the context menu that appears, as depicted in Illustration 4.
Illustration 4: Copying
the Sample FoodMart 2000 Database
the white space beneath the FoodMart 2000.mdb file.
from the context menu that appears, as shown in Illustration 5.
Illustration 5: Select
NOTE: You can obviously place the file in a more convenient
place, as desired. Simply keep in mind the substituted path within the context
of any references we make to the current path later in our practice procedures.
database file is duplicated.
the new copy of FoodMart 2000.mdb
from the context menu that appears, as depicted in Illustration 6.
Illustration 6: Renaming
the Database ...
cursor appears within the file name in Edit mode.
file name with the following (ensuring the addition of ".mdb"):
database, renamed as above, appears as shown in Illustration 7.
Illustration 7: The
Renamed Sample Database Clone
have an insulated environment within which to perform a few modifications to
support our relative time dimension in the targeted sample cube. Our
next steps will surround these enhancements, as we shall see in the following