Professional SQL Server™ Analysis Services 2005 with MDX

13.1. Optimizing UDM Design

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 attributes the user won't be interested in analyzing.

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.

13.1.1. Fine Tuning Your Dimensions

In Analysis Services 2005, dimensions can contain several hierarchies. Often when you create your dimension using the wizard, all the columns in the relational table(s) are added as attribute hierarchies in support of relational and OLAP querying. You can easily end up with a dimension that can have hundreds of attributes. In most business scenarios, the attributes within a dimension are not used in many queries. Only a subset of the attributes might ...