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 Jan 24, 2008

Dimensional Model Components: Dimensions Part I - Page 2

By William Pearson

Procedure: Examine Dimension Properties in Analysis Services 2005

In the practice procedures that follow, we will examine the properties, which we have introduced in the foregoing sections of the article. We will examine the properties for a representative database dimension within this section of this, Part I, of the article; we will cover the properties that specifically define and support Cube dimensions within the similar procedural section of Part II. 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.

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

Illustration 3: 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 4.

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

Contrast Cube Dimensions with Database Dimensions

Let’s examine both Database dimensions and Cube dimensions in general, mostly to gain an understanding of the differences between the two, before proceeding to our overview of the properties of Database dimensions. (We will overview properties for Cube dimensions in Part II of this article.)

1.  Within the Solution Explorer, expand the Dimensions folder (by clicking the “+” sign to its immediate left), if necessary, to expose the Database dimensions for our new sample UDM.

The Database dimensions appear as depicted in Illustration 5.

Illustration 5: The Database Dimensions of Our Sample UDM

We notice that eight Database dimensions appear within the Dimensions folder of the Solution Explorer.

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

3.  Click Open on the context menu that appears, as shown in Illustration 6.

Illustration 6: Opening the Cube Designer ...

The tabs of the Cube Designer open.

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

5.  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 depicted in Illustration 7.

Illustration 7: The Cube Dimensions, Basic Cube

We notice that ten Cube dimensions appear within the Dimensions pane of the Cube Structure tab. The difference in number between the Cube dimensions and the Database dimensions we saw earlier reflects a powerful capability that accrues to dimensional model designers that use Analysis Services 2005 and beyond: we can reuse single Database dimensions for multiple Cube dimensions. The benefits we enjoy in doing so include simplified dimension management, reduced overall processing time, and usage of less disk space by our deployed models.

The Basic cube contains more dimensions than the Analysis Services database because three separate Cube dimensions that relate to time / date (and which are based on different time-related facts in the fact table) share the Time Database dimension as their bases. 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.

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

An important fact to grasp when considering the differences between Database dimensions and Cube dimensions is that different properties exist for each dimension type, even though the former serves as the basis for the latter. All other dimensions within the model derive from the Database dimension. The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.

To optimize the numerous properties settings that are available to us within our design and implementation efforts, we need to understand the different settings that are made in each of the Database and Cube dimensions. These settings become confusing to many who are new to Analysis Services, and so we will examine them individually beginning with our next section, where we will take up the few properties involved with Database dimensions. We will examine the properties for Cube dimensions in Part II of this article.

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