Dimensional Model Components: Dimensions Part II - Page 2

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.

The tabs of the Cube Designer open.

The Cube dimensions belonging to our Basic cube appear as shown in Illustration 4.

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.

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.

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.

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.