SAP BW: Importing Hierarchies from Flat Files created using Open Hub Destination
I will be sharing here a different approach other than the very old program that downloads SAP BW Hierarchies to flat files.
That is usually done to keep the maintenance of the SAP BW Hierarchies in Development Box and Export/Import them through SAP BW systems.
In order to upload the file back to SAP BW we just need the Data Source based on Hierarchies. Nothing new here.
However, I came across an issue that happens very often with the sorting (or order) of the nodes. This because the segment 3 of the datasource (Hierarchy Structure) does have the fields Child ID and Next ID in its metadata. Those fields provide the hierarchy with the right positioning of the nodes:
as we can see here, the right order for the hierarchy are nodes 115, 88 and 89.
This is what we can see from the segment datasource based on Hierarchy for segment 3 (Structure):
To make things a bit more difficult, the export file provided by the custom ABAP program (the one out there!) has the sorting by Node ID:
so, by the time we import the file here (in this sample) the results will be the following:
Hierarchy is sorted by NodeID from the underling node(no matter what). It lost its original presentation. (fig. 1)
We could just ignore the old solution and try something else. To bypass this issue I carried out the following steps:
- New Open Hub Destination based on SAP BW Hierarchies to a Flat File
- New “Flat File” datasource using the Open Hub Destination previously created
- Transformation from the datasource to the infoobject (Hierarchy)
Open Hub Destination based on Infoobject Hierarchies: I will not go through the creation of the Open Hub here. Just follow this post for the complete details.
New flat file Datasource using the Open Hub as a template: keeping it simple. Just select the one created on the previous step.
This is the new structure now showing all the internal fields:
Transformation based on the new Datasource: In this case, I coded an ABAP Expert routine. This is because the regular transformation based on Hierarchies has 5 segments (Header. Hierarchy Description, Structure (nodes), Texts for the Nodes, and Texts for Hierarchy levels (not needed here)). Moreover, the Datasource based on the OHD file is not segmented. Only one structure is provided.
Inside the Expert Routine, the transformation based on SAP BW Hierarchies in this case uses the following 4 ABAP internal tables:
- RESULT_PACKAGE_1 : Header
- RESULT_PACKAGE_2 : Hierarchy Description
- RESULT_PACKAGE_3 : Hierarchy Structure
- RESULT_PACKAGE_4 : Hierarchy Nodes Text
The ABAP code is very simple. I only had to populate the internal tables accordingly using MOVE-CORRESPONDING based on the source_package:
DATA: ls_source_fields TYPE _ty_s_sc_1.
* Hierarchy Header – RESULTS_PACKAGE_1
APPEND RESULT_FIELDS_1 TO RESULT_PACKAGE_1.
* Hierarchy Description : RESULTS_PACKAGE_2
APPEND RESULT_FIELDS_2 TO RESULT_PACKAGE_2.
* Hierarchy Structure : RESULTS_PACKAGE_3
* TExt for Hierarchy Level: RESULTS_PACKAGE_4
APPEND RESULT_FIELDS_4 TO RESULT_PACKAGE_4.
if you follow those steps the Hierarchy will be imported as same as the original one. No need to use that old program again.