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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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