Mastering Enterprise BI: Time Intelligence Pt. II - Page 2

Hands-On Procedure

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

Preparation

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:

Ascertain Connectivity of the Relational Data Source

Let’s 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 sample’s 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 articles index:

Deploy 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 DBJ AdventureWorks DW project at this point.

Procedure: Add Time Intelligence with the Business Intelligence Wizard

With 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:

• Define time intelligence (the subject of this article);
• Define account intelligence;
• Define dimension intelligence;
• Specify a unary operator;
• Create a custom member formula;
• Specify attribute ordering;
• Enable dimension writeback;
• Define semiadditive behavior;
• Define currency conversion.

We 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 steps:

• 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);
• A decision 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).

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

Examine Cube Structure Prior to Leveraging the Business Intelligence Wizard

Let’s 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.

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

(Compressed View)

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

First, let’s 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 our model.

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

Next, let’s 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 don’t 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.”

A portion of the pre- existing eighteen (18) attributes is shown in Illustration 6.

No Calculated Attributes Appear ...

The Dimension Designer closes.

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

The Cube Designer opens, defaulted to the Cube Structure tab.

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

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