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.
» 
Comment and Contribute
