Create Calculation Views Related to Master Data

Two kinds of hierarchies can be defined in a dimension calculation view:

• Parent-Child Hierarchy.

• Level Hierarchy.

The preceding figure compares the two options for a hierarchy of product categories.

To understand the features of these hierarchy types, the following terminology of hierarchies is useful:

• An entity in a hierarchy is called a node.

• Almost all nodes have one assigned parent node. That is, the corresponding node on the next aggregation level. A node without a parent node is called a root node.

• A relationship between a node and its parent node is called a link.

• Nodes that are assigned to the same parent node are the children of this parent.

• Children of the root node are nodes of level one, children of nodes of level one are nodes of level two, and so on.

• Nodes that have no children nodes, are called leaf nodes, or leaves.

• Nodes that are not leaf nodes, and not the root node, are called inner nodes.

• If all leaves are on the same level, a hierarchy is called a balanced hierarchy. In other words, each path from the root node to a leaf node contains one node of each level. All paths have the same length.

• The opposite, a hierarchy that has leaves on different levels, is an unbalanced hierarchy.

You may have a table of all links, which lists all the nodes in a key column, and the parent for each child in a separate column. In this case, you can create a parent-child hierarchy out of this table.

Suppose you have a list of all employees and their managers, and the managers themselves are employees in this table. By searching each manager as an employee, the system can build up a hierarchy of employees. On the highest level, a top manager who doesn't report to another manager is the root of the hierarchy. All employees who are not managers themselves, form the leaves of the hierarchy. This is a typical example of an unbalanced hierarchy. The secretary of the top manager might be a leaf node on level one, and ordinary employees within big departments, are on levels further down in the hierarchy.

Parent-child hierarchies are common in other domains, such as product categories, cost centers, or departments.

To create a Parent-Child Hierarchy, you define a table with two columns of the same data type. One column with key property, for the "child" of each link, the second column for the "parent" of the link. An entry without a parent is a root.

Suppose your basic entities (leaf nodes) and the higher levels are different entities with different data types. Then, you cannot build parent-child hierarchies.

If you have a table with a fixed number of attributes that correspond to well-defined aggregation levels above each leaf element, a level hierarchy can be created. A level hierarchy is a balanced hierarchy.

Suppose for each date, you extract the month, quarter, and year as three new calculated columns. Note that these columns have different data types (eight, six, five, or four digits), and each column represents a specific level of aggregation. All dates of the same month can be combined to a node for this month, all months of the same quarter can be combined to a node for this quarter, and so on. This is a typical example of a level hierarchy. A root node is automatically added.

Suppose you want to create a level hierarchy using products and product categories, which have different data types. To do so, you need a table that contains the products and the categories in different columns, and the products column must be the key column. If you want to add a new level for main category aggregating different categories, you can add a new column. However, it is important that each product has a suitable entry for each column, and that products of the same category have the same main category.

To create a level hierarchy, define a table with as many columns as levels with possibly different data types. The first column for the lowest level (leaves) must have the key property, all others are dependent. The root node does not have to be included in the table. It is automatically added as an additional single node. Adding a new level is complicated because many new links for this level must be added, and many existing ones must be removed. Due to compression, storing many attribute values repeatedly is not as bad as it is on other databases.

Let's now compare the parent-child hierarchy and the level hierarchy.

• If your hierarchy model is an unbalanced hierarchy and your data is available as a list of links, you need a Parent-Child Hierarchy.

• You save disk space because each link information is stored only once (no redundancy).

• Moving a subtree is easy because it just requires a change in one row.

• If your hierarchy is balanced, and the source provides a list of attributes that represent aggregation levels, implement a level hierarchy.

• If higher levels can be derived from existing attributes, the implementation is straightforward. If the attributes can be derived as the first or last N characters, like in nested namespaces, use the leftstring or rightstring function to derive these attributes as calculated columns.

• More attributes are possible in the data table that are not part of the hierarchy, for instance texts, or prices.

• Adding or changing levels is easy. Simply add a new column to the table and the hierarchy, or remove or change a column in the hierarchy.

• Moving a subtree of the hierarchy is complicated because for all leaves that are part of the moved subtree several attribute values must be changed.