Browse Prior Art Database

Converting of Flattened Table structure into Hierarchical Table Table structure and vice versa for any given levels

IP.com Disclosure Number: IPCOM000244431D
Publication Date: 2015-Dec-11
Document File: 8 page(s) / 218K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is basically a Utility which address both forms of transformation Flattened data structure to Hierarchical and vise versa.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 50% of the total text.

Page 01 of 8

Converting of Flattened Table structure into Hierarchical Table Table structure and vice versa for any given levels

Business Case


 We receive customer data in flattened Fig. 1,Fig. 2 format wherein the system requires a hierarchical Fig .3 structure to import and load in the data mart. We need to prepare this hierarchical structure through manual queries. This method was highly prone to error, was effort intensive and was consuming lot of time. We also receive data where id's are not present. Hence we designed this method where id's can be system generated in hierarchical format. We required something quick which could help us to reduce manual intervention and errors. To overcome this, we have devised a method and a prototype where data can be imported and then transformed from flattened to hierarchy and vice versa in matter of seconds


 For ragged hierarchy (uneven number of levels) it requires to extend the intermediate node to act as leaf node.


 For some cases it requires to identify the change in hierarchy comparing it with master structure. Requirements is to retain the historic id's to parent child relation which is not changed. However should add new unique id to newly added relationship between a given parent and child.


 Flat table structure is more user friendly to read, user can easily walk through from first level to leaf /last level in single row.


 Flat table can be use to easy filtering of data for analysis.

   Hierarchical Input is use for processing in many BI industry product for DW. Actual Prototype of the Utility is as follows. Use below are the forms of MS Access. User Interface for user to create the required option.

Fig. 1

1


Page 02 of 8

Utility report back to user when transformation of Flattened to Hierarchical is completed.

Utility report back to user when transformation of Hierarchical to Flattened is completed.

2


Page 03 of 8

Business results.

Algorithm has following features,
 Ability to handle any levels during transformation.


 Ability to create general nodes (extending to leaf level) as a optional feature.


 Manage transformation for ragged (uneven number of levels) table structure.


 Retaining existing reference ids and add ids only for those records which are newly added and part of hierarchy on runtime.


 Works for all combination, With Id and Name, Only With Names.


 It can generate unique ids if flattened structure is only with names. Else it is able to use the available IDs with Name for any given level to perform the transformation.


 Transformation from Flattened to Hierarchy can be made in 4 different methods, refer Fig.1


 Transformation from Hierarchy to Flattened can be made in 2 different methods, rfer Fig. 1


 Transform both Flattened to Hierarchical and Hierarchical to Flattened in single window.

3


Page 04 of 8

Please Find below Pseudo Code for both Flat to Hier and vice versa.

Part 1:-Converting form Flattened Table Structure to Hierarchical.


1.1) Take...