Optimizing Cube Schemas
As I mentioned earlier, cube processing could require significant amount of downtime. Fortunately, MSAS has an option that could help speed up cube processing by eliminating joins between fact and dimension tables. This option is referred to as optimizing cube schemas. You can optimize a cube’s schema by choosing Tools, Optimize Schema within the cube editor.
If the cube is optimized, MSAS doesn’t have to join the fact table to the dimension tables during processing. Instead, it uses a foreign key that is already within the fact table to join to the internal representation of the dimension structure. Doing so makes cube processing queries considerably simpler and therefore faster. Certain conditions must be met in order to be able to optimize cube schema:
- Each dimension that will be optimized must be a shared dimension and must be processed prior to schema optimization.
- The optimized dimension must contain a member key on the lowest level that is the only column necessary to relate the dimension to the fact. Keys for the lowest level of the dimension must be unique. The lowest level of the dimension may not be disabled.
- Private and virtual dimensions cannot be optimized.
You may be able to optimize some or all dimensions within the cube. Analysis Manager will inform you if any of the dimensions cannot be optimized. Even so, I’d highly recommend optimizing schemas whenever possible.