Mastering Enterprise BI: Time Intelligence Pt. I

Monday Feb 12th 2007 by William Pearson
Share:

BI Architect Bill Pearson introduces enhanced features in Analysis Services 2005 for Time dimension support. Our hands-on exposure to Time dimension creation includes a focus upon automatic schema generation, with the complete creation and population of a Time dimension table in a designated target relational database.

About the Series ...

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

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.

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

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

Introduction

As I stated in my article Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions, it is a rare thing to encounter an instance of an OLAP cube that does not require a Time dimension. Throughout years of business intelligence consulting, I have only witnessed this scenario a handful of times within a production environment. Although there often seems to be no shortage of people to argue any side of any statement, few of us would disagree that the measurement of activity over time - and, hence, the Time dimension that supports this capability - is important to both analysis and operational management in general.

As an aside, I refer to the dimension as a “Time” dimension because my preference is to name dimensions after the generic concepts they represent – thus “time” versus “date.” While I can certainly live with “date” as the name of the dimension that represents the concept of time, I do not agree with the argument advanced by some that “date” is the more appropriate choice because, after all, we are working with “date” hierarchies that may not subanalyze to “time” - as in “time of day.” My response is that “date” itself is a subordinate member within the larger concept of time, and typically a level within the Time dimension, hence my choice of “Time” as a dimension name.

(I hope that not too much angst is aroused by Microsoft’s decision to use terms like “time intelligence,” “server time dimension,” “time periods,” and the like, throughout Analysis Services and its documentation, for those who might confuse time to mean “time of day ...”. Moreover, I heartily encourage substituting “date” for “time” as a dimension name when the latter leads to undue stress, justified or not. This is one of the beauties of working within semantic layers...)

The Time dimension has several unique characteristics, relative to other dimensions within our cube models. Among these is the fact that all businesses employ the same core calendar time hierarchy of days (and sometimes lower levels), weeks, and months, together with quarters and years (with various subdivisions included to meet local business and reporting needs) - even though treatment of these various levels can vary widely within the alternative considerations of fiscal years and periods. Moreover, the pervasive nature of time - within and surrounding all organizational activity - means the universal juxtaposition of the Time dimension and the other dimensions within our cube models. Another characteristic of time is its incremental continuation, like a ray in geometry, from a fixed beginning to a typically indefinite end.

The Time dimension has received special focus within the design of enterprise business intelligence applications. Common features include capabilities ranging from the recognition of date fields with minimal intervention to the automatic generation of members of the Calendar time dimension as a part of cube design and / or creation. Most of the dominant applications have even offered support for the dynamic creation of various “relative” time periods and aggregations. (For a discussion of some of the specific support provided by leading applications, as well as the Analysis Services 2005 approach to meeting and exceeding these features, see other articles within my Introduction to SQL Server 2000 Analysis Services series here at Database Journal).

As we shall see in this article and its successor, 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 remains readily available to assist developers. In this article, we will gain some familiarity with creating a Time dimension within Analysis Services 2005, focusing upon enhanced features as we encounter them. In Part II, we will examine new features that support the easy addition of Time intelligence within our cube models. Our examination of working with the Time dimension within Analysis Services 2005 includes:

  • An introductory discussion of the Time dimension, focusing on unique characteristics that distinguish it from other dimensions within our cube models;
  • Mention of the special focus that has been given to the Time dimension within the design of enterprise business intelligence applications, and features that have been added to the applications to provide an “assist” with the Time dimension as a part of cube design and / or creation;
  • A look ahead to our sequel article, wherein we discuss the support, offered by most of the recently-dominant applications for the dynamic creation of various “relative” time periods and aggregations, and how this support has been enhanced in Analysis Services 2005;
  • Creation of a new Analysis Services Project in preparation of our practice session;
  • Creation of a target database within SQL Server Management Studio for the schema generation procedure within our practice session;
  • Ascertaining connectivity of the relational data source, along with other preparatory procedures, within the new Analysis Services 2005 Project;
  • Creation of a rudimentary cube, via the “top down” approach (whereby no underlying data source is in place), containing a Time dimension, upon which to base our general examination;
  • Examination of the structure of the new Time dimension;
  • Generation of the underlying schema for the new cube model, including the generation of a Time table design, as well as its subsequent population, from within the cube model that it is designed to support;
  • Review of the new Date dimension within the Designer; and
  • Review of the generated schema, and the populated table supporting the Date dimension, within SQL Server Management Studio.

