Using the SAP SQL Connector

Connections to the Qlik SAP SQL Connector are made from the list of connectors in Qlik Sense.

Supported objects

The SAP SQL connector supports the following SAP objects only:

• Transparent table
• CDS DDIC-based views (to be continued)

Create an SAP SQL connection

To access SAP SQL data, you need to know the type of server you want to connect to, and have access credentials. When using Qlik Sense SaaS you must also have a valid license, one that supports Qlik connector for use with SAP NetWeaver.

Do the following:

1. Open a new or existing Qlik Sense app, or add the connection as a data source in a space.

2. Select the SAP SQL from the list of connectors.

   A create connection dialog opens.

3. From the Direct Gateway drop-down list, select the data gateway through which you need to connect to your data source.

   Information noteThis field will only appear if you selected the "via Direct Access gateway" data connection. Users that need to leverage gateway-enabled data connections must have the Can Consume Data permission for the space with which the gateway is associated.

Connection fields

Passing through a SAP Router

Do the following:

1. When you are creating the connection, go to the Application Server Host/Message Server Host field.

2. Input the following string: /H/<Host name or IP number of SAP Router>/S/3299/H/<Fully Qualified Domain Name of target system>

   The port which the SAP Router is listening to is always 3299.

3. (Optional) Fill in the following connection string properties: SAPRouter, GatewayHost, and GatewayService. These are described below under "Connection string properties".

   This step is not mandatory, but is useful if the connection needs to be made through a firewall via SAP router.

4. If passing through a Message server, please read that section below. Note that the services file must also be updated on the SAP Router server. In that case, the SAP Router needs to be restarted.

Passing through a Message server

If passing through a Message server, you may need an entry for the Message server port in the services file.

Do the following:

1. Locate the services file in: C:\Windows\System32\drivers\etc

2. Add sapmsxxx 36nn/tcp, where:

   1. xxx is the system ID

   2. nn is the system number (of the Message server).

   If it is the last line in the file, add a new line break after the entry.

There is an alternative to updating the services file. When you create the connector connection, you can enter the Message server port number directly, instead of the System ID.

The port number is of the form 36nn, where nn is the system number.

Connection string properties

There are a number of properties that can be added to the connection string in either the Qlik Sense Data load editor if needed. Normally, the default values for these properties are sufficient. The following can be added as advanced properties in the connection dialog:

Select and load data from an SAP SQL connection

Once a connection to SAP has been established, you can select data and load it into a a Qlik Sense app.

Qlik Sense: Selection properties

Data types in SAP

A field in an SAP database table is assigned to a Data Type. Fields assigned to the following Data Types cannot be downloaded by the SQL Connector:

• LCHR (Long character string, requires preceding INT2 field)
• LRAW (Long byte string, requires preceding INT2 field)
• RAWSTRING (Byte String of Variable Length)

Changing the field names

While the SELECT and LOAD statements are built automatically in Qlik Sense when you select tables and fields, you can make certain changes to those statements.

Field names can be changed in the Fields panel by clicking on the existing name and editing the name text.

The changed name is then used as an alias for the original name when the field is loaded into Qlik Sense.

STORE and DROP TABLE

STORE and DROP TABLE statements are automatically added to the generated script. However they are commented out as it is necessary to manually add a folder data connection that will be used in the STORE statement.

Example:  

LIB CONNECT TO 'H6Q';

[VBAK]:

LOAD MANDT,

NETWR,

VDATU;

SELECT MANDT, // Client

NETWR, // Net value

VDATU // Req.dlv.dt

FROM VBAK;

// STORE * FROM [VBAK] INTO LIB://[FolderConnection]/VBAK.qvd;

// DROP TABLE [VBAK];

 

If the statements were not commented out a script execution error would be raised, as there is no folder data connection named [FolderConnection]. A folder data connection must be manually created before running the script.

If you want to use the STORE and DROP TABLE statements, then once the folder data connection has been added to the app, the statements can be uncommented.

ScriptBuilder

ScriptBuilder is a QlikView application, ScriptBuilder.qvw, that is used to find tables to download from the SAP system and to generate the script code. We recommend you copy the complete folder to be able to use this application for several SAP systems.

C:\ProgramData\QlikTech\Custom Data\QvSAPConnector\ScriptBuilder\.

Start with the ReLoadSAPDD.qvw application, which downloads data from the data dictionary of the SAP system. Since the content of the dictionary differs for different variants and versions of SAP, this is a necessary first step.

ReLoadSAPDD.qvw creates .qvd files that can be loaded to the ScriptBuilder application.

Change the script regarding the connection and language in this application prior to doing a reload.

