Working with Recursive and Alternate Hierarchies with Custom Rollups in Kyvos

Maintaining the accuracy of results in complex data models is a difficult task particularly if the underlying data consists of hierarchies. A hierarchy is the way of organizing data in form of levels of aggregation. Typically, analysts use hierarchies to recognize trends at one level, drill down to lower levels to find reasons for these trends, and roll up to higher levels to see the effect of trends at a broader level.

A Hierarchy is used to organize the levels of a dimension into a logical tree structure that defines parent-child aggregation relationships. Here, the parent level, i.e., aggregate members correspond to the consolidation of child levels. By arranging members of a dimension into a hierarchy, not only can the OLAP model calculate the aggregation of dimension level, but also eliminate the need for mathematical calculations in the form of cube formulas as the values for the child levels will automatically sum up or total to the parent level.

In my previous blog, I discussed how you can handle ragged and unbalanced hierarchies in Kyvos. In this post, I will discuss the approach for working with recursive and alternate hierarchies with custom rollups.

First, let us understand the underlying concepts for parent-child recursive hierarchy, alternate hierarchy, and custom rollups.

Parent-child Recursive Hierarchy

In a recursive hierarchy, relationships are represented by using parent and child relationships. A child of a parent node can be a parent itself, which creates the hierarchy.

For example, consider a pharmaceutical use case where a dimension Products is defined with the following hierarchies:

Figure 1. Sample Recursive Hierarchy

When presented in a table, this hierarchy is represented as:

Here, you can see OTC Products and Vitamins and Supplements appear both in Parent and Child columns, representing a recursive parent-child relationship.

In most tools, you will need to flatten the recursive table and create dimension tables at each level or simplify the recursive structure into just one level of relationships. However, with Kyvos, you can handle these hierarchies while maintaining their structure, as explained later in this blog.

Custom Rollups

Custom rollups define how the child values roll up into the parent value in a parent-child hierarchy. In a dimension containing a parent-child relationship, you can define weight for specifying rollup for all non-calculated members of the parent. The weight is applied to members whenever the values of parent members are evaluated. Custom rollup uses the weight values to go back, remain static, or go forward a level. You can use custom rollup for all hierarchies, including parent-child and alternate hierarchies.

Alternate Hierarchy

An alternate hierarchy refers to a situation where a single level appears under multiple parents and contributes differently to each parent. Alternate hierarchies offer multiple paths to roll up the information represented while still preserving the integrity of their aggregations.

To ensure that the same value does not get aggregate twice to its parent, usually, some custom rollup rules are specified to tell how an entity rolls up to its parent entity member. Custom rollups offer varying rollup aggregation settings to ensure the measures are handled appropriately.

Let us now look at an example to understand the need for using custom ups with alternate hierarchies.

Consider a pharmaceutical use case, where a dimension Products is defined with the following hierarchies:

Figure 2. Sample Alternate Hierarchy with Shared Members

Here you can see, Masks and Blood Glucose Monitors are shared members in both OTC Products and Healthcare Devices. If you want to view total sales, the values corresponding to Masks and Blood Glucose Monitors will rollup in both Products and Healthcare Devices, resulting in incorrect sales numbers.

For example,

The total units sole is reflected as 2355. However, the actual number of units sold is 2355 – (600+120) = 1635

To ensure that values are not counted twice, we can define custom rollups. This will ensure that only those numbers that we want to roll up in the parent. In this case, we can define custom roll up for Masks and Blood Glucose Monitors as 0 in the Healthcare Devices. This means the number of units sold for Masks and Blood Glucose Monitors with be multiplied with 0, and hence 0 will be added to the total number of units sold.

Custom roll-up and alternate hierarchy both are independent features, and a hierarchy can have either of them or both.

Simplifying Hierarchies and Custom Rollups in Kyvos

With Kyvos, you can create any of the following:

• Parent-child recursive hierarchy
• Parent-child recursive hierarchy with Custom rollups
• Alternate hierarchy with custom rollups
• Click to choose which days the event occurs and specify the time.
• A combination of all the above as parent-child recursive hierarchy with alternate path and custom rollups

