Connections to the QlikSAP Extractor Connector are made from the list of connectors in Qlik Sense or QlikView.
This SAP connector is available in:
Qlik Sense Enterprise on Windows
The Extractor Connector contains two different options.
IDoc (Intermediate Document) is a standard SAP document format. IDocs enable the connection of different application systems using a message-based interface.
tRFC (Transactional RFC), previously known as asynchronous RFC), is an asynchronous communication method that executes the called function module only once in the RFC server.
The Extractor Connector user configuration is applicable for SAP BASIS System 7.00, and later. After the transports have been installed in the system, create one or more new users for the extractor connector (QTQVCEXTRACTOR). Administrative roles QTQVCEXTRADM can be added to existing Admin users of the SAP system.
The SAP Extractor Connector can also be used against an SAP BW system. The structure of the BW system differs from that of an SAP ERP system, but the principle is the same.
User Configuration is the same as for SAP BASIS System 7.00 and later.
Establishing a connection
To access SAP Extractor data, you need to know the type of server you want to connect to. You must also have access credentials.
Do the following:
Open a new or existing Qlik Sense or QlikView app.
Select the SAP Extractor from the list of connectors.
A create connection dialog opens, like the following from the Qlik Sense Data load editor.
|Service Host||Address of the installed network server exe/service file|
|Service Port||Network server port. The default port is 8680.|
Application Server or Message Server
Warning 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. Using the SAP Extractor Connector
|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||The system ID of the SAP Message Server system.|
|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 Extractor 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.|
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.|
|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.
|Secure Network Settings||
Information note Secure Network Connection is not available for the Extractor Connector.
The Secure Network Settings properties appear in the QlikView connection dialog, but they cannot be used.
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.
If connecting through an SAP Router, the RFC connection in SAP needs to be manually changed in transaction SM59. The Gateway Host needs to be changed to:
The Gateway Service value can be kept if correctly set in: /N/QTQVC/EXTRACTOR_ADM.
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 not set in the connection string, each timeout has a default value. All timeout values are given in seconds.
The following properties can be specified for the connection string.
|ENotationLegacy||0/1 (default/off = 0 or false, on = 1 or true)||
When the property is turned on (=1), fields of the type CHAR that contain a value that can be interpreted as a Scientific Notation Number are converted to a number in QlikView. When the property is turned off, all values that can be interpreted as a Scientific Notation Number are handed as text values.
A value that can be interpreted as a Scientific Notation Number consists of numbers and the letter E, where E appears within the string of numbers. For example, 2E2, 0E123456, 1e2349.
|TraceWinUserName||0/1 (default/on = 1 or true, off = 0 or false)||
If set to ‘0’ the Windows-API (GetUserName()) will be bypassed with the result that the Windows user name will not be saved in the corresponding job record in the database table /QTQVC/STATUS.
In a small number of isolated cases a connector has been found to ‘freeze’. The solution is to set this property to value 0.
|TimeoutSAP||n (default = 14400 seconds)||This timeout is used in the SAP part of the connector. When a new Extractor job is started in SAP, there is a check to see that no other Extractor job is already started. When using the transfer method IDoc, only one job per Logical system is allowed at a time. When using the transfer method tRFC there can be parallel jobs for the same Logical system if different Extractors are used. An Extractor job in SAP consists of two parts. The first part is an extraction batch job that creates all IDocs or tRFC packages containing the extracted data. The second part is the RFC transfer of the IDocs or tRFC packages to Qlik. The value in the TimeoutSAP property determines how long time SAP should wait to start a new job if there is an ongoing conflicting RFC transfer of data from an Extractor job. When the number of seconds in the timeout is exceeded, it is likely that there is a permanent error in the ongoing RFC transfer. The Extractor job is then regarded as failed (the corresponding record in the SAP status table is set to ‘aborted’) and a new job is allowed to start. The default value is 14400 seconds (240 minutes).|
|SocketTimeout||n (default = 900 seconds)||This property can be used to fine tune the timeout trigger that is set for the socket connection to the Qlik SAP Network Server. This will only affect the connection between the Extractor Connector DLL and the SrvService.exe.|
|TimeoutInit||n (default = 900 seconds)||
This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait to receive data from SAP about the activated Extractors and hierarchies available to use. If the timeout is exceeded, the QlikView job is aborted. The default value is 900 seconds (15 minutes).
|TimeoutActivity||n (default = 7200 seconds).||This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait to receive data from the extraction job. If the timeout is exceeded, the QlikView job is aborted, the corresponding SAP extraction job is canceled, and the status value in the corresponding record in the SAP status table is set to 'aborted.' The default value is 7200 seconds (120 minutes).|
|TimeoutData||n (default = 3600 seconds).||This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait between receiving each IDoc/tRFC data package. If the timeout is exceeded, the QlikView job is aborted, the corresponding SAP extraction job is canceled, and the status value in the corresponding record in the SAP status table is set to 'aborted.' The default value is 3600 seconds (60) minutes.|
|TCPBufSize||n (default = 1024 KB)||This property can be used to fine tune data transfer time between SrvService.exe and the SAP Extractor Connector. The max value is 16384 KB but in many networks it may be sufficient to use the default value. (Note that this property does not change the SAP packet size.)|
|MultipleServicesOverride||0/1 (default/off = 0 or false, on = 1 or true)||
The main use for this property is to reset a flag of the target SAP system if a client receives the error message "Another SrvService is already connected..." when in fact no other client is using it.
Default setting is off. With default setting, error messages are sent to the Extractor Connector client if it attempts to start a new job using a different Qlik SAP Network Server service than the one currently registered with the target SAP system.
Warning noteWhen override is turned on, the connected Network Server will override any previously registered Network Server on the SAP system and any running job will fail on both Network Servers.
|ReplaceNullValue||0/1 (default/on =: >=1 or true, off = 0 or false)||This property can be used to switch off null value replacement in data. When it is turned on, the first data row containing null values in each packet will be logged. If the property setting is greater than 1, additional rows from each packet are logged. For example, to log 14 rows from each packet, change the parameter to ReplaceNullValue=14.|
|ReplaceNullValueChar||default is a blank space||This property is used by ReplaceNullValue and can be used to make replaced null values more visible. For example, ReplaceNullValueChar=# can be used to replace the default blank space to make the null values stand out. If ReplaceNullValue is turned off--ReplaceNullValue=0, this property is ignored.|
|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.|
Qlik Sense: Select data to load
Do the following:
Select an SAP Extractor connection.
When you are using Add data, connections that have already been defined are listed under Data connections in panel on the left. When you are using the Data load editor, click the select data icon for one of the existing connections listed in Data connections panel on the right.
Select a system in the Logical systems drop-down list.
Select Extractors or Hierarchies from the Display drop-down list.
Select an extractor or hierarchy from the list in the left column.
Select IDoc or tRFC from the Transfer method drop-down list when selecting data from hierarchies.Information noteWhen changing to tRFC you have to deactivate old sources and activate again. It is possible to do a mass-deactivation in /n/QTQVC/DEACTIVATE.
Select fields to load.
Click Add data to load the data.
In the Data load editor, click Insert script to add the selection to the script.
QlikView: Select data to load
Do the following:
- Click Extractor in the Script Editor.
- Click Get.
Select a system in the Logical System drop-down list.
Select a language in the Language drop-down list.
- Click Search to get the activated extractors.
Select an extractor in the ActivatedExtractors list and then click Get Fields.
The Method column shows if the Extractor is activated as IDoc or tRFC method.
To edit the selection to add filters, click the edit button (not all fields are editable).
Click Add in the pop-up window and add the required filters.
- Click OK and then click Add to Script to add the selection to the script.
A predefined script is added to the QlikView application:
The standard script is a proposal and the functionality to activate has to be uncommented.
In the example script, the selection is initially loaded from the logical system QTQVCEXTR2 and only for KOKRS (controlling area) 1000. The QVD is stored in the qvd/init/ folder and the name of the QVD file is INIT_0COSTCENTER_TEXT.QVD.
One of the major advantages of the Extractor Connector is the ability to use delta load capabilities (if allowable in the Extractor itself).
Do the following:
Create an Initialization in the SAP system for the Extractor.
Uncomment only the UPDMODE C statement and perform a reload to retrieve all the data from the Extractor.Information noteOnce this Initialization has been performed for this Extractor, it should not be repeated.
Re-comment the UPDMODE C statement.
Uncomment only the UPDMODE D statement.
Any subsequent reloads will retrieve the new delta records, if any.
To resend the most recent delta load:
Do the following:
- Re-comment the UPDMODE D statement.
- Uncomment the UPDMODE R statement.
- Perform a reload.
This works for both tRFCand IDoc transfer methods. The repeat delta load retrieves both the most recently extracted delta and any new delta records that have been added to the delta queue since the previous delta load.
If an old initialization already exists for an Extractor when you are trying to create a new initialization, the old initialization must first be deleted. The old initialization can be deleted using the feature found in the Extractor GUI (SAP Job Tools):
The old initialization can also be deleted by using the transaction /N/QTQVC/DELETE_INIT. Enter the Logical system and Extractor and execute.
The GUI feature or transaction should also be used to remove an Initialization, if no additional delta loads should take place for an Extractor.
To change the transfer method for an Extractor with an active Initialization, delete the existing Initialization, deactivate the Extractor by using the transaction /N/QTQVC/DEACTIVATE and activate the Extractor again with the new transfer method by using the transaction /N/QTQVC/ACTIVATE.
Hierarchy properties are the properties of all hierarchies for a hierarchy basic characteristic, delivered by SAP, and freely definable according to the needs of the customer.
Hierarchy properties are fixed in InfoObject maintenance for a characteristic and valid for all hierarchies that have been created for the characteristic.
During hierarchy maintenance, the hierarchy attributes can be set and, as a result, influence the display and processing of hierarchies in reporting.
Unlike all other data sources in SAP, hierarchies do not have to be activated. When loading a hierarchy, proceed as follows to change the screen view in the pop-up window.
Do the following:
- Select the Show Hierarchies radio button.
- Click Get.
- Select the logical system in the Logical System drop-down list.
- Select a hierarchy in the Hierarchies list.
Click Get Hierarchies.
All available options for the selected hierarchy are displayed.
Select an option in the Nodes box.Information noteThe extraction of hierarchies with the Qlik SAP Extractor Connector has been changed to incorporate DATEFROM and DATETO statements in the script. This enables the connector to extract hierarchies that are time-dependent. Existing scripts are still supported, but all new scripts have a DATETO and DATEFROM for the hierarchy.
- Click Add to Script.
As a result, two statements are added to the script for the hierarchy load.
- First statement:
The first statement uses the HIERARCHY function to create nodes for each level. To link the hierarchy table to other standard Extractors, a function can be deployed to NODENAME, for example:
Mid(NODENAME,5,13) as [SAKNR],
Each hierarchy requires different manipulation to perform the join operation.
The second load statement provides the option for descriptions of the levels in the hierarchy.
Logs and processes
There are a number of transactions in SAP to monitor the processes involved with the Extractor Connector.
To display the processed IDocs, use transaction code WE02:
To follow the process in SAP, use transaction code SM50:
To monitor the initialized Extractors in an ERP system, use transaction code RSA7:
In a BW system you have to use transaction SE16 and Table: ROOSPRMSC to retrieve the same information.
The QlikView log is stored in C:\ProgramData\QlikTech\Custom Data\QvSAPConnector\Log:
The Qlik Sense log is stored in C:\ProgramData\Qlik\Custom Data\QvSAPExtractorConnector\Log:
The Qlik Network Server logging levels can be changed during runtime. In addition, metadata and data rows (for TRFC method only) can be captured in the log. The log levels are split between two areas: normal logging and advanced logging.
The current levels can be changed in the settings.config file, which is located in:
|error||Log critical errors that will probably affect the job|
|warn||Log unexpected events that are not critical|
|info||Log items in medium detail|
|debug||Log items in great detail|
|metaData||Log field names and data types only|
|func||Log internal functions as they execute|
|dataRow_X||Log only row X, where X is an absolute row number|
|dataRow_Y||Log Y rows of data per packet from SAP, where Y is a whole number|
|data||Log all data as received from SAP|
|all||Includes all levels|
The Qlik Sense log provides information on which IDocs or TID nr (TRFC) have been created for the request and if the load has been successful.
There are a number of methods to monitor the jobs of the Extractor Connector.
The download for every Extractor can be followed in the status table, /QTQVC/STATUS, which is available in transaction SE16. Possible values for Job Status:
- R: Released
- S: Started
- F: Finished
- A: Aborted
- C: Job cancel
- K: Keep
For the IDoc transfer method only a single extraction job at a time can run in a logical system. Qlik Sense returns an error message if you try to run multiple Extractors on the same logical system simultaneously.
For the tRFC transfer method many extraction jobs can run in parallel in a logical system, however the same Extractor cannot run in parallel. Qlik Sense returns an error message if that happens.
In the event of an Extractor job not being able to finalize correctly, the status record contains an “S” for started. See the following for how to cancel such a job from the connector or from the SAP GUI.
Canceling a job from the SAP GUI
Do the following:
- In the status table, get the job time and date and then go to transaction /N/QTQVC/delete.
- Select /QTQVC/STATUS in the Delete single record from table section.
- Enter the correct CONNECTOR, JOBDATE and JOBTIME.
- Run the job in Simulate Deletion mode.
- Run the job in Delete Records mode.
Resend Extractor jobs
If a communication error has occurred for a delta or full load and the IDocs or tRFC packages have been produced in the SAP system, the IDocs or tRFC packages can be re-sent to Qlik.
IDoc transfer method: Resend a complete extraction job
Do the following:
Get the INITRNR from the Qlik log file:
2018-01-10 13:22:59 Info 5 : Started job (INITRNR): REQU_QTQVCEXTR1_20180110132247
Open the script.
Construct the script as follows (insert the INITRNR from the job log such as below):SQL EXTRACTOR 0FI_GL_4 TFRMETHOD I // Transfer method (I=IDoc) //UPDMODE F // Full extractor //UPDMODE C // Initial extraction, to be followed by delta extractions //UPDMODE D // Delta extraction //UPDMODE R // Delta repeat (resend the most recent delta load) INITRNR REQU_QTQVCEXTR1_20180110132247 // Resend extraction (for IDoc Transfer Method) //IDOC <NR> // Resend single IDoc EXTRLANGUAGE system_default // SAP System Default LOGSYS QTQVCEXTR1;
IDoc transfer method: Resend a single IDoc
It is also possible to resend a single IDoc. Insert the IDoc number as in the script below:
tRFC transfer method: Resend a full load or initial extraction
For a full load (UPDMODE 'F'), it is possible to simply reload the script. No resend is necessary with UPDMODE 'F' because the full job is performed again. Construct the script as follows:
For an Initial extraction (UPDMODE 'C'), it is necessary to delete the previous initialization before a new reload can take place. This is done by using the transaction /n/QTQVC/DELETE_INIT. After that you can simply reload the script with UPDMODE 'C'.
IDoc and tRFC transfer methods: Resend a Delta load
Delta loads can also be re-sent. The following is an example of a delta load for a tRFC transfer. To resend a delta load using the IDoc transfer method, use TFRMETHOD 'I' instead of 'T'.
Discovery of multiple services
An error message is sent to the Extractor Connector client if it attempts to start a new job using a different Qlik SAP Network Server service than the one currently registered with the target SAP system. The IP address of the registered service can be found in the log file.
There is a rare case when a status record in the table /QTQVC/STATUS (connector: 'SERVICE') gets stuck and prevents a Qlik SAP Network Server from running as described above. This can be resolved by temporarily setting the MultipleServicesOverride property to 1 (true). Using MultipleServicesOverride to override an active job will result in lost data packets for both clients.
If the logical system is correctly set up and the SAP system can be contacted when connecting, the standard setting for tRFC in the logical system setup may have to be changed. This is done in transaction SM59 and TCP/IP connections.
The default settings are as follows:
The database table /QTQVC/EXTRACT will contain the activated extractors from the latest execution of the transaction /QTQVC/MIGRATE_PREP, that is the transaction overwrites the previous content in the table for a Logical system. Therefore it is important to execute the transaction /QTQVC/MIGRATE_PREP before executing the transaction /QTQVC/MIGRATE_EXTR which creates the Transport request. When the Transport request is created it is not allowed to change the content of the database table /QTQVC/EXTRACT until the Transport request has been migrated. No new Transport request should be created until the last one has been used in all systems in the landscape.
However the database table /QTQVC/EXTRACT is using Logical system as key, so it is possible to put activated extractors for a different Logical system in the table and to create a separate Transport request in parallel for that Logical system.
When the Extractors are uploaded and activated in the next system in the landscape there is a check that an Extractor is not already activated. If so, there will be a warning in the log file, the activation of the Extractor is skipped and the program continues with the next Extractor from the Transport request. If the user wants to change the fields of an activated extractor by using this functionality, the Extractor has to be manually deactivated in each system before uploading and activation takes place.
Deactivating an Extractor
Do the following:
- Use Transaction /N/QTQVC/DEACTIVATE.
Enter Logical system name and Transfer Method and click the GetExtractors button.
Select Extractor(s) to deactivate and click button DeactivateExtractors.
There are a few timeout properties that can be set. Each timeout has a default value, but the timeout properties' values can be changed. All timeout values are given in seconds.
This timeout is used in the SAP part of the connector. When a new Extractor job is started in SAP, there is a check to see that no other Extractor job is already started. When using the transfer method IDoc, only one job per Logical system is allowed at a time. When using the transfer method tRFC there can be parallel jobs for the same Logical system if different Extractors are used. An Extractor job in SAP consists of two parts. The first part is an extraction batch job that creates all IDocs or tRFC packages containing the extracted data. The second part is the RFC transfer of the IDocs or tRFC packages to Qlik. The value in the TimeoutSAP property determines how long time SAP should wait to start a new job if there is an ongoing conflicting RFC transfer of data from an Extractor job. When the number of seconds in the timeout is exceeded, it is likely that there is a permanent error in the ongoing RFC transfer. The Extractor job is then regarded as failed (the corresponding record in the SAP status table is set to ‘aborted’) and a new job is allowed to start.
The default value is 14400 seconds (240 minutes).
This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait to receive data from SAP about the activated Extractors and hierarchies available to use. If the timeout is exceeded, the Qlik Sense job is aborted.
The default value is 900 seconds (15 minutes).
This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait to receive data from the extraction job. If the timeout is exceeded, the Qlik Sense job is aborted, the corresponding SAP extraction job is canceled, and the status value in the corresponding record in the SAP status table is set to 'aborted.'
The default value is 7200 seconds (120 minutes).
This timeout is used in the Windows part of the connector. It specifies the maximum time Windows should wait between receiving each IDoc/tRFC data package. If the timeout is exceeded, the Qlik Sense job is aborted, the corresponding SAP extraction job is canceled, and the status value in the corresponding record in the SAP status table is set to 'aborted.'
The default value is 3600 seconds (60 minutes).
The big advantage of using the SAP Extractor Connector is the delta mechanism, which is built into some of the standard extractors.
It is also easier to use a pre-defined data source from SAP, as no knowledge is required of the complex table structures in the SAP systems. Most of the data sources and Extractors are self explanatory.
A time lag is experienced during the initialization of the connector whilst the SAP processes run.
The amount of time to load from an ERP system is similar to that of the SQL Connector. To get good loading performance from a BI/BW system, use the connector against a DSO/ODS source.
No null value is allowed in any Object because the Extractor looks for an empty row to know when to finish loading. Null values must be replaced. The connection property ReplaceNullValue can be used to control null value replacement. If null values are not replaced, the connector will fail. See Connection string properties.
Qlik Sense Trim function
When loading from an InfoCube, the positive values of a key figure can end-up as a text in Qlik Sense. To avoid this, use the Qlik Sense Trim function in the script as follows:
Trim([CREDIT_DC]) as [CREDIT_DC.Credit amount in foreign currency],
Trim([CREDIT_LC]) as [CREDIT_LC.Credit amount in local currency],
The following sample Extractors are available for use:
|0FI_GL_4||General Ledger: Line Items with Delta Extraction|
|0FI_AP_4||Vendors: Line Items with Delta Extraction|
|0FI_AR_4||Customers: Line Items with Delta Extraction|
|CO_OM_CCA_9||Cost Centers: Actual Costs Using Delta Extraction|
|0CO_PC_ACT_02||Material Valuation: Per Ending Inventory|
|0CO_PC_01||Cost Object Controlling: Plan/Actual Data|
|0EC_PCA_3||Profit Center: Actual Line Items|
The following transaction codes are available for use. Since the transaction codes starts with '/' you must prefix them with /n when they are used. For example: /n/QTQVC/ACTIVATE
|/QTQVC/ACTIVATE||Activate an Extractor (datasource)|
|/QTQVC/DELETE||Delete Database Table Records|
|/QTQVC/EXTRACTOR_ADM||Maintain Extractor environment|
|/QTQVC/MIGRATE_ENV||Migrate Extractor environment|
|/QTQVC/CONNECT_ENV||Store a Connection to QV|
|/QTQVC/MIGRATE_PREP||Put activated Extractors into table|
|/QTQVC/MIGRATE_EXTR||Migrate Activated Extractors|
|SE 16||Data Browser|
|RORQSTPRMS||(Protocol table Request)|
|ROOSPRMSC||(Control Parameter Per Data Source Channel)|
|ROOSGEN||(Generated Objects for OLTP Source)|
|RSA6||Post process Data Sources and Hierarchy|
|RSA7||BW Delta queue Maintenance|
|SMQ1||qRFC Monitor (Outbound Queue)|
Qlik Network Server File
Store the attached SrvService.exe file on one server, preferably in the same folder as the SAP Connectors (C:\Program Files\Common Files\QlikTech\Custom Data\QvSAPConnector).