Creating a Shared Dimension with the Wizard

Analysis Services

Analysis Services

Creating a Shared Dimension with the Wizard

To create a regular shared dimension quickly and easily, use the Dimension Wizard. The wizard takes you through steps to specify the structure of your shared dimension:

  • Required features, such as the dimension's source data

  • Features required in some situations, such as dimension levels, dimension table joins, and various types of dimension table columns

  • Optional features, such as special properties and custom formulas
Specifying Dimension Data Sources

You can specify whether the shared dimension will be created from a single dimension table, multiple dimension tables, an OLAP data mining model, or from the member properties of another dimension. The dimension table or tables or the mining model you select should contain the column or columns you want in your shared dimension. If you create the dimension from a single dimension table, when you add the shared dimension to a cube and the shared dimension table joins only to the fact table, the cube will have a star schema. If the shared dimension is created from multiple dimension tables, the cube will have a snowflake schema. In either case, you can define a new data source if the table you want to use is not visible in the wizard.

If you create the dimension from an OLAP mining model, you create the dimension from a column that has contents predicted by the mining model, and the mining model dimension has the same impact on a cube's schema as dimensions created from tables when it is added to the cube.

If you use the member properties of another dimension, you can create a virtual dimension. A virtual dimension can be used like other dimensions when it is added to a cube.

Another type of dimension you can create when using a single dimension table is a parent-child dimension. Two columns of the dimension table are used to define the dimension where one column identifies each dimension member, and the other defines its parent. You can also define a new data source for a parent-child if needed.

Steps Required in Some Situations

The following steps are required in certain situations, which are described here.

Select the Dimension Type

If the dimension table contains columns that contains both date-formatted columns and columns of other formats, you specify whether the shared dimension is a standard or time dimension.

Create Time Dimension Levels

If you specified the creation of a time dimension, you also specify the levels for the time dimension.

Create and Edit Joins

If you are creating a snowflake-schema dimension, you confirm, delete, or specify new joins between dimension tables.

Select Levels

For star-schema, snowflake-schema, or virtual dimensions, you define the levels to be used in the dimension. Each level is defined by a column (except in some time dimensions).

Levels within a regular shared dimension are organized hierarchically. For example, in a Location dimension, the levels are Country, State, and City. The values (that is, members) in each level determine the column and row headings that end users see when they browse the cube with a tabular browser. (In graphical browsers, end users see other types of descriptive labels produced by the members. For example, each member produces a value on the scale of an axis.) The column you select is used to supply member names and member keys.

Specify Member Key Columns

For star-schema or snowflake-schema dimensions, you can change the member key columns if the default values supplied by the wizard do not uniquely identify level members.

Select Columns for Parent-Child Dimension

If you are creating a parent-child dimension, you select the two columns upon which the parent-child dimension is based.

Select the Dimension with Member Properties

If you are creating a virtual dimension, you select the source dimension containing the member that you will use to define your dimension.

Select Levels for Virtual Dimension

If you are creating a virtual dimension, you also select the member properties from the source dimension to define the levels of your dimension.

Select Mining Model and Predictable Column

This step appears only if you are using an OLAP mining model to define a data mining dimension. You select the mining model and one of its available predictable columns to create the dimension.

Optional Features

Advanced options in the Dimension Wizard allow you to further tailor your shared dimension. Depending upon the type of dimension being created, these are accessible by selecting them from the Select Advanced Options step of Dimension Wizard. Another option, Create a hierarchy of a dimension, is available in the last step of the wizard. If you started the Dimension Wizard from Cube Editor or the Cube Wizard, you can change the new dimension, which is a private dimension by default in this case, to a shared dimension.

Advanced options that can be enabled include the following:

Changing dimension

If you are creating dimensions from single or multiple tables, you can make the dimension a changing dimension. Compared to other dimensions, a changing dimension allows more kinds of changes without the necessity of fully processing the dimension or the cubes that contain it.

Custom rollups

For parent-child dimensions, you can define formulas or mathematical operators that determine how members are aggregated.

Members with data

For parent-child dimensions, you can enable associated data for dimension members.

Ordering and uniqueness of members

For all dimension types except data mining dimensions, you can enable member sorting. Select from member name, member key, or the contents of a specified column. For each level, you can also specify the scope of uniqueness among member names and member keys.

Storage mode and members groups

If you are creating dimensions from single or multiple tables, you can determine the storage mode for dimension members and enable member grouping. You can specify whether the dimension has a storage mode of MOLAP or ROLAP. The preferred storage mode is MOLAP, but it is not supported in extremely large dimensions, that is, those generally having 5 million members or more. If you select MOLAP, you can specify that member groups be created in a level inserted automatically above the dimension's lowest level. This occurs only if the lowest level contains more than 64,000 members under a common parent.

Writeback

For parent-child dimensions, you can enable writeback capability. Changes to dimension members are written directly to the dimension table.

As mentioned previously, the Create a hierarchy of a dimension option is available in the last step of the wizard. This option is usually selected to create one hierarchy of a multiple-hierarchy dimension. To create another hierarchy within the dimension, run the wizard again and specify the same dimension name but a different hierarchy name.

To create a shared dimension using the Dimension Wizard