Dimensional Model Components: Dimensions Part I

Thursday Jan 24th 2008 by William Pearson
Share:

Business Intelligence Architect Bill Pearson launches a new subseries surrounding components of the Analysis Services dimensional model. In this two-part article, we introduce dimensions, and focus upon property settings for Database Dimensions.

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 portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

Introduction

The general consensus within current data warehousing and business intelligence communities is that the preferred structure for presenting quantitative and other organizational data to information consumers lies within the dimensional model. The dimensional model has consistently been shown to meet the primary objectives of business intelligence: to present relevant and accurate information representing business operations and events, and to return query results rapidly and accurately. The dimensional model is particularly optimal in its support of “slice and dice” query creation and modification; it supports an environment wherein information consumers can pose quick and easy questions, and achieve rapid results datasets, using various perspectives called dimensions.

In this, Part I of a two-part article, the first of several articles focusing upon dimensional model component structures, we will gain an introduction, with hands-on exposure, to dimensions within a sample cube. Our examination will include:

  • An introduction to the Dimensional Model and a discussion of its role in meeting the primary objectives of business intelligence.
  • An introduction to Dimensions from a general perspective, including the two primary dimension types within Analysis Services.
  • An examination of the Database Dimensions and the Cube Dimensions that exist within our sample UDM, together with a discussion surrounding the differences between the two dimension types.
  • Coverage of the general concepts and properties (including what they define and support, and how we can manage them) underpinning Database Dimensions.
  • A review of the Properties associated with a Database Dimension, based upon the examination of a representative dimension within our sample UDM.
  • A look ahead to Part II of this article, where we explore the Properties associated with a representative Cube Dimension.

Dimensions in Analysis Services

Dimensions form the foundation of the dimensional model. They represent the perspectives of a business or other operation, and reflect the intuitive ways that information consumers need to query and view data. To use an analogy, if we consider the facts or measures contained within our business intelligence system as verbs (or actions / transactions undertaken by the business), then the dimensions might be considered as nouns that take part in, or are otherwise associated with, those verbs. Common examples of dimensions include time (or date), customer, product, geography, lab type, campus, patient, promotions, gender (and other demographics), and so forth. Each dimension is associated with the facts / measures to which it relates via the linkages / joins between the table(s) housing the dimension (the dimension table) and the fact table. As in illustration (found within the Adventure Works sample environment), the link / join between the DimCustomer table and the FactInternetSales table reflects that the Customer dimension participates in sales, order quantities, discounts, freight, and various other measures.

Because we typically support each dimension with an underlying table(s) within a database, we commonly see a complete, discrete list of dimension members within each table. (Within each single member row are also included various attributes that describe, classify, and otherwise act as adjectives to the dimension noun. We will examine dimension attributes in subsequent articles of this subseries and elsewhere within my Introduction to MSSQL Server Analysis Services, and other Database Journal, series’.)

We can identify dimensions as a part of business requirements gathering when we discuss the perspectives by which information consumers within the organization wish to be able to analyze, or report upon, the activities, results, and other facets of business operation. As illustration, we might determine early in our requirements gathering efforts that information consumers need to see Internet Sales by geography, by month, by product, or by a combination of these perspectives, among others. Such perspectives need to be included within our design, within the appropriate dimension tables (or snowflake tables linked to those tables, as appropriate).

We will get some hands-on exposure to dimensions in the practice session below. Before we get started working within a sample cube clone, we will need to prepare the local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Create a Sample Basic UDM within Which to Perform the Steps of Our Practice Session

Create Sample Primary Analysis Services UDM for the Practice Exercise

Before getting started with our practice session, we will need a basic sample Analysis Services UDM (containing a cube and other objects) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:

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

Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:

Ascertain Connectivity of the Relational Data Source

Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for those appropriate to your own environment, when following the procedure):

Deploy the Analysis Services Project

Ascertain that the New Practice Database is in Place and Rename It

We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.

1.  Start Microsoft SQL Server Management Studio.

2.  Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.

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

4.  Supply authentication information, as required in your own environment.

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


Illustration 1: Connecting to the Server ...

5.  Click the Connect button to connect with the specified Analysis Server.

The Microsoft SQL Server Management Studio opens.

6.  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 Analysis Server within which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.

7.  Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.


Illustration 2: Renaming the Sample Database

8.  Rename the Analysis Services database as follows:

ANSYS065_Basic AS DB

9.  Exit SQL Server Management Studio when ready.

Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.

Procedure: Examine Dimension Properties in Analysis Services 2005

In the practice procedures that follow, we will examine the properties, which we have introduced in the foregoing sections of the article. We will examine the properties for a representative database dimension within this section of this, Part I, of the article; we will cover the properties that specifically define and support Cube dimensions within the similar procedural section of Part II. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will perform our overviews of dimensions within our new Analysis Services database, ANSYS065_Basic AS DB.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Analysis Services Database ... from the cascading menu, as depicted in Illustration 3.


Illustration 3: Opening the Analysis Services Database ...

The Connect to Database dialog appears.

6.  Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.

7.  Using the selector just beneath, labeled Database, select ANSYS065_Basic AS DB, as shown in Illustration 4.


Illustration 4: Selecting the New Basic Analysis Services Database ...

8.  Leaving other settings on the dialog at default, click OK.

