Open Hub is the official way to unload data from SAP BI. This blog explains how to link SSIS with SAP BI and extract BI data to Microsoft SQL Server. This is made possible via the free Microsoft Connector 1.0 for SAP BI component.
SAP BI Open Hub (OH), which is the official way to unload data from SAP BI, supports three types of destinations: CSV file, database table, and third-party tool. Here is a comparison of the three options:
OH Options | Pros | Cons |
CSV File |
|
|
Database Table |
|
|
Third-party |
|
|
The flat file approach is likely the most popular among the three. However, cleansing data in SAP BI to avoid the conflict with delimiter character is a hassle. Lots of transformations, even it is just one line of REPLACE statement, can add up the overall development and slow down the performance. If the target database is SQL Server, then Unicode is another big headache, because SQL Server 2000/2005/2008 only supports UCS-2 (or UTF-16), while SAP OH files are only in UTF-8 format by design. Converting UTF-8 to UCS-2 adds an extra step to the overall process.
If the requirement is to extract data from SAP BI to downstream SQL Server, now there is a *free* tool available: Microsoft Connector 1.0 for SAP BI, which leverages the OH third-party interface. The connector encapsulates the OH third-party function calls and provides a simple interface to manage the OH dataflow to SQL Server.
Microsoft Connector 1.0 for SAP BI is delivered in the Microsoft SQL Server 2008 Feature Pack. It enables data extraction from and to SAP NetWeaver BI in both Full and Delta modes via standard interfaces, within the Microsoft SQL Server Integration Services (SSIS) environment. The SAP datasets supported by the connector include SAP BI InfoProviders like InfoCubes, Data Store Objects (DSO), and InfoObjects.
The Microsoft Connector 1.0 for SAP BI has three main components:
Microsoft Connector 1.0 for SAP BI is an add-in for SQL Server Integration Services. It provides an efficient and streamlined solution for integrating non-SAP data sources with SAP BI. It also enables the construction of data warehouse solutions for SAP data in SQL Server 2008, where SAP BI is exposed as a data source of SQL Server.
Notes
The application scanarios, InfoCube-to-SSAS Cube use case, and step-by-step configuration guide can be found in the whitepaper.
Here we use the SAP Account Payable extract as an example. The green triangles shown in Figure 1 represent "delta" dataflows.
![]()
Figure 1: FI AP extract dataflow
![]()
Figure 2: Process chain
![]()
Figure 3: SSIS package
Executing the SSIS package will trigger the process chain in SAP BI. When the DTP in SAP BI is completed, the DTP request's technical status is set to green, which means the data is ready in internal OH table. At this moment, the DTP request's overall status is still yellow. Immediately after that, the data further flows through the third-party OH interface to SQL Server (via the Connector), and SQL Server side is able to track the data packets. Once SQL Server successfully receives all the data, the Connector will set the DTP request's overall status to green, and SSIS package execution will turn green as well.
A common problem of using the Connector is that the default timeout value of 300 seconds is not long enough. This value needs to be slightly longer than the longest Open Hub DTP execution time. There is no hard rule for it, but any value between 300 and 3600 should be acceptable under normal delta data ETL circumstances. For further details how to manage the Connector, check the whitepaper.
Performance
The single-threaded SSIS which runs the connector does not utilize network throughput fully. For a typical dataset like SAP Account Payable line item extract, the performance is several million rows per hour. Such performance can be significantly improved through parallelism in linear factor. For example, running three(3) SSIS packages based upon partitioned Open Hub extracts at same time can achieve 3 times faster ETL performance on a dual-core workstation. 10 parallel SSIS packages can achieve nearly 8 times faster performance on same dual-core workstation, and should get close to 10 times if on a better hardware platform. However, once the hardware capability is saturated, the performance improvement will not be linear to the number of threads any more, so it needs a thoughtful balance during System Integration Testing.
This can be quite helpful in the case of initial full load or full repair request load, which is easy to be partitioned. The partitioning and parallel executing approach does not apply to delta load scenario though. But on the other hand, delta usually does not have huge volume, thus partitioning is not needed.
The example of parallelism of SSIS and Microsoft Connector, in a very simplified way for demonstration purpose, looks like this:

Figure 4: SSIS package parallelism
This weblog introduces the approach to move full or delta data from SAP BI to SQL Server via SAP approved interface and the new Microsoft Connector 1.0 for SAP BI. Further interest to the details of its configurations can be found in the Microsoft whitepaper.
This pattern based upon Microsoft Connector has been implemented in production successfully at Microsoft IT. Feel free to ping me for any questions around the Connector, SAP BI implementation at Microsoft IT, and MS-BI and SAP-BI integration.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 524 | |
| 263 | |
| 238 | |
| 234 | |
| 167 | |
| 157 | |
| 152 |