The various hierarchies associated with a dimension can be recorded in the WhereScape RED metadata. These hierarchies are often not used in any form, except to provide documentary completeness. 
When used in conjunction with the hierarchy maintenance utility, these dimension hierarchies add a powerful method of enriching the analysis capabilities of the data warehouse. For example, we may have a source system that has a dimension called sales_person. This dimension has no information apart from employee_code, name and title. We could add additional columns of sales_manager, territory, state and region to this dimension. A hierarchy could then be formed from the salesperson name, sales_manager, territory, state and region. The hierarchy maintenance utility allows the maintenance of this hierarchy externally to the data warehouse. This external hierarchy can then become a source system to enrich the data in the warehouse.
Two areas will be covered. Firstly the creation of hierarchies using WhereScape RED, and secondly the process required to setup and use externally maintained hierarchies as source systems to the data warehouse.
 

Adding a Dimension Hierarchy

Any number of hierarchies can be created against a dimension. There is no restriction on the form of the hierarchy.

  1. To add a new hierarchy, right-click the dimension table in the left pane and select Hierarchies > Add Hierarchy.
    The following window appears.
  2. Enter a meaningful name for the hierarchy.
  3. Enter a meaningful description for the hierarchy. This description is carried through into the Hierarchy Description field of any OLAP Dimensions that are built from the original Dimension object.

Note

The description text is automatically set to "Added at dimension creation for cube support" but this can be edited to match the user's intended description.

The hierarchy is built with the highest level at the top; for example a customer dimension may have category_code at the highest level, then group_code, then subgroup_code and finally name at the lowest level.

  1. To enter the hierarchy elements, select them, in the required order, from the left pane and click the right arrow (>) to add them to the right pane.
  2. Once all the hierarchy elements have been added, click OK. 
  3. A hierarchy and its elements can be edited by listing the hierarchies associated with a dimension and using the right-click menu options available in the middle pane.

Copying Dimension Hierarchies from Source

Hierarchies are automatically copied from a source table when the source table is dragged into the middle pane to create a new Dimension.
To copy the hierarchies from the source objects manually, right-click on a Dimension in the Object pane and select Hierarchies > Copy Hierarchies from Source. This feature is useful when the source for a Dimension has been updated to contain new hierarchies.

  • No labels