MSAS: Virtual Dimensions

A logical dimension created out of the columns of a physical dimension is a virtual dimension. The contents of a virtual dimension are member properties of the physical dimension or columns and tables of a physical dimension. For instance the Store name level of the Store dimension has a member property named Store Sqft. This member property identifies the area of the store in square feet. This member property can be used to create a virtual dimension and this can be added to any cube that contains the Store Dimension.

The virtual dimension can be used like any other dimension by end users. A virtual dimension based on member properties can enable end users analyze data based on member properties.

The depth of the virtual dimension is directly proportional to the number of member properties used to define it.

Addition of a virtual dimension to a cube does not increase its size because the virtual dimension does not have aggregation data.

Queries on virtual dimension are slower than queries on parent child dimensions.

Virtual dimensions can be created using the Virtual Dimension Wizard in Analysis Services.

To create a virtual dimension based on member properties

Depending on whether the virtual dimension will be shared or private, do one of the following:

• If the virtual dimension will be shared, in the Analysis Manager tree pane, expand the database in which you want to create the virtual dimension. Right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard.
• If the virtual dimension will be private, in the Analysis Manager tree pane, right-click the cube in which you want to create the virtual dimension, and then click Edit. On the Insert menu, point to Dimension, and then click New.

In the Welcome step, click Next.

In the Choose how you want to create the dimension step, select Virtual Dimension: The member properties of another dimension. Click Next.

In the Select the dimension with the member properties step, select the dimension that contains the member properties. Click Next.

In the Select the levels for the virtual dimension step, select the levels for the virtual dimension: 

• For each level you want to define, beginning with the top level, in the Available member properties box, select the member property that defines the level, and then click >. 
•  Click Next.

In the Select advanced options step, choose any advanced options that may apply. If no advanced options apply, click Next.

If the Specify ordering and uniqueness step appears, for each level, in the Order by column, select the value that determines the order by which the level's members are displayed to end users. Also, for each level, in the Keys unique and Names unique columns, specify the scope of uniqueness among the member keys and member names, respectively. Click Next.

In the Finish step:  In the Dimension name box, type a name up to 24 characters long. 

• If the virtual dimension will be private, clear the Share this dimension with other cubes check box. (This check box is not displayed if you are following the procedure for a shared virtual dimension.)
• Click Finish.

In Analysis Manager, a virtual dimension is identified by the following icon.

In Dimension Editor and Cube Editor, the Virtual property of a virtual dimension is always True. Its Depends On Dimension property indicates the dimension that contains the member properties or columns on which it is based.

When a dimension's Virtual property is set to True, an (All) level is created if one does not already exist.