Mastering Enterprise BI: Time Intelligence Pt. II

About the Series …

article is a member of the series Introduction to MSSQL Server Analysis
. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services (“Analysis
”), 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

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

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

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.

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

  • 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
(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
/ 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
for the Time dimension of a sample PowerPlay cube model,
appears in Illustration

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
might be
important to us …).

solution available in Analysis Services 2000 lay within calculated
, 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.

second approach I demonstrated involved the creation of a special dimension
to house the relative

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.

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

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles