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

Monday Jul 11th 2005 by William Pearson
Share:

Discover a more sophisticated approach to the replication of the prefabricated relative time periods found in Cognos PowerPlay Transformer. MSAS and Cognos Architect Bill Pearson continues his examination of ways to replicate Cognos relative time structures in Analysis Services.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Current Service Pack updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, within which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for over ten years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I became convinced, from their earliest appearance, that the components of the Microsoft integrated BI solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos enterprise BI to the Microsoft solution. In addition to converting formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves – and, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft solution will commoditize Business Intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.

Introduction

In this article, we will continue our examination of the design and creation, within Analysis services, of relative time periods. As we stated in our previous article, Relative Time Periods in an Analysis Services Cube, Part I, a popular feature found in Cognos PowerPlay Transformer, is a set of relative time periods, which can be generated automatically or manually for reporting in the Cognos PowerPlay application. Because of the vast market share currently possessed by Cognos in the business intelligence space, Cognos PowerPlay Transformer (the cube design component) and Cognos PowerPlay (the OLAP reporting component) are pervasive in industry. The debut of the Microsoft integrated BI solution, including MSSQL Server, MSSQL Server Analysis Services, and, most recently, Reporting Services, has driven a high level of interest in adopting the solution, in part or in whole, and a resulting demand to evaluate the tool set against the leading vendors in the BI arena, including Cognos.

It is in this rapidly moving environment of change that I receive many requests centering upon the replication, within the Analysis Services / Reporting Services combination, of features found within popular enterprise BI applications. Because the relative time periods are so popular, I constantly receive requests for assistance in setting up a similar functionality in Analysis Services, examples of which include current "period," (meaning month, quarter, year, or other levels of the Time / Date dimension), prior period, period to date, and others. An increasing number of these requests have begun to originate from organizations which have already converted from Cognos, among other enterprise BI leaders, and who are seeking to replicated functionality they enjoyed prior to converting.

In our last article, we introduced relative time periods, discussing their general importance in analysis and reporting. We described how dominant enterprise business intelligence vendor Cognos has provided these easy-to-use relative time structures within the Cognos PowerPlay Transformer application for reporting in Cognos PowerPlay. After discussing the frequent request for replicating similar capabilities, we began with a straightforward approach to meeting the requirement for relative time periods, highlighting differences in operation inherent in the use of a calculated member to achieve the capabilities offered in Cognos PowerPlay Transformer.

We then continued our exploration of this simple approach, within a practice exercise whereby we added an example relative time structure. As a part of constructing a Year-to-Date calculated member for a given measure within our sample cube, we discussed the manner in which the combined PeriodsToDate() and Sum() MDX functions could be used to support our relative time period. We then verified the adequacy of our solution through the use of the Cube Browser in the Analysis Services Cube Editor, discussing the use of the new calculated member in browses of the cube, reports and other queries. We demonstrated that the calculated member operates in a "contextually sensitive" way, from the perspective of the time dimension levels at which it is employed, leveraging the power of OLAP beyond the capabilities of a simple fixed calculation.

In this article, we will develop a somewhat more sophisticated approach that closely replicates relative time period functionality in Cognos PowerPlay Transformer, and which thereby provides a solution even more user friendly for reporting specialists and information consumers. Having examined the creation of such time aggregations in Analysis Services, we will later take a look at putting that solution into use in the reporting component of the Microsoft integrated BI solution, Reporting Services, much as we would use Cognos PowerPlay as the application to report from a cube created in Cognos PowerPlay Transformer.

In this, the second half of a two-part discussion surrounding relative time periods in an Analysis Services cube, we will briefly re-examine the capabilities found in Cognos PowerPlay Transformer and other enterprise cube design applications, and then::

  • Discuss a more sophisticated approach to meeting the requirement for relative time periods;
  • Highlight the differences between our expanded approach and the simple approach we examined in our previous article, where we created a standalone calculated member to meet a narrower need;
  • Perform a practice exercise, whereby we add relative time capabilities with this more evolved method, creating the structural members in Analysis Manager that we require to implement the solution;
  • Verify adequacy of our solution by demonstrating the use of the new capabilities from the perspective of the Cube Browser in Analysis Manager;
  • Generally discuss the use of our new structures in the ultimate reporting application. We will also look forward to a subsequent article where we employ the relative time structures that we create in this article within Reporting Services. This will effectively demonstrate that the functionality provided by Cognos PowerPlay Transformer to Cognos PowerPlay can be duplicated within the Microsoft BI solution.

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.

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.

1.  Navigate to the FoodMart 2000.mdb file, via Windows Explorer.

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.


Illustration 3: Navigate to FoodMart 2000.mdb in Windows Explorer

2.  Right-click FoodMart 2000.mdb.

3.  Select Copy from the context menu that appears, as depicted in Illustration 4.


Illustration 4: Copying the Sample FoodMart 2000 Database

4.  Right-click the white space beneath the FoodMart 2000.mdb file.

