Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

14.1. Optimizing UDM Design

You learned to create the UDM and refine it in Chapters 4 through 12. The data modeling completed during UDM creation has a significant impact on both query performance and processing performance, so it is not something to be rushed through. Even before starting to build your UDM, you must understand the business requirements of the system under assembly as much as possible. You need to have clarity regarding the goal of the system, and that, in turn, feeds directly into creating the analysis requirements, and what potential queries the system needs to support. That understanding will also provide insight into what dimension attributes the user won't be interested in analyzing. Based on your design requirements, certain attributes do not have to be included in your UDM design or can be fine-tuned to with dimension properties for optimal UDM design.

Every dimension, and attribute in a dimension, will demand processing time for your UDM. In addition, because adding unnecessary dimensions and attributes will increase the cube space, it can slow the query performance too. You should use the business requirements to drive your design; just be sure to avoid unnecessary dimensions and keep your system compact and performant. In this section you learn various techniques to optimize your dimension and cube using the dimension or attribute properties as well as best practices for designing your cube so that you can get the best performance from your UDM. For ...