Mastering Enterprise BI: Time Intelligence Pt. II - Page 2
March 12, 2007
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.
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
Lets 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 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.
1. 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 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:
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:
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
Lets 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.
1. Within the Solution Explorer inside the opened Analysis Services project, expand the Dimensions folder, as required.
2. Right-click Date.dim within the folder.
3. Select View Designer from the context menu that appears, as depicted in Illustration 3.
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.
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, 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 our model.
NOTE: For more information about Named Calculations in Analysis Services 2005, see my articles Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I and Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II, both members of my monthly Introduction to MSSQL Server Analysis Services series at Database Journal.
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, 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.
A portion of the pre- existing eighteen (18) attributes is shown in Illustration 6.
No Calculated Attributes Appear ...
4. Close the Dimension Designer by selecting File -> Close from the main menu, as depicted in Illustration 7.
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.
5. Within the Solution Explorer, once again, expand the Cubes folder, as required.
6. Right-click Adventure Works.cube within the folder.
7. Select View Designer from the context menu that appears, as shown in Illustration 8.
The Cube Designer opens, defaulted to the Cube Structure tab.
8. Click the Calculations tab, as depicted in Illustration 9.
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.