Using the SAP OLAP Connector

The OLAP Connector enables extraction of data from BEx queries in SAP BW. The OLAP Connector does not require any transports to be installed in SAP BW because it is only using SAP functionality.

Both the BEx and the OLAP Connector can be used to extract data from BEx queries to QlikView. The BEx Connector can extract data from BEx queries to Qlik Sense. The difference is the functionality in SAP that is used for query execution. In general, theBEx Connector has better performance than the OLAP Connector.

The load script for the Qlik OLAP Connector is converted to an MDX query that is sent to the MDX interface of SAP BW. BEx Connector utilizes the BEx query execution functionality available through BEx function modules in SAP BW.

Using the SAP BEx Connector

Supported objects

The SAP OLAP connector supports the SAP BEx query object only.

Establishing a connection

Do the following:

1. Start QlikView.
2. Open the Script Editor.

3. Select the Data tab, and then select the QvSAPOLAPConnector.dll option from the drop-down 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.

   

   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.

• 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.
• ConsistencyCheck=0/1 (default/on = 1): If on, optimization is done for maximum speed. If this fails due to inconsistent metadata, change this property to off and retry.
• PartitionSize=nnnnnnn (default = 4000000): Increasing this property increases the speed, but also the risk of getting dumps in the SAP system. If many dumps are received, adjust this property downwards. In most cases, the download recovers.
• MinMembersInSlicedCharacteristic=nn (default = 10): Automatic slicing only chooses among characteristics that have more members than this value. In odd cases, no other suitable characteristic is available, which means this value might have to be lowered.

• 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

Defining a query

Do the following:

1. Click Cubes in the Script Editor.

   

2. Select an InfoCube in the Info provider drop-down list.

   The drop-down lists all cubes that have at least one query that allows external access.

   Information noteThe first item in the drop-down is $INFOCUBE, which is a top level for all InfoCubes in the system. $INFOCUBE should not be selected as the Query list then displays Infoproviders instead of queries. Selection of Infoproviders by this means is not supported. You must select an InfoProvider in the drop down list.

   

3. Select a query in the Query drop-down list.

   The drop-down lists all queries (that allow external access) built on the selected cube.

4. Select at least one characteristic (or navigational attribute) in the Characteristics box to get any rows downloaded. A key figure does not have to be selected.

   When selecting a characteristic, the bottom part of the window is populated. If there is more than one hierarchy, select one of them (since only one hierarchy/characteristic can be downloaded). Additional display attributes for the characteristic can also be selected. The default attribute is always downloaded.

   Uncheck the Include All Values of Characteristic box to display the possible values for the members of the characteristic. Selecting a value generates a slice (filter) with that value. If the box is checked, all members of the characteristic are downloaded.

   The Variables box contains any variables defined for the selected query. These can be Optional or Mandatory, Single-value or Ranges. Mandatory variables must be populated.

5. Click OK.

The generated pseudo-MDX statement can be manipulated manually, but this is generally to be avoided.

Select PseudoMDX (

Dimensions (

[0APO_PROD] (),

[0CUSTOMER] (),

[0MATERIAL] (),

[0CALWEEK] ()),

Measures (

[64381YV80FHCMZ26ZQQD1003D].[7A9LKMEDKUB9T6IKWEQ73C3PV], //Base Sales Quantity

[64381YV80FHCMZ26ZQQD1003D].[CD68DKVB8003MAL0SAICC8R6F], //Cost of Sales

[64381YV80FHCMZ26ZQQD1003D].[AEAIWVOIFO6I466U6IDWVT3D8], //Discount 1

[64381YV80FHCMZ26ZQQD1003D].[7UBTSBFL7JOPKTEUVINKD4TX6], //Ind. Sales Costs

[64381YV80FHCMZ26ZQQD1003D].[ETLOUTKELIRDGUSQNJ5CVLRS5], //Net sales

[64381YV80FHCMZ26ZQQD1003D].[CJQ2FSM751JT7SSRRFJ0T8ICL], //Planning Status

[64381YV80FHCMZ26ZQQD1003D].[9X0UTMFZ5VWQT208HAJSLV3QM]), //Revenue

From (0CSAL_C02/LWT1));

