Verification
We will take a look at the Member Key Column property settings once again, and see the physical changes are as expected in our copy of the Warehouse cube. To do this from our current position within the Cube Editor, we will take the following steps:
1. Expand the Dimensions folder, as required, in the Tree pane, by clicking the “+” sign to its left, if necessary.
2. Expand the Store dimension in the Tree pane, if required.
3. Click the lowest level in the hierarchy, the Store Name level, as we did in our earlier examination of the associated Member Key Column property.
4. With the Basic tab selected in the Properties pane, below the Tree pane, examine the Member Key Column property setting, as shown in Figure 14.
Figure 14: 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 inventory_fact_1997 table, just as we might have expected. We can also examine the Member Key Column property for the lowest levels of the Warehouse and Product dimensions, Warehouse Name and Product Name, respectively, to see that an identical modification has occurred: each is pointed to the column in the inventory_fact_1997 table that corresponds to the column (from the respective dimension table) that occupied it before we selected Optimize Schema (warehouse_id and product_id, respectively). An examination of the same property in the Time dimension, of course, makes it clear that no modification in the Member Key Column property has occurred, as we have discussed above.
To summarize the process we have witnessed within our practice cube, when we selected the Optimize Schema option, each dimension was evaluated to determine its compliance with the conditions for optimization. For each of the dimensions that met the required conditions, the Member Key Column property of the lowest level of the dimension was changed to reference the foreign key in the fact table, instead of the key in the dimension table to which it had previously referred. Going forward, the new reference directs MSAS to use the key from the fact table during processing, instead of issuing queries that join the dimension to the fact table in the relational database. The removal of joins results in reduced processing time. In many cases, the Optimize Schema option alone can enhance the cube processing cycle significantly.
Summary
In this article, we examined the use of the Optimize Schema option within the Cube Editor, as a means of making our cubes process faster. As in the other articles of this series, after introducing our topic, we prepared for our practice exercise by creating and processing a “clone” of the Warehouse sample cube.
We then discussed the Member Key Column property of our cube, and examined the existing settings within our practice cube. Moreover, we relayed important considerations to ponder in determining the appropriateness of our use of the Optimize Schema option in our respective business environments. Next, we instituted the Optimize Schema option within the Cube Editor, and noted the information that MSAS provided via the resulting dialog, informing us of the modifications that it had made in the Member Key Column property of the applicable dimensions. Finally, we examined and discussed the immediate effects of the Optimize Schema option within the structure of our cube.
Copyright 2004 by the author.
]]>