Creating a Time Dimension in Analysis Services 2005

Overview and Discussion

In this article, we will begin our examination of Analysis Services 2005 features that support the Time dimension within our Analysis Services 2005 cube models. We will use the Cube Wizard to generate a quick, basic cube, within which we will create a Time dimension. To further expose new features within Analysis Services 2005, we will exercise the option to build our cube without an underlying data source, and then demonstrate the use of the Schema Generation Wizard within the context of creating (and populating) a supporting Time dimension table for our underlying warehouse / mart environment.

We will create an 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 orInstalling 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.

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:

Make a Copy of an Analysis Services Sample Project within a New Solution

Create a Target Database for the Schema Generation Procedure

Because one of the objectives of our practice session will be to get some exposure to the process of generating tables that we design “from the top down,” within the design environment, we will need a target database within which to generate the schema that we design (one of the tables within which we will also populate). We will create the database we need within the SQL Server Management Studio, by taking the following steps:

1.  Click the Start button on the PC.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.


Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Database Engine in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment. (I have selected Windows Authentication in my own environment.)

The Connect to Server dialog appears similar to that depicted in Illustration 2.


Illustration 2: Preparing to Connect to the Server ...

7.  Click the Connect button to connect with the specified SQL Server Database Engine server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Database server within which we are working.

The Databases folder opens, exposing the detected database(s), as shown in Illustration 3.


Illustration 3: Exposing the Databases in the Object Browser

NOTE: The databases that appear will depend upon the activities that have taken place in your own environment, and will differ from those shown in Illustration 3 above.

9.  Right-click the Databases folder that we just expanded.

10.  Select New Database ... from the context menu that appears, as depicted in Illustration 4.


Illustration 4: Select New Database ...

The New Database page appears.

11.  Type the following into the Database name box at the top of the New database page:

DBJ_Schema_Target

The relevant portion of the New Database page appears, with our input, as shown in Illustration 5.


Illustration 5: The New Database Page (Partial View), with Database Name ...

12.  Click OK to create the new database.

The new database appears within Object Explorer, among the other databases.

13.  Select File -> Exit from the main menu to exit SQL Server Management Studio, as depicted in Illustration 6.


Illustration 6: Exiting SQL Server Management Studio ...

Having created a target database for one of our practice exercise procedures, we are ready to return to the Business Intelligence Development Studio.

14.  Return to the Business Intelligence Development Studio.

Ascertain Connectivity of the Relational Data Source

Let’s ensure that the data source within our project is in working order. (We will be creating a data connection to the new database, as well, in a step within our practice session.) 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:

Ascertain Connectivity of the Relational Data Source

Create a Cube with No Underlying Data Source

To save preparation time, we will be working within our copy of the sample Analysis Services Project. We will be creating a new cube, however, within which we will get some hands-on exposure to working with a Time dimension. Having established the presence of a working Data Source within our Analysis Services Project, our next steps surround the creation of very basic cube, within which we will perform our practice session.

Although our intent is to continue our development efforts with the metadata without an open connection, most of us probably find it more common to build our cubes and other objects within the Unified Dimension Model (UDM) based upon an actual underlying database. We have ensured the presence of working Data Source for steps that we will perform subsequent to the design of our Time dimension.

Create a Basic Cube – Up to Insertion of a Time Dimension

We will create a basic cube, to house a representative Measure and a few dimensions. We will focus upon other areas within the overall design and creation process in other articles of the series – our primary objective here is to gain some familiarity with a Time dimension – not to concern ourselves with the myriad other options and topics contained within general cube design.

1.  Right-click the Cubes folder within the Solution Explorer.