5.  Select Paste from the context menu that appears, as shown in Illustration 5.


Illustration 5: Select Paste ...

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.

6.  Right-click the new copy of FoodMart 2000.mdb

7.  Select Rename from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Renaming the Database ...

The cursor appears within the file name in Edit mode.

8.  Replace the file name with the following (ensuring the addition of ".mdb"):

RelativeTimeSample.mdb

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


Illustration 7: The Renamed Sample Database Clone

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.

Modify the Database to Support a Relative Time Dimension in the Cube

Let's create a couple of objects within our new MS Access database to support relative time periods within the sample cube we will use later in our exercises. Again, we are doing this in MS Access to leverage existing samples and save time – the concepts are similar, although methods vary somewhat, in the more robust MSSQL Server environment.

1.  Open the RelativeTimesample.mdb in MS Access (I'm using Access 2003 in my illustrations, but Access 2000 and beyond will work, and will be very similar in operation).

2.  Get to the Database View for the database.

3.  Click the New button atop the view, as depicted in Illustration 8.

Click for larger image

Illustration 8: Adding a Table in the Database View – FoodMart 2000.mdb

4.  Select Design View from the New Table dialog that appears, as shown in Illustration 9.


Illustration 9: New Table Dialog: Select Design View

5.  Click OK.

Design View for the new table opens.

6.  Type the information presented in Table 1 into the designated places in the Design View.

Field Name

Data Type

relativetime_id

Number

relativetime_member

Text


Table 1: Creating a New Table - Design View

Design View for the new table appears as partially depicted in Illustration 10.


Illustration 10: The New Table in Design View (Partial View)

7.  Select View --> Datasheet View from the main menu, as shown in Illustration 11.


Illustration 11: Shift to Datasheet View

A dialog appears, stating that we must save the table first.

8.  Click Yes to save the table, as depicted in Illustration 12.


Illustration 12: Notice to Save the Table First ...

9.  Type the following into the Save As dialog that next appears:

relativetime

The completed Save As dialog for the new table appears, as shown in Illustration 13.


Illustration 13: Saving the New Table in the Sample Database

10.  Click OK to save the table and close the Save As dialog.

11.  Click No on the next dialog to appear, to pass on creating a Primary Key in this practice instance, as depicted in Illustration 14.


Illustration 14: Just Say "No" to Creating a Primary Key ...

The table opens in Datasheet View.

12.  Type the information assembled in Table 2 into the designated columns of the table within Table View.

relativetime_id

relativetime_member

1

Current

0

Table 2: Inserting Data into the Table - Datasheet View

NOTE: The "0" appears in the second row when we type the "1" into the first row.

Datasheet View for the table appears as shown in Illustration 15.


Illustration 15: The New Table in Datasheet View (Partial View)

13.  Select File --> Close from the main menu to close the table and return to Data View.

The new relativetime table appears among the other tables, in Data View for the database, as depicted in Illustration 16.


Illustration 16: The New Table Appears in Database View

We will next create views, simply to establish a common key within each of our fact tables, so as to leverage our new table, as we shall see. Because the Warehouse sample cube is drawing upon "facts for two years" (with the years, in effect, serving as the basis for partitioning of the cube, we will create a couple of views where we might only create one in a simpler cube. Rather than attempt to rework the cube to make its structure more basic in this regard, we will simply create two views. Keep this in mind in your own environment as you go forward in attempting to tailor the foregoing steps to a real world scenario, where this step will likely be unnecessary, as it is a mere expedient for the practice example, and has little to do with the conceptual scope of our session.

14.  Click the Queries button in the Objects pane of the Database View.

15.  Double-click Create query in Design view, as shown in Illustration 17.

Click for larger image

Illustration 17: Beginning the Creation of Views ...

The Design View opens, together with the Show Table dialog.

16.  Select the inventory_fact_1997 table, as depicted in Illustration 18.


Illustration 18: Beginning the Creation of Views ...

17.  Click Add to include the table in the upper portion of the Design View.

18.  Click Close to close the Show Table dialog.

19.  Select View --> SQL View from the main menu, as shown in Illustration 19.


Illustration 19: Shift to SQL View ...

Design View becomes SQL View, where we see a "template" query string automatically appear.

20.  Type (or cut and paste) the following query into the query pane, replacing the existing string:


SELECT 
     1 AS relativetime_id,
     inventory_fact_1997.product_id,
     inventory_fact_1997.time_id, 
     inventory_fact_1997.warehouse_id, 
     inventory_fact_1997.store_id,
     inventory_fact_1997.units_ordered,
     inventory_fact_1997.units_shipped,
     inventory_fact_1997.warehouse_sales,
     inventory_fact_1997.warehouse_cost,
     inventory_fact_1997.supply_time,
     inventory_fact_1997.store_invoice

FROM 
    inventory_fact_1997;

The query pane appears, with our input, as depicted in Illustration 20.


Illustration 20: Query to Generate View to Support Relative Time

21.  Click the Run (!) button in the toolbar to execute the query.

The query runs, and returns the data, simultaneously shifting to Datasheet View. A partial representation of the results appears in Illustration 21.


Illustration 21: Partial Representation of the New View

We will see, as we move into the Analysis Services portion of our practice session, the importance of the creation of the relativetime_id key here.

22.  Select File --> Close.

23.  Click Yes when prompted to save the query.

24.  Type the following into the Save As dialog that next appears:

vW_inventory_fact_1997

The Save As dialog appears, with our input, as depicted in Illustration 22.


Illustration 22: Saving the New View

25.  Click OK to save the view and close the dialog.

We return to the Database View, where we see our new view in place. We now need to repeat several of the steps above to create an identical view based upon the inventory_fact_1998 table. As I mentioned earlier, this is only to streamline our practice session, going forward, rather than restructuring the Warehouse cube, which happens to be partitioned on operating years. This would not be required / appropriate in a real world scenario where the relative time structures would ideally be constructed from the beginning, particularly before cube storage was designed and so forth.

As we shall see, the view itself is key to the process. (For that matter, I generally use views underneath my Analysis Services structures, albeit in a major RDBMS, as this practice offers many benefits, not the least of which is flexibility in design changes with minimal impact on both the underlying data source and the cube structure. A change such as the above to an existing, already aligned view would allow a simple change to the view itself, with subsequent refreshment of the data source (with some possible realignment) in Analysis Services. In the absence of a view between the data source tables and the Analysis Services layer, we would be forced to manipulate the schema of the data source, before performing subsequent re-alignments in Analysis Services.)

26.  Double-click Create query in Design view, once again.

The Design View opens, together with the Show Table dialog.

27.  Select the inventory_fact_1998 table in the Show Table dialog that appears.

28.  Click Add to include the table in the upper portion of the Design View.

29.  Click Close to close the Show Table dialog.

30.  Select View --> SQL View from the main menu, as we did earlier.

Design View becomes SQL View, where we see a "template" query string automatically appear.

31.  Replace the existing "template" query string that appears in the query pane, as before, this time with the following query:


SELECT 
     1 AS relativetime_id,
     inventory_fact_1998.product_id,
     inventory_fact_1998.time_id, 
     inventory_fact_1998.warehouse_id, 
     inventory_fact_1998.store_id,
     inventory_fact_1998.units_ordered,
     inventory_fact_1998.units_shipped,
     inventory_fact_1998.warehouse_sales,
     inventory_fact_1998.warehouse_cost,
     inventory_fact_1998.supply_time,
     inventory_fact_1998.store_invoice

FROM 
    inventory_fact_1998;

32.  Click the Run (!) button in the toolbar to execute the query.

The query runs, and returns the data, simultaneously shifting to Datasheet View, in a manner similar to that we saw in the creation of vW_inventory_fact_1997 earlier.

33.  Select File --> Close.

34.  Click Yes when prompted to save the query, once again.

35.  Type the following into the Save As dialog that next appears:

vW_inventory_fact_1998

36.  Click OK to save the view and close the dialog.

We return to the Database View, where we see our new view in place, underneath the first view we created, as shown in Illustration 23.


Illustration 23: The Two New Views Appear (Compressed View)

37.  Select File --> Exit to close MS Access and the clone database, saving as necessary.

This concludes the preparation steps at the source database level. We will next move into Analysis Services, where we will finalize preparation for our practice exercise by creating a copy of the FoodMart 2000 sample Analysis Services database, within which we can undertake the structural setup required to support the relative time capabilities requested by the client information consumers.

Clone the FoodMart 2000 Sample Database

We will create a copy of the FoodMart 2000 sample database, which, along with the several cubes and other objects it contains, accompanies an Analysis Services installation. This will allow us to keep the original sample database intact for other uses.

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as depicted in Illustration 24.

Click for larger image

Illustration 24: Example Databases Displayed within Analysis Manager

NOTE: Your databases / cube tree will differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

4.  Right-click the FoodMart 2000 database.

Again, we are making a copy of the database, because our lesson will involve making changes to the data connections, as well as the Warehouse cube and other objects, within the practice example. As we have noted, working with the copy will allow us to maintain our existing sample cube in its current condition, and available to other possible users and applications.

5.  Select Copy from the context menu that appears.

6.  Right-click on the Analysis Server.

7.  Select Paste from the context menu that appears, as shown in Illustration 25.


Illustration 25: Paste the Cloned Database to the Analysis Server

The Duplicate Name dialog appears.

As noted in previous articles, we cannot have two databases of the same name in a given Analysis Server.

8.  Type the following into the Name box of the Duplicate Name dialog:

Relative Time Sample

The Duplicate Name dialog appears, with our modification, as depicted in Illustration 26.


Illustration 26: The Duplicate Name Dialog, with New Name

TIP: As I have mentioned elsewhere, in this and other series, the foregoing is also an excellent way of renaming a cube (a "rename" capability is not available here, as it is in many Windows applications). Simply create a duplicate, give it the name to which you wish to rename the old cube, and then delete the old cube, as appropriate. This also works for Analysis Services databases, dimensions and other objects.

9.  Click OK to apply the name change.

The new database, Relative Time Sample, appears in the tree beneath the Analysis Server, among the databases already in place, as shown in Illustration 27.


Illustration 27: The New Database Appears ...

We now have a copy of the FoodMart 2000 database, within which we can perform the steps of our practice exercise. Let's process the new database to "register" it with Analysis Services, and to ensure that we are all in a "processed" state.

Process the Copy Database

1.  Right-click the new Relative Time Sample database.

2.  Select Process the Database from the context menu that appears, as depicted in Illustration 28.


Illustration 28: Select Process the Database from the Context Menu

Processing begins immediately, and the Process viewer appears, displaying various logged events. Processing completes, and the viewer presents a green Processing completed successfully message, as shown in Illustration 29.


Illustration 29: Indication of Successful Completion Appears (Compact View)

3.  Click Close to dismiss the viewer.

We will next take the final preparation steps for establishing relative time capabilities within the Warehouse cube.

Re-Align the New Analysis Services Database to Use the Enhanced Source Database

The final step of preparatory step for adding relative time capabilities to the Warehouse cube clone is to point the new Analysis Services database to the enhanced Access database. Recall that our modified database contains structural elements to support the relative time dimension.

We can accomplish this source re-alignment with the following steps:

1.  Expand the new Relative Time Sample database by clicking the "+" sign to its left.

2.  Expand the Data Sources folder that appears, just below the expanded Relative Time Sample database.

3.  Right-click the FoodMart data source that appears.

4.  Select Edit from the context menu that appears, as depicted in Illustration 30.

Click for larger image

Illustration 30: Editing the Data Source

The Data Link Properties dialog appears, defaulted to the Connection tab. Since we are simply re-pointing to another MS Access database, we will only need to make minimal changes at this level.

5.  Type the following into the bottom selector box on the dialog, just under the title "3. Enter the initial catalog to use":

D:\Program Files\Microsoft Analysis Services\Samples\RelativeTimeSample

NOTE: If you placed your copy of the database in another location, type in the correct location here, instead.

6.  Click Test Connection to ascertain connectivity.

A Data Link message box appears, indicating that the Test connection succeeded.

NOTE: An error here likely means an incorrectly typed path. See the steps above if this is the case.

7.  Click OK to dismiss the message box.

The Data Link Properties dialog, Connection tab, appears as shown in Illustration 31.


Illustration 31: Our Modifications to the Data Link Properties Dialog – Connection Tab

8.  Click OK on the Data Link Properties dialog to accept changes and close it.

We now have to perform a set of steps that allow us to work with the Warehouse cube in its currently partitioned state. As I mentioned earlier, we might have merged the partitions, or "started out fresh" otherwise, but taking the following steps to re-align the new views to the partitions represented a quick approach to preparing to work within the Warehouse cube structure, while minimizing rework.

In addition to handling the aforementioned eccentricities, we also get practice at a couple of things that might be useful to us in a real world scenario. Keep in mind that this "re-alignment" step for the partitions will not likely be a common requirement in most scenarios, as I stated earlier, as final partitioning, if enacted, would likely come after major cube design work was done. Even if partitioning was in place, we would likely merge to a single source, make our design changes, and then re-partition based upon the new design (which might change the overall partitioning strategy anyway).

9.  Expand the Warehouse cube within the Relative Time Sample database, by clicking the "+" to its immediate left.

10.  Expand the Partitions folder that appears immediately underneath the expanded cube.

11.  Right-click the Warehouse (the uppermost) partition.

12.  Select Edit from the context menu that appears, as depicted in Illustration 32.


Illustration 32: Select Edit to Re-align the Partition

The Partition Wizard: Warehouse dialog appears.

13.  Click the Change button.

The Choose a fact table dialog appears.

14.  Scroll to the bottom of the Tables pane, and select vW_inventory_fact_1997, one of the views we created in MS Access as part of our preparation for this exercise, as shown in Illustration 33.


Illustration 33: Select Edit to Re-align the Partition

15.  Click OK to accept the choice.

We are returned to the Partition Wizard: Warehouse dialog, which now appears as depicted in Illustration 34.


Illustration 34: Re-aligning the Partition

16.  Click Next to move to the Select the data slice dialog.

17.  Click the Time dimension, in the Dimensions box, to select it.

18.  Click 1997 in the Members box, to select it.

The Select the data slice dialog, with our selections, appears as shown in Illustration 35.


Illustration 35: Select the Data Slice Dialog, with Our Selections

19.  Click Next.

20.  Click Next at the Specify the partition type dialog, which is fixed with regard to the Local setting, anyway, as depicted in Illustration 36.


Illustration 36: Specify Partition Type – Fixed as Local

We arrive at the Finish the Partition wizard dialog.

21.  Leaving the setting of Use the current set of aggregations in place, put a check in the checkbox to the left of the Process the partition when finished entry (bottom of the dialog).

The Finish the Partition wizard dialog, with our settings, appears as shown in Illustration 37.


Illustration 37: Finish the Partition Wizard Dialog

22.  Click Finish.

23.  Click Yes on the warning message box that appears, as depicted in Illustration 38.


Illustration 38: Click Yes to the Warning ...

Processing begins immediately, is it did in earlier processing evolutions, and the Process viewer once again displays various logged events. The Processing completed successfully message appears, as before, upon processing completion, as depicted in Illustration 39.


Illustration 39: Indication of Successful Processing Appears (Compact View)

24.  Click Close to dismiss the viewer.

We return to Analysis Manager. We will now repeat the steps for re-pointing the partition for the second entry in the Partitions folder, Warehouse 98.

25.  Right-click the Warehouse 98 partition.

26.  Select Edit from the context menu that appears.

The Partition Wizard: Warehouse dialog appears.

27.  Click the Change button.

The Choose a fact table dialog appears.

28.  Scroll to the bottom of the Tables pane, and select vW_inventory_fact_1998, the second of the views we created in MS Access as part of our preparation for this exercise.

29.  Click OK to accept the choice.

We are returned to the Partition Wizard: Warehouse dialog, which now appears as shown in Illustration 40.


Illustration 40: Re-aligning the Partition

30.  Click Next to move to the Select the data slice dialog.

31.  Click the Time dimension, in the Dimensions box, to select it.

32.  Click 1998 in the Members box, to select it.

33.  Click Next.

34.  Click Next at the Specify the partition type dialog, which is, again, fixed with regard to the Local setting.

We arrive at the Finish the Partition wizard dialog.

35.  Leaving the setting of Use the current set of aggregations in place, once again, put a check in the checkbox to the left of the Process the partition when finished entry (bottom of the dialog).

36.  Click Finish.

37.  Click Yes on the warning message box that appears, as we did with the first partition above.

Processing begins immediately, as before, and the Process viewer once again displays various logged events. The Processing completed successfully message appears, as within our earlier processing evolutions, upon completion.

38.  Click Close to dismiss the viewer.

We return to Analysis Manager. We are now ready to create the structure that will support Relative Time aggregations within our Warehouse cube.

Procedure

Create a New Relative Time Shared Dimension

We will begin the procedure for creating support for relative time periods by adding a new shared dimension, with which we will next associate calculated members that will perform the aggregations, as we shall see.

1.  Right-click the Shared Dimensions folder within the new Relative Time Sample Analysis Services database.

2.  Select New Dimension from the context menu that appears.

3.  Select Editor from the cascading menu that appears next, as depicted in Illustration 41.

Click for larger image

Illustration 41: Select New Dimension --> Editor from the Context Menus

The Choose a Dimension Table dialog appears.

4.  Scroll down in the Tables pane (left half of the dialog), to locate the relativetime table in the clone database.

5.  Select the relativetime table, which we created expressly to support the new relative time dimension, as shown in Illustration 42.


Illustration 42: Select the Relativetime Table

6.  Click OK to accept the selection, and to progress to the Dimension Editor.

7.  From the Schema tab in the Dimension Editor, drag the relativetime_id field from within the relativetime table to the left, dropping it onto the <New> dimension in the tree, as depicted in Illustration 43.


Illustration 43: Drag the Relativetime_id Field to the New Dimension

Relativetime_Id appears as the first level under the <New> dimension.

8.  Click <New> in the Dimension tree to highlight it.

9.  Type Relative Time into the Name box, on the Basic tab of the Properties pane.

10.  Click the Relativetime_Id level in the Dimension tree.

11.  Replace the Name on the Basic tab of the Properties pane with the following:

Relative Time

12.  Replace the current text in the Member Name Column box, "relativetime"."relativetime_id", with the following:

"relativetime"."relativetime_member"

Our changes appear highlighted in a partial view of the Dimension Editor in Illustration 44.


Illustration 44: Highlighted Changes in the Dimension Editor (Partial View)

13.  Select Tools --> Process Dimension from the main menu within the Dimension Editor, as depicted in Illustration 45.


Illustration 45: Processing the New Dimension

14.  Click Yes when prompted to save the dimension.

The Process a Dimension – Select the processing method dialog appears, with only the Rebuild the dimension structure option available (this is the first time the dimension has been processed), as shown in Illustration 46.


Illustration 46: Rebuild the Dimension Structure is the Sole Option ...

15.  Click OK to proceed with processing.

Processing begins immediately, and the Process viewer appears, displaying various logged events. Processing completes, and the viewer presents a green Processing completed successfully message, as shown in Illustration 47.


Illustration 47: Indication of Successful Processing Appears (Compact View)

16.  Click Close to dismiss the Process viewer.

17.  Select File --> Exit to close the Dimension Editor, and return to Analysis Manager.

We now have a Relative Time dimension within which we will concentrate the relative time capabilities requested by the information consumers. We will add the new dimension to the Warehouse cube in our next steps, where we will make other enhancements to the cube structure to complete our addition of relative time structures.

Modify the Cube to Support Relative Time Periods

The objective behind our efforts in this article is to create relative time capabilities that perform similarly to those generated in Cognos PowerPlay Transformer, specifically, as well as to expose a more sophisticated way of adding these powerful structures to our existing model. A key difference exists between this approach to providing relative time structures and the standalone calculated member approach we examined in Relative Time Periods in an Analysis Services Cube, Part I: the more sophisticated approach of using a specially created dimension means far more flexibility in an OLAP environment. At the conceptual heart of the mechanism is the fact that an independent dimension means yet another point of convergence with the measures in the cube – a point of convergence that can be leveraged by any member of the dimension that houses it.

Because this intersection occurs with every measure, we need provide only a single calculation for each relative time period we wish to be able to report upon from our cubes. In contrast, using the method illustrated in our previous article, we constructed a calculated member for the year-to-date value of a single measure as an illustration. The same year-to-date scenario (as well as other cumulative and comparative relative time periods we will create), as we will discover using the dimensional approach, can be applied to any measure in our cube simply by juxtaposing the desired calculated member with the measure to which we wish to apply its logic.

1.  Expand the Cubes folder within the new Relative Time Sample database.

The sample cubes appear.

2.  Right-click on the Warehouse sample cube.

3.  Select Edit from the context menu that appears, as depicted in Illustration 48.


Illustration 48: Opening the Cube Editor for the Warehouse Cube

The Cube Editor opens.

Before we go further, let's update the fact table to reflect our earlier changes within the relational and Analysis Services databases.

4.  On the Schema tab, right-click the current fact table, inventory_fact_1997.

5.  Select Replace ... from the context menu that appears, as shown in Illustration 49.


Illustration 49: Targeting the Fact Table for Replacement

The Select table dialog appears. We will select the first view we created in the FoodMart 2000 MS Access database, vW_inventory_fact_1997.

6.  In the Table pane of the dialog, scroll to, and select, the vW_inventory_fact_1997 view, appearing near the bottom, as depicted in Illustration 50.


Illustration 50: Selecting the View as the Replacement Fact Table

The columns of the view appear in the Details pane, as shown above.

7.  Click OK to accept selection.

The Select table dialog closes, and we see that the fact table reference swap has occurred in the Schema view.

Next, we will add the shared dimension, Relative Time, which we created earlier to the Warehouse cube, and then create six calculated members within the cube. The calculated members will contain the relative time logic we need to add the desired capabilities to the cube, as we shall see. For purposes of our practice example, we will create a handful, consisting of the following popular variations, to illustrate the concepts:

  • Year-to-Date Total
  • Quarter-to-Date Total
  • Month-to-Date Total
  • Prior Year Total
  • Prior Quarter Total
  • Prior Month Total

Keep in mind that the tiny set we will assemble might be expanded to meet virtually any relative time consideration. We can leverage a vast assortment of MDX functions (and combinations of functions) via the same concept. Our point here is to illustrate an approach to unleashing some of that power in an efficient approach to meeting common business requirements.

8.  Select Insert --> Dimension from the main menu of the Cube Editor.

9.  Select Existing ... from the cascading menu that appears, as shown in Illustration 51.


Illustration 51: Select Insert --> Dimension --> Existing ...

Dimension Manager opens.

10.  Scroll to, and select, Relative Time in the Shared Dimension pane, as depicted in Illustration 52.


Illustration 52: Select the Relative Time Shared Dimension

11.  Click the Add a dimension (for a single selection) button, the top ">" button (circled in Illustration 52 above) between the panes of the Dimension Manager.

The Relative Time dimension appears in the Cube dimensions pane.

12.  Click OK to accept the new addition.

The Dimension Manager closes, and we see the Relative Time dimension appear in the tree, within the Dimensions folder, as shown in Illustration 53.


Illustration 53: The Relative Time Dimension Appears within the Cube

13.  Right-click the Calculated Members folder in the tree.

14.  Select New Calculated Member ... on the context menu that appears, as depicted in Illustration 54.


Illustration 54: Select New Calculated Member ...

The Calculated Member Builder opens.

15.  Select Relative Time in the top Parent dimension selector box of the Builder.

16.  Type the following into the Parent member box:

[All Relative Time]

17.  Type the following into the Member name box:

Year-to-Date Total

18.  Type the following MDX into the Value expression box:

SUM(PeriodsToDate([Time].[Year]), ([Relative Time].[Relative Time].[Current]) )

19.  Click Check to perform syntactical verification.

An Analysis Manager message box appears, indicating "Syntax is OK, as shown in Illustration 55.


Illustration 55: "Syntax OK" Indication

20.  Click OK to close the message box.

The Calculated Member Builder appears, with our input, as depicted in Illustration 56.


Illustration 56: The Calculated Member Builder with Our Input (Compressed View)

21.  Click OK.

The Calculated Member Builder closes. We see the new Calculated Member within the Calculated Members folder of the tree, as shown in Illustration 57.


Illustration 57: The Year-to-Date Total Calculated Member Appears ...

NOTE: For more information on the PeriodsToDate() function, see my article MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions in the MDX Essentials series at Database Journal. The article details "shorthand" versions of the PeriodsToDate() function, which include a "YTD" version.

In addition, we discuss the use of the PeriodsToDate() function in a simpler approach for providing relative time capabilities individually for a given measure in our previous article, Relative Time Periods in an Analysis Services Cube, Part I. In that article, our practice exercise centers upon the creation of a standalone calculated member, which exploits a combination of the SUM() and PeriodsToDate() functions to accomplish a more basic objective than the need we have described in this article.

At this point, we will create calculated members for the other relative time aggregations that the information consumers have requested in their business needs specifications. We will follow the steps we took in creating the Year-to-Date Total for each of the additional relative time members we need, basing our construction of the calculated members on the settings presented in the steps and tables that follow.

22.  Right-click the Calculated Members folder in the tree, once again.

23.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

24.  Create the Quarter-to-Date Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 3 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Quarter-to-Date Total

Value Expression

SUM(PeriodsToDate([Time].[Quarter]),([Relative Time].[Relative Time].[Current]))


Table 4: Calculated Member Builder Settings: Month-to-Date Total Calculated Member

The Calculated Member Builder appears, with our input, as shown in Illustration 59.


Illustration 59: The Calculated Member Builder: Month-to-Date Total (Compressed View)

29.  Click OK.

The Calculated Member Builder closes, and the new Month-to-Date Total calculated member joins the other members of the Calculated Members folder. Let's create the three "comparative" calculated members requested by the information consumers next.

30.  Right-click the Calculated Members folder in the tree, once again.

31.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

32.  Create the Prior Year Total calculated member by completing the fields of the Calculated Member Builder to with the settings presented in Table 5 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Year Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Year], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 5: Calculated Member Builder Settings: Prior Year Total Calculated Member

The Calculated Member Builder appears, with our input, as depicted in Illustration 60.


Illustration 60: The Calculated Member Builder: Prior Year Total (Compressed View)

33.  Click OK.

The Calculated Member Builder closes, and the new Prior Year Total calculated member joins the other calculated members in the tree, as before.

NOTE: For more information on the ParallelPeriod() function, see my article MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions in the MDX Essentials series at Database Journal.

34.  Right-click the Calculated Members folder in the tree, once again.

35.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

36.  Create the Prior Quarter Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 6 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Quarter Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Quarter], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 6: Calculated Member Builder Settings: Prior Quarter Total Calculated Member

