Skip to main content
Using the SAP DSO/ODS Connector

ON THIS PAGE

Using the SAP DSO/ODS Connector

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

Information noteThis SAP connector is only available in QlikView.

Establishing a connection

Do the following:

  1. Start QlikView
  2. Open the Script Editor.
  3. Select the Data tab, and then select the QvSAPDSOConnector.dll option from the drop-down list.

    Data tab with QvSAPDSOConnector.dll selected in Database list

  4. Click Connect….

  5. Enter the Application Server Host address, Client, and System Number of the target SAP system, or select the Message Server Host option and enter the Message Server Address, Client, System ID, and Group.

    SAP connection dialogue image

    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.

    If passing through an SAP router, paste the router string in the Application Server Host/Message Server Host address field.
  6. Enter the Username and Password of the user that is to be used for this specific download.
  7. Click Test Connection to verify that all fields are correctly filled in.

  8. Finally, click OK to get a connection string in the script.

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

There are a number of properties that can be added to the connection string, if needed. Normally, the default values for the properties are sufficient. Separate properties with ; (semi-colon) in the connection string:

  • ENotationLegacy=0/1(default/off = 0, on = 1): When the property is turned on, 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.

  • ODSMAXROWS: By default, this property is 10 000 000 records. This is to avoid huge memory consumption, which is a problem with this BAPI. The connector stops reading data when reaching the maximum number and returns an error message. Be careful when reading very large tables, since memory consumption may get high.
  • Logpath=xxxx: Places log files in a subfolder named xxxx. The folder is created, if needed. xxxx can be any text string that can be a valid part of a folder name in Windows.
  • LogFile=yyyy: Names the log file yyyydatetime-n.txt. yyyy can be any text string that can be a valid part of a filename in Windows.
  • SAPRouter: Use this field to specify the SAPRouter parameters if the connection needs to be made through a firewall via a SAP Router. Possible value is a string of host name and service name/ port in the following format: /H/hostname/S/portnumber

  • GatewayHost: 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: The Service name (as defined in etc/services) or the port number under which the gateway is listening. Possible values are: sapgw00 - sapgw99, 3300 - 3399

Select and load data

Do the following:

  1. Click DSO/ODS in the Script Editor.
  2. Data tab with QvSAPDSOConnector.dll selected in Database list

  3. Select an InfoArea in the InfoArea drop-down list or leave it as-is (<All>) to display all InfoAreas.

    Example image of InfoAreas with InfoArea set to <All>

  4. Select a DSO object in the DSOObject list.
  5. Select the fields in the Info Object list.

    The script appears in the lower area

    To reduce the memory requiement, the slice functionality can be used to slice by column (row slicing is not possible). The generated script stores the result in separate QVD files, which have to be merged later on. All QVD files have a common key field to simplify the merge.

    No navigational attributes are available.

  6. Click OK to return to the Script Editor with the generated script.
  7. Using the following syntax, a WHERE clause can be added manually:

    WHERE
    ColumnName1 sign option value,
    ColumnName2 sign option value1 value2

    No display attributes or key characteristics are allowed as columns in the WHERE clause.

    The following values are valid in the SIGN field:

    • E’ = exclude
    • 'I’ = include

    The following values are valid in the OPTION field:

    • EQ’ = equal to
    • GE’ = greater than or equal to
    • LE’ = less than or equal to
    • GT’ = greater than
    • LT’ = less than
    • NE’ = not equal to
    • CP’ = contains
    • BT’ = lies between (upper and lower limits)

    Conditions for the same column (regardless of the number and sequence in the table) are treated as OR operations. Conditions for different columns are treated as AND operations.

    Example:  

    from 0SAL_DS01
    where 0DIVISION I EQ 01;
     

    or

     
    from 0SAL_DS01
    where 0CREATEDON I BT 20100101 20101231