Technical Articles
Restoring the hierarchical order of a flattened source message via the pre-transformation feature in Integration Advisor
Introduction
Recently we have introduced a new feature in Integration Advisor called “pre-transformations“. This feature allows the reordering of the source MIG structure of a mapping guideline before the actual mapping so that the mapping later is simpler and focuses only on mapping of content between source and target fields.
Currently, two operations are supported “Copy Referenced Node” and “Group by Key” (see online docu). These two operations however can be used to model even more complex uses cases. One example of such a more complex use case is to restore the hierarchical structure of a flattened structure what shall be explained in this blog article.
Scenario
As an example we use the following scenario: Assume you have a source MIG based on X12 856 message (Ship Notice/Manifest) which shall be mapped to a cXML ShipNoticeRequest based target MIG. The source X12 message has a flat structure. Here we used the qualification feature to model the different instances of the HL nodes which corresponds to Shipment, Order, Pack, and Item:
Structure of source MIG (X12 856)
That means that Shipment, Order, Pack, and Item are siblings in the source structure. The target message, however, has a hierarchical structure, where ShipNoticePortion (corresponds to Order) is the parent of ShipNoticeItem (corresponds to Item), which again is the parent of Packaging (corresponds to Pack):
Structure of target MIG
If we naively did the following mappings
- HL [Order] -> ShipNoticePortion,
- HL [Pack] -> Packaging, and
- HL [item] -> ShipNoticeItem,
we would get in each Packaging instance a merge of all HL [Pack] instances instead of only the HL [Pack] instance which corresponds to the parent ShipNoticeItem and similar each item is added to each ShipNoticePortion instead of only the the corresponding ones:
Mapping without pre-transformation
Therefore we need to transform the structure of the source message first so that it reflects the hierarchy which is encoded in the HL segments of the X12 message. For that we want to use the pre-transformation feature.
Preparation
For creating the pre-transformation you first need to build two MIGs: One based on X12 message 856 (Ship Notice/Manifest), which will be the source message, and another based on cXML ShipNoticeRequest. For the former one we use the qualification feature to create 4 sibling qualified nodes for the HL loop:
- 735 = S with cardinality 1..1
- 735 = O with cardinality 1..*
- 735 = P with cardinality 1..*
- 735 = I with cardinality 1..*
Please, note that this qualification is not yet proposed by the MIG editor. You first have to create a corresponding qualifier marker (see online docu).
Add the following segments to the respective nodes:
- N1
- PRF
- PO4 (select some child elements) and MAN
- LIN and SN1
Then your MIG shall look similar to
Source MIG
For cXML ShipNoticeRequest add the following group nodes:
- ItemID
- Packaging
Now your MIG shall look similar to
Target MIG
As last step of the preparation create a MAG with your two MIGs, navigate to “Overview”, and add a pre-transformation via the + button.
Build the pre-transformation
Now we can build our pre-transformation. Add a new transformation step with operation “Copy referenced node”. Assign the following nodes as operation parameters:
- Receiving node: G_HL_gq_P
- Node with reference ID: D_628 (child node of G_HL_gq_P)
- Referenced Node: G_HL_gq_I
- Referenced ID Node: D_734 (child node of G_HL_gq_I)
and execute the pretransformation.
Then add a second transformation step of type “Copy referenced node” and assign the following nodes:
- Receiving node: G_HL_gq_O
- Node with reference ID: D_628 (child node of G_HL_gq_O)
- Referenced Node: G_HL_gq_P
- Referenced ID Node: D_734 (child node of G_HL_gq_P)
and execute the pretransformation again. Now the pre-transformed MIG shall look like
Result of pretransformation
Create the Mapping
The next step is the creation of the main transformation. For that press the “Main Transformation” button and connect the following nodes (here only the mapping elements are listed which are necessary for the demonstration of the restoration of the tree structure, a real-live scenario will contain more mapping elements):
Mapping Elements
Simulate the Mapping
Now you can simulate the mapping. Use the following payload for the simulation:
<?xml version="1.0" encoding="UTF-8"?>
<M_856>
<S_ST>
<D_143>856</D_143>
<D_329>0001</D_329>
</S_ST>
<S_BSN>
<D_353>00</D_353>
<D_396>SOPI-1lvlPack</D_396>
<D_373>20180122</D_373>
<D_337>101059</D_337>
<D_1005>0001</D_1005>
</S_BSN>
<S_DTM>
<D_374>011</D_374>
<D_373>20180222</D_373>
<D_337>100000</D_337>
<D_623>ET</D_623>
</S_DTM>
<S_DTM>
<D_374>017</D_374>
<D_373>20180223</D_373>
<D_337>100000</D_337>
<D_623>ET</D_623>
</S_DTM>
<G_HL>
<S_HL>
<D_628>1</D_628>
<D_735>S</D_735>
<D_736>1</D_736>
</S_HL>
<G_N1>
<S_N1>
<D_98>ST</D_98>
<D_93>ShipTo Name</D_93>
</S_N1>
</G_N1>
<G_N1>
<S_N1>
<D_98>SU</D_98>
<D_93>Supplier Name</D_93>
</S_N1>
</G_N1>
</G_HL>
<G_HL>
<S_HL>
<D_628>2</D_628>
<D_734>1</D_734>
<D_735>O</D_735>
<D_736>1</D_736>
</S_HL>
<S_PRF>
<D_324>Order ID 1</D_324>
</S_PRF>
<S_DTM>
<D_374>004</D_374>
<D_373>20180201</D_373>
<D_337>093059</D_337>
<D_623>ET</D_623>
</S_DTM>
</G_HL>
<G_HL>
<S_HL>
<D_628>7</D_628>
<D_734>1</D_734>
<D_735>O</D_735>
<D_736>1</D_736>
</S_HL>
<S_PRF>
<D_324>Order ID 2</D_324>
</S_PRF>
<S_DTM>
<D_374>004</D_374>
<D_373>20180202</D_373>
<D_337>093059</D_337>
<D_623>ET</D_623>
</S_DTM>
</G_HL>
<G_HL>
<S_HL>
<D_628>3</D_628>
<D_734>2</D_734>
<D_735>P</D_735>
<D_736>1</D_736>
</S_HL>
<S_PO4>
<D_103>CTN</D_103>
<D_384>100</D_384>
<D_355_2>KG</D_355_2>
</S_PO4>
<S_MAN>
<D_88>GM</D_88>
<D_87>CTN1</D_87>
</S_MAN>
</G_HL>
<G_HL>
<S_HL>
<D_628>5</D_628>
<D_734>2</D_734>
<D_735>P</D_735>
<D_736>1</D_736>
</S_HL>
<S_PO4>
<D_103>BOX</D_103>
<D_384>10</D_384>
<D_355_2>KG</D_355_2>
</S_PO4>
<S_MAN>
<D_88>GM</D_88>
<D_87>BOX2</D_87>
</S_MAN>
</G_HL>
<G_HL>
<S_HL>
<D_628>8</D_628>
<D_734>7</D_734>
<D_735>P</D_735>
<D_736>1</D_736>
</S_HL>
<S_PO4>
<D_103>BOX</D_103>
<D_384>500</D_384>
<D_355_2>KG</D_355_2>
</S_PO4>
<S_MAN>
<D_88>GM</D_88>
<D_87>BOX4</D_87>
</S_MAN>
</G_HL>
<G_HL>
<S_HL>
<D_628>4</D_628>
<D_734>3</D_734>
<D_735>I</D_735>
<D_736>0</D_736>
</S_HL>
<S_LIN>
<D_350>1</D_350>
<D_235>PL</D_235>
<D_234>10</D_234>
<D_235_2>VP</D_235_2>
<D_234_2>Supplier Part ID 1</D_234_2>
</S_LIN>
<S_SN1>
<D_350>10</D_350>
<D_382>100</D_382>
<D_355>EA</D_355>
<D_330>100</D_330>
<D_355_2>EA</D_355_2>
</S_SN1>
</G_HL>
<G_HL>
<S_HL>
<D_628>6</D_628>
<D_734>5</D_734>
<D_735>I</D_735>
<D_736>0</D_736>
</S_HL>
<S_LIN>
<D_350>2</D_350>
<D_235>PL</D_235>
<D_234>20</D_234>
<D_235_2>VP</D_235_2>
<D_234_2>Supplier Part ID 2</D_234_2>
</S_LIN>
<S_SN1>
<D_350>20</D_350>
<D_382>10</D_382>
<D_355>EA</D_355>
<D_330>100</D_330>
<D_355_2>EA</D_355_2>
</S_SN1>
</G_HL>
<G_HL>
<S_HL>
<D_628>9</D_628>
<D_734>8</D_734>
<D_735>I</D_735>
<D_736>0</D_736>
</S_HL>
<S_LIN>
<D_350>4</D_350>
<D_235>PL</D_235>
<D_234>20</D_234>
<D_235_2>VP</D_235_2>
<D_234_2>Supplier Part ID 3</D_234_2>
</S_LIN>
<S_SN1>
<D_350>20</D_350>
<D_382>200</D_382>
<D_355>EA</D_355>
<D_330>200</D_330>
<D_355_2>EA</D_355_2>
</S_SN1>
</G_HL>
<G_HL>
<S_HL>
<D_628>10</D_628>
<D_734>8</D_734>
<D_735>I</D_735>
<D_736>0</D_736>
</S_HL>
<S_LIN>
<D_350>5</D_350>
<D_235>PL</D_235>
<D_234>30</D_234>
<D_235_2>VP</D_235_2>
<D_234_2>Supplier Part ID 4</D_234_2>
</S_LIN>
<S_SN1>
<D_350>30</D_350>
<D_382>300</D_382>
<D_355>EA</D_355>
<D_330>300</D_330>
<D_355_2>EA</D_355_2>
</S_SN1>
</G_HL>
<S_CTT>
<D_354>5</D_354>
</S_CTT>
<S_SE>
<D_96>80</D_96>
<D_329>0001</D_329>
</S_SE>
</M_856>This payload has the following logical structure:
+--Order ID 1 (D_628=2)
| |
| | (D_734=2)
| |
| +--CTN1 (D_628=3)
| | |
| | | (D_734=3)
| | |
| | +--Supplier Part ID 1 (D_628=4)
| |
| | (D_734=2)
| |
| +--BOX2 (D_628=5)
| |
| | (D_734=5)
| |
| +--Supplier Part ID 2 (D_628=6)
|
+--Order ID 2 (D_628=7)
|
| (D_734=7)
|
+--BOX4 (D_628=8)
|
| (D_734=8)
|
+--Supplier Part ID 3 (D_628=9)
|
| (D_734=8)
|
+--Supplier Part ID 4 (D_628=10)
The simulation with this payload gives the following result:
<?xml version="1.0" encoding="UTF-8"?>
<ShipNoticeRequest xmlns:f="http://www.sap.com/ns/integration-advisor/xslt-functions"
xmlns:msg="http://sap.com/xi/SAPGlobal20/Global">
<ShipNoticeHeader shipmentID="ShipTo Name Supplier Name" noticeDate=""/>
<ShipNoticePortion>
<OrderReference orderID="Order ID 1" orderDate="20180201 093059">
<DocumentReference payloadID=""/>
</OrderReference>
<ShipNoticeItem quantity="100" lineNumber="10">
<ItemID>
<SupplierPartID>
<_CONTENT_>Supplier Part ID 1</_CONTENT_>
</SupplierPartID>
</ItemID>
<UnitOfMeasure/>
<Packaging>
<PackagingCode lang="">
<_CONTENT_>CTN</_CONTENT_>
</PackagingCode>
<PackageTypeCodeIdentifierCode>CTN</PackageTypeCodeIdentifierCode>
<ShippingContainerSerialCode>CTN1</ShippingContainerSerialCode>
<DispatchQuantity quantity="100">
<UnitOfMeasure>KG</UnitOfMeasure>
</DispatchQuantity>
</Packaging>
</ShipNoticeItem>
<ShipNoticeItem quantity="100" lineNumber="20">
<ItemID>
<SupplierPartID>
<_CONTENT_>Supplier Part ID 2</_CONTENT_>
</SupplierPartID>
</ItemID>
<UnitOfMeasure/>
<Packaging>
<PackagingCode lang="">
<_CONTENT_>BOX</_CONTENT_>
</PackagingCode>
<PackageTypeCodeIdentifierCode>BOX</PackageTypeCodeIdentifierCode>
<ShippingContainerSerialCode>BOX2</ShippingContainerSerialCode>
<DispatchQuantity quantity="10">
<UnitOfMeasure>KG</UnitOfMeasure>
</DispatchQuantity>
</Packaging>
</ShipNoticeItem>
</ShipNoticePortion>
<ShipNoticePortion>
<OrderReference orderID="Order ID 2" orderDate="20180202 093059">
<DocumentReference payloadID=""/>
</OrderReference>
<ShipNoticeItem quantity="200" lineNumber="20">
<ItemID>
<SupplierPartID>
<_CONTENT_>Supplier Part ID 3</_CONTENT_>
</SupplierPartID>
</ItemID>
<UnitOfMeasure/>
<Packaging>
<PackagingCode lang="">
<_CONTENT_>BOX</_CONTENT_>
</PackagingCode>
<PackageTypeCodeIdentifierCode>BOX</PackageTypeCodeIdentifierCode>
<ShippingContainerSerialCode>BOX4</ShippingContainerSerialCode>
<DispatchQuantity quantity="500">
<UnitOfMeasure>KG</UnitOfMeasure>
</DispatchQuantity>
</Packaging>
</ShipNoticeItem>
<ShipNoticeItem quantity="300" lineNumber="30">
<ItemID>
<SupplierPartID>
<_CONTENT_>Supplier Part ID 4</_CONTENT_>
</SupplierPartID>
</ItemID>
<UnitOfMeasure/>
<Packaging>
<PackagingCode lang="">
<_CONTENT_>BOX</_CONTENT_>
</PackagingCode>
<PackageTypeCodeIdentifierCode>BOX</PackageTypeCodeIdentifierCode>
<ShippingContainerSerialCode>BOX4</ShippingContainerSerialCode>
<DispatchQuantity quantity="500">
<UnitOfMeasure>KG</UnitOfMeasure>
</DispatchQuantity>
</Packaging>
</ShipNoticeItem>
</ShipNoticePortion>
</ShipNoticeRequest>
Here we can see that the items (<ShipNoticeItem>) are correctly assigned to the corresponding orders (<ShipNoticePortion>) and have the correct packinging nodes as children.
Summary
In this blog article we showed how we can use the available operations of the pre-transformation feature for solving even more complex mapping challenges like the restoration of a tree structure from a flat source message. We will be happy to know more about your use cases for this new feature.
Please, share your ideas and feedback in a comment. Furthermore, you can follow the SAP Integration Suite topic page to get to know more about Integration Suite in general, or read other posts on Integration Advisor. You can also follow my profile if you want to read similar content in the future.
Hi Stefan
Thank you for the blog. That makes this feature more understandable. But I'm actually looking for a way to achieve the reverse, means flattening of a nested structure. Is that possible too with this feature?
Thank you,
Philippe
Hi Philippe,
actually, we are working on a new operation to flatten a nested structure. Even with the current Integration Advisor without any pretransformation it is possible to flatten nested structures. Can you share more details about your scenario, like source message type and target message type, source payload and expected target payload. Then we can check if we already cover your scenario and, if not, it would help us for the development of the next operation.
Best regards
STefan
Hi Stefan,
What I basically tried to achieve is the following: We receive a DELFOR Edifact document that contains SG12 segments with different qualifiers (1 and 2 among others) in segment QTY-C186. The Edifact parser creates one SG12 segment in the XML per group, which is correct. However, if I want to map data from the DTM segment of another group (SG12 (1)) to the target which is mapped from SG12 (2)/QTY, I don't have access to it as SG12 (1)/DTM is not accessible as it's under a different qualified source group node.
That's the initial structure:
SG12
-- QTY (1)
---- C186
-- DTM
...
SG12
-- QTY (2)
---- C186
SG13
...
So my idea was to kind of flatten then SG12 segments by combining them, to get the following structure:
SG12
-- QTY (1)
---- C186
-- DTM
-- QTY (2)
---- C186
SG13
...
I have now achieved this via a custom built XSTL mapping which I use before the mapping from the Integration Advisor. It would be nicer though to have everything done in IA.
Philippe
Hi Philippe,
somehow I overlooked your reply. However, I didn't understand your scenario completely. Why can't you just map the DTM segment to your target? If you like you can export the whole Migs and Mag and send share it with me (stefan.boller@sap.com)
Best regards
Stefan