The Calculated Member Builder appears, with our input, as shown in Illustration 61.


Illustration 61: The Calculated Member Builder: Prior Quarter Total (Compressed View)

37.  Click OK.

The Calculated Member Builder closes, and we see Prior Quarter Total appear in the Calculated Members folder within the tree. Let's create the final calculated member, Prior Month Total, to support the information consumers' expressed business requirements via the Relative Time dimension.

38.  Right-click the Calculated Members folder in the tree, as before.

39.  Select New Calculated Member ... on the context menu that appears.

The Calculated Member Builder opens.

40.  Create the Prior Month Total calculated member by completing the fields of the Calculated Member Builder, with the settings presented in Table 7 below.

Setting

Our Input:

Parent Dimension

Relative Time

Parent Member

[All Relative Time]

Member Name

Prior Month Total

Value Expression

([Measures].CurrentMember, ParallelPeriod ([Time].[Month], 1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))


Table 7: Calculated Member Builder Settings: Prior Month Total Calculated Member

The Calculated Member Builder appears, with our input, as depicted in Illustration 62.


Illustration 62: The Calculated Member Builder: Prior Month Total (Compressed View)

41.  Click OK.

The Calculated Member Builder closes. We see the results of our handiwork within the complete members within the Calculated Members folder that appears in Illustration 63.


