Data Integration for hierarchies

Introduction

This article focuses on the second step of data integration: updating Hierarchies. We will update the Hierarchy target (H_ and HO_) tables.

• The H_ table stores a subset of a D_ table's Dimension Members and any Parent-Child (PC) relationships.
• The HO_ table stores the Hierarchy Members' ordering information.

Tip: Please refer to our Data Integration Guide before attempting the process below. Click here for the first data integration step and here for the third. 

Process

Follow these steps to perform data integration for Hierarchies: 

1.  Truncate H_ table.

2.  Populate the H_ table using the D_ table based on your criteria.

3.  Update HO_ table for ordering.

Note: Updating the HO_ table is optional because it can be manually updated through the Modeler. 

4.  Only for Hierarchy Mode:

     4.1  Truncate SysCache_V_H_.

     4.2  Insert data from V_H_ into SysCache_V_H_.

5.  Repeat steps 1-4 for other Hierarchies, as needed.

Columns

The following columns comprise an H_ table:

Hierarchy Mode

Here's an example of a Member List in Hierarchy Mode: 

Here's what the data would be in the H_ table: 

List Mode

Here's an example of a Member List in List Mode: 

Here's what the data would be in the H_ table: 

Note: When MemberId and ParentMemberId share the same value, that Member is directly under the default All Member. 

Tip: Refer to Configure Member List Mode for more information on these Modes. 

Table Example

The images below show a typical Hierarchy structure and the Members it can contain:

H_ table

HO_ table

Validation

When a Hierarchy is populated, check for the following:

• All MemberId and ParentMemberId values are in the D_ table.
• All MemberId values are unique within each H_ table.
• Each Member has a path up to a root member. I.e., All ParentMemberId values must also be MemberId values. 
• There are no cycles. For example, if a MemberId is 1 and its ParentMemberId is 2, we can't have another MemberId be 2 and its ParentMemberId be 1.

-- Show MemberIDs that do not exist with the dimension 
SELECT [MemberID] FROM [dbo].[H_Account_Account] H
WHERE H.[MemberID] NOT IN (SELECT [MemberID] FROM [dbo].[D_Account]);

-- Show ParentMemberIDs that do not exist within the dimension
SELECT [ParentMemberID] FROM [dbo].[H_Account_Account] H
WHERE H.[ParentMemberID] NOT IN (SELECT [MemberID] FROM [dbo].[D_Account]);

-- Display non-unique MemberIDs
SELECT [MemberID], COUNT([MemberID]) [Count]
FROM [dbo].[H_Account_Account]
GROUP BY [MemberID]
HAVING COUNT([MemberID]) <> 1;

-- Ensure all members have a path to the root
SELECT [ParentMemberId] FROM [dbo].[H_Account_Account]
WHERE [ParentMemberId] NOT IN (SELECT [MemberId] FROM [dbo].[H_Account_Account]);

Example

Below is an example of loading the hierarchy tables for the Product dimension.

USE [Integrated Financial Planning]
GO

-- 1. Truncate H_ table.
TRUNCATE TABLE [dbo].[H_Product_Product];

-- 2. Populate the H_ table.
INSERT INTO [dbo].[H_Product_Product] ([MemberId], [ParentMemberId])
SELECT [MemberId], [MemberId] 
FROM [dbo].[D_Product]
WHERE [Property] = N'SKU'; -- Use attributes to scope down members as needed.

-- 3. Update HO_ table for ordering.
-- This example orders members by MemberName.
TRUNCATE TABLE [dbo].[HO_Product_Product];

INSERT INTO [dbo].[HO_Product_Product] ([MemberId], [MemberOrder])
SELECT H.[MemberId], ROW_NUMBER () OVER (ORDER BY D.[MemberName]) [MemberOrder]
FROM [dbo].[H_Product_Product] H
INNER JOIN [dbo].[D_Product] D ON H.[MemberId] = D.[MemberId];