Using Multiple Hierarchies

IBM® Cognos® TM1® dimensions can include one or more hierarchies. This section describes the characteristics of multi-hierarchy dimensions, comparisons to single-hierarchy dimensions, and the available functions to manage and leverage multiple hierarchies.

Currently, multiple hierarchies can be implemented using TurboIntegrator or Planning Analytics Workspace, but not the Architect/Perspectives Dimension Editor or Dimension Worksheets.

Note: By default, multiple hierarchies are not enabled. An administrator must configure the EnableNewHierarchyCreation tm1s.cfg setting before you can work with multiple hierarchies and use the related TurboIntegrator (TI) and Rules functions.

The dimensional modeling approach, wherein all dimensions include a single hierarchy, typically results in a larger number of dimensions per cube. In some cases, repeating groups can occur. For example, a ProductCategory dimension includes Product1 in its Commercial category and Product2 in its Retail category. When a value is addressed by Product1, the ProductCategory coordinate is always Commercial. If the product category is a true attribute, then these repeating groups are not necessary since a product will never exist in both Commercial and Retail categories.

Alternatively, a multi-hierarchical approach can be used where a Product dimension includes a ByCategory hierarchy, including other hierarchies such as ByPriceRange or ByRegion. This method reduces the number of dimensions in the cube. Added complexity comes with the need to specify the hierarchy that an element pertains to; whether in querying, or in TI or cube rules.

Multiple hierarchies also provide greater flexibility when querying. With a single-hierarchy model, querying against a 10 dimension cube requires all queries to have exactly 10 hierarchies. With multiple hierarchies, queries can have 10 or more hierarchies, as needed. When a query includes more than one hierarchy, an intersection effect occurs. For example,

SELECT { [Products].[ByPriceRange].[PriceRange1] } ON ROWS, { [Measures].[Target] } ON
      COLUMNS FROM [Cube]

displays the total Target from the PriceRange1 products. We can refine the total by including an extra hierarchy in the query. For example,

SELECT { [Products].[ByPriceRange].[PriceRange1] } ON ROWS, { [Measures].[Target] } ON
      COLUMNS FROM [Cube] WHERE ( [Products].[ByCategory].[Retail] )

reduces the total by only including PriceRange1 products that appear in the Retail category.

TurboIntegrator and cube rule functions have been added to permit explicit hierarchy specification. Separate functions are available for single and multiple hierarchies. If your cubes contain single-hierarchy dimensions only, you can use either variant. For example, the following two statements are identical.

DimensionElementInsert('dimension', '', 'element', 'c');
HierarchyDimensionElementInsert('dimension', 'dimension', 'element', 'c');

For single-hierarchy dimensions, the hierarchy shares the same name as the dimension. The second parameter of the HierarchyDimensionElementInsert function is the hierarchy name. Either of the above statements can be used for a single-hierarchy dimension. However, if you have a dimension with a second hierarchy (such as hierarchy2), you must use the multi-hierarchy function and specify the particular hierarchy name. For example,


HierarchyDimensionElementInsert('dimension', 'hierarchy2, 'element', 'c');

For detailed information on supported TI and cube rule functions, see TM1 Reference.