Illustration 63: The Relative Time Calculated Members Set

We have created the members of the Relative Time dimension required to support the capabilities requested by the client information consumers. We will conclude this exercise with a quick verification of the operation of these structures in the section that follows. A subsequent article in my MSSQL Server Reporting Services series will focus upon the use of the structures within an enterprise reporting environment.

Verification

Re-Process the Cube and Review the New Relative Time Capabilities within the Cube Browser

Let's perform a re-process of the cube to ensure we are in sync, before reviewing the new relative time structures in action.

1.  From within the Cube Editor, select Tools --> Process Cube ... from the main menu, as shown in Illustration 64.

Click for larger image

Illustration 64: Select Tools ---> Process Cube ... from the Main Menu

2.  Click Yes when prompted to save the cube.

3.  Click No when prompted to design aggregations, in the next message box that appears.

4.  Select the radio button corresponding to the Full option on the Process a Cube dialog that appears next.

5.  Click OK to begin processing the Warehouse cube.

Processing begins immediately, and the Process viewer appears, displaying various logged events. Processing completes, and the viewer presents the familiar green Processing completed successfully message.

6.  Click Close to dismiss the viewer.

We will next review our new relative time dimension in action.

7.  Click the Data tab within Analysis Manager.

The Cube Browser – Data view appears.

