Using the SAP BEx Connector
The purpose of the BEx Connector is to enable extraction of data from BEx queries in SAP BW.
This SAP connector is available in:
-
Qlik Sense Enterprise on Windows
-
QlikView
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).
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.
Supported objects
The SAP BEx connector supports the SAP BEx query object only.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.
Create an SAP BEx connection
Do the following:
Open a new or existing Qlik Sense or QlikView app.
Select the SAP BEx from the list of connectors.
A create connection dialog opens, like the following from the Qlik Sense Data load editor.
Connection fields
| 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. |
| 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. |
| 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:
When you are creating the connection, go to the Application Server Host/Message Server Host field.
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.
(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.
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:
Locate the services file in: C:\Windows\System32\drivers\etc
Add sapmsxxx 36nn/tcp, where:
xxx is the system ID
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
| 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
| 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 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. |
to select values for the query variables.QlikView: Selection properties
| 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.
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:
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.