2.  Select New Cube ... from the context menu that appears, as shown in Illustration 7.


Illustration 7: Select New Cube ... to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as depicted in Illustration 8.


Illustration 8: The Welcome to the Cube Wizard Dialog Appears ...

3.  Click Next.

4.  Click the Build the cube without using a data source radio button on the Select Build Method page that appears next.

5.  Click the checkbox just underneath the radio button we just checked, labeled Use a cube template.

6.  Ensure that Adventure Works Standard Edition is selected in the template selector list.

The Select Build Method page appears as shown in Illustration 9.


Illustration 9: Our Settings on the Select Build Method Dialog

These settings tell the Wizard that we have chosen to create our cube using a template, but without basing it upon an actual database. Using a template allows us to design a cube based upon the structure, including dimensions and measures, of an already existing cube, saving significant time and effort.

7.  Click Next.

8.  In the next page, Define New Measures, click the left corner box in the header row of the section labeled Select measures from template (in the upper half of the page), as depicted in Illustration 10.


Illustration 10: Clearing the Measures Selected by Default ...

This clears selection of the measures from the cube we have selected as a template. By default, all measures are selected.

9.  Select Internet Sales Amount (the top measure in the section labeled Select measures from template) by clicking the checkbox to its left.

10.  Click Next.

The Review Shared Dimensions page appears next. Here we see available shared dimensions that exist within the UDM, but which have yet to be selected as cube dimensions. We will not include these shared dimensions in our cube.

The Review Shared Dimensions page appears as shown in Illustration 11.


Illustration 11: The Review Shared Dimensions Page

11.  Click Next.

12.  On the next page, Define New Dimensions, click the left corner box in the header row of the section labeled Select dimensions from template (in the upper half of the page), as depicted in Illustration 12.


Illustration 12: Clearing the Dimensions Selected by Default ...

This clears selection of the dimensions (all of which were initially selected by default) from the template cube we have selected as a template.

13.  Select Customers (the second dimension from the top in the section labeled Select dimension from template) by clicking the checkbox to its left.

14.  In like manner, select Products, two rows below (the fourth row from the top).

15.  Similarly, select Time (the tenth row from the top).

While the measure and two of the dimensions we have selected are merely to give our cube some substance, the Time dimension we have selected above is the key focus of this session. (We will assume, for our immediate purposes, that the default attributes and settings for each dimension will be acceptable.)

The Define New Dimensions page appears, with our selections, as shown in Illustration 13.


Illustration 13: The Define New Dimensions Page with Our Selections

Our next steps will take us into the “focus” portion of our practice exercise: working with a Time dimension in Analysis Services 2005.

Procedure

Work with a Time Dimension

Continue Basic Cube Creation – Time Dimension

1.  Click Next.

The Define Time Periods page appears next. Here we will define the date range within which our cube data will exist.

2.  Click the default Year value (2004) in the First calendar day setting to select it, as depicted in Illustration 14.


Illustration 14: Selecting the Year in the First Calendar Day Setting for Modification ...

3.  Type “2002” over the currently existing 2004.

4.  Click the downward-pointing arrow on the right side of the Last calendar day setting box, just underneath the First calendar day setting, to open the calendar.

5.  Using the “right” button atop the calendar selector, scroll forward in time to December 2007.

6.  Select December 31, 2007 on the drop-down calendar, as shown in Illustration 15.


Illustration 15: Selecting Last Calendar Day of the Time Period Range ...

7.  Leave the First day of the week setting at its default of Sunday.

8.  Select (via the respective checkboxes) the following attributes within the Time periods pane:

  • Year
  • Half Year
  • Quarter
  • Month
  • Date (key dimension attribute - required)

The Define Time Periods page appears, with our selections, as depicted in Illustration 16.


Illustration 16: The Define Time Periods Page, with Our Selections

9.  Click Next.

10.  Check the box to the left of the first option, Fiscal calendar, on the Specify Additional Calendars page that appears next.

11.  Set Start day and month (the selectors beneath the Fiscal calendar checkbox we have checked) as follows:

  • Day: 1 (the default)
  • Month: April

