How to repair and prevent missing relationships in hierarchies

Document created by ggallo Employee on Jul 4, 2017Last modified by ggallo Employee on Jul 5, 2017
Version 2Show Document
  • View in full screen mode

MISSING RELATIONSHIPS: ORPHANS

In BOARD relationships and hierarchies, "orphans" are members of a BOARD entity that are not related to any member of the parent entity/ies.

For example, if a relationship exists between Product and Product Group, the Product member “Baby Shampoo 100m Honey” would be an orphan if it were not related to any member of the Product Group entity.

Orphans should be avoided in all BOARD databases.They will produce inaccurate summarizations through the relationship structure and will likely cause InfoCube versions to be misaligned

Orphans are typically created during data reading, when entity members belonging to a single relationship structure (tree) are loaded or mapped in multiple data reader protocols. 

 

To check for the existence of orphans, click the "Scan Bottom Up" button that you can find as a Developer user, under the DATABASE tab, more specifically in the Relationships Tab. The Scan function analyzes every hierarchy and every existing relationship between elements: it then shows in red any Entities containing "orphans". 

 

MISSING RELATIONSHIPS: FATHERS WITHOUT CHILDREN

The opposite situation, is when members of a parent entity are not related to any member of the more granular/child entity. This situation can be defined as "fathers without children" and is to be avoided as much as the "orphans".

"Fathers without children" should be avoided in all BOARD databases. Fathers without children are typically created when entity members belonging to a single group are moved or mapped to a different group (e.g. a business division is no more active and all departments belonging to that division are moved to the new division)

 
To check for the existence of "fathers without children", click the "Scan Top Down" button that you can find as a Developer user, under the DATABASE tab, more specifically in the Relationships Tab. The Scan function analyzes every hierarchy and every existing relationship between elements: it then shows  in red any Entities containing "fathers without children". 

 

 

 

 

HOW TO REPAIR MISSING RELATIONSHIPS

Missing relationships can be fixed automatically clicking on the Fix Relationship Button in the Relationships Tab.

The "Fix Relationships" action fills any existing "hierarchical gap" automatically, creating Entity members and assigning relationships to those missing it.

When executed it contemporary fixes the Bottom Up and Top Down relationships 

(see below)

 

All elements created by the FIX RELATIONSHIPS action will be labelled as "» Element", where Element stands for the Description of the "father without children" element. The number of "»" symbols placed in front of the Description element, is proportional to the levels below in the hierarchy at which the element has been created. Code and Description are created following the same logic

 

Example

In the below hierarchy example, let's pretend there is a Region Item without any City or Customer assigned to it. (e.g. RegionNew)

 

image338.jpg

 

Click in the Relationship Control Panel (Database Design) the Button Fix Relationships. The Action :

> creates the "» RegionNew" City Item and links it to the RegionA element (1 - one -  symbol only because it is the hierarchical level immediately below the Region, where you have a "father without children")

> creates the "»» RegionNew" Customer item  (2 - two -  symbols because it is the second hierarchical level below the Region) and links it to the new City "» RegionNew and to the "RegionNew" element

 

Find more information about the Normalization and Fix Relationships in the Relationship Sanitizer section of our Help

 

 

 

 

HOW TO PREVENT MISSING RELATIONSHIPS:

Orphans can be repaired and prevented scheduling the Normalize Procedure Command, which can be found among the actions available in the DataRead Tab.

(see below) 

 

Activating the option "Do not fix missing relationships" skips the Fix Relationship action, running only the Normalize action

 

 

 

HOW TO CREATE A PROCEDURE THAT AUTOMATICALLY FIXES ORPHANS IN THE HIERARCHY:

Orphans are removed by assigning them to a parent member. This association can be automated by creating a BOARD procedure that automatically maps all orphaned members to a new member named “Not Available”

Adding an ETL rule would allow you to customize this mapping and assign them to a different default element

 

In the below example, the orphaned Product “Baby Shampoo 100ml Honey” can be automatically assigned to the Product Group “Not Available

 

ProductProduct Group
Baby Oil 100mlBaby Oil
Baby Cotton Buds 50'sBaby Cotton Buds
Baby Liquid Soap 300ml HoneyBaby Liquid Soap
Baby Shampoo 100ml Honey
Baby Cotton Buds 100'sBaby Cotton Buds

 

Perform the following steps to create the orphan assignment procedure: 
1. Create a new Procedure

2. Create an Extract tree action to export the desired tree to an ASCII file.

3. Save and execute the procedure to export the ASCII file.

4. Open the Data Reader tab and create a new ASCII Data Reader protocol to read the extracted tree back into the BOARD database.

Important: Enable the "Append" option against the entities you know (or expect) the elements might be missing relationships with

 

Note: BOARD creates two files during the ASCII export, the .TXT file contains the exported data, the .CFG file contains configuration information for Board’s ASCII data reader. To automatically define the Data Reader protocol, simply click the name of the .TXT file within the ASCII Protocol Definition.

 

5. Go back to the Procedure Designer, and edit the procedure created previously.

6. Add an action to run the ASCII Data Reader Protocol previously created.

7. Create an action to Normalize the tree (activate the "Do not fix missing relationships" option)

 

 

The fixed hierarchy should look like this

 

ProductProduct Group
Baby Oil 100mlBaby Oil
Baby Cotton Buds 50'sBaby Cotton Buds
Baby Liquid Soap 300ml HoneyBaby Liquid Soap
Baby Shampoo 100ml HoneyNot Available
Baby Cotton Buds 100'sBaby Cotton Buds
8 people found this helpful

Attachments

    Outcomes