SQL Server Business Intelligence Development Studio briefly reads the database from the Analysis Server, and then we see the Solution Explorer populated with the database objects. We will contrast the dimension types, and then focus upon Database dimensions using the Cube Designer from within our new sample UDM.

Contrast Cube Dimensions with Database Dimensions

Let’s examine both Database dimensions and Cube dimensions in general, mostly to gain an understanding of the differences between the two, before proceeding to our overview of the properties of Database dimensions. (We will overview properties for Cube dimensions in Part II of this article.)

1.  Within the Solution Explorer, expand the Dimensions folder (by clicking the “+” sign to its immediate left), if necessary, to expose the Database dimensions for our new sample UDM.

The Database dimensions appear as depicted in Illustration 5.


Illustration 5: The Database Dimensions of Our Sample UDM

We notice that eight Database dimensions appear within the Dimensions folder of the Solution Explorer.

2.  Within the Solution Explorer, right-click the Basic cube (expand the Cubes folder as necessary).

3.  Click Open on the context menu that appears, as shown in Illustration 6.


Illustration 6: Opening the Cube Designer ...

The tabs of the Cube Designer open.

4.  Click the Cube Structure tab, if it has not already appeared by default.

5.  Examine the Cube dimensions that appear within the Dimensions pane of the Cube Structure tab.

The Cube dimensions belonging to our Basic cube appear as depicted in Illustration 7.


Illustration 7: The Cube Dimensions, Basic Cube

We notice that ten Cube dimensions appear within the Dimensions pane of the Cube Structure tab. The difference in number between the Cube dimensions and the Database dimensions we saw earlier reflects a powerful capability that accrues to dimensional model designers that use Analysis Services 2005 and beyond: we can reuse single Database dimensions for multiple Cube dimensions. The benefits we enjoy in doing so include simplified dimension management, reduced overall processing time, and usage of less disk space by our deployed models.

The Basic cube contains more dimensions than the Analysis Services database because three separate Cube dimensions that relate to time / date (and which are based on different time-related facts in the fact table) share the Time Database dimension as their bases. These three Cube dimensions represent Role-playing dimensions within our cube, which allow information consumers to dimension the cube from three separate sales-related date perspectives: the date a given product was ordered, the due date that was applicable for fulfillment of the order, and the date that the order was actually shipped.

NOTE: We explore Role-playing dimensions in other articles of my Introduction to MSSQL Server Analysis Services series at Database Journal.

An important fact to grasp when considering the differences between Database dimensions and Cube dimensions is that different properties exist for each dimension type, even though the former serves as the basis for the latter. All other dimensions within the model derive from the Database dimension. The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.

To optimize the numerous properties settings that are available to us within our design and implementation efforts, we need to understand the different settings that are made in each of the Database and Cube dimensions. These settings become confusing to many who are new to Analysis Services, and so we will examine them individually beginning with our next section, where we will take up the few properties involved with Database dimensions. We will examine the properties for Cube dimensions in Part II of this article.

Review Database Dimension Properties

We can modify, or even delete, a Database dimension by using the Dimension Designer within the Business Intelligence Development Studio. Let’s take a look at the properties in place for a Database dimension within our new sample UDM by taking the following steps.

1.  Within the Solution Explorer, right-click the Product dimension (expand the Dimensions folder as necessary).

2.  Right-click the Product dimension.

3.  Click Properties on the context menu that appears, as to shown in Illustration 8.


Illustration 8: Select Properties from the Context Menu ...

The Properties pane appears for the Product dimension. (The Properties pane likely appeared already, by default, below the Solution Explorer. The design environment can, of course, be customized in many ways to accommodate your local environment and development needs.)

We can see the two Object Model properties for the Product dimension within the Properties pane. These are, quite simply, Object ID and Object Name. The Properties pane for the Product Database dimension appears as depicted in Illustration 9.

Illustration 9: The Properties Pane for the Product Database Dimension

Object Name can be modified within the Solution Explorer by simply using the Rename selection within the context menu that we accessed in the last step above.

We will extend our examination of dimensions yet further in the second half of this article, where we will examine Cube dimensions and the more numerous properties that are associated with them.

NOTE: Please consider saving the project we have created to this point for use in the second half of this article, as well as for subsequent related articles within this subseries, so as to avoid the need to repeat the preparation process we have undertaken above.

4.  Select File -> Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for Part II of this article, as well is for other articles within this subseries.

5.  Select File -> Exit to leave the design environment, when ready, and to close the Business Intelligence Development Studio.

Conclusion

In this, Part I of a two-part article, we began a subseries of several articles focusing upon dimensional model components, with an objective of discussing the associated concepts, and of providing hands-on exposure to the properties supporting each. We began with an introduction to the dimensional model and a discussion of its role in meeting the primary objectives of business intelligence. Next, we introduced dimensions in general, including the two primary dimension types within Analysis Services, Database dimensions and Cube dimensions.

As a means of distinguishing the two types of dimensions, we examined both within the design environment. We compared the Database dimension type to the Cube dimension type, and explained differences in the number of each occurring within our sample model. We then reviewed the Properties associated with a Database Dimension, based upon the examination of a representative dimension within our sample UDM. Finally, we looked forward to the second half of this article, where we explore the general concepts and properties (including what they define and support, and how we can manage them) underpinning Cube Dimensions.

» 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