12.  Leave Fiscal calendar naming convention set at default of Calendar year name + 1.

The Specify Additional Calendars page appears, with our settings, as shown in Illustration 17.


Illustration 17: The Specify Additional Calendars Page, with Our Settings

13.  Click Next.

We arrive at the Define Dimension Usage page. Here we see that the Internet Sales measure group will be related to the dimensions we have selected within our cube. The Define Dimension Usage page appears as depicted in Illustration 18.


Illustration 18: The Define Dimension Usage Page

NOTE: For more information about Measure Groups and Dimension Usage in general, see my article Mastering Enterprise BI: Working with Measure Groups.

14.  Click Next.

The Completing the Wizard page appears next.

15.  Replace the default Cube name with the following:

DBJ_Adventure Works

16.  Expand the Date dimension in the Preview pane (named Date 1, by default), by clicking the “+” sign to its immediate left.

17.  Expand the Hierarchies folder that appears within the Date 1 dimension.

Here we can see both the “cloned” and alternate Date hierarchies we have established via the Wizard.

The Completing the Wizard page appears, with our expansion, as shown in Illustration 19.


Illustration 19: The Completing the Wizard Page

18.  Click Finish.

The cube is generated, and opens immediately, within the Cube Designer - Cube Structure tab, as depicted in Illustration 20.


Illustration 20: The New Cube Opens, Cube Designer – Cube Structure Tab

The cube also appears within the Solution Explorer, as shown in Illustration 21.


Illustration 21: The New Cube in the Solution Explorer

Examine the Structure of the New Time Dimension

Having created our cube, we can overview the Time dimension we have added, to ascertain that the structure appears to meet our needs.

1.  Within the Dimensions pane (in the lower left corner of the Cube Structure tab), expand the Date 1 dimension (named Date 1 by default, since a dimension named Date already exists within the UDM), by clicking the “+” sign to its immediate left.

Here we can see both the “cloned” and alternate Date hierarchies, as before, as depicted in Illustration 22.


Illustration 22: The Date Dimension (“Date 1”) in the Dimensions Pane ...

2.  Click Edit Date 1, which appears just under the expanded Date 1 dimension.

The Dimension Structure tab for the new Date dimension opens, and appears similar to that shown in Illustration 23.


Illustration 23: The New Date Dimension within the Dimension Structure Tab (Compressed View)

Here we can see details about the makeup of the dimension, including Attributes, Hierarchies and Levels, and so forth. We can also examine, and modify as desired, the Properties for each respective object. We will not make any changes for the purposes of our practice exercise; we will leave everything as defined at this stage.

Generate the Underlying Schema for the Cube Model

Because we have designed our cube in “top-down” fashion, it has no underlying relational database, and, therefore, no underlying data. The next step in a typical design and development evolution (that is, when using the “top-down” approach) would be to generate the underlying relational schema, and then to populate it with data (which would usually be loaded via extraction, transformation, and loading (ETL) processes) from one or more operational / transactional / other external data sources.

We will generate a schema with the rudimentary cube that we have, as a means of illustrating the process; in some cases, as we have noted, this might provide a quick and easy means of generating a Time dimension table which is based upon a structure we have designed within the cube model. This “push-down” method of table construction is not only quick and easy; it assists us in building a table that is guaranteed to support our dimensional design, once populated appropriately (and, as we shall see, population can be set to occur automatically). Once we had generated a table to support the Time dimension, say in a target database created especially to contain such a table, we could move the table(s) to another place, delete the empty tables, etc., as our needs dictated.

1.  Within the Cube Structure tab, click the link Click here to generate data source view, appearing in the middle of the Diagram pane, as depicted in Illustration 24.


Illustration 24: “Click Here to Generate Data Source View ...”

The initial page of the Schema Generation Wizard appears, as shown in Illustration 25.


Illustration 25: The Schema Generation Wizard Initializes ...

2.  Click Next.

3.  On the Specify Target page that appears next, click New.

