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,
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.
- Select a namespace or folder where you want to place the dimension.
- Click Actions, Create, Regular Dimension, and then click the Dimension tab.
- 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.
- 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.
- 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
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.
- 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.
- Choose the additional tasks that you want to perform:
- Specify roles Roles.
- Embed calculations by clicking Add and then defining the expression Creating calculations.
To change a calculation that has been embedded in the dimension, in the Dimension Map tab, click Attributes, right-click the query item, and click Edit Expression.
- Embed filters Creating filters.
- Specify the sort characteristics of levels Sorting members of a level.
- Test the dimension Testing dimensions.
- Edit the SQL and change various options Cognos SQL.
- Click OK.
- To change the default hierarchy for a dimension with multiple hierarchies, do the
- In the Properties pane, click the ellipsis (...) button in the Default Hierarchy box.
- Select a different hierarchy, and click OK.