Skip to main content

Using the SAP BEx Connector

The purpose of the BEx Connector is to enable extraction of data from BEx queries in SAP BW.

Information note

This SAP connector is available in:

  • Qlik Sense Enterprise on Windows

  • QlikView

Warning noteThe SAP BEx Connector and SAP InfoProvider Connector will eventually be replaced by the SAP BW Connector. For more information, see SAP BW Connector user configuration and Using the SAP BW Connector.

Both the BEx and the OLAP Connector can be used to extract data from BEx queries to QlikView. The difference is the functionality in SAP that is used for query execution.

The BEx Connector utilizes the BEx query execution functionality available through BEx function modules in SAP BW. The OLAP Connector formulates an MDX-query and sends it to the MDX interface (MDX BAPI).

Using the SAP OLAP Connector

The use of BEx query execution function modules enables the BEx Connector to support time dependency in query variables and attributes. It also enables all built in SAP query optimization alternatives to be used. BEx query execution function modules also offer faster query execution than MDX resulting in faster loading of data from SAP to QlikView and Qlik Sense.

How the BEx Connector works

The load script for the Qlik BEx Connector consists of four parts: Dimensions, Measures, Units, and Variables. This information is sent to function modules on the SAP BW side where the BEx query is executed. Once the query execution is complete and the result is in a temporary table in SAP, the result is sent back to Qlik by way of the BEx Connector. The function modules used for metadata extraction and query execution is a mixture of SAP-delivered code and custom development.

When to use the BEx Connector

The Qlik BEx Connector offers an easy way to access existing logic in SAP BW, such as restricted and calculated key figures and characteristic text and key value in just one extraction. It is, however, important to be aware of the existing limitations for variables, hierarchies, and data volumes before assuming the BEx Connector can be used for all types of data extraction from SAP BW to Qlik.

Information noteWe strongly recommend that you use the BW Connector instead of the BEx and InfoProvider Connectors when creating new scripts.

Create an SAP BEx connection

Do the following:

  1. Open a new or existing Qlik Sense or QlikView app.

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

    A create connection dialog opens, like the following from the Qlik Sense Data load editor.

    Create new connection dialogue

Connection fields

Connection fields available to be configured
Connection field Description
Server Type Application Server or Message Server
Information noteDo not change the Server Type once you start to edit the remaining fields as a connection error may result. If this is the case close the Connect… dialog and restart the script editor.
Host Address of the Application Server or Message Server.
Client The client number of the SAP system.
System Nr The system number of the SAP Application Server system.
System ID / Port The system ID of the SAP Message Server system.If passing through a message server, an entry may have to be added in the C:\WINDOWS\system32\drivers\etc\services file. Add sapmsxxx 36nn/tcp, where xxx is the system ID and nn is the system number. If it is the last line in the file, add a new line break after the entry.You can also enter the Port number directly, instead of the System ID. The Port number is of the form 36nn where nn is the system number.
Group The Message Server group
Logon Language The language to use when logging into the SAP system. The default is to use the language that is set on the SAP system. The logon language is used to get the BEx names and descriptions that will populate the selection dialog. If the language is not specified, the user's default language in SAP is used. If the user's default language in SAP is not set, the system language of the SAP system is used. The language can be specified with either the SAP one-character or ISO two-character format (such as EN for English and SV for Swedish). You can also use the complete name of a language, such as English and Deutsch.
Username

A user name authorized to access the SAP system.

Information noteRemember that different users can get different results due to row based access control.

Access control

Password The authorized user's password.
BEx Legacy Data Types

Handles the data sent from SAP without data type mapping in the client. Mimics the data handling prior to version 7.0.2 . When using an old BEx script in the BW connector the connection string property 'BEx Legacy Data Types' should be turned on.

An old InfoProvider script can be used as it is.

Secure Network Settings

Select Secure Network Connection if Secure Network Communication is installed.

Secure network communication

Enable Server Side Trust