The first page of the Data Source Wizard (titled Welcome to the Data Source Wizard) opens next, and appears as depicted in Illustration 26.


Illustration 26: The Data Source Wizard is Launched ...

4.  Click Next.

5.  Click New on the Select how to define the connection page that appears next, as shown in Illustration 27.


Illustration 27: Create a Data Source Based upon a New Connection

The Connection Manager appears.

6.  Type / select the correct server / instance name into the Server name box of the dialog.

7.  Ensure that authentication settings are correct for the local environment.

8.  In the Connect to a database section, under Select or enter a database name, select DBJ_Schema_Target, the target database we created in our preparation section earlier.

The Connection Manager appears, with our input, as depicted in Illustration 28.


Illustration 28: The Connection Manager with Our Input

9.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 29.


Illustration 29: Testing Positive for Connectivity ...

10.  Click OK to dismiss the message box.

11.  Click OK to accept changes, and to dismiss the Connection Properties dialog.

We return to the Select how to define the connection page of the Data Source Wizard, where we see our new data connection appearing in the Data connections list, as depicted in Illustration 30.


Illustration 30: The Select How to Defined the Connection Page – New Data Connection

12.  Click Next.

13.  Click / select the radio button labeled Use the service account on the Impersonation Information page that appears next, as shown in Illustration 31.


Illustration 31: The Impersonation Information Page of the Data Source Wizard

14.  Click Next, once again.

15.  On the Completing the Wizard page of the Data Source Wizard, upon which we arrive next, leave the Data source name with the default of DBJ Schema Target, as depicted in Illustration 32.


Illustration 32: The Completing the Wizard Page ...

16.  Click Finish to complete the steps of the Data Source Wizard, completing the creation of the new data source.

We are returned to the Specify Target page of the Data Source Wizard, where we see our new data source appearing in the Data source selector, underneath the Data source view name, which is defaulted to DBJ_Adventure Works DW.

17.  Leave the proposed Data source view name at its default of DBJ_Adventure Works DW.

18.  Ensure that DBJ_Schema Target is selected within the Data source selector, just underneath the Data source view name box.

The Specify Target page of the Schema Generation Wizard appears, with the settings described, as shown in Illustration 33.


Illustration 33: The Specify Target Page of the Schema Generation Wizard ...

Here we are establishing the target database for the output of the Schema Generation Wizard. We will except the defaults and continue with schema generation.

19.  Click Next.

The Subject Area Database Schema Options page appears. We will leave the Owning schema name at default (the name of the Analysis Services project within which we are working).

(As an aside, were we targeting an existing database which already had tables, we could be assured that the tables that we are about to create would remain physically independent from those that already exist within the sample Adventure Works DW database. This would allow us to easily isolate the new tables from those supporting other cubes, etc., affording an easy move / delete action at any point in time after creation, if desired.)

Because this is a practice session, we will leave the checkboxes for the various options selected. The settings might differ within a real world situation, depending upon our intended use of the output of the Schema Generation Wizard. Of particular note is the Populate time table(s) selector setting in the lower part of the page: the default setting is Populate, which meets our immediate purposes precisely. We wish to practice the generation of a Time table design from within the cube model that it will ultimately support. As we have noted, this byproduct of the “top-down” design approach can save time and effort in generating such a (populated) table - the table that we know will support the intended dimension, because it is “reverse engineered” from the very specifications of that dimension.

Within the stated context, the Subject Area Database Schema Options page appears as shown in Illustration 34.


Illustration 34: The Subject Area Database Schema Options Page...

20.  Click Next.

We arrive at the Specify Naming Conventions page of the Schema Generation Wizard, which appears as depicted in Illustration 35.


Illustration 35: The Specify Naming Conventions Page of the Schema Generation Wizard ...

While we would likely make adjustments to naming conventions to otherwise fit our individual environments and policies, we will leave them at default for the purposes of our practice exercise.

21.  Click Next.

We are given an opportunity to verify options settings on the Completing the Wizard page that appears next, as shown in Illustration 36.


