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 Mar 14, 2005

Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification - Page 5

By William Pearson

Review Select Property Settings

Before we pursue schema optimization further, let's take a look at the initial settings of the Member Key Column property in our copy of the Warehouse cube, to get a feel for how the initial developer "pointed" it. We have already discussed the fact that the Optimize Schema option changes this property. We can verify that fact with ease by examining the setting before and after the selection of the option.

1.  Right-click the OPTIMAL SCHEMA cube in the cube tree.

2.  Select Edit from the context menu that appears.

The Cube Editor opens.

3.  Expand the Dimensions folder in the Tree pane, by clicking the "+" sign to its left, as necessary.

4.  Expand the Store dimension in the Tree pane.

5.  Click the lowest level in the hierarchy, the Store Name level, as depicted in Illustration 7.

Illustration 7: Levels Appear in the Expanded Store Dimension

6.  With the Basic tab selected in the Properties pane, below the Tree pane (click Properties beneath the tree pane if the Properties pane does not appear), examine the Member Key Column property setting, as shown in Illustration 8.

Illustration 8: Member Key Column Property - Basic Tab of Store Dimension Property Pane

We note that the Member Key Column is tied to the store_id column of the store table. This is not an unusual approach when one is first developing cubes, particularly with a wizard, and optimization has not become a focus. We can view this column, also, with a glance at the store dimensional table, which we can easily see on the Schema tab, as depicted in Illustration 9.

Illustration 9: The Member Key Column - In the Dimensional Table, As Well as the Fact Table

We also notice that the same column, store_id, exists in the fact table of the same schema. It is upon this key that the dimensional (store) and fact (inventory_fact_1997) tables in the star schema are joined.

If we look at the Member Key Column property for the lowest levels of the Warehouse and Product dimensions, Warehouse Name and Product Name, respectively, we see a similar occurrence - each is pointed to a column with a similar name in the associated dimension table (warehouse_id and product_id, respectively), which has an identical counterpart in the fact table

If, however, we examine the same property in the Time dimension, we see a bit of a difference.

7.  Expand the Time dimension in the Tree pane.

8.  Click the lowest level in the hierarchy, the Month level, as shown in Illustration 10.

Illustration 10: The Month Level in the Time Dimension

With the Basic tab selected in the Properties pane, we can examine the Member Key Column property setting, as we did for the Store dimension earlier. A glance to the schema tab on the right reveals a difference, in this case, between this key and the two joined keys in the dimension and fact tables: We note that the Member Key Column for the lowest level of the Time dimension, Month, is set to the month_of_year column of the time_by_day table.

The Schema tab reveals that the Member Key Column for Month, month_of_year, is not the key with which the time_by_day table is joined to the inventory_fact_1997 table. The common key in the schema is time_id, as depicted in Illustration 11.

Illustration 11: Key Joining Fact and Dimensional Tables Differs from Member Key Column

(Make a mental note of the difference in this case; we will revisit it shortly.)

We have examined the Member Key Column property settings, as the original developer left them, for the standard dimensions within our practice cube. Let's move next to optimize the schema and return to review these settings, to reinforce our understanding of the workings of the process.

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