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 Figure 11.
Figure 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 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.
Procedure
The process of selecting the Optimize Schema option is about as straightforward as it gets. The ease with which the option can be applied is a major enticement to its use, for anyone wishing to enhance cube processing performance quickly, and with minimal effort. Let’s take the plunge, and then study the results by returning to the settings we have just examined.
1. Click Tools in the main menu of the Cube Editor.
2. Click Optimize Schema from the menu that appears, as shown in Figure 12.
Figure 12: Select Tools —> Optimize Schema
The Cube Schema Optimization dialog appears, as depicted in Figure 13.
Figure 13: The Cube Schema Optimization Dialog – Details of the Optimization
We can ascertain, through the information provided on the Cube Schema Optimization dialog, that the Member Key Column properties for the Store, Warehouse and Product dimensions have been optimized. The initial settings, pointed to the keys in the respective dimension tables, have now been pointed to their (identical) counterparts in the fact table, inventory_fact_1997. We also note the statement that the Time dimension cannot be optimized through the mechanism of the option. This is because of the disparity between the Time key column identifiers shared in the join between the Time dimension table and the fact table, and the Member Key Column property for the lowest-level Time dimension member, Month, which we mentioned earlier.
We are also informed that private and virtual dimensions cannot be subjected to the optimization process. This behavior is consistent with the set of requirements that MSAS imposes on dimensions that are candidates for the Optimize Schema option, as we learned in the Considerations and Comments section above.
3. Click OK to close the Cube Schema Optimization dialog.