Illustration 36: The Completing the Wizard Page of the Schema Generation Wizard

22.  Click Finish.

The Schema Generation Progress viewer appears, and logging of events begins. We note that several warning messages appear, informing us that various dimensions were not generated, due to their being “bound to a user table.” (This is expected for the dimensions existing within the template that we did not select earlier for adoption, as part of our new cube design). We note that the message Generation Completed Successfully appears (in green text) in the status area in the bottom left corner of the viewer, once generation is completed. We also note that an event is logged within the viewer informing us of the Committing changes to subject database event.

The Schema Generation Progress viewer appears, upon completion of schema generation, as depicted in Illustration 37.


Illustration 37: The Schema Generation Progress Viewer at Completion ...

23.  Click Close to dismiss the Schema Generation Progress viewer.

We are returned to the Cube Structure tab within the Designer. We see a new Data Source View, reflecting the dimension and fact tables we have defined for our new cube, which appears as shown in Illustration 38.


Illustration 38: The New Data Source View Appears ...

We have now created our cube, as well as its underlying Data Source View. Moreover, we have created the tables that support our Data Source View within the designated target database, as we shall see in the section that follows.

Verification

Review the New Time Dimension Structure and Underlying Table

Review the Time Dimension within the Designer

1.  Right-click the newly created Date dimension (Date 1.dim) within Solution Explorer.

2.  Click Open from the context menu that appears, as depicted in Illustration 39.


Illustration 39: Opening the Dimension Designer for the New Date Dimension

The Dimension Designer for Date 1.dim opens, defaulting to the Dimension Structure tab. Here are presented the Attributes, Hierarchies and Levels, and Data Source View panes, as shown in Illustration 40.


Illustration 40: Panes of the Dimension Structure Tab

From this perspective, we can examine virtually any detail of the structure of the new Date dimension. In addition, from the Data Source View pane, we can at least partially verify the fact that, not only has an underlying table has been created, but the table has been populated with data, based upon the date ranges we provided when creating our cube.

3.  Right-click the Date_1 table appearing within the Data Source View pane of the Dimension Structure tab.

4.  Select Explore Data from the context menu that appears, as depicted in Illustration 41.


Illustration 41: Select Explore Data ...

A sample portion of the data loads, and the Explore Date_1 Table tab appears. Various tools present themselves within its sub tab region (including the default Table tab, a Pivot Table tab, a Chart tab, and a Pivot Chart tab), as shown in Illustration 42.


Illustration 42: The Explore Date_1 Table Tab (Partial View), Displaying Data from the Table

5.  When finished examining the data, right-click the Explore Date_1 Table tab.

6.  Select Close from the context menu that appears, as depicted in Illustration 43.


Illustration 43: Closing the Explore Date_1 Table Tab

Having examined various aspects of the support for the new Date dimension within the design environment, we will next take a look at the underlying table within the Database Engine. We will accomplish this in the section that follows.

Review the Generated Schema, and the Table Supporting the Date Dimension, within MSSQL Server

One of the objectives of our practice session was to demonstrate the ease with which we can generate a schema (and, within our focus upon the Time dimension, a populated Date table for its support) that we design “from the top down” within Analysis Services. As a part of preparation, we created a target database within which to generate the schema that we designed. At this point, we will return to SQL Server Management Studio, to verify the creation of the tables involved, including the populated Date table, by taking the following steps:

1.  Click the Start button on the PC.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as we did in our preparatory steps earlier.

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Database Engine in the Server type selector, as we did before.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector, once again.

6.  Supply the required authentication information.

7.  Click the Connect button to connect with the specified SQL Server Database Engine server, as we did in accessing the Management Studio earlier.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane, expand the Databases folder appearing underneath the Database server within which we are working, once again.

The Databases folder opens, exposing the detected databases. We see the DBJ_Schema_Target database appear among those shown, as shown in Illustration 44.


Illustration 44: The DBJ_Schema_Target Database, among Other Databases within the Object Explorer

9.  Using the “+” sign to its immediate left, expand the DBJ_Schema_Target database.