When creating a single sign-on (SSO) connection using server-side trust, you must leave the Username and Password fields blank in the SAP BEx connection dialog .

Select the Secure Network Connection and Enable Server Side Trust options instead, and supply the SNC Name.

For more information on single sign-on (SSO), see Single sign-on (SSO).

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

Connection string properties available in connection dialog
Properties Values Descriptions
SAPRouter Possible value is a string of host name and service name/ port in the following format: /H/hostname/S/portnumber Use this field to specify the SAP Router parameters if the connection needs to be made through a firewall via a SAP Router.
GatewayHost Not applicable The hostname of the Gateway at which you want to register. It must be the same as defined in SAP transaction SM59 (if the RFC destination defines an explicit gateway).
GatewayService Possible values are sapgw00 - sapgw99, 3300 - 3399 The Service name (as defined in etc/services) or the port number under which the gateway is listening.

Select and load data from an SAP BEx connection

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

Qlik Sense: Selection properties

Selection properties available to be configured
Properties Descriptions
All Providers The drop-down lists all providers that have at least one query that allows external access. The technical name or description of the InfoProvider can also be entered in the field. The name is filtered as each character is typed.
Query

The drop-down lists all queries that are in the BW system. To include inactive queries in the list, turn on the Show inactive queries switch. Inactive queries are queries that have not yet been executed or that have errors.

When Show inactive queries is turned off, only BEx queries that have been successfully loaded are displayed in the Select Query drop-down list. Inactive queries that fail to load because of errors must be fixed on the BW system.

All Query dimensions

Select the required dimensions.

Information noteThe maximum number of dimensions that can be extracted in a single load script is 49. To extract more than 49 dimensions from a BEx query, divide the extraction into several load scripts.

If the BEx query contains a Structure, the Structure must be selected for extraction in the BEx Connector's selection dialog. If there is more than one Structure, only one of the Structures can be selected. If there are dimensions in addition to the Structure, at least one dimension must be selected. Otherwise the script execution will fail in SAP (with an ABAP runtime error).

To keep the intended result of exception aggregation, the dimension selection must be the same as in the BEx query rows section (no free characteristics and all dimensions in the rows must be selected in the load script).

All Query measures

Select the required measures.

Variable names for measures are not supported (the measure will be named with the variable name instead of what the variable would return at execution).

Warning noteThe BEx Connector cannot expand hierarchical structures. All hierarchical structures must be expanded for the connector to be able to retrieve the data. The setting of the must be set to Expanded.
Technical names

Displays the technical names for each query dimension and measure.

This is optional. If you need to know the technical names for the dimensions and measures to make a selection, the Technical names switch displays them. The technical names are unique SAP identifiers that can be used to distinguish fields that have the same common name.

Query Variables

The fields contain the variables defined for the selected query. The Sign, Option, Low and High fields contain drop-down menus (where applicable) for defining the selected variable.

Information noteWhen the query dimensions and measures selected do not have query variables, the Query variables panel does not appear.

If the query contains a time dependent hierarchy with a date variable or a Key Date, those time variables will be listed in the variables section. If the query contains more than one variable that is used for defining time dependency, the variables will be linked to each other and set to the same date independent of which one is changed. The date that will be used as default, if Key Date is not actively changed, is today’s date. Transfer of explicit values for Key Date, that are set in the BEx query, is not supported.

Select Values

Click the Apps to select values for the query variables.

Values can be selected by scrolling through the list or by entering the value in Selected Values field. You can also use the filter field to find values. If the required value does not fall within the default value range (1 – 2000), the From and To values can be adjusted.

Where a single value variable is to be defined, only one value can be selected in the dialog. If the variable allows multiple values then more than one value can be selected in the dialog. The values will be added to the variable and be visible in the final script.

Where the variable requires an interval to be defined then the Option field must be changed to Between, otherwise the variable will be set to the Low value only, that is, no interval.

Script Preview

Shows load script built from the selections made.

QlikView: Selection properties

