Skip to Content

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.

Open Hub Overview 

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

  • Straightforward and easy to manage

  • Mature processes are available, which are supported by many legacy systems

  • No support for fixed-width format

  • Field delimiter is limited to one-character only

  • Escape delimiter is not supported, thus the delimiter character must be cleansed in any text field where delimiter could be a valid character

  • Unicode encoding is in UTF-8 only

  • Need investment of file server

Database Table

  • Easy to view the extracted data within same SAPGUI

  • No need to maintain extra file server

  • Unicode is retained in database without any re-encoding problem

  • The OH tables are in the same database with SAP BI DB, thus the access to OH tables need careful DBA work to manage the security without jeopardizing the security restriction to SAP BI DB layer.

  • Extra development is required in order to fetch the data from OH tables to external database.

Third-party

  • Flexible in managing the data through existing OH function modules provided by SAP

  • No need to maintain file server or OH table access

  • Unicode is retained

  • Extra development is required in order to work with the third-party interface

  • Unit testing requires more time as it involves the debugging on two systems: SAP BI itself and third-party system.

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 Overview

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:

  • SAP BI Source, to extract data from SAP BI
  • SAP BI Destination, to load data into SAP BI
  • SAP BI Connection Manager, to manage the RFC connection between the Integration Services package and SAP BI

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

  • Microsoft Connector 1.0 for SAP BI can only be used with SQL Server 2008 Integration Services. However, you can load data from or extract data to SQL Server 2008, SQL Server 2005, or SQL Server 2000 databases.
  • Librfc32.dll is a component owned by SAP. Microsoft does not support this SAP component and assumes no liability for its use.
  • Microsoft Connector 1.0 for SAP BI does not support SAP BW 3.5 and earlier versions.
  • Extracting data from an SAP BI system by using Microsoft Connector 1.0 for SAP BI only supports Open Hub Destinations. It does not support InfoSpokes, because InfoSpokes are obsolete in SAP NetWeaver BI.

The application scanarios, InfoCube-to-SSAS Cube use case, and step-by-step configuration guide can be found in the whitepaper.

Case Study

Here we use the SAP Account Payable extract as an example. The green triangles shown in Figure 1 represent “delta” dataflows.

image

Figure 1: FI AP extract dataflow

 

image

Figure 2: Process chain

 

image

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:

image

Figure 4: SSIS package parallelism

Conclusion

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.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

    1. Chunhui Zhu
      Normally OH license depends on the number of target systems. In reality it also depends on how the license agreement with SAP is negotiated. You might want to confirm with your SAP rep.

      Thanks.

      (0) 
  1. Naga Udaya Kumar Pavuluri
    This is an excellent blog, explaining the new concept and the new RFC destination for the Open Hub.

    Do you know if we have a connector, like the Microsoft Connector, for the Oracle Server. On our project, we use open hub extensively, but our destination Server is an Oracle Server.

    Thanks in advance.

    Naga Pavuluri

    (0) 
  2. Prakash Holalkere
    Hi Chunhui,

    Reminds me of the days when we started off on this and it seems you have made good inroads into the whole integration between SSIS and SAP BI. Thanks for sharing this with everyone.

    Prakash

    (0) 
  3. Matthias Konetzny
    Hi Chunhui,

    I’ve read the article you mentioned in the top of your BLOG within the URL. Herein I’ve a question concerning the Scenario 2: Loading Non-SAP data into SAP BI, starting page 11.

    Within the section, at figure 21, an RFC destination will be prepared with a registered server program. But when testing the connection, a failure occurs, that program is not registered.
    Now the question will be: which way do you prefer to register the mentioned program id?

    Regards
    Matthias

    (0) 
    1. Chunhui Zhu
      The RFC is set up to be activated during run time, so when you build the RFC and test connection, it should fail, but once you set up the entire ETL, and trigger the load from Microsoft BI Studio, then you go to SM59 to test again, SAP will report that it connects OK.

      (0) 
  4. Donald Wrighter
    I see in SQL Server 2008, the SAP BI Connector is provided.  However, we are running SQL Server 2005, and will probably not go to SQl Server 2008 for another year.  Is there the ability in SQL Server 2005 (via SSIS?) to get data from SAP BI?
    (0) 
    1. Chunhui Zhu
      Unfortunately the BI Connector, which is free from Microsoft, is only compatible with SSIS 2008. You may install SSIS 2008 component only without the whole SQL Server 2008 database, to work with SQL Server 2005 database.

      If this is still not an option, then you have to look at other similar solutions like XtractIS which is not free.

      Thanks.

      (0) 
  5. Maik Burbach
    Hi Chunhui,

    we are currently trying to connect an SQL Server 2008 with SAP BI 7.0.

    The connection itself works fine, SAP metadata is visible in SSIS and we can trigger a process in SAP BI, but somehow the notifications between SQL Server and SAP BI do not work properly when we try to extract data from SAP BI. SSIS log shows the correct number of lines moved to the OpenHub destination, but the data is not extracted out of the OH destination to SQL Server although the technical status of the request is green.

    Is there any hint to locate the cause of the issue?

    Regards

    (0) 
    1. Chunhui Zhu
      Technical status shows the OHS DTP is successful, but the overall status will only be set from SSIS side.

      1) Make sure the OHS destination type is “Third-party Tool”.
      2) Go to “SAP BI Source” in SSIS, make sure the “Execute mode” is “P – Trigger process chain”.

      If all these settings are correct, please email the screenshots (the DTP RSMO and SSIS log) to chunhui dot zhu at microsoft dot com. We will get it resolved.

      (0) 

Leave a Reply