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

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

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:

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:

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

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

The SQL Server Management Studio opens.

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

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.

The New Database page appears.

DBJ_Schema_Target

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

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

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