Optimizing Microsoft SQL Server Analysis Services: Enhance Cube Processing Performance with Optimize Schema
5. Click the lowest level in the hierarchy, the Store Name level, as depicted in Figure 7.
Figure 7: Expanding the Store Dimension – Levels Appear
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 Figure 8.
Figure 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. 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 Figure 9.
Figure 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 Figure 10.
Figure 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.