10.  In like manner, expand the Tables folder that appears within the expanded DBJ_Schema_Target database, as necessary.

The tables within the folder appear, as depicted in Illustration 45.


Illustration 45: The Tables of the DBJ_Schema_Target Database Appear ...

We see that the target database contains each of the tables that we defined within the BI Development Studio. Let's take a look at the Date table, both from a structural viewpoint (as an example of the schema generated), and also because we expect it to be completely populated, based upon our settings within the Schema Generation Wizard in the procedures above.

11.  Right-click the table DBJ_Adventure Works DW.Date_1 within the Tables folder of the expanded DBJ_Schema_Target database.

12.  Select Modify from the context menu that appears, as shown in Illustration 46.


Illustration 46: Select Modify to Examine Table Structure ...

A tab named Table - DBJ_Adventure Works DW.Date_1 opens, displaying Column Names, Column Properties, and other information about the table. A portion of the tab appears as depicted in Illustration 47.


Illustration 47: Details for the New Date Table (Partial View)

Here, we might make modifications to this mechanically generated table, in scenarios where the local environment requires different settings, data types, and the like. We still would have enjoyed the benefit of rapid construction of the table, even if we faced a need to make modifications to some of the columns, etc.

13.  Right-click the table DBJ_Adventure Works DW.Date_1 within the Tables folder of the expanded DBJ_Schema_Target database, once again.

14.  Select Open Table from the context menu that appears, as shown in Illustration 48.


Illustration 48: Select Open Table to View the Populated Table ...

Another tab named Table - DBJ_Adventure Works DW.Date_1 opens, this time to the left of the tab that appeared previously, and displaying the table itself, together with the data that it contains. The new tab appears as partially depicted in Illustration 49.


Illustration 49: Data View of the New Date Table (Partial View)

The data view confirms that the table has, indeed, been populated. We can easily scroll down the tab to ascertain that the date ranges we have requested have been generated. We can modify the table as needed to “tune” it further to meet our immediate needs - or perhaps even use it as a basis for a Time dimension within a totally unrelated data warehouse or mart - having enjoyed the benefits, in the meantime, of full design and generation from a graphical user interface. These options alone present a compelling motivator for use of the Schema Generation Wizard in Analysis Services 2005.

15.  Further inspect the properties and data of the new Date dimension table, as desired.

16.  Select File -> Exit, when ready, to leave the SQL Server Management Studio as shown in Illustration 50.


Illustration 50: Exiting the SQL Server Management Studio

17.  Return to the BI Development Studio.

18.  Further inspect newly created Date (and other) dimensions, as well as the related Data Source View, together with their properties and other settings. Perform additional browses, as desired.

19.  Select File -> Save All from the main menu, to save our work through this point, as shown in Illustration 51.


Illustration 51: Saving All Work from Our Session

20.  Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.

Conclusion

In this article, we explored various unique characteristics that distinguish a Time dimension from other dimensions within our cube models, as well as the special attention that has been given to the Time dimension, in general, within the design of enterprise business intelligence applications. We then narrowed our focus to the topic of this and the subsequent article: how this support has been enhanced in Analysis Services 2005;

We next performed preparation steps for the hands-on practice session that was to follow, creating an Analysis Services Project within which to perform the anticipated steps, and creating a target database within SQL Server Management Studio for the schema generation procedure we would also perform. We then concluded preparation by ascertaining connectivity of the relational data source, along with other preparatory procedures, within the new Analysis Services 2005 Project.

We next began the creation of a rudimentary cube via the “top down” approach (with no underlying data source in place), containing a Date dimension, upon which to base our general examination of a Time dimension. Once this was completed, we examined the structure of the new Time dimension within the design environment, discussing its various characteristics and settings. We then generated the underlying relational schema for the new cube model, including the generation of a Time table design, as well as its subsequent population, from within the cube model that it was designed to support. Finally, we reviewed the new Date dimension within the Dimension Designer, and then examined the generated schema, focusing upon the populated table supporting the Date dimension, within SQL Server Management Studio.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved