Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 15, 2008

Dimensional Model Components: Dimensions Part II - Page 2

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM