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.
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.
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.
Within the Solution
Explorer, right-click the Basic cube (expand the Cubes folder
on the context menu that appears, as depicted in Illustration 3.
3: Opening the Cube Designer ...
tabs of the Cube Designer open.
Click the Cube
Structure tab, if it has not already appeared by default.
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.
4: The Cube Dimensions, Basic Cube
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.
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.
5: Two Options for Working with Cube Dimensions within the Cube Designer
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
our sample UDM by taking the following steps.
Within the Dimensions
pane of the Cube Structure tab, right-click the Product
on the context menu that appears, as shown in Illustration 6.
6: Select Properties from the Context Menu ...
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.)
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:
three Basic properties, appearing underneath the Advanced
properties group, include the following:
pane for the Product Cube dimension, with Advanced and
Basic properties groups expanded, appears as depicted in Illustration 7.
7: The Properties Pane for the Product Database Dimension
take a look at each of the individual properties, starting with the Advanced
properties group, examining the possible settings with which we can come
The AllMemberAggregationUsage property governs the manner
in which the Aggregation Designer designs aggregations within Analysis
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
Illustration 8: The
Available Settings for the AllMemberAggregationUsage Property
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.)
are placed upon the Aggregation Designer
Default: The same functionality as
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.
The HierarchyUniqueNameStyle property dictates the manner
in which unique names are generated for hierarchies contained within the Cube
input box to the right of the box marked HierarchyUniqueNameStyle within the Properties pane,
to enable the selector for the setting.
for the HierarchyUniqueNameStyle property displays our selection options
as depicted in Illustration
Illustration 9: The
Available Settings for the HierarchyUniqueNameStyle Property
values we might assign to the HierarchyUniqueNameStyle property field
the selector are as follows:
name is to be included as part of the name of the hierarchy. (This is the
name is to be included as part of the name of the hierarchy.
property dictates the manner in which unique names are generated for members of
hierarchies contained within the Cube dimension.
input box to the right of the box marked MemberUniqueNameStyle within the Properties pane,
to enable the selector for the setting.
for the MemberUniqueNameStyle property displays our selection options as
shown in Illustration
Illustration 10: The
Available Settings for the MemberUniqueNameStyle Property
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
NamePath: A name is compounded based upon
the name of each of the member level and caption.
property dictates Cube dimension visibility.
input box to the right of the box marked Visible within the Properties pane,
to enable the selector for the setting.
for the Visible property displays our selection
options as depicted in Illustration
Illustration 11: The
Available Settings for the Visible Property
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.
individual properties of the Basic properties group are straightforward.
property simply supplies a descriptive name for the level. The default setting
property contains the unique identifier assigned to the Cube dimension.
This property cannot be altered, and is therefore grayed within the property
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
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.
-> 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.
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
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.