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.
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
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:
a Target Database for the Schema Generation Procedure
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:
Click the Start
button on the PC.
SQL Server 2005 within the Program group of the menu.
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.
Engine in the Server type selector.
Type / select
the server name (server name / instance, if appropriate) in the Server name
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 ...
Click the Connect
button to connect with the specified SQL Server Database Engine
The SQL Server
Management Studio opens.
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
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.
the Databases folder that we just expanded.
Database ... from the context menu that appears, as depicted in Illustration
4: Select New Database ...
The New Database
following into the Database name box at the top of the New database
The relevant portion of
the New Database page appears, with our input, as shown in Illustration
Illustration 5: The New
Database Page (Partial View), with Database Name ...
Click OK to
create the new database.
The new database appears
within Object Explorer, among the other databases.
-> 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.
Return to the Business
Intelligence Development Studio.