//*****

 

Optimizing a query

If dumps are produced in the SAP system or if the performance is slow, there are a number of performance improvements that can be implemented.

By default, the connector suggests the “D” (direct) parameter in the script. This is only intended for small amounts of data, since no slicing is performed.

Select PseudoMDX D (

Dimensions ( [BUD_CTRY] (),

[BUD_LOC] (),

[BUD_PROD] (),

[BUD_SECT] (),

[0CALMONTH] ()),

Measures (

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[05N6UOUENHI2PSWMWNWV0HQS0],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[6VMTYSJE733GVSGXA7WME01WO],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[AN043YDNYQQUUJIIW73G7SLCX],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[AYC6O2WD0MJ1CXERY8AHAKUVC]),

From (ZBUD_CUBE/ZZBUD_LWT));

 

If “D” is removed, automatic slicing is performed. Automatic slicing only works for queries that have an InfoCube as InfoProvider.

The automatic method primarily uses a time characteristic for slicing. If this fails or is missing, the largest characteristic is used instead. If this fails or is too slow, the connector can be forced to slice on a specific characteristic by using the “S” parameter. Check the connector log file for details when the job has failed.

Select PseudoMDX (

Dimensions (

[BUD_CTRY] (),

[BUD_LOC] S (),

[BUD_PROD] (),

[BUD_SECT] (),

[0CALMONTH] ()),

Measures (

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[05N6UOUENHI2PSWMWNWV0HQS0],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[6VMTYSJE733GVSGXA7WME01WO],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[AN043YDNYQQUUJIIW73G7SLCX],

[3ZAJ9QPTM5D8U5L9A1RCNSWWE].[AYC6O2WD0MJ1CXERY8AHAKUVC]),

From (ZBUD_CUBE/ZZBUD_LWT));

 

To improve performance, it is also possible to download the characteristics and key figures in one load and create separate loads for each characteristic with its attributes and hierarchies. If these separate loads are stored in QVD files, they can be easily merged together using the KEY field of the characteristic:

//*** Load Characteristics and Key Figures

LOAD [Country - Country Level 01 (Text)],

[Country - Country Level 01 (Key)],

// [Location - Location Level 01 (Text)],

[Location - Location Level 01 (Key)],

mid([Location - Location Level 01 (Key)],index([Location - Location Level 01 (Key)],'.[')+1) as [Location_Key], // link to Region hierarchy bottom level

[Month - Month Level 01 (Text)],

[Month - Month Level 01 (Key)],

[Organization - Organization Level 01 (Text)],

[Organization - Organization Level 01 (Key)],

// [Product - Product Level 01 (Text)],

[Product - Product Level 01 (Key)],

mid([Product - Product Level 01 (Key)],index([Product - Product Level 01 (Key)],'.[')+1) as [Product_Key],

[Sector - Sector Level 01 (Text)],

[Sector - Sector Level 01 (Key)],

// [Calendar Year/Month - Calendar Year/Month Level 01 (Text)],

[Calendar Year/Month - Calendar Year/Month Level 01 (Key)],

Factor, Cost, Budget, Revenue

FROM D:\Testing\5.2\olap\ZBUD_CUBE_Measures.qvd (qvd);

//*** Load Region Hierarchy and Display attributes

LOAD [Location - Regions Level 01 (Text)],

[Location - Regions Level 01 (Key)],

"Location - Regions Level 01 - [1BUD_LOC]",

"Location - Regions Level 01 - [2BUD_LOC]",

"Location - Regions Level 01 - [4BUD_LOC]",

"Location - Regions Level 01 - [5BUD_LOC]",

[Location - Regions Level 02 (Text)],

[Location - Regions Level 02 (Key)],

"Location - Regions Level 02 - [1BUD_LOC]",

"Location - Regions Level 02 - [2BUD_LOC]",

"Location - Regions Level 02 - [4BUD_LOC]",

"Location - Regions Level 02 - [5BUD_LOC]",

[Location - Regions Level 03 (Text)],