After defining the hierarchy with alternate paths or custom weights or both, you can build a cube. Once the cube is built, now we know which hierarchies have alternate paths and which hierarchies have a custom roll-up, and the Kyvos Query Engines can handle them automatically while serving queries.

Parent Child Recursive Hierarchy with Custom Rollup in Kyvos

Kyvos provides a dynamic way of representing the recursive hierarchies. To analyze hierarchical structures in your visualization, you can create a parent-child hierarchy in Kyvos. A single level hierarchy is defined in the cube, but you can set up additional levels based on parent-child combinations in the underlying data. You can set up naming conventions for the generated child levels that are seen in visualizations.

Additionally, you can define custom rollups on the same dimension using the unary operator field and/or custom rollups expression field.

For such a dimension, a single level hierarchy is defined in the cube design but when this is exposed to the visualization layer, it shows multiple levels based on parent-child combinations in the underlying data source. You can use the Dimension and Level Properties to define the hierarchy and custom rollups.

To define a recursive parent-child hierarchy:

1. From the Toolbox, click Cubes.
2. Click the Action menu ( ⋮ ) at the top of the Cubes column.
3. Click Add Cube.
4. Enter a name for the cube.
5. Select a Relationship from the list and click Add. You can enter a term in the search box to quickly find a name.
6. Add a hierarchy and view the Hierarchy properties.
7. To specify a parent-child relationship, select the Has Parent Child Relation checkbox.

   

   Figure 3. Cube with parent child hierarchy

8. Click the level and in Level properties, select the field name of the child key. For example, Product level key.
9. In the Parent field, select the parent key. For example, Product key.
   NOTE: For creating a parent-child hierarchy, the columns represented by both Key and Parent Field must have the same data type. Both fields must also exist in the same table.
10. Optionally select a field for Display field, such as Productname.
    This is the data that will be displayed.

Figure 4. Parent-child hierarchy – Level Properties

To configure additional settings:

1. Click Parent Child Additional Settings.
2. Select a Parent field.
3. Configure the parent child additional settings.
4. Select the Root member or use Auto. Choices are:
5. Select a Relationship from the list and click Add. You can enter a term in the search box to quickly find a name.
   • Auto
   • Parent is self
   • Parent is blank
   • Parent is missing

   

   Figure 5. Parent-child hierarchy – Additional Settings

6. Click … to create a level naming template.
   1. Click Add Level.
   2. Enter a name. For example, you can name the levels Product Category, Product Level, Product sub category and so on.
      You can delete a level by selecting it and clicking the delete icon (trash can).
   3. Click Apply.

   

   Figure 6. Level Naming Template

7. Choose to show or hide non-leaf data member by clicking Hide or Visible.
8. Enter a caption for non-leaf data members. For example, “self”.
9. To display the name of the parent, enter *, then select a field to show in Display field such as ProductName.
10. Click Apply.

You can now build the cube to browse hierarchical data in visualizations.
Using this approach, you can maintain the dynamic nature of the recursive hierarchy. You can perform drilling operations and get rolled up values at each node level as well.

Alternate Hierarchy with Custom Rollup in Kyvos

On the Kyvos Cube Designer, you can specify if a dimension has an alternate hierarchy.

For this, define a hierarchy, as explained in the previous section.

In the Hierarchy properties, select Has Alternate Path to indicate that there are shared members within other hierarchies.

Figure 7. Configuring Alternate Hierarchy in Kyvos

Further, for each level of a hierarchy, you can define a using the custom rollup, as shown in the following figure.

You must include a field in your source data that you can use that contains values ranging from -1 to 1. You can use floating values like -1, -0.25, 0, 0.25, 0.5, 0.75, 1 and so on. Here, the positive values add to the parent level, negative values subtract to the next level, and 0 values make no change to the next level.

Figure 8. Configuring Custom Rollup in Kyvos

Kyvos makes it easy for you to design the data model using the interactive user interface. Once the hierarchy rules are defined in a cube, the Kyvos Query Engines automatically handle these hierarchies to ensure correct reporting in the visualization tools.

To know more about advanced modeling features in Kyvos, schedule a demo now.