Extracting hierarchies from SAP BW 7.3
1. the issue
In 2009 Microsoft certified the Microsoft Connector 1.1 for extracting data from SAP BW 7.x via the
Open Hub Service API :
The SSIS connector for SAP BW is for free but has to be downloaded from the SQL Server "feature pack".
Here is a link to the 2008 R2 feature pack :
You have to scroll down until you find
"Microsoft® Connector 1.1 for SAP BW for SQL Server® 2008 R2".
Additional documentation can be found here :
https://msdn.microsoft.com/de-de/library/dd299430.aspx
Unfortunately the Open Hub Service API had a restriction regarding hierarchy information. It was possible
to extract InfoObject attributes as well as texts - but not hierarchies. The feature simply wasn't there.
2. how the issue was handled in the past
As mentioned above the Open Hub Services API didn't support hierarchy extraction in the past. Therefore
it was necessary to look for alternative ways in order to extract the data.
I published a little paper here : https://www.microsoft.com/enterprise/partners/sap/technology.aspx
You have to look in the "Technology->Interoperability" section under "Business Intelligence". There you
find a little paper with the title : "How to unload Hierarchies from SAP BI 7.0" :
How to unload Hierarchies from SAP BI 7.0
This paper shows a few options how to solve the problem.
3. how the issue was solved in BW 7.3
One new feature in SAP BW 7.3 is the improvement of the Open Hub Service interface to allow extraction
of hierarchies. The Open Hub Destination mechanism works the same way as with InfoObject attributes
or texts. Therefore no change was necessary on the Microsoft Connector side. It works absolutely
transparent for SSIS.
The following walk-through section shows some screenshots of a simple hierarchy extraction example.
This blog just focuses on the data extraction. How to use this data to map the hierarchy into SSAS is a
different story and will be handled in a separate blog. While it's easy to do this with a basic parent-child
hierarchy there are some challenges when compound keys are involved or in case the BW hierarchy is
time dependent.
4. walk-through section to show how the new SAP BW 7.3
feature works
A very simple hierarchy was created manually with SAP BW 7.3. It should reflect a kind of org chart where you
will see different managers as hierarchy nodes and finally some employees on the leaf level.
When creating an Open Hub Destination BW 7.3 offers the new Subobject Type "InfoObject Hierarchies".
This didn't exist in the past. So far you could only choose between Attributes or Texts.
The creation of the corresponding "Transformation" and "Data Transfer Process" works the same way as
with the other object types.
And it's again necessary to create a process chain which triggers the DTP to copy the data to the
Open Hub Destination - in case the data extraction should be started out of SSIS.
Like in the past one has to look for the appropriate entries in the dialog box of the SAP BI Source
component within SSIS. In this sample I just selected the Open Hub Destination and the Process
Chain which I created before. The RFC destination existed already and was used for other SSIS
tests in the past.
Voila ! Here we go - SSIS successfully extracted the sample hierarchy from BW 7.3.
Looking at the target table which we get in SQL Server we see columns like "H_PARENTID" and "H_CHILDID".
The values in these columns represent the hierarchy structure. As mentioned before it's a different story how
to map this into SSAS. This will be described in a separate blog.
Comments
Anonymous
October 12, 2011
nice to know new thing or new feature thanksAnonymous
November 09, 2011
Can Micorosoft Connector 1.1 be used to load data into SAP BW 7.3Anonymous
November 10, 2011
Hi, go to the official SAP list of certified solutions : www.sap.com/.../SearchSolution.epx Type in "Microsoft" as the company. Then you will get a list which includes SSIS. Click on this entry and there you can see that the connector was certified for extracting data from SAP BW via the Open Hub API and also for the so-called staging BAPI to load data into SAP BW. As far as I know the staging BAPI didn't change in 7.3. It should work. Regards Hermann