Creating parent child hierarchies

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

In a parent-child hierarchy, also known as a Recursive parent-child 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 Product is defined with the following hierarchies:

Open

Sample Recursive parent-child hierarchy

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

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

Custom rollup

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 rollups use 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 aggregated 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 rollups with alternate hierarchies.

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

Open

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 roll up 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 rollup 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 by 0, and hence 0 will be added to the total number of units sold.

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

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

• A combination of all the above as parent-child recursive hierarchy with alternate path and custom rollups

After defining the hierarchy with alternate paths, custom weights, or both, you can build a semantic model. Once the semantic model is built, 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.

To define a parent-child hierarchy

You can create a parent-child hierarchy for use in visualizations. A single-level hierarchy is defined in the semantic model, 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. This feature is only available for new cubes. You can't retrofit this feature into an existing semantic model.

The parent-child relation checkbox is only available when using regular data types. Parent-child also can't be used with SQL or drill through. When used, it deletes the other levels of the hierarchy. You will see a warning message that the other levels will be deleted. This option allows a parent member to be linked to fact data at a child level. For example, if a manager sells products as well as has direct reports that sell, you can capture their data as a manager (the sum of the department sales) and as a sales associate (the amount they directly sold). 

In a parent-child hierarchy, some non-leaf members can also have data derived from underlying data sources. Consider an organization hierarchy, where a sales manager also contributed to the sales figures in addition to the sales done by his or her reporting employees. In that case, a special data member will be generated for non-leaf members. You can specify the naming convention for this special data member to avoid confusion. In this example, you could set the special data member name to "self".

You can also control the visibility of these Non-leaf data members by choosing Hide. 

When you configure the level naming template, you specify the level names displayed to the user as they browse the cube. For example, you can create level names of CEO, VP, Associate VP, Managers, and Employees. These values are seen when using MDX. For example, you can see these levels in Kyvos, Tableau, and MicroStrategy to name a few. You can use an asterisk * to act as a wildcard. For example, if your employee field names are empl0, empl1, empl2, empl3, and so on, you can name the levels CEO, VP, Manager, and empl* to collect all of the rest of the remaining employee field name values into a single level.

To create a parent-child relationship, perform the following steps. 

1. From the Toolbox, click Semantic Models.

2. Select the semantic model to which you want to add the hierarchy or create a new semantic model as:

   1. Click the Actions menu ( ⋮ ) at the top of the Semantic models column.

   2. Click Add Semantic Models.

   3. Enter a name for the semantic model.

   4. Select a Relationship from the list and click Add.
      You can enter a term in the search box to quickly find a name.

3. Add a hierarchy and view the Hierarchy properties on the right side of the page.

4. To specify a parent-child relationship, select the Has Parent Child Relation checkbox.
   
   

   NOTE: This option deletes the levels in the hierarchy along with other hierarchies in this dimension.

5. Click the level and in Level properties, select the field name of the child key. For example, Product level key.

6. In the Parent field, select the parent key. For example, Product key.

7. Optionally select a field for the Display field, such as Productname. This is the data that will be displayed.
   
   

To configure additional settings, perform the following: 

1. Create a parent-child hierarchy as described above.

2. Click the level, click Parent Child Additional Settings

3. Level properties and.

4. Select a Parent field.

5. Configure the parent child additional settings.
   
   

6. Select the Root member or use Auto. Choices are:

   • Auto: Only members that meet one or more of the conditions described for Parent Is Blank or Parent Is Self will be treated as root members.

   • Parent is self: Only members with themselves as parents, will be treated as root members.

   • Parent is blank: Only members with null, zero, or an empty string in the columns represented by the parent field, will be treated as root members.

7. Click the Actions menu (...) to create a Level Naming Template.

   1. Click Add Level.

   2. Enter a name.
      For example, you can name the levels CEO, VP, Manager, Employee. 

   3. You can delete a level by selecting it and clicking the delete icon (trash can).

   4. Click Apply.

8. Choose to show or hide non-leaf data members by selecting Hide or Visible.

9. Enter a caption for non-leaf data members. For example, "self". 
   To display the name of the parent, enter *, then select a field to show in Display field such as Fullname.

10. Click Apply.

Related topics

• Defining custom rollups

• Supporting alternate hierarchies