Example - Hierarchical to Relational Transformation
The Logistics department of the Harrinder Shipping company must process shipment data. They need to transform inventory and customer data from hierarchical format into relational data that they can store in database tables.
They need to create a mapping that transforms hierarchical data into relational data. The organization inventory system generates shipment inventory data in hierarchical format. The mapping needs to use a Hierarchical to Relational transformation that inputs shipment data and outputs the details in a usable relational format.
The Shipments input is in hierarchical format. The Shipment element contains sub-elements with customer and inventory data for each shipment:
Shipments
Shipment
Items
Item_Name
Inventory_ID
Customer
Customer_Name
Customer_ID
Customer_Address
In the relational output, the Customer_ID element is a primary key in the Customer table, and is a Foreign key in the Shipment table.
Customer_ID | Customer_Name | Customer_Address |
|---|
3543766 | Tony Birch | 6 Moby Drive |
6342562 | Sujita Man | 22 Dan Street |
6471862 | Dwayne Horace | 7 Jafendar Boulevard |
7265204 | Carmela Perez | 23 Dan Street |
4559672 | Delilah Soraya | 28 Jafendar Boulevard |
Shipment_ID | Inventory_Item | Customer_ID |
|---|
9173327437 | 908274 | 7265204 |
9174562342 | 553439 | 7265204 |
8484526471 | 546584 | 3543766 |
7023847265 | 908274 | 3543766 |
9174596725 | 553439 | 3543766 |
The following image shows the mapping in this example:
The mapping contains the following objects:
- Read_input
- The source that contains the path to the file with hierarchical data. Reads billing data from an XML file.
- Shipping_Transform
- A Hierarchical to Relational transformation that transforms XML input into relational output.
- Write_Output2
- A target that stores part of the transformed data, the Customer table, in relational format.
- Write_Output3
- A second target that stores another part of the transformed data, the Shipment table, in relational format.
The mapping uses the Read_input flat file to input the target path for the hierarchical input. The mapping processes and transforms the data with the Shipping_Transform transformation. Then the mapping stores the output in the two output targets.
