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 Jul 11, 2005

Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 9

By William Pearson


Create a New Relative Time Shared Dimension

We will begin the procedure for creating support for relative time periods by adding a new shared dimension, with which we will next associate calculated members that will perform the aggregations, as we shall see.

1.  Right-click the Shared Dimensions folder within the new Relative Time Sample Analysis Services database.

2.  Select New Dimension from the context menu that appears.

3.  Select Editor from the cascading menu that appears next, as depicted in Illustration 41.

Click for larger image

Illustration 41: Select New Dimension --> Editor from the Context Menus

The Choose a Dimension Table dialog appears.

4.  Scroll down in the Tables pane (left half of the dialog), to locate the relativetime table in the clone database.

5.  Select the relativetime table, which we created expressly to support the new relative time dimension, as shown in Illustration 42.

Illustration 42: Select the Relativetime Table

6.  Click OK to accept the selection, and to progress to the Dimension Editor.

7.  From the Schema tab in the Dimension Editor, drag the relativetime_id field from within the relativetime table to the left, dropping it onto the <New> dimension in the tree, as depicted in Illustration 43.

Illustration 43: Drag the Relativetime_id Field to the New Dimension

Relativetime_Id appears as the first level under the <New> dimension.

8.  Click <New> in the Dimension tree to highlight it.

9.  Type Relative Time into the Name box, on the Basic tab of the Properties pane.

10.  Click the Relativetime_Id level in the Dimension tree.

11.  Replace the Name on the Basic tab of the Properties pane with the following:

Relative Time

12.  Replace the current text in the Member Name Column box, "relativetime"."relativetime_id", with the following:


Our changes appear highlighted in a partial view of the Dimension Editor in Illustration 44.

Illustration 44: Highlighted Changes in the Dimension Editor (Partial View)

13.  Select Tools --> Process Dimension from the main menu within the Dimension Editor, as depicted in Illustration 45.

Illustration 45: Processing the New Dimension

14.  Click Yes when prompted to save the dimension.

The Process a Dimension – Select the processing method dialog appears, with only the Rebuild the dimension structure option available (this is the first time the dimension has been processed), as shown in Illustration 46.

Illustration 46: Rebuild the Dimension Structure is the Sole Option ...

15.  Click OK to proceed with processing.

Processing begins immediately, and the Process viewer appears, displaying various logged events. Processing completes, and the viewer presents a green Processing completed successfully message, as shown in Illustration 47.

Illustration 47: Indication of Successful Processing Appears (Compact View)

16.  Click Close to dismiss the Process viewer.

17.  Select File --> Exit to close the Dimension Editor, and return to Analysis Manager.

We now have a Relative Time dimension within which we will concentrate the relative time capabilities requested by the information consumers. We will add the new dimension to the Warehouse cube in our next steps, where we will make other enhancements to the cube structure to complete our addition of relative time structures.

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