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

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

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 11, 2002

Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor - Page 8

By William Pearson

Expanding the Basic Cube with The Cube Editor

We will now expand our cube to include data from the respective dimension tables, among other enhancements. First, we will add the dimension tables we require to the existing core cube, and derive more useful dimension data thereby, using the following steps.

1.      Click the Schema tab to return to the table view.

Only the sales_fact_1997 table appears, at this stage of our development effort, as shown below:

Illustration 29: Schema Pane, Initial Cube Design (Compressed)

2.      Click Insert from the Main Menu.

3.      Click Tables from the menu that appears, as shown.

Illustration 30: Insert -> Tables from the Main Menu

The Select Table dialog appears.

4.      Select the time_by_day table by clicking and highlighting it.

The Detail section of the dialog becomes populated with the columns of the time_by_day table. The Select Table dialog now appears as shown in Illustration 31.

Illustration 31: The Select Table Dialog, time_by_day Table Selected

5.      Click the Add button.

The time_by_day table appears on the schema tab, while the Select Table dialog remains open.

9.      Add the following additional tables after the manner of the time_by_day table insertion above:

  • customer
  • product

10.  Click Close on the Select Table dialog.

The dialog disappears, leaving the schema view as depicted below (after arrangement):

Illustration 32: The Arranged Schema View with Added Dimension Tables

Note that joins between the fact table and the dimension tables are already in place, using the id keys in each. This will likely not be the case in a real world design effort, and the appropriate joins (perhaps not as straightforward as those found in our model) would need to be created.

As part of making our cube more user friendly for information consumers, we wish to substitute the dimension ID keys that it currently presents with more intuitive dimension fields. Let's begin with the time_by_day table.

In a case where we may not be familiar with the nature of the data or the characteristics of the fields in a given table, such as our time table, we can browse the data to get a look at its makeup.

11.  Click the upper portion (where the name appears) of the time_by_day table to select the table.

12.  Right-click and select Browse Data from the context menu, as shown in Illustration 33.

Illustration 33: Select Browse Data to View a Sample of a Table's Data

The Browse Data Viewer appears, as shown in Illustration 34 below.

Illustration 34: Partial View of the Browse Data Viewer

13.  Close the viewer after reviewing the data columns and the formats of their contents.

14.   In the Cube Tree, expand the existing Calendar.Time dimension by clicking the "+" sign to its immediate left.

Beginning with the Calendar.Time dimension and proceeding to the Customer and Product dimensions, we will eliminate the original dimension key as the member and substitute a more user friendly field from the related dimension table.

15.  Select the Calendar Time dimension level member (the single level under the Calendar.Time dimension), as shown below:

Illustration 35: Calendar.Time Dimension as Currently Constructed

16.  Right-click the Calendar Time dimension level member.

17.  Select Delete from the context menu.

18.  Click Yes at the Confirm Level Delete dialog to delete the member.

19.  Perform steps 15 through 18 for the Customer and Product dimension level members (the only level members within each), respectively.

Page 9: The Cube Editor (Continued)

See All Articles by Columnist William E. Pearson, III

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