Creating regular dimensions

Regular dimensions represent descriptive data that provides context for data modeled in measure dimensions.

A regular dimension contains descriptive and business key information and organizes the information in a hierarchy, from the highest level of granularity to the lowest. It usually has multiple levels and each level requires a key and a caption. If you do not have a single key for your level, it is recommended that you create one in a calculation.

Model regular dimensions are based on data source or model query subjects that are already defined in the model. You must define a business key and a string type caption for each level. When you verify the model, the absence of business keys and caption information is detected. Instead of joining model regular dimensions to measure dimensions, create joins on the underlying query subjects and create a scope relationship between the regular dimension and the measure dimension.

A regular dimension is broken into groups of information called levels. In turn, the various levels can be organized into hierarchies. For example, a product dimension can contain the levels Product Line, Product Type, and Product organized in a single hierarchy called Product. Another example is a time dimension that has the levels Year, Quarter, Month, Week, and Day, organized into two hierarchies. The one hierarchy YQMD contains the levels Year, Quarter, Month, and Day, and the other hierarchy YWD contains the levels Year, Week, and Day.

The simplest definition of a level consists of a business key and a caption, each of these referring to one query item. An instance (or row) of a level is defined as a member of that level. It is identified by a member unique name, which contains the values of the business keys of the current and higher levels. For example, [gosales].[Products].[ProductsOrg].[Product]->[All Products].[1].[1].[2] identifies a member that is on the fourth level, Product, of the hierarchy ProductsOrg of the dimension [Products] that is in the namespace [gosales]. The caption for this product is TrailChef Canteen, which is the name shown in the metadata tree and on the report.

The level can be defined as unique if the business key of the level is sufficient to identify each set of data for a level. In the Great Outdoors Sales model, the members of the Product level do not require the definition of Product type because there are no product numbers assigned to many different product types. A level that is not defined as unique is similar to a determinant that uses multiple-part keys because keys from higher levels of granularity are required. See Using determinants with multiple-part keys. If members within ancestor members are not unique but the level is defined as unique, data for the non-unique members is reported as a single member. For example, if City is defined as unique and identified by name, data for London, England and London, Canada will be combined.

Procedure

  1. Select a namespace or folder where you want to place the dimension.
  2. Click Actions, Create, Regular Dimension, and then click the Dimension tab.
  3. Click Add Hierarchy and then drag one or more objects from the Available items box to the Hierarchies box.

    You can define multiple hierarchies for a dimension. The first hierarchy is used as the default, or primary, hierarchy.

    You can also create an alternate hierarchy by copying a level. Click a level and drag it to the right border of the dimension. You can only copy a level within the same dimension.

  4. Click Add Level and then drag one or more objects from the Available items box into the new level.

    You can also create copies of levels in the Dimension Definition dialog box or in the Dimension Map tab. Click the level and drag it to another position in the hierarchy. All attributes of the level are also copied. You can only copy a level within the same dimension.

  5. If you want to use a different item in a level, drag it from the Available items box to the Select a level in the hierarchy control to see the query items box.

    You are prompted to specify its role.

    By default, Framework Manager adds the name of the namespace.

    Tip: To have a multiple-part key such as first name plus last name, create a new attribute that combines the items, and then specify that the new attribute is the business key.

  6. If you want to indicate that the keys of the levels above the current level are not necessary to identify the members in this level, select the item and select the Unique Level check box. This indicates that key values belonging to the level should be considered unique regardless of context. In some circumstances, the Unique Level setting is used by IBM® Cognos® software to optimize SQL queries.

    Note: The Unique Level check box does not affect the generation and handling of MUN identifiers for the members in this level. All MUNs are fully-qualified.

  7. Choose the additional tasks that you want to perform:
  8. Click OK.
  9. To change the default hierarchy for a dimension with multiple hierarchies, do the following:
    • In the Properties pane, click the ellipsis (...) button in the Default Hierarchy box.
    • Select a different hierarchy, and click OK.

Results

You can also use the Dimension Map tab to create a regular dimension. Click the regular dimension button regular dimension icon.