Selection properties available to be configured
Properties Descriptions
InfoProvider The drop-down lists all providers that have at least one query that allows external access. The technical name or description of the InfoProvider can also be entered in the field. The name is filtered as each character is typed.
Query

The drop-down lists all queries that are in the BW system.

Dimensions

Select the required dimensions.

Information noteThe maximum number of dimensions that can be extracted in a single load script is 49. To extract more than 49 dimensions from a BEx query, divide the extraction into several load scripts.

If the BEx query contains a Structure, the Structure must be selected for extraction in the BEx Connector's selection dialog. If there is more than one Structure, only one of the Structures can be selected. If there are dimensions in addition to the Structure, at least one dimension must be selected. Otherwise the script execution will fail in SAP (with an ABAP runtime error).

To keep the intended result of exception aggregation, the dimension selection must be the same as in the BEx query rows section (no free characteristics and all dimensions in the rows must be selected in the load script).

Measures

Select the required measures.

Variable names for measures are not supported (the measure will be named with the variable name instead of what the variable would return at execution).

Warning noteThe BEx Connector cannot expand hierarchical structures. All hierarchical structures must be expanded for the connector to be able to retrieve the data. The setting of the must be set to Expanded.
Set Query Variables

The fields contain the variables defined for the selected query. The Sign, Option, Low and High fields contain drop-down menus (where applicable) for defining the selected variable.

Mandatory variables in the query must be populated, otherwise there will be an ABAP runtime error in SAP (visible in transaction ST22) when the QlikView script is executed. A syntax check is performed in QlikView to ensure that the mandatory variables are populated, so the user must ensure this when creating the load script. Mandatory variables are marked with bold text and placed at the top of the list of variables in the Set Query Variables section.

If the query contains a time dependent hierarchy with a date variable or a Key Date, those time variables will be listed in the variables section. If the query contains more than one variable that is used for defining time dependency, the variables will be linked to each other and set to the same date independent of which one is changed. The date that will be used as default, if Key Date is not actively changed, is today’s date. Transfer of explicit values for Key Date, that are set in the BEx query, is not supported.

Select Values

To enter attributes click twice on the Set Query Variables field. Click on the > button to open up the Select Values dialog.

Values can be selected by either entering the value in Search, or by scrolling through the list. If the required value does not fall within the default value range (1 – 2000), the From and To values can be adjusted.

Where a single value variable is to be defined, only one value can be selected in the dialog. If the variable allows multiple values then more than one value can be selected in the dialog. The values will be added to the variable and be visible in the final script.

Where the variable requires an interval to be defined then the Option field must be changed to Between, otherwise the variable will be set to the Low value only, that is, no interval.

Formatted values

If the Formatted Values check box is left unchecked, the measures in the load script can be defined as either Unit or Currency and the script contains both lines required for the definitions. When the load script is inserted, comment out the lines that are not required.

Information noteIf the sort order of values in the variable selection is not in sequence, see if SAP Note 1567394 and SAP Note 971164 could be applicable to your SAP system.
Load Script Preview

Shows load script built from the selections made.

Default values

Optional variables with default values will still be filtered on the default values, even if the variable has been removed from the script. The default values will be overridden if another value is entered for the variable. To completely remove the filtering from the default values, remove everything but the variable name from the script:

With default values :

[NAME=Z_INT_DEF_OPT_ZROWNBR, SIGN=I, OPTION=BT, LOW=0000000001, HIGH=0000000100]

 

Default values removed:

[NAME=Z_INT_DEF_OPT_ZROWNBR]

 

Text Fields

Some dimensions have key and text fields, and some have only key fields (not known when the load script is generated). Inclusion or exclusion of compounded InfoObjects in the dimension selection will also affect the extracted texts.

Text fields are added automatically for all dimensions, whether there are texts or not in the BEx query. As a result, the load script will contain empty fields in the form <InfoObjectName>_TEXT when a dimension does not have a text field.

Whether the text is available in QlikView depends on the Value Display-> Display As setting available in the query.

