Technical Articles
Integrate it! – Qlick View/Sense and SAP integration. Step-by-step guide
Quote.
“The increased scale and complexity of SAP and non-SAP systems challenges require that we look for
ways to simplify the system-integration requirements”
This quote about a series of blog-posts (“Integrate it!”) about integration between different SAP and non-SAP systems
Another posts from this series (The list is being updated.)
About.
Qlik Sense is a popular data discovery and analysis tool. It provides an interactive user interface where you can use the tools for modeling and managing data, creating visualizations, layouts, and stories.
Prerequisites.
You want to know more about SAP – Qlick View/Sense integration
Resolution.
Step 1. Install Connectors into ERP/BW system.
Download archive with SAP transport requests from qlick web-site
Unzip it and upload to an application server
Upload K* files to /usr/sap/trans/cofiles
Upload R* files to /usr/sap/trans/data
N.B. Of course on application server file owner must be <sid>adm and correct permissions
* .H6A – Data extraction
*.E66 – User profile
*.H7T – User profile BW
Add Transport Requests to the import queue.
Transaction STMS
Extras – Other requests – Add
Transport Requests:
|
Data extraction
|
H6AK900045
|
|
User profile
|
E66K900154
|
|
User profile BW
|
H7TK900185
|
N.B. This information and TR numbers actual on 18/03/2020 and can differ in future
Import Transport Request
Result:
Step 2. Creating and maintaining an SAP User ID.
Create user BC_APP_QLICKUSER , (In this suggestion BC means Basis Components)
Type – Service
Roles:
QTQVCBWACCESS
(This role to be used by OLAP, DSO/ODS, BEx and InfoProvider Connectors back-end user for doing extraction jobs from SAP BI/BW system from Qlik.)
QTQVCACCESS
(To be used by a connector back-end user for doing extraction jobs from Qlik.)
Administrative Roles:
QTQVCADMIN There are some Qlik transactions in the SAP GUI. Most of them require that this role is assigned to the user.
Administrative role QTQVCEXTRADM can be added to existing Admin users of the SAP system.
Step 3. Open System for Changes.
Transaction – SCC4 (Client Administration)
Select an actual client and set changes as on the following image
Step 4. Creating the Extractor Environment in the SAP system.
Transacation – /QTQVC/EXTRACTOR_ADM (Maintain Extractor environment)
Step 5. Check FQDN server name.
Transaction – RZ11 (Maintain Profile Parameters)
Parameter – SAPLOCALHOSTFULL must contain a FQDN server name
Step 6. Activating Extractors.
Transaction – SBIW (BIW in IMG for OLTP)
Transaction – RSA6 (Maintain DataSources).
For example: Activate Extractors for QM-data:
2LIS_05_Q0CAUSE – DataSource – Quality Notifications – Causes
2LIS_05_Q0ITEM – DataSource – Quality Notifications – Items
Copy DataSources to clipboard
Transacation – /QTQVC/ACTIVATE (Activate an Extractor (datasource))
Step 7. Migrate Extractor Environment
Quote: “When the Extractor environment has been created, it is possible to migrate it to other SAP systems in the landscape by using the SAP transport management system. This functionality can be used by customers who do not want to change the SAP configuration in production systems. Instead, the Extractor environment can be created in a development system and migrated to the production system.”
In the Source System:
Transaction – /QTQVC/MIGRATE_ENV
Add Extractor environment into the transport request
Release TR and import in into Target System (For example this is QAS-system)
In the Target System:
Transaction – /QTQVC/CONNECT_ENV
Step 8. Migrate activated Environment
In the Source System:
Transaction – /QTQVC/MIGRATE_PREP
Get extractors to migration
Transaction – /QTQVC/MIGRATE_EXTR
Add activated Extractors into transport request (Step 1…Step 4)
In the Target System:
Transaction – /QTQVC/MIGRATE_EXTR
Perform Step 5
Step 9. Settings from the Qlick View/Sense server-side
Prerequisites for this step
- Windows 2012 R2 and above server
- SSL certificate
- Qlick Sense Enterprise installed
- Qlick Sense Enterprise Updates installed
- Qlick Connector for SAP installed
-
Qlik SAP Network Server installed
Open file
C:\Windows\System32\drivers\etc\services
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
System number for message server we can find in transaction SMMS
Step 10. Using the SAP Extractor Connector
List of possible Qlick Extractors:
Useful information about SAP Extractor Connector here – https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Clients/SAP-Extractor-connection.htm
Sap Extractor:
Host, Client, System ID (SID), Username, Password – parameters related to SAP System
Service Host and Service Port – parameters related to Qlick.
We can check that Qlick SAP Network Server Run:
We can check that Qlick SAP Network Server listen 8680 port:
netstat -o | findstr “8680”
The result must be:
Troubleshooting.
Problem:
ERROR partner ‘*.*.*.*:sapgw00’ not reached”
Resolution:
-
Check Qlick Community
2. Check dev_rfc* files in /usr/sap/<SID>/D<instance number>/work directory
3. Look at Notes
63347 – List: CPIC error codes
and Note #353597 – Registration of RFC server programs
2104408 – Checklist for “program <program ID> not registered” errors
Look at this wiki:
-
Look at Qlick Trace Files:
C:\ProgramData\QlikTech\Custom Data\QvSAPConnector\Log
C:\ProgramData\Qlik\Custom Data\QvSAPExtractorConnector\Log
Update – 31.03.2020
Problem: DBSQL_DUPLICATE_KEY_ERROR during extraction procedure
Resolution:
Note #1862969
https://launchpad.support.sap.com/#/notes/1862969
Conclusion.
Approach to SAP – Qlick integration was provided for SAP Extractor connection
Also available SAP SQL, SAP BAPI, SAP BEx connectors
Appendix.
List of Qlick Transactions:
/QTQVC/ACTIVATE (Activate an Extractor (datasource))
/QTQVC/CONNECT_ENV (Store a Connection to QV)
/QTQVC/DEACTIVATE (Deactivate an Extractor)
/QTQVC/DELETE (Delete Database Table Records)
/QTQVC/DELETE_BGD (Delete records from Qlik DB tables)
/QTQVC/DELETE_INIT (Delete Delta Init)
/QTQVC/EXTRACTOR_ADM (Maintain Extractor environment)
/QTQVC/MIGRATE_ENV (Migrate Extractor Environment)
/QTQVC/MIGRATE_EXTR (Migrate Activated Extractors)
/QTQVC/MIGRATE_PREP (Put activated Extractors into table)
/QTQVC/SQL (Testing Statements for QVC)
/QTQVC/UPDATE (Update Database Table Records)
/QTQVC/AUTH_MAINTAIN (Control Access for QlikView)
Links.
Installing the SAP transports
Using the SAP Extractor Connector
Installing the Qlik SAP Network Server service
Be the first to leave a comment
You must be Logged on to comment or reply to a post.