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 Aug 25, 2003

Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II - Page 5

By William Pearson

Creating the Dimensions

The next step in the design and construction of our cube will be the addition of the dimensions that we will use for our lesson. We will continue within the Cube Editor to specify the dimension data from the table, to demonstrate the process.

1. Right-click the Date column in the ServerAccessLog table.

2. Click the Insert as a Dimension option on the context menu that appears, as shown in Illustration 18.

Illustration 18: Select Insert as a Dimension ...

3. Right-click the IPAdd column in the ServerAccessLog table.

4. Click the Insert as a Dimension option on the context menu that appears, as before.

5. Click the "+" sign to the left of the Dimensions folder to expand it, if necessary.

The two selected dimensions' key fields appear in the Dimensions folder (sans underscores, and with initial caps, once again) as depicted in Illustration 19.

Illustration 19: The Dimensions Folder with Newly Added Dimensions

The dimensions that we have added are private dimensions, meaning that they belong to this cube only. While we would, in most real-world scenarios, want to leverage the advantages of sharing our date data among cubes, and therefore to use a shared dimension in this instance, we will pursue the private dimension route here, to add a bit of instruction with regard to handling source data that is not ideally formatted. This will also be expedient for another reason: We are using a field within our "fact table," and not a separate time dimension table, to supply the date information for our cube.

One issue within the implementation of OLAP systems that I come across consistently is source data that is less-than-ideally transformed (if you can imagine such a set of circumstances). Despite the wonderful tools that we have available to us - particularly those close at hand in the MSSQL Server environment like Data Transformation Services (to which we gained some exposure in Part I), we often find ourselves acting in the role of OLAP architect, and designing a cube, downstream of data that has been converted by someone else; or in a situation where we otherwise have source data, but little control over its formatting, and so forth. Notwithstanding this less-than-ideal scenario, client needs typically dictate that we formulate a solution, if only to allow us to continue in our design endeavors while the ETL process is "straightened out" by the parties involved.

The Date field found in our data source, as a particularly useful example of this scenario, is a string, consisting of a date in the following format:


Not only is this field potentially problematic with regard to its data type, but it also presents a challenge in the operation of various functions that we might use to carve the date into its constituent parts, so as to allow us to create the various levels of a date hierarchy. There are no doubt numerous approaches to handing this; we will do so in a simple manner that both makes sense within the context of this lesson and illustrates the use of functions within the Cube Editor.

Let's make some adjustments to the dimension properties, to make them more useful to our model; this will also help to provide a review of some of the member properties attributes that we have discussed in earlier lessons, while providing the additional benefit of illustrating the use of functions as a part of dimension level design.

6. Expand the Date dimension.

Both the Date dimension and level are visible.

7. Select the Date dimension in the Dimensions folder.

8. Click Properties beneath the tree pane (if necessary) to display the properties of the Date dimension.

9. Click the Basic tab in the Properties pane, if required.

10.  Rename the dimension to Time.

11.  Click-select the Description property text box.

12.  Type in the following:

     Time Dimension

The Basic tab of the Properties pane for the Time dimension appears as shown in Illustration 20.

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