Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 12, 2007

Mastering Enterprise BI: Time Intelligence Pt. II

By William Pearson

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 (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portion of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

About the Mastering Enterprise BI Articles ...

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 – or outstrip - 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 integrated Microsoft BI solution to their counterparts among other enterprise BI vendors, to date, represents a serious “undersell” of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope, 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 integrated 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.


As I stated in my previous article, Mastering Enterprise BI: Time Intelligence Pt. I, Analysis Services 2005 witnesses further enhancements with regard to supporting the Time dimension. Moreover, in addition to these extended features, support for the creation of virtually any “custom” relative time aggregation that we might need is available to developers. In Part I of this article we gained some hands-on exposure to creating a Time dimension within Analysis Services 2005, focusing upon numerous enhanced features as we encountered them. In this, the second part of the article, we will examine new features that support the easy addition of Time Intelligence within our cube models.

Our examination of adding Time Intelligence within Analysis Services 2005 will include:

  • A discussion surrounding the concept of relative time aggregations, and how the Time dimension in general, and custom time periods specifically, have been accommodated in formerly dominant BI applications, such as Cognos Transformer / PowerPlay;
  • A discussion of general approaches to meeting the requirement for relative time aggregations in Analysis Services 2005;
  • A general introduction to the Business Intelligence Wizard in Analysis Services 2005;
  • A discussion surrounding the accommodation of custom relative time aggregations using the Business Intelligence Wizard;
  • A practice exercise, whereby we add Time Intelligence to a sample cube with this straightforward method;

In our concluding article, Mastering Enterprise BI: Time Intelligence Pt. III, we will move beyond the steps we have completed above with an examination of the structures put into place by the Business Intelligence Wizard, together with a discussion of relevant properties and settings surrounding their successful use. Finally, we will verify the adequacy of our solution by demonstrating the use of the new capabilities from the perspective of the Cube Browser in Analysis Manager.

Adding Time Intelligence in Analysis Services 2005

Overview and Discussion

In this article, we will continue our examination of Analysis Services 2005 features that support the Time dimension within our Analysis Services 2005 cube models. In our last article, Mastering Enterprise BI: Time Intelligence Pt. I, we focused upon adding a Time dimension to an Analysis Services 2005 cube, together with the underlying support via schema generation. Thus far, our concentration has been upon the basic support of the information consumers’ needs to perform analysis based upon the simple selection of specific years, months, and other levels of the Time dimension.

In this article, we will examine fulfillment of the need to support additional common business requirements surrounding the Time dimension. As we stated in the aforementioned previous article of our series, as well as in Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part I and Part II, as a part of the special focus upon the Time dimension within their design, support for the dynamic creation of various “relative” time periods and aggregations - flexible date calculations built into our model, such as a Month-to-Date value, Year-over-Year Growth (value or percent are common), and so forth - has been supported within enterprise business intelligence applications, for some time.

An excellent example of this support exists within Cognos PowerPlay Transformer, with which I became familiar through years of consulting with the once dominant Cognos business intelligence suite. The Date Wizard in Transformer creates the vast majority of the Time dimension for us, with perhaps a little remaining effort due upon us to modify the end presentation of members at the various hierarchical levels. This was similarly accomplished within Analysis Services 2000 (as we noted in Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part I and Part II) when we specified, within the more universal Dimension Wizard, that we were creating a Time dimension, as depicted in Illustration 1.

Illustration 1: Designating a Time Dimension in the Analysis Services 2000 Date Wizard

We noted that, 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 could go a step further and create the "relative time categories" we have mentioned. Just one of many advanced multidimensional modeling options within Transformer, relative time aggregations, such as year-over-year, quarter-to-date and year-to-date, among 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 PowerPlay cube model, appears in Illustration 2.

Illustration 2: Relative Time Structures in Cognos PowerPlay Transformer

Much ink had been spilt (perhaps “sprayed” is a more apt term) in forums, blogs and elsewhere regarding the fact that relative time periods did not automatically appear in the Analysis Services 2000 environment – “... and, 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 such a wizard is supposed to know which relative time periods might be important to us ...).

The solution available in Analysis Services 2000 lay within calculated members, where we could create the structures manually. The good news was that we could have precisely what we wanted, and nothing more - that is, we did not have a scenario where a wizard also generated cleanup / pruning tasks in its wake, once it had preemptively created relative time aggregations, etc., that were inappropriate to our local environments.

In my articles Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part I and Part II, I demonstrated the creation of relative time aggregations in Analysis Services 2000 in a couple of ways, stating that the approach taken might depend upon the need for which a given cube was being designed, among other factors. The first approach used time-related MDX functions within calculated members to meet needed relative time capabilities individually for a given measure. As we saw within the article, this accomplished the purpose, but differs from the Cognos relative time structure in that it did not apply to all measures within the cube model.

The second approach I demonstrated involved the creation of a special dimension to house the relative time calculations. While this was slightly more involved from a development perspective, the end result was that reporting specialists, and other "end users" of the cube, would find that the relative time structures parallel those found in the Cognos PowerPlay Transformer rendition. In fact, the approach also conceptually parallels the approach taken within the wizard-driven implementation of Time Intelligence that is introduced in Analysis Services 2005, as we shall see.

Concepts such as “Month-to-Date” obviously derive meaning from query context - that is, from “coordinates,” such as the member of the Time dimension selected by the information consumer at a given point. While it is possible, as we saw in my article Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part I, to manually add calculated members to a cube for each of the relative time calculations we might require, this could get overly complicated from the perspective of an uninformed user, as well as perhaps an administrator or other practitioner who finds himself in the position of maintaining such structures when, say, they were inherited from a now displaced developer. Analysis Services 2005 offers us a far easier approach to generating custom time calculations, and, as we shall see, a means for generating “templates” which we can extend even beyond our basic “relative time” requirements.

The Business Intelligence Development Studio includes a new wizard, the Business Intelligence Wizard, which, among other enhancements, offers us features that we can leverage to create some of the relative time calculations we need. In this article, we will get some hands-on exposure to using these features, while pointing out possible disadvantages and suggesting alternate approaches. The Wizard, as we shall see, offers a quick and easy means of adding a range of Time Intelligence structures, and quite often meets the needs of many environments “out of the box.”

We will introduce the Time Intelligence enhancement offered through the Business Intelligence Wizard in this article, focusing upon the ways in which it helps us to create time-based calculations to support the time-series analyses that are so useful in our business environments. We will start with a copy of the Adventure Works sample Analysis Services 2005 project, which is available for installation to anyone installing Analysis Services 2005. Because the sample Adventure Works cube already contains a Time dimension, we can more speedily get to the focus of our practice session. We will create our clone Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in other articles of this series, the Analysis Services Project that we create within the Studio will assist us in organizing and managing the numerous objects that we will need to support our work with an Analysis Services database.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 for use with Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics “Running Setup to Install AdventureWorks Sample Databases and Samples” in SQL Server Setup Help orInstalling AdventureWorks Sample Databases and Samples” in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM