Dimensional Model Components: Dimensions Part II

Friday Feb 15th 2008 by William Pearson
Share:

Business Intelligence Architect Bill Pearson continues his subseries surrounding components of the Analysis Services dimensional model. In the second half of this article, we extend our focus on dimensions to include property settings for Cube 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

In Dimensional Model Components: Dimensions Part I, we noted that 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. We discussed several reasons why this is the case, including the fact that the dimensional model, through its use of various perspectives called dimensions, meets the primary objectives of business intelligence in numerous ways, including its capacity to support:

  • the presentation of relevant and accurate information representing business operations and events;
  • the rapid and accurate return of query results;
  • “slice and dice” query creation and modification;
  • an environment wherein information consumers can pose quick and easy questions, and achieve rapid results datasets.

In this, the second half of our two-part examination of dimensions (which itself represents the first of several articles focusing upon dimensional model component structures), we will gain further introduction, with hands-on exposure, to dimensions within a sample cube. Our examination will include:

  • A brief review of Dimensions from a conceptual perspective, including the two primary dimension types within Analysis Services that we discovered in Part I;
  • Coverage of the general concepts surrounding Cube Dimensions;
  • An examination of the properties (including what they define and support, and how we can manage them) underpinning Cube Dimensions.
  • A look ahead to other articles of this subseries, where we explore the attribute, hierarchy, and other components of the dimensional model as implemented by Analysis Services.

Dimensions in Analysis Services: Cube Dimensions

We learned, in Dimensional Model Components: Dimensions Part I, that 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. We noted that we might consider dimensions as nouns that take part in, or are otherwise associated with, the verbs (or actions / transactions undertaken by the business) that are represented by the facts or measures contained within our business intelligence system. We cited common examples, such as time (or date), customer, product, lab type, campus, patient, gender (and other demographics), and discussed how 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(s) ) and the fact table.

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, comparing the Database dimension type to the Cube dimension type. We then focused upon Database Dimensions, reviewing the Properties associated with a representative Database Dimension within our sample UDM.

In this lesson we will concentrate on the other of the two main dimension types we introduced in Part I, the Cube dimension. As we have already discovered, the multidimensional structure of a cube is defined by the dimensions that it contains. The Database dimensions within a given UDM can often be used to define a cube that we create therein, but the Cube dimensions concept affords us the capability to dictate that the dimensions available within such a cube can be a subset of the Database dimensions, and that the structure of the cube can therefore be limited to those dimensions that we define as being visible or used within the cube. Cube dimensions exist at a lower object level than the Database dimensions and the cube itself.

Preparation: Locate and Open the Sample Basic UDM Created in Pt I

In Part I, we created a sample basic UDM within which to perform the steps of the practice sessions we set out to undertake in the various articles of this subseries. Once we ascertained the new practice database appeared to be in place, and once we had renamed it to ANSYS065_Basic AS DB, we began our examination of dimension properties. We will perform our examination of Cube dimensions within the same practice environment, which we will access using the following steps within the SQL Server Business Intelligence Development Studio, as we did within Part I.

NOTE: Please access the UDM which we prepared in Part I before proceeding with this article. If you have not completed the preparation to which I refer in the previous article, or if you cannot locate / access the Analysis Services database with which we worked there, please consider taking the preparation steps provided in Part I before continuing, and saving the objects with which you work, so as to avoid the need to repeat the preparation process we have already undertaken for subsequent related articles within this subseries.

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


Illustration 1: 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 2.


Illustration 2: 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 review the dimension types, and then focus upon Cube dimensions using the Cube Designer from within our sample UDM.

Procedure: Examine Dimension Properties in Analysis Services 2005

In the practice procedures that follow, we will examine the properties that define and support a representative Cube dimension within this section of this, Part II, of the article. 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.

In Part I, we began the Practice session with a general examination of both Database dimensions and Cube dimensions, mostly to gain an understanding of the differences between the two, before proceeding to our overview of the properties of Database dimensions. In this article, we will overview the properties underpinning Cube dimensions. While we can access the Database dimensions from our present position, via the Dimensions folder within the Solution Explorer, to access the Cube dimensions we will need to open the cube first.

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

2.  Click Open on the context menu that appears, as depicted in Illustration 3.


Illustration 3: Opening the Cube Designer ...

The tabs of the Cube Designer open.

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

4.  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 shown in Illustration 4.


Illustration 4: The Cube Dimensions, Basic Cube

We noted, in Part I, the difference in the number of Cube dimensions appearing here, within the Dimensions pane of the Cube Structure tab (ten), and the number of Database dimensions appearing within the Solution Explorer (eight). We discussed the reason for the difference within our sample environment: three separate Cube dimensions that relate to time / date (and which are based upon different time-related facts in the fact table) share the Time Database dimension as their bases. We further noted that 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. Moreover, we emphasized the power that this capability affords us as dimensional model designers: we can reuse single Database dimensions for multiple Cube dimensions. The benefits we enjoy in doing so, we concluded, includes simplified dimension management, reduced overall processing time, and usage of less disk space by our deployed models.

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

Because different properties exist for each of the Database and Cube dimensions (even though the former serve as the basis for the latter) dimension types, we need to examine each set of properties separately. Having already examined the two (Name and ID) properties associated with the Database dimension, we will pick up where we left off in Part I and concentrate our efforts in this article upon the examination of the properties associated with Cube dimensions.

Review Cube Dimension Properties

A Cube dimension is an instance of the Database dimension within a cube. A Database dimension can be used in multiple cubes, and multiple Cube dimensions can be based on a single Database dimension. The concept, at least, is very similar to that of using shared dimensions within various cubes in an Analysis Services 2000 environment.

As we have seen in other articles of my Introduction to MSSQL Server Analysis Services series, we can define Cube dimensions within the Business Intelligence Development Studio by using either the Cube Wizard or Cube Designer. We can add or delete a Database dimension to a given cube via the Cube Structure tab of the Cube Designer, where we configure Cube dimensions within the Dimensions pane. We can accomplish these actions via the context menu that becomes available when we right-click a dimension within the Dimensions pane, or when we select Cube on the toolbar atop the development environment, as depicted in composite Illustration 5.


Illustration 5: Two Options for Working with Cube Dimensions within the Cube Designer (Composite)

In Part I, we noted the importance of optimizing the numerous properties settings that are available to us within our design and implementation efforts, and emphasized the value of a thorough understanding of the different settings that are made in each of the Database and Cube dimensions. We examined the few properties involved with Database dimensions in Part I, and will examine the more numerous properties for Cube dimensions within our sample UDM by taking the following steps.

1.  Within the Dimensions pane of the Cube Structure tab, right-click the Product dimension.

2.  Click Properties on the context menu that appears, as shown in Illustration 6.


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

