Using the SAP Extractor Connector

Connections to the QlikSAP Extractor Connector are made from the list of connectors in Qlik Sense or QlikView.

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.

Supported objects

Configuration

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.

SAP Extractor Connector configuration

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:

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

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

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

   

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.

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:

/H/addr.to.SAPRouter/H/addr.to.SAPsystem

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:

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, 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.

Qlik Sense: Select data to load

Do the following:

1. 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.

2. Select a system in the Logical systems drop-down list.

   

3. Select Extractors or Hierarchies from the Display drop-down list.

4. Select an extractor or hierarchy from the list in the left column.

5. 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.

6. Select fields to load.

7. 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:

1. Click Extractor in the Script Editor.



2. Click Get.

3. Select a system in the Logical System drop-down list.

   

4. Select a language in the Language drop-down list.

5. Click Search to get the activated extractors.

6. 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.

7. To edit the selection to add filters, click the edit button (not all fields are editable).

   

8. Click Add in the pop-up window and add the required filters.

   

9. 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.

Delta loads

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:

1. 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.

2. Re-comment the UPDMODE C statement.

3. 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:

1. Re-comment the UPDMODE D statement.
2. Uncomment the UPDMODE R statement.
3. 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

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:

1. Select the Show Hierarchies radio button.
2. Click Get.
3. Select the logical system in the Logical System drop-down list.

4. Click Search.

5. Select a hierarchy in the Hierarchies list.

6. Click Get Hierarchies.

   All available options for the selected hierarchy are displayed.

   

7. 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.
8. Click Add to Script.

As a result, two statements are added to the script for the hierarchy load.

• First statement:



• Second 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:

C:\ProgramData\QlikTech\Custom Data\QvSAPConnector

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.

Error handling

There are a number of methods to monitor the jobs of the Extractor Connector.

Status table

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:

1. In the status table, get the job time and date and then go to transaction /N/QTQVC/delete.
2. Select /QTQVC/STATUS in the Delete single record from table section.
3. Enter the correct CONNECTOR, JOBDATE and JOBTIME.



4. Run the job in Simulate Deletion mode.
5. 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:

1. Get the INITRNR from the Qlik log file:

   2018-01-10 13:22:59 Info 5 : Started job (INITRNR): REQU_QTQVCEXTR1_20180110132247

2. Open the script.

3. 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'.

Delta loads

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.

Communication Error

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:

General rules

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:

1. Use Transaction /N/QTQVC/DEACTIVATE.

2. Enter Logical system name and Transfer Method and click the GetExtractors button.

   

3. Select Extractor(s) to deactivate and click button DeactivateExtractors.

Timeouts

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.

• TimeoutSAP

  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).

• TimeoutInit

  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).

• TimeoutActivity

  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).

• TimeoutData

  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).

Important information

Delta Mechanism

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.

Load Time

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.

Null Value

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],

Sample Extractors

The following sample Extractors are available for use:

Transaction codes

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

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).

Installing the Qlik SAP Network Server service