Architect Bill Pearson continues his introduction to enhanced features in
Analysis Services 2005 for Time Intelligence support. In this session, we
examine new, wizard-driven features that support the easy addition of Time
Intelligence within our cube.
About the Series ...
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
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.
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.
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.
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
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;
of general approaches to meeting the requirement for relative time aggregations in Analysis Services 2005;
introduction to the Business Intelligence Wizard in Analysis Services
surrounding the accommodation of custom relative time aggregations using
the Business Intelligence Wizard;
exercise, whereby we add Time Intelligence to a sample cube with this
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
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
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
Illustration 2: Relative Time
Structures in Cognos PowerPlay Transformer
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
periods might be
important to us ...).
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.
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
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 or Installing 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.
We will get some
hands-on experience with our subject matter in a practice session. We will
first need to prepare for our exercises by creating an Analysis Services
project, within which to work from the Business Intelligence Development
Studio. We will rely heavily upon samples that ship with MSSQL Server 2005,
to minimize the preparation time required to create a working practice
environment. This shortcut will afford anyone with access to the installed
application set and its samples an opportunity to complete the steps in the
If you prefer to work
within an existing copy of the Adventure Works Analysis Services
project (perhaps you have already made a copy for work with previous articles),
or you intend to create a new, pristine copy of the original from the CDs or
another source for this (and possibly other) purposes, please feel free to skip
the related preparatory sections.
Create a New
Analysis Services Project within a New Solution
For purposes of our
practice session, we will create a copy of the Adventure Works Analysis
Services project, one of several samples that are available with (albeit
installed separately from) the integrated Microsoft SQL Server 2005
business intelligence solution. Creating a clone of the project means we can
make changes to select contents (perhaps as a part of later exploration with
our independent solution), while retaining the original sample in a pristine
state for other purposes, such as using it to accompany relevant sections of
the Books Online, and other documentation, as a part of learning more
about Analysis Services and other components of the integrated Microsoft
business intelligence solution in general.
To create a copy of
the sample Adventure Works Analysis Services project, please see
the following procedure in the References section of my articles index:
Connectivity of the Relational Data Source
ensure that the data source within our project is in working order. Many
of us will be running side-by-side installations of MSSQL Server
2000 and MSSQL Server 2005. This means that our installation of the
latter will need to be referenced as a server / instance combination,
versus a server name alone (the default for the Adventure Works DW project
samples connection is localhost, which will not work correctly in
such a side-by-side installation, as MSSQL Server 2000 will have assumed
the identity of the local PC by default).
If you do not know how
to ascertain connectivity of the relational data source, please perform the
steps of the following procedure in the References section of my
the Analysis Services Project
Because we will need a fully deployed project to enable us
to perform a few of the early steps of our practice session, we will deploy the
DW project at this point.
Deploy the DBJ
AdventureWorks DW project.
NOTE: If you do not know how to
ascertain alignment of the project to the destination server, and / or to
deploy the project, please perform the steps of the following procedure in the References
section of my articles index:
Procedure: Add Time Intelligence with the Business
the introduction of the Business Intelligence Wizard, Analysis
Services 2005 makes available several enhancements through which we can
enable additional functionality within our cubes and dimensions. Among the options,
the Business Intelligence Wizard makes available are the following:
intelligence (the subject of this article);
custom member formula;
will add Time Intelligence to the existing Adventure Works sample
cube contained within our project clone, and then examine the structural
changes that have ensued. As many of us are likely to be aware, this
enhancement is best undertaken after basic cube design has been largely
completed at least to the extent of three primary prerequisites / preparatory
A finalized Time
(or Date) dimension should be in place (it is upon this that we base the
calculations generated by the Business Intelligence Wizard);
should have been made as to which role, or roles (if applicable)
of the existing Time dimension is to be included in the implementation
of Time Intelligence;
A finalized Measures
dimension (complete with calculated measures, as appropriate) is in place
(again, to ensure that we have the option of referencing them in the
appropriate pages of the Business Intelligence Wizard).
are other decisions to be made as we proceed; we shall discuss each upon
encountering it. Before we begin, however, we will examine a couple of
structural perspectives within the cube model to establish a frame of
reference which we can revisit, after undertaking the actions led by the Business
Intelligence Wizard, to help us to ascertain the changes that the Wizard
has wrought within the structure.
Structure Prior to Leveraging the Business Intelligence Wizard
take a look at a couple of basic structural perspectives, together with the
existing cube script, to establish an idea of their current states, so as to
make the changes plain that are to come about within our practice session.
With regard to the structure, the Dimension Structure tab of the design
environment is an excellent place to start, because it depicts two areas with
which we are concerned from the unified three-pane view.
Within the Solution
Explorer inside the opened Analysis Services project, expand the Dimensions
folder, as required.
within the folder.
Designer from the context menu that appears, as depicted in Illustration
Illustration 3: Select
View Designer for the Date Dimension
Designer opens, defaulted to the Dimension Structure tab, where we
see the Attributes, Hierarchies and Levels, and Data Source
View panes, as is shown in Illustration 4.
Illustration 4: Dimension
Designer Dimension Structure Tab for the Date Dimension...
we can preview a couple of areas that will be impacted by our running the Business
Intelligence Wizard in the next section of our practice session. The
before and after comparison that our preview will support will be useful in
reinforcing in our minds the structural changes that occur.
lets examine the Data Source View from the perspective of the Date
dimension. We can see within the Data Source View pane that several Named
Calculations exist within the table supporting the Date dimension in
quick review of the Named Calculations reveals that, while there are,
indeed, fifteen (15), these Named Calculations exist primarily to support
descriptions and labels. The fifteen pre-existing Named Calculations
are depicted in Illustration 5.
Illustration 5: Data
Source View for Date Dimension Table: Pre-Existing Named Calculations
(Primarily Label / Description Support)
lets preview the Attributes pane. The relevant point we need to note
here is that, having expanded the various pre-existing Attributes, it
becomes apparent that, while many exist, we dont see any instances of a calculated
Attribute Hierarchy that relates to relative time aggregations. The eighteen
(18) Attributes we see appear to apply to run-of-the-mill labeling and
description characteristics related members within hierarchies, in several
cases, which might well lend themselves to the support of calculations,
but not anything that appears to accumulate data based upon time-based concepts
such as month to date or this year over prior year.
portion of the pre- existing eighteen (18) attributes is shown in Illustration
Attributes Pane for the Date Dimension (Partial View):
No Calculated Attributes
Close the Dimension
Designer by selecting File ->
Close from the
main menu, as depicted in Illustration 7.
Illustration 7: Select
File -> Close to Dismiss the Dimension Designer ...
we will pre-examine another area that we will later review for changes once
the Business Intelligence Wizard has done its work designing Time
Intelligence into our cube. We will take a quick look at the Calculations
tab for the UDM, where we can see the cube-level MDX expressions in a convenient,
single location. In effect, we will be examining the MDX Script that
underlies our model via the onboard MDX Editor when we take this
Within the Solution
Explorer, once again, expand the Cubes folder, as required.
Works.cube within the folder.
Designer from the context menu that appears, as shown in Illustration 8.
Illustration 8: Select
Designer opens, defaulted to the Cube Structure tab.
Click the Calculations
tab, as depicted in Illustration 9.
Illustration 9: Click
the Calculations Tab ...
tab provides a central place for interaction with the full inventory of
cube-level expressions. By default, the MDX Editor appears in the pane
to the right, and the Script Organizer pane lies to the left. As we can
easily see, forty-nine (49) line items appear within the Script Organizer,
which appears similar to that shown in Illustration 10.
Illustration 10: The
Script Organizer with Pre-Defined Line Items...
have now examined a few structural areas to establish a frame of reference
prior to beginning our work with the Business Intelligence Wizard. In
our next steps, we will get some hands-on practice with adding Time Intelligence
to our cube.
Add Time Intelligence with the Business Intelligence Wizard
We will return to the Solution Explorer, from which we will launch the Business Intelligence Wizard.
1. Within the Solution Explorer, expand the Cubes folder, as required.
2. Right-click Adventure Works.cube within the folder.
3. Select Add Business Intelligence... from the context menu that appears, as depicted in Illustration 11.
Illustration 11: Select Add Business Intelligence ...
The Welcome to the Business Intelligence Wizard page appears, as shown in Illustration 12.
Illustration 12: The Welcome to the Business Intelligence Wizard Page Appears
The Choose Enhancement page next appears. Here we instruct the Business Intelligence Wizard to add Time Intelligence to our cube.
5. Ensure that the top item in the Available enhancements list, Define time intelligence, is selected, as depicted in Illustration 13.
Illustration 13: The Choose Enhancement Page with Our Selection
The Choose Target Hierarchy and Calculations page appears. As we noted in our introduction, we have several items to consider before and during our implementation of Time Intelligence through the mechanism of the Business Intelligence Wizard. Some of those considerations confront us here, where we must indicate the target Time Hierarchy, as well as which Calculations to generate.
7. Click the downward pointing arrow on the right side of the selector labeled Use the following hierarchy to analyze time calculations.
8. Click-select the Calendar item (second from the top) under the Date dimension (expanded at the top of the selection list that appears), as shown in Illustration 14.
Illustration 14: Hierarchy Selection atop the Choose Target Hierarchy and Calculations Page
While we would typically do as we have done here, and select multi-level hierarchies (so that we can provide relative time aggregations at multiple time levels), we can see that we have other options to more precisely fit our business needs. We also select the specific calculations we want to create, which we will accomplish in the next steps.
NOTE: We will need to execute the Time Intelligence creation steps individually for each hierarchy. As an example, say we wanted to perform these steps for the Fiscal hierarchy, in addition to the Calendar hierarchy with which we are working at present, should business requirements dictate that we generate calculations for multiple hierarchies.
9. Click OK to accept the choice of Calendar hierarchy.
10. Within the Available time calculations selection list, just below the hierarchy selector, place a check in the checkbox to the immediate left of each of the following calculation selections:
- Year to Date
- Semester to Date
- Quarter to Date
- Month to Date
- Twelve Months to Date
- Twelve Month Moving Average
- Six Month Moving Average
- Three Month Moving Average
- Year Over Year Growth %
- Year Over Year Growth
- Quarter Over Quarter Growth %
- Quarter Over Quarter Growth
- Month Over Month Growth %
- Month Over Month Growth
The Choose Target Hierarchy and Calculations page appears, with our selections, as depicted in Illustration 15.
Illustration 15: The Choose Target Hierarchy and Calculations Page with Selections ...
Because the Time Intelligence enhancement capability of the Business Intelligence Wizard is based upon a template, TimeIntelligence.xml (found at C:\Program Files\Microsoft SQL Server\90\Tools\Templates\OLAP\1033 in a default installation), we can add additional calculations to customize the selections to meet the specific needs of our own environments, just as we can customize other Analysis Services capabilities and characteristics. A small sample of the .xml template, with Description and View Name enclosed in red rectangles, appears in Illustration 16.
Illustration 16: Sample Section of the Time Intelligence Template
11. Click Next to accept our input and proceed.
The Define Scope of Calculations page appears next. Here, we essentially assign the Time calculations we have specified to the desired measures. The Wizard presents the option to select from both regular and calculated measures, so that our only real limitations here are our business requirements (and, of course, the state of whether or not a given calculation / measure juxtaposition is logical in nature).
12. Within the Available measures selection list of the Define Scope of Calculations page, place a check in the checkbox to the immediate left of each of the following measure selections:
- Internet Sales Amount
- Internet Order Quantity
- Internet Gross Profit
- Internet Gross Profit Margin
- Internet Average Unit Price
- Internet Average Sales Amount
- Internet Ratio to All Products
- Internet Ratio to Parent Product
The Define Scope of Calculations page appears, with our selections, as depicted in Illustration 17.
Illustration 17: The Define Scope of Calculations Page with Selections ...
13. Click Next to accept our input, once again, and proceed.
The Completing the Wizard page appears next. At this point we can overview the changes that are about to be undertaken, based upon our selections within the pages of the Business Intelligence Wizard. Proposed changes to structural elements within the three main locations that house them, including the Data Source View, the Time Dimension, and the MDX Script, are presented within the Changes list, which appears as partially shown in Illustration 18.
Illustration 18: The Completing the Wizard Page (Partial View) with Proposed Changes
14. Click Finish to execute the changes and to dismiss the Business Intelligence Wizard
The Wizard closes and we are returned to the Cube Designer, Calculations tab.
15. Right-click the Adventure Works.cube (Design) tab.
16. Select Close from the context menu that appears, as depicted in Illustration 19.
Illustration 19: Closing the Cube Designer ...
17. Click Save on the Microsoft Visual Studio dialog that next appears, asking if we wish to save changes to cube Adventure Works, as depicted in Illustration 20.
Illustration 20: Click Save to Confirm Wish to Save Changes ...
The dialog disappears, and the Cube Designer closes.
Having completed Time Intelligence implementation for the Calendar hierarchy, we are ready to examine the areas we previewed in the previous section to confirm our understanding of the changes made to the cube model. We will perform this examination, together with a review of various settings and properties that may be considerations, in our next article, Mastering Enterprise BI: Time Intelligence Pt. III. Our extended examination will support a compelling case for our exploiting the Time Intelligence capabilities of the Business Intelligence Wizard in Analysis Services 2005.
18. Preview the changes within the Date Dimension Designer and the Cube Designer, (which we will examine together as one objective in our next article), as desired.
19. Select File -> Save All from the main menu, to save our work through this point, as shown in Illustration 21.
Illustration 21: Saving All Work from Our Session
20. Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.
In this article, we continued the examination, begun in Mastering Enterprise BI: Time Intelligence Pt. I, of Analysis Services 2005 features that support the Time dimension within our Analysis Services 2005 cube models. Our focus extended into further enhancements introduced in Analysis Services 2005 to specifically support virtually any custom relative time aggregation requirement that we might encounter within our respective business environments. Our examination of new features that support the easy addition of Time Intelligence within our cube models began with 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. We then touched upon general approaches to meeting the requirement for relative time aggregations in Analysis Services 2005.
Next, we provided a general introduction to the Business Intelligence Wizard in Analysis Services 2005, narrowing our focus to one of its many cube enhancement capabilities, the implementation of Time Intelligence in general, and the accommodation of custom relative time aggregations specifically. We previewed various perspectives in the cube structure that we had expected to be altered by the Business Intelligence Wizard, concomitant to its implementation of Time Intelligence, to provide a basis of comparison in our next article, where we will examine the structures put into place by the Business Intelligence Wizard, together with relevant properties and settings that form considerations surrounding their successful use. We then performed a practice exercise, whereby we added relative time aggregations support with this straightforward method. Throughout the steps of our practice session, we touched upon various details to consider when implementing Time Intelligence in our respective business environments.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.