8.  Within the Data view, ensure that the Product dimension is in place as the row axis, by dragging it there to replace the existing dimension, as necessary.

9.  Within the Data view, ensure that the Measures dimension is in place as the column axis, dragging it there to replace the existing dimension, if required.

The Data view, with the aforementioned dimensions in place within the respective axes, partially appears as depicted in Illustration 65.


Illustration 65: Data View, with Initial Axes in Place (Partial View) ...

We will drag a couple of additional dimensions into place, to emphasize the operation of the Relative Time dimension in juxtaposition to the regular Time dimension, where we can assess its effectiveness easily.

10.  Drag the Time dimension to the right of the Product dimension, the current occupant of the row axis, until, with the cursor touching the right side of the Product axis, the icon shown in Illustration 66 appears.


Illustration 66: Drop Point to "Crossjoin" Product and Time Dimensions (Icon Enlarged)

11.  Drop the Time dimension in place, to form a crossjoin with the Product dimension, as partially depicted in Illustration 67.


Illustration 67: Crossjoined Product and Time Dimensions (Partial View)

12.  Drag the Relative Time dimension to just below the Measures dimension, the current occupant of the column axis, until, with the cursor touching the bottom side of the Measures axis, the icon shown in Illustration 68 appears.


Illustration 68: "Crossjoining" Measures and Relative Time Dimensions (Icon Enlarged)

13.  Drop the Relative Time dimension in place, to form a crossjoin with the Measures dimension, as partially depicted in Illustration 69.


Illustration 69: Crossjoined Measures and Relative Time Dimensions (Partial View)

The resulting axes will allow us to perform a quick visual verification of the effectiveness of our new Relative Time capabilities. Let's drill into a sample and examine the results.

14.  Double-click the Year column heading, to drill down for all rows.

15.  Double-click the Quarter column heading, to achieve the same effect.

16.  Scroll down in the Data view to the Food Product Family.

17.  Scroll over in the Data view, so that the Store Invoice measures appear, with Current as the leftmost column appearing in the matrix.

The cube browser matrix, aligned with the intersects upon which we have chosen to focus, appears similar to that partially shown in Illustration 70.


Illustration 70: Relative Time Dimension Members in Action ...

We can see, within the small sample of data we have aligned in the browser, how the members of the Relative Time dimension accomplish the objectives for which they were created. For example, we can see that the Current value (really the simple Warehouse Sales value with no relative time calculation applied – somewhat opposite to the "rollup" behavior we typically see in dimensions) is accumulating in our "to date" calculations just as we might expect. We can also see that the 1998 Prior Year Total, 73,367.55, agrees with the 1997 Current (as well as 1997 Year-to-Date) value. We can also easily verify that the Prior Quarter Totals and Prior Month Totals appear to be functioning as we might expect.