The Popular Tables and Data Models sheets may refer to tables that do not exist in the version of SAP used.

Detailed usage instructions can be found in the application.

SQL SUBSELECT syntax

An addition to the standard SAP OPENSQL syntax in the SQL Connector is SUBSELECT. This addition has been developed since JOIN or SUBSELECT cannot be done with cluster tables. Quite often this is required for tables like BSEG or KONV in order to do delta loads.

The result of the select from the main table is temporarily stored in the ABAP program in an internal table. There is a size limitation to internal tables, so try to avoid SELECT *. Only select the fields necessary.

When using the SUBSELECT method against big database tables, always try to limit the selection with a WHERE condition which is using KEY fields or INDEXED fields. Before using the SUBSELECT statement in a production system, monitor it in a test system to make sure it does not consume too much memory or affect the system performance.

The syntax of SUBSELECT in the script is as follows:

SQL SUBSELECT BUKRS BELNR GJAHR BUZEI BUZID AUGDT FROM BSEG WHERE BUKRS BELNR GJAHR IN ( SELECT BUKRS BELNR GJAHR FROM BKPF WHERE BLDAT GE '20070101' );

• Only one SUBSELECT is allowed (within the parenthesis).
• The SUBSELECT can have condition(s).
• One or many comparison fields can be passed.
• If the SELECT of the sub-table returns duplicates, they are removed before selecting from the main table. So, there is no need (and it does not work) to use the DISTINCT addition to the SUBSELECT.
• The comparison field or fields has to match between main and SUBSELECT. The field names of the comparison fields do not have to be the same in the main and SUBSELECT, but the corresponding fields (of main and SUBSELECT) must have the same data types.
• To achieve good performance, it is important to provide as many of the key fields in the main table as possible. Try to select them from the sub-table. The most important item to provide is the first key field (after client).
• If the SUBSELECT fails, the reason can often be found in the log for the job /QTQVC/READ_DATA in transaction SM37.

Other examples of SUBSELECT statements:

Example 1:  

SQL SUBSELECT KDATU KAWRT KBETR WAERS FROM KONV WHERE KNUMV IN ( SELECT KNUMV FROM VBRK );

Example 2:  

SQL SUBSELECT CHANGENR TABNAME TABKEY FNAME CHNGIND VALUE_NEW VALUE_OLD FROM CDPOS WHERE OBJECTCLAS OBJECTID CHANGENR IN ( SELECT OBJECTCLAS OBJECTID CHANGENR FROM CDHDR WHERE CHANGENR BETWEEN '0000100000' AND '0000300000' );

SQL WIDESELECT syntax

Another addition to the standard SAP OPENSQL syntax in the SQL Connector is WIDESELECT.

SAP database fields with a column width exceeding 800 characters cannot be extracted by the ordinary SQL Connector.

With the Wideselect functionality there is no limitation on column width. It is possible to extract one wide field at a time together with a number of associated key fields.

• The key fields are optional.
• A key field can be any field in the database table with a fixed column width.
• The sum total of the column widths of the key fields cannot exceed 255 characters.
• The WIDE field must be of the datatype CHAR (character) or STRG (string).

Two parameters are applicable for the key fields in the Wideselect connection string:

The syntax of WIDESELECT in the script is as follows:

SQL WIDESELECT keyfield1 keyfield2 keyfieldn WIDE(widefield) FROM table WHERE <condition>

Example:  

SQL WIDESELECT object joining_date WIDE(test_string) FROM zhrgtest WHERE connector = ‘WIDE12000’;

 

SQL CDSSELECT syntax

Another addition to the standard SAP OPEN SQL syntax in the SAP SQL Connector is CDSSELECT. An SAP HANA system with SAP BASIS 740 SP08 or higher is required to use the functionality.

It is possible to select CDS views with parameters.

A CDS view has a name in the ABAP Dictionary. It also has a DDL (Data Definition Language) Source name. For CDSSELECT, the DDL Source name should be used.

The syntax of CDSSELECT in the script is as follows:

SQL CDSSELECT [*] [fieldname_1, fieldname_2, fieldname_n]

FROM 'CDS view name'(

parameter_1 = 'X',

parameter_2 = 'X',

parameter_n = 'X' )

WHERE [condition];

• If there are parameters, the left parenthesis must be placed on the same row, immediately after the CDS view name, with no white space. Following the left parenthesis, there must be at least one white space before the first parameter. After the last parameter, there must be one white space before the right parenthesis.
• The field names and parameters are comma-separated.

Example:  

CDS view with parameters:

CDS view without parameters:

The CDSSELECT method cannot be used if the row-based access control concept of the SQL Connector is activated.

The available Connection String Properties are the same as for the ordinary SQL connector.