The Properties pane appears for the Product dimension. (The Properties pane likely appeared when we selected the Product dimension within the Dimensions pane, 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, at this stage, see the eight Object Model properties for the Product Cube dimension within the Properties pane. The first five, Advanced properties include the following:

  • AllMemberAggregationUsage
  • DimensionID
  • HierarchyUniqueNameStyle
  • MemberUniqueNameStyle
  • Visible

The three Basic properties, appearing underneath the Advanced properties group, include the following:

  • Description
  • ID
  • Name

The Properties pane for the Product Cube dimension, with Advanced and Basic properties groups expanded, appears as depicted in Illustration 7.


Illustration 7: The Properties Pane for the Product Database Dimension

Let's take a look at each of the individual properties, starting with the Advanced properties group, examining the possible settings with which we can come into contact.

Advanced Property: AllMemberAggregationUsage

The AllMemberAggregationUsage property governs the manner in which the Aggregation Designer designs aggregations within Analysis Services.

3.  Click the input box to the right of the box marked AllMemberAggregationUsage within the Properties pane, to enable the selector for the setting.

The selector for the AllMemberAggregationUsage property displays our selection options as shown in Illustration 8.


Illustration 8: The Available Settings for the AllMemberAggregationUsage Property

The values we might assign to the AllMemberAggregationUsage property field from the selector are as follows:

  • Full: Every cube aggregation is to include the All member.
  • None: No cube aggregation is to include the All member. (This is the default value.)
  • Unrestricted: No restrictions are placed upon the Aggregation Designer
  • Default: The same functionality as Unrestricted.

Advanced Property: DimensionID

The DimensionID property contains the unique identifier assigned to the underlying Database dimension. This property cannot be altered, and is therefore “grayed” within the property settings.

Advanced Property: HierarchyUniqueNameStyle

The HierarchyUniqueNameStyle property dictates the manner in which unique names are generated for hierarchies contained within the Cube dimension.

4.  Click the input box to the right of the box marked HierarchyUniqueNameStyle within the Properties pane, to enable the selector for the setting.

The selector for the HierarchyUniqueNameStyle property displays our selection options as depicted in Illustration 9.


Illustration 9: The Available Settings for the HierarchyUniqueNameStyle Property

The values we might assign to the HierarchyUniqueNameStyle property field the selector are as follows:

  • IncludeDimensionName: The dimension name is to be included as part of the name of the hierarchy. (This is the default value.)
  • ExcludeDimensionName: The dimension name is to be included as part of the name of the hierarchy.

Advanced Property: MemberUniqueNameStyle

The MemberUniqueNameStyle property dictates the manner in which unique names are generated for members of hierarchies contained within the Cube dimension.

5.  Click the input box to the right of the box marked MemberUniqueNameStyle within the Properties pane, to enable the selector for the setting.

The selector for the MemberUniqueNameStyle property displays our selection options as shown in Illustration 10.


Illustration 10: The Available Settings for the MemberUniqueNameStyle Property

The values we might assign to the Member UniqueNameStyle property field via the selector are as follows:

  • Native: The unique names of members are automatically determined by Analysis Services. (This is the default value.)
  • NamePath: A name is compounded based upon the name of each of the member level and caption.

Advanced Property: Visible

The Visible property dictates Cube dimension visibility.

6.  Click the input box to the right of the box marked Visible within the Properties pane, to enable the selector for the setting.

The selector for the Visible property displays our selection options as depicted in Illustration 11.


Illustration 11: The Available Settings for the Visible Property

The values we might assign to the Visible property field using the selector are as follows:

  • True: The Cube dimension is visible. (This is the default value.)
  • False: The Cube dimension is not visible.

The individual properties of the Basic properties group are straightforward.

Basic Property: Description

The Description property simply supplies a descriptive name for the level. The default setting is blank.

Basic Property: ID

The ID property contains the unique identifier assigned to the Cube dimension. This property cannot be altered, and is therefore “grayed” within the property settings.

Basic Property: Name

The optional Name property allows us to assign a friendly name to the Cube dimension. The name of a given Cube dimension is identical to its underlying Database dimension, unless another existing Cube dimension has the same name.

We will extend our examination of dimensions within our next article, where we will introduce and overview dimension attributes. Subsequent articles will focus upon characteristics and properties of attributes, including member keys and names, relationships, discretization and other considerations.

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

7.  Select File -> Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for our activities within subsequent articles of this subseries.

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

Conclusion

In this, the second half of a two-part article, we continued the initial article within a new subseries 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 reviewed our Part I introduction to the dimensional model and summarized its role in meeting the primary objectives of business intelligence. Next, we provided a brief review of dimensions in general, including the two primary dimension types within Analysis Services, Database dimensions and Cube dimensions.

Having examined the Database and Cube dimension types within the design environment in Part I (primarily as a means of comparing the two types, and explaining their differences), we began our focus upon the Properties associated with a Cube dimension, based upon the examination of a representative dimension within our sample cube. Finally, we looked forward to subsequent articles of this subseries, where we explore the attribute, hierarchy, and other components of the dimensional model as implemented by Analysis Services.

» 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