Example image of Display As setting set to Key and Text

Non Navigational Attributes

Non Navigational Attributes are not listed amont the dimensions. These have to be added manually to the load script. In the load statement section of the script, add a line with the following syntax:

[InfoObjectName_AttributeName],

For example, if the desired attribute is the 0MANUFACTOR of the info-object 0MATERIAL, the line in script would be:

[0MATERIAL_0MANUFACTOR] as [Manufactor]

Hierarchical data

The data is returned as flat data to QlikView. This means that if a dimension is set to hierarchical display, the nodes and leaves will be in the same dimension when extracted to QlikView. To avoid node-names in the dimensions, set the Activate Hierarchy Display option, in BEx query designer, to unchecked:

Unchecked Activate Hierarchy Display option in query designer

To preserve the hierarchy display of the data, load the hierarchy separately using the Qlik SAP Extractor Connector and join the data by altering the QlikView script.

Limitations and suggested workarounds

Memory consumption

A common problem with the BEx Connector is that the SAP BW system runs out of memory when executing a load script that extracts a large amount of data. In general, SAP claims that BEx queries should not be used for mass data extraction. This is discussed in SAP Note 1968598.

How much data can be extracted at one time depends on the allocated memory on the app server where the SAP BW system is running. A common configuration is to have 2GB of memory available, which should allow an upper limit of approximately 500000 cells per extraction with the BEx Connector. There can, however, be other factors besides the amount of extracted data that affects the memory consumption during query execution. This is discussed in SAP Note 1879725.

If large amounts of data will be extracted from BEx queries, it is important to test the capacity of the SAP BW to make sure the extraction will not fail in production. It is possible to increase the query execution capacity in terms of data volumes by increasing the allocated memory in the SAP BW system. Make sure the BEx queries that will be used have variables that can be used to reduce the data volume, such as variables for time slicing (year, month, day).

Hierarchies

The BEx Connector extracts the data in a flat structure. This means that hierarchical structures in the BEx query will not be represented hierarchically in Qlik. The nodes and leaves of a hierarchy will be extracted to the same field (dimension) in Qlik, and that is usually not usable in an analysis scenario. As a workaround to this limitation, hierarchies can be extracted using the Qlik SAP Extractor Connector and then joined with the BEx data in Qlik.

Collapsed key figure structures

The BEx Connector cannot access key figures or characteristics that are hidden in collapsed structures. As a workaround to this limitation, edit the BEx query in Query Designer and set all nodes to expanded.

Customer exit variables

The behavior of customer exit variables in the QlikSAP BEx Connector is different from the behavior of customer exits in SAP reporting products. Some variables function as intended but some do not. The reason is that SAP and Qlik use different methods for extracting the query variables. If the customer exit variable is of type I_STEP=1, the CMOD code is not executed before the variables are extracted to Qlik. As a result, the variable is not populated with a value before the variables are loaded in the BEx Connector's selection dialog. The variable is, however, populated in the background once the query executes, unless it is overwritten with another value actively set in the selection dialog. If the customer exit variable is of type I_STEP=2, the CMOD code executes after the query executes, but the functionality might differ from the behavior of the variable in SAP.

Because there are various ways of implementing CMOD variables, the general recommendation is to test the behavior for the specific variable. In case of difficulties, change th BEx query to extract the correct data to Qlik.

Maximum number of dimensions

The maximum number of dimensions that can be extracted in a single load script is 49. To extract more than 49 dimensions from a BEx query, divide the extraction into several load scripts.

Structures

If the BEx query contains a Structure, the Structure must be selected for extraction in the BEx Connector's selection dialog. If there is more than one Structure, only one of the Structures can be selected. If there are dimensions in addition to the Structure, at least one dimension must be selected. Otherwise the script execution will fail in SAP (with an ABAP runtime error).

Hidden key figures

Hidden key figures are not accessible for extraction. Change the display setting for the key figure in Query Designer to access the key figure with the BEx Connector.