INF: When to Create a Virtual Dimension Instead of a Physical Dimension (306990)

SUMMARY

This article describes a situation in which you have an attribute in your dimension table and how you can determine when to make the attribute a member property, and when to create a virtual dimension on the property instead of a physical dimension.

MORE INFORMATION

Usually, for performance reasons, you will want to build a virtual dimension from a member property. In Analysis Services, virtual dimensions have multiple levels that give you more flexibility, so you should map attributes as member properties, and then create virtual dimensions from the properties.

To learn how to create member properties and virtual dimensions, refer to the "Virtual Dimensions" topic in Microsoft SQL Server 2000 Books Online.

However, if a member property has thousands of values, it is better to use one of the following methods:

Create a member property that has smaller compartments for the attribute. For example, instead of an income property where you list each individual income, create a range of income, $30k-$50k,$50k-$70k and so forth. Then, build the virtual dimension from the smaller domain. An example of this is in the Foodmart 2000 database in the Customer shared dimension. The member property is Yearly Income and is broken into different ranges of income. The virtual dimension named Yearly Income is created from the Yearly Income member property. -or-
If you cannot create ranges or compartments, or it is too confusing, then build a regular dimension from the attribute.