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.