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

Hands-On Procedure

Adding More Sophisticated Relative Time Periods to an Analysis Services Cube

We will 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 in time.

We will 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.

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

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

We will 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.

Preparation

Clone the FoodMart 2000 Sample Database

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

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

We 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 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

The FoodMart 2000 database appears as shown in Illustration 3.

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.

The database file is duplicated.

The cursor appears within the file name in Edit mode.

RelativeTimeSample.mdb

The new database, renamed as above, appears as shown in Illustration 7.

We now 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 section.