We will examine the use of these new structures from within a reporting environment in a subsequent article in my MSSQL Server Reporting Services series at Database Journal.

18.  Select File --> Exit to close the Cube Editor.

19.  File --> Exit again, to close Analysis Services.

Conclusion

In this article, we extended the examination of the design and creation, within Analysis services, of relative time periods that we began in Relative Time Periods in an Analysis Services Cube, Part I. As we noted in the introductions of both articles, a popular feature found in Cognos PowerPlay Transformer, among other popular enterprise BI applications, is a set of relative time periods, which can be generated automatically or manually for reporting in the Cognos PowerPlay application. After discussing, in Part I, the frequent request that I receive to provide options afforded within Analysis Services for replicating the popular relative time period capabilities, we began with a straightforward approach to meeting the requirement for relative time periods, highlighting differences in operation inherent in the use of a calculated member to achieve the capabilities offered in Cognos PowerPlay Transformer.

In this article, we developed a more sophisticated approach that closely replicates relative time period functionality in Cognos PowerPlay Transformer, and thereby provides a solution even more user friendly for reporting specialists and information consumers. After briefly revisiting the capabilities found in Cognos PowerPlay Transformer, we began an exploration of this approach, highlighting its advantages over the simple option we offered in our previous article, a calculated member to meet a narrower need. We then performed a practice exercise, whereby we added relative time capabilities with this more evolved method, creating the structural members in Analysis Manager that we require to implement the solution.

A significant part of our exercise involved preparation of the data source to support a special dimension, populated with calculated members that perform the relative time aggregations, which we in turn created in Analysis Services. As a concluding step in our practice session, we verified the adequacy of our solution by demonstrating the use of the new capabilities from the perspective of the Cube Browser in Analysis Manager, looking forward to a subsequent article where we employ our handiwork within Reporting Services to more closely replicate the way the functionality offered within Cognos PowerPlay Transformer can make the solution even more user friendly, in the Microsoft counterpart to Cognos PowerPlay, for reporting specialists and information consumers.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved