Mastering Enterprise BI: Time Intelligence Pt. I - Page 2
February 12, 2007
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.
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:
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.
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.
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.
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.
The New Database page appears.
11. Type the following into the Database name box at the top of the New database page:
The relevant portion of the New Database page appears, with our input, as shown in Illustration 5.
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.
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.