This Blog will provide information about running SAP applications on SQL Server and Windows. The Blog is written by folks of Microsoft who are working with SAP and SQL Server for more than a decade.
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 : http://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.
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 : http://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.
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.
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.
nice to know new thing or new feature
Can Micorosoft Connector 1.1 be used to load data into SAP BW 7.3
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.