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