Using the SAP Open SQL Connector

SAP ECC (also known as SAP ERP) is enterprise resource planning software. Use Birst Connect and the SAP Open SQL Connector to extract data from SAP ECC version 6.0 and newer.

Watch the Video: SAP Open SQL Connector

Extracting data from SAP ECC to Birst requires you to install and configure transports that define remote function modules in SAP, and then edit the queries in Birst Connect. The SAP Open SQL Connector is different than the other Birst SAP Connectors in that it doesn't require ABAP programming or SAP queries. See SAP Connectors.

Prerequisites

• SAP ECC version 6.0 or newer
• SAP ECC EHP5 BASIS Release 702 or later
• Confirm that Birst Connect and the Infor SAP Open SQL Connector products are installed on the Birst account. See Checking Which Products Are Installed.
• Install the transport files that enable remote function calls and act as a wrapper for the SQL statements submitted by Birst.
  • SAP Basis Adminstrator user permissions are required.
  • Install and configure the SAP transports. Follow the instructions at Installing the Birst Transport Files for SAP.
• Install the SAP Java Connector library files sapjco3.dll and sapjco3.jar.  See Downloading SAP Library Files.
• Configure Birst Connect to run from the command line. See Running Infor Connect from the Command Line.

Implementation and Usage Notes

• The SAP Open SQL Connector supports querying transparent tables.
• There can be multiple SAP Open SQL queries in a single task.
• The connector supports basic SQL with simple WHERE clauses and INNER/LEFT OUTER JOIN. It does not support aggregation in the SELECT statement or in a subquery, in place subqueries, or subqueries in WHERE clauses.
• The connector was developed and tested on SAP ECC 6.0 with EHP6. The SAP JCo version is 3.0.12.

Known Issues

• Columns of type LRAW are not supported.
• Columns of length more than 999 are not supported.
• Due to a limitation in SAP ABAP data structures, there might be cases where a query would fail with an error: "A dynamically specified FROM clause has an unexpected format". Adding an extra white space in the FROM clause can resolve this error.
  Example with error:
  SELECT
  VBAK~MANDT,
  VBAK~VBELN
  FROM VBAK
  INNER JOIN VBRP ON VBRP~AUBEL = VBAK~VBELN
  JOIN BKPF ON BKPF~AWKEY = VBRP~VBELN AND BKPF~AWTYP = 'VBRK' AND (BKPF~CPUDT >
  '20150331') AND (BKPF~CPUDT %8B '20150701')
  INNER JOIN VBRK ON VBRK~VBELN = VBRP~VBELN
  WHERE VBRK~VTWEG = 'OG' AND VBRK~VKORG = 'SG01'
  
  Adding a white space before the JOIN keyword fixes the error:
  
  SELECT VBAK~MANDT,
  VBAK~VBELN
  FROM VBAK
  INNER JOIN VBRP ON VBRP~AUBEL = VBAK~VBELN
  JOIN BKPF ON BKPF~AWKEY = VBRP~VBELN AND BKPF~AWTYP = 'VBRK' AND (BKPF~CPUDT >
  '20150331') AND (BKPF~CPUDT %8B '20150701')
  INNER JOIN VBRK ON VBRK~VBELN = VBRP~VBELN
  WHERE VBRK~VTWEG = 'OG' AND VBRK~VKORG = 'SG01'

To set up the connection to SAP ECC

1.

Start Birst Connect, add the task and set its properties. See Using Birst Connect.

2.

Click Add Task.

3.

Click the Sources tab.

4.

Click Add SAP Open SQL. The SAP Connection Details dialog opens.

5.

Enter the connection information:

Router Host

Router Port

Server Host

Server Name

Server Port

AS Host String

System Number

Client

UserName

Password

Language - for labels

6.

Click OK to save the connection details.

7.

Click Test to test the connection.

To set up data extraction from SAP ECC

8.

Open the Query Object dialog.

9.

Enter the Object Label of the source to be used.

10.

Enter the SAP table name for which Birst Connect generates the initial query and mapping information. You edit this information in Step 5.

11.

Click Get Query to generate a query that select all columns from the table.

Birst Connect creates a query that specifies the qualified column name using the format: TableName~ColumnName.

Birst Connect creates mapping syntax to map the technical column name in the SAP source to a user-friendly column name, a label, for use in Birst. By default the label is also from SAP. The syntax is:

UserFriendlyColumnName=TableName_TechnicalColumnName=IsPrimaryKey(true/false)

Important: The mapping syntax specifies that a column is a primary key using the syntax =true. Columns that are not primary keys are marked =false. Do not change these designations.

12.

Edit the query:

• Delete the columns you don't need from the query.
• Optionally, you can add the following syntax:
  • Inner joins
  • Left outer joins
  • WHERE clause
  • Existing Birst variable
• Add single spaces around operators ( < , >, = , etc.) in a query.

13.

Edit the mapping to match the query updates.

14.

Optionally, change the user-friendly column name labels.

Characters allowed in labels include: alphanumeric, space, underscore, and hyphen.

Trailing spaces are not allowed in labels.

Duplicate labels are not allowed.

15.

Optionally, enter the Batch Size if the data is to be retrieved in chunks. The default is 20000.

16.

Optionally, enter Extract Top Records if top few records are to be extracted . The default is to extract all records.

17.

Click Validate to validate the query and mapping information.

18.

Click Save to save the source details.

19.

Optionally, set up scheduling.

20.

Select File, Save Settings in the main Birst Connect window.

21.

To immediately extract, select the task you created and click Run Tasks Now.

Next Steps

22.

See the results in Birst, for example:

23.

Process the data. See Processing Data.

24.

Use the processed data in reports and dashboards.