[Location - Regions Level 03 (Key)],

mid([Location - Regions Level 03 (Key)],index([Location - Regions Level 03 (Key)],'.[')+1) as [Location_Key], // link to Location

"Location - Regions Level 03 - [1BUD_LOC]",

"Location - Regions Level 03 - [2BUD_LOC]",

"Location - Regions Level 03 - [4BUD_LOC]",

"Location - Regions Level 03 - [5BUD_LOC]"

FROM D:\Testing\5.2\olap\ZBUD_CUBE_BUD_LOC.qvd (qvd);

LOAD [Product - Product Hierarchy Level 01 (Text)],

[Product - Product Hierarchy Level 01 (Key)],

"Product - Product Hierarchy Level 01 - [1BUD_PROD]",

"Product - Product Hierarchy Level 01 - [2BUD_PROD]",

[Product - Product Hierarchy Level 02 (Text)],

[Product - Product Hierarchy Level 02 (Key)],

mid([Product - Product Hierarchy Level 02 (Key)],index([Product - Product Hierarchy Level 02 (Key)],'.[')+1) as [Product_Key], // Link to Product

"Product - Product Hierarchy Level 02 - [1BUD_PROD]",

"Product - Product Hierarchy Level 02 - [2BUD_PROD]"

FROM D:\Testing\5.2\olap\ZBUD_CUBE_BUD_PROD.qvd (qvd);

LOAD

[Calendar Year/Month - Calendar Year/Month Level 01 (Text)],

[Calendar Year/Month - Calendar Year/Month Level 01 (Key)], // link to Calendar Year/Month

"Calendar Year/Month - Calendar Year/Month Level 01 - [20CALMONTH]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20CALMONTH2]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20CALYEAR]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20DATEFROM]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20DATETO]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20NUMDAY]",

"Calendar Year/Month - Calendar Year/Month Level 01 - [20NUMWDAY]"

FROM D:\Testing\5.2\olap\ZBUD_CUBE_0CALMONTH.qvd (qvd);

Accessing BEx queries

To access BEx queries through the OLAP interfaces, they need to set the following property in the BEx Query Designer (differs slightly between BEx versions):

• By OLE DB for OLAP

Delta loads

A special template, OLAP_delta.qvw, has been produced as an example of how delta loads can be performed. The procedure is described in a separate document, OLAPDeltaLoad.doc.

Both can be downloaded from the QlikCommunity user group SAP under Documents.

Limitations and suggested workarounds

No direct connection to InfoCubes

The OLAP Connector is designed to extract data from BEx queries, not InfoCubes. The first item in the drop-down is $INFOCUBE, which is a top level for all InfoCubes in the system. $INFOCUBE should not be selected because the Query list then displays Infoproviders instead of queries. Selection of Infoproviders by this means is not supported. You must select an Infoprovider in the drop down list.

InfoCube data can be extracted using the Extractor connector or the InfoProvider functionality of the BW connector.

Duplicate hierarchy leaves

The OLAP Connector cannot load hierarchies with duplicate leaves, such as hierarchies with link-nodes in SAP. Trying to do so will result in the following error:

Return data type: Key: [INFOOBJECT] has been assigned KeyPos: -1 in c.m_HierarchyKeys. Thus, the Key already exists in c.m_HierarchyKeys. Handling of duplicate Keys not possible.

To load hierarchies with duplicate leaves, use the SAP Extractor connector.

Hierarchy Selection variables

The OLAP Connector has the limitation that it cannot handle Hierarchy Selection variables in the select dialog in QlikView or Qlik Sense. There are two possible solutions to this problem:

• Recreate the BEx query in SAP Query Designer with static filters for the desired hierarchy to get around the variables issue.
• Manually modify the variables section in the load script in QlikView or Qlik Sense. For guidance, please consult Qlik Support.

Maximum number of dimensions

There is a hard limit of a maximum of 48 dimensions per extraction in SAP so the OLAP Connector is also limited to a maximum of 48 dimensions for each data extraction. The workaround for this limitation is to split the data extraction into multiple extractions and then link the data in QlikView or Qlik Sense.