The Qlik SAP InfoProvider Connector connects Qlik Sense and QlikView to five types of InfoProviders in SAP BW:
- InfoCube (CUBE)
- MultiProvider (MPRO)
- DataStore Object (Advance) (ADSO)
- CompositeProvider (HCPR)
- Open ODS View (FBPA)
This SAP connector is available in:
Qlik Sense Enterprise on Windows
The InfoProvider Connector extracts the characteristic key values and the measures that constitute the InfoProvider. Data is extracted in packages, and there is no limit to the amount of data that can be extracted at one time.
The InfoProvider Connector adds RowNum as a row-number counter to the fields that can be selected by the connector. In addition to counting the rows (starting at 1), the RowNum counter ensures that the extracted rows are unique.
The functionality of the InfoProvider Connector resembles the functionality of the transaction LISTCUBE in SAP BW or the Display Data functionality in SAP BW and HANA Studio. Fields or InfoObjects that are not extractable through these interfaces will not be extractable by the InfoProvider Connector either.
Create an SAP InfoProvider connection
Access to the Qlik SAP InfoProvider Connector is provided by QlikView and Qlik Sense. QlikView apps connect to data sources through the Script editor. Qlik Sense apps connect through Add data or the Data load editor.
Do the following:
Open a new or existing QlikView or Qlik Sense app.
Select the SAP InfoProvider from the list of connectors.
The SAP InfoProvider connection dialog opens with the following fields:
Connection fields available to be configured Connection field Description Server Type Application Server or Message ServerInformation 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 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. 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 InfoProvider 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. Secure Network Settings
Select Secure Network Connection if Secure Network Communication is installed.
Connection String Properties
Additional properties to use for the connection. The connection string properties do not appear in the QlikView connection dialog, but the properties can be added to the load script. In Qlik Sense, the properties can be added by selecting the properties in the connection dialog.
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.
There are a number of properties that can be added to the connection string, if needed. Normally, the default values for these properties are sufficient. If the properties are added manually to the connection string, separate the properties with a ; (semi-colon) in the connection string.
The following can be added as advanced properties in the connection dialog:
|DB Aggregation||0/1 (default/off = 0, on = 1)||When turned off, the field RowNum, which is added by the connector, guarantees that the extracted rows are unique. When turned on, SAP aggregates the selected measure values in relation to the selected characteristics.|
|Use_Aggregates||0/1 (default/on = 1, 0 = off)||When on, data is read from aggregates if the InfoProvider is a cube.|
|Currency_Conv||0/1 (default/on = 1, 0 = off)||Currency key figures are stored in the database with two decimal places. For output, these amounts have to be converted to a format according to the decimal places definition in table TCURX. Because the converted currency is written to a field that only has two decimal places, conversion of currencies with more than two decimal places causes rounding errors.|
|ConvRoutine||0/1 (default/off = 0, on = 1)||Indicates that output conversion routines are used. This is commonly used for fields like Material Number (MATNR).|
|CheckSeparator||0/1 (default/off = 0, on = 1):||A specific combination of characters is used as field separator in the connector. This combination may occur in a field in the database and cause an error. If that happens, turn on this property to make the connector try alternative character combinations. Since this slows down the performance, it is only to be used when necessary.|
|ReplaceNullvalue||0/1 (default/on = 1, 0 = off)||
If on, replaces all ’null’ values in the data with ‘ ‘ (SPACE) before sending it to Qlik Sense.
|Trace||0/1 (default/off = 0, on = 1)||Turns on/off the trace functionality in SAP programs. The trace information is written in the table /QTQVC/TRACE.|
|PacketSize||n (default = 20000)||The maximum number of rows in a packet that the connector downloads with each fetch operation. This is re-calculated by the connector and might be reduced automatically, depending of the actual amount of shared memory in the SAP system. The PacketSize setting can be further limited by MaxRows.|
|MaxRows||n (default = 0, where 0 means no limit)||
The maximum number of rows the connector downloads. MaxRows works independently from PacketSize and sets a limit on the total number of rows to download.
For example, if PacketSize is set to 100 and the query returns 1000 rows, the data will be downloaded in 10 packets of 100 rows each. But if MaxRows is set to 500, only 5 packets of 100 rows each will be downloaded.
|BufferPercentage||nn (default = 10)||
Defines the amount of the free shared memory buffer that can be used by the job. A higher value increases the speed, but also increases the risk for conflicts with other jobs.
Special value 999 can now be used to force the connector to use 10% of the available shared memory, not only a percentage of free shared memory. This should be used if the Connector starts to show deteriorating performance.
|TimeOutBatch||n (default = 600 seconds)||The number of seconds that the background job waits for the client side to fetch data.|
|TimeOutFetch||n (default = 1200 seconds)||The number of seconds spent trying to fetch from SAP without getting any records back.|
|TimeOutStartBatch||n (default = 2400 seconds)||The number of seconds that the client side waits for the background job to start.|
|BatchJobName||XXX (default = /QTQVC/READ_MPDATA)||
BatchJobName is the name of the data extraction batch job and can contain up to 32 characters.
BatchJobName can be found in Job Overview (transaction SM37).
|TargetServer||xxxxxxx||If specified, forces the background job to be executed on the specified application server. The correct name can be found in transaction SM51. The name is case-sensitive.|
|JobClass||A/B/C (default = C)||Sets the priority for the background job, which can be useful for small jobs that need to be reloaded often.|
|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.|
The SAP connector roles contain the authorization objects that are checked when using the connectors. Authorization objects may be checked both in the SAP connector code and in standard SAP code called from the Qlik code. It is not necessary to import and use the Qlik roles. You can create your own authorizations, as well as copy or modify the Qlik roles.
The role QTQVCBWACCESS is used for extraction jobs from SAP BI/BW system from Qlik. It is used with BEx query, InfoProvider, DSO and OLAP.
The role QTQVCBWACCESS contains authorization objects that can be used to restrict which InfoProviders a user can access. If users should have different access, update the authorization objects in copies of the role QTQVCBWACCESS and assign the relevant copy to each user. It is suggested that you copy the role QTQVCBWACCESS and use the copy. If the role QTQVCBWACCESS is updated in a future connector release, InfoProvider authorizations in that role will be overwritten.
The default behavior in the Qlik role QTQVCBWACCESS is to allow the usage of all InfoProviders.
How to restrict usage for all types of InfoProviders
There are five different authorization objects in role QTQVCBWACCESS, which can be used to restrict usage of different types of InfoProviders. Each authorization object is described in the table below:
|S_RS_ADSO||Defines access to Advanced DataStore objects.||RSOADSONM (Advanced DataStore object)||If set to * then all DataStore objects are allowed.||Replace * in the Value field with a list of Advanced DataStore objects that the user can access.|
|S_RS_HCPR||Defines access to HANA CompositeProviders.||RSHCPR (HANA CompositeProvider)||If set to * then all HANA CompositeProviders are allowed.||Replace * in the Value field with a list of HANA CompositeProviders that the user can access.|
|S_RS_ICUBE||Defines access to InfoCubes.||RSINFOCUBE (InfoCube)||If set to * then all InfoCubes are allowed.||Replace * in the Value field with a list of InfoCubes that the user can access.|
|S_RS_MPRO||Defines access to MultiProviders.||RSMPRO (MultiProvider)||If set to * then all MultiProviders are allowed.||Replace * in the Value field with a list of MultiProviders that the user can access.|
|S_RS_ODSV||Defines access to Open ODS Views.||RSFBPNAME (Open ODS View)||If set to * then all Open ODS Views are allowed.||Replace * in the Value field with a list of Open ODS Views that the user can access.|
The following example shows the default setup for the authorization object S_RS_ICUBE (InfoCube). In this setup the * after InfoCube indicates that all InfoCubes are allowed access:
Replacing * with a list of the InfoCubes, restricts access to the InfoCubes listed:
After listing the allowed InfoCubes, save, generate, and assign the role used to the SAP user ID. Use the SAP transaction PFCG to display and maintain authorization objects in a role.
Select and load data
Once a connection to an SAP InfoProvider has been established, you can select data and load it into a QlikView or Qlik Sense app.
Do the following:
Select Load texts to load the Text Table of the InfoObjects.
When this property is selected, a script for loading the Text Table of the InfoObjects included in the InfoProvider is created. All available text fields of an InfoObject are included in the generated script and the fields are named:
- <InfoObject Name>_TEXT_SH (for short texts)
- <InfoObject Name>_TEXT_MD (for medium-length texts)
- <InfoObject Name>_TEXT_LG (for long texts)
If an InfoObject is configured to have text but no text has been loaded to the InfoObject in SAP BW, the script for that InfoProvider will be commented.
Select Load attributes to load the Attribute View of the InfoObjects.
When this property is selected, a script for loading the Attribute View of the InfoObjects included in the InfoProvider is created. No check is performed to verify that Attribute data has been loaded in SAP BW. All available Attribute fields of an InfoObject are included in the generated script, and the name of the fields is <InfoObject Name>_<Attribute Name>.Information noteTime-dependent Texts and Attributes are extracted with a condition to extract the current value of the text or attribute. The condition has the form DATEFROM <= SY-DATUM AND DATETO >= SY-DATUM.
Select Show friendly names.
Select Provider Types.
The Qlik SAP InfoProvider Connector connects to five types of InfoProviders in SAP BW:
- InfoCube (CUBE)
- MultiProvider (MPRO)
- DataStore Object (Advance) (ADSO)
- CompositeProvider (HCPR)
- Open ODS View (FBPA)
You can select multiple provider types.
Select the InfoProviders whose data you want to load.
You can filter the InfoProviders list using the search field () above the list. Select one InfoProvider at a time. If you intend to load data from more than one InfoProvider, you must complete the selection from the first InfoProvider and then reopen the data selection dialog to select data from the next InfoProvider.
Clicking on the InfoProvider name causes all of its fields to be displayed.
Select the fields whose data you want to load.
You can filter the fields shown by using the search field () above the list of fields.
When selecting fields, you can click the Metadata button to see which fields, if any, are primary key fields. Under Metadata you can also set conditions for each field by clicking . Conditions are set to include by default, but the Exclude option can be selected to use the condition for excluding rather than including values. The available conditions are:
- Greater than or equal to
- Less than or equal to
- Greater than
- Less than
- Not equal to
The drop-down list of values in the Conditions for field dialog is populated with a maximum of 10,000 characteristic values. If the value you want to enter is not in the list of the first 10,000 rows, you can still enter the value manually in the entry field to the left of the drop-down list.Information noteThe InfoProvider Connector adds RowNum as a row-number counter to the fields that can be selected by the connector. In addition to counting the rows (starting at 1), the RowNum counter ensures that the extracted rows are unique. If RowNum is excluded from the selection, the output will not be handled correctly in Qlik Sense or QlikView. To calculate Sales Quantity per 0CALYEAR, the measure would have to be aggregated in Qlik Sense and QlikView.
Select Include LOAD statement, if not already checked, to create a LOAD statement before the SELECT statement.
In QlikView, this option is labeled Preceding load.
The LOAD statement before the SELECT statement allows the LOAD statement to use the SELECT statements as input. Basically, it is a LOAD statement that loads from the LOAD or SELECT statement below, without specifying a source qualifier, such as From or Resident, as you would normally. You can stack any number of LOAD statements this way. The statement at the bottom will be evaluated first, then the statement above, and so on until the top statement has been evaluated. Another advantage of the LOAD preceding SELECT is that you can keep a calculation in one place and reuse it in LOAD statements placed above. For example, when you load data from a database using a SELECT statement, you cannot use Qlik Sense or QlikView functions to interpret data in the SELECT statement. The solution is to add a LOAD statement and perform data transformations above the SELECT statement.
The LOAD statement created in the script panel is a simple load.
Limitations and workarounds
To extract ADSOs, CompositeProviders, and Open ODS Views, the SAP BW system needs to be at least on basis level 7.4 SP9 and the database needs to be HANA. Cubes and MultiProviders are supported from basis level 7.0 and any database.
The Qlik SAP InfoProvider Connector does not connect semantically partitioned cubes. Copies of the cube are generated, and it is those generated cubes that appear in the list of InfoProviders in the selection dialog.
MultiProviders that have VirtualProviders as part providers are not supported by the connector.
DataStore Object (Advanced) (ADSO)
Only the fields that are available for reporting can be extracted by the connector. For more information, see SAP Note 2185212. To extract fields that are not available for reporting, use the Qlik SAP SQL Connector and extract the Active Table of the ADSO. The table name is /BI0/A<Name_of_ADSO>1 or /BIC/A<Name_od_ADSO>1.
Only Central CompositeProviders (Object type HCPR) are supported. Local and Ad-Hoc CompositeProviders (Object type COPR) are not supported by the connector. For CompositeProviders with ambiguous joins, there is a risk that the extracted key figure values will be incorrect. For more information see SAP Note 2372430. You can extract the data through a BW query using the Qlik SAP BW Connector instead.
Open ODS View (FBPA)
Open ODS Views build on SAP BW DataSources are not supported.