Connect SAP BW/4 HANA to SAP Data Hub
In this blog post you’ll learn the settings required in SAP BW/4 HANA in order to connect SAP Data Hub. In particular it describes the authorizations required for the connecting SAP BW/4 HANA user. For this post a SAP BW/4 HANA 1.0 system was used. The settings are similar in every SAP Business Warehouse system however. To connect your SAP Business Warehouse to SAP Data Hub the following Support Packages are required.
|SAP BW release||Support Package|
|SAP BW 7.40||20|
|SAP BW 7.50||14|
|SAP BW 7.51||10|
|SAP BW 7.52||6|
|SAP BW4/HANA 1.0||8|
|SAP BW4/HANA 2.0||1|
Please note that this blog post focuses on the scenario of SAP Data Hub calling SAP BW4/HANA. The integration in the direction of SAP BW4/HANA calling SAP Data Hub is not part of the post.
Prerequisites in SAP Business Warehouse
In order to connect to a SAP Business Warehouse system, you need to make sure that certain services and SAP Notes are applied. It’s assumed that you have already made a Client Copy and, in case of SAP BW/4 HANA, for example the task list SAP_BW4_SETUP_SIMPLE has been executed via transaction STC01. Please make sure that you include the task ‘Activate InA Services for SAP Analytics Cloud Integration’.
As the InA protocol and the Business Warehouse REST-based Discovery service are used from SAP Data Hub to communicate with SAP BW4/HANA make sure that the SAP Business Warehouse system is reachable via HTTP or HTTPS (ensure that the ports are opened and, in case of HTTPS, that the relevant certificates are uploaded). You can maintain the required parameters in transaction RZ10 using the ABAP profile and restart the ABAP server afterwards to activate the entries. This is an example of HTTP port and HTTPS port:
icm/server_port_0 = PROT=HTTP, PORT=<your port number>, PROCTIMEOUT=600, TIMEOUT=60 icm/server_port_1 = PROT=HTTPS, PORT=<your port number>, PROCTIMEOUT=600, TIMEOUT=60
You can check if the InA protocol is working by use of transaction SICF. Choose Service as the Hierarchy Type and press the Execute button. Navigate to default_host –> sap -> bw -> ina right click on GetServerInfo and choose Test Service from the menu. If the services are not active, you need to activate them.
The Test Service functionality will open your browser to connect to the Service you specified. As a response a JSON Object should be displayed which contains some general information about your system (e.g. System Id and Client).
In case the endpoint cannot be reached you may try using the Gateway Client (transaction /IWFND/GW_CLIENT) to test it.
Go back and check if the services and the sub services of default_host –> sap -> bw -> whm; default_host -> sap -> bw4 and default_host -> public -> sap -> icf -> logoff are also active.
Check if the following SAP Notes are applied in your system if required.
|2701529||Scheme/term combinations in response to GET /sap/bw4/discovery are not correct|
|2685195||BW4 JSON Schemes: Error in discovery scheme|
|2676083||GET /sap/bw4/discovery does not contain any entries with scheme = processchain, term = modelingUI/monitoringUI|
|2675224||GET /sap/bw4/discovery terminates with 500 Internal Server Error|
|2671554||GET /sap/bw4/discovery terminates with 404 Not Found|
|2500019||SAP BW REST-based discovery service|
|2765410||Enhancements of the BW4 MAPI|
|2766598||BW4HANA: Missing information in catalog resources|
|2415249||Required to read metadata of queries and InfoProviders via the INA layer.|
|2236064||Describes how to make sure that only calculation views are generated in the BW system. If you still generate the old attribute and analytic views, you must migrate these to calculation views as described in the note|
|2761552||Required for SAP Data Hub 2.4 and higher. It fixes the BW discovery service needed for a new API that is used in 2.4 and later.|
|2715756||Required for SAP Data Hub 2.5 and higher. It provides fixes for the new query catalog service that SAP Data Hub uses in 2.5 and later.|
|2799738||Incorrect JSON Schema for BW4 discovery|
Create connection in SAP Data Hub
The next thing you’ll need to do is to create a connection in the SAP Data Hub. To create a connection, go to the SAP Data Hub launchpad and choose the Connection Management tile.
Create a connection to SAP HANA
If you are using BW/4 HANA or SAP Business Warehouse powered by SAP HANA first create a HANA DB connection. This is required as the connection is used for the data transfer process to SAP Data Hub via HANA views. If you are using SAP Business Warehouse on another DB, this step is not necessary.
In the connection screen, click on the Create button
Enter the following data
|ID||Name of your connection|
|Description||Describe your connection|
|Connection Type||Type of system you want to connect to – in this case HANA_DB|
|Manage Metadata||If set to True, this allows you to search connections in the Metadata Explorer. Only required if you want to see the HANA tables in the Metadata Explorer as well (in addition to the BW objects).|
|Host||Host name or IP address of the server|
|Port||The SQL port of your HANA database. In case of a single DB, this is 3<instance number>15. In case of a tenant DB you need to check this for example by executing this SQL statement in your System DB
|User||Your HANA user|
|Password||Password of your HANA user|
|TLS||Choose True if you have set up SSL (recommended)|
You should use a HANA user which has select access on the SAP Business Warehouse external views which you want to access. Otherwise you will get an error message, as the user needs this privilege to trigger the data transfer between SAP Business Warehouse and SAP Data Hub.
You can test the connection under Action Check Status
You should get the following success message.
Create a connection to SAP Business Warehouse
Click on the Create button again
Enter the following data
|ID||Name of your connection|
|Description||Describe your connection|
|Connection Type||Type of system you want to connect to – in this case BW|
|Manage Metadata||If set to True, this allows you to search connections in the Metadata Explorer. In case of a BW connection set it to True|
|Orchestration||Not changeable: Indicates that the BW system can be used to orchestrate processes, for example trigger BW process chain execution|
Host name (or IP address) of the ABAP web server without protocol (HTTP or HTTPS).
The host name of the BW System is displayed for example in the “InA Testmonitor” (transaction RSBITT) after you selected a value for the protocol field. Other ways of displaying the host name are the service display of the ICM Monitor (transaction SMICM), or to display the Connection Properties of the BW Connection in your SAP Logon.
|Port||HTTP or HTTPS port of the BW server|
|Client||Client to log on to. If left empty, the BW default client is used|
|Protocol (HTTP or HTTPS) – defaults to HTTPS||
For security reasons, we recommend using the default https protocol.
If using http you will not be able to schedule BW process chains by the Data Hub.
|User||User to log on to BW system (can be a service user)|
|Password||Password of your BW user|
|HANA DB Connection ID||ID of the HANA connection created in the previous step|
Test the connection and check if you get a success message.
SAP Business Warehouse Process Chain
With the BW Process Chain operator, you can start an SAP Business Warehouse process Chain from SAP Data Hub. You need to parameterize the operator using the BW connection and an existing process chain from you SAP Business Warehouse system.
It is assumed that you activated the necessary services as described in the prerequisites chapter. The execution might still fail however with a message like “forbidden” or “no suitable resource found” if the user doesn’t have the required authorization in the SAP Business Warehouse system.
Please be aware that only the minimum authorization to orchestrate a Process Chain in SAP Business Warehouse is shown in the following steps. Every customer has its own authorization concept, and you need to know what best suits your particular requirements.
Authorization Check for Service /sap/bw/whm/backend/discovery
SAP Data Hub calls two services for which your user needs authorization. These services are /sap/bw/whm/backend/discovery and /sap/bw4/v1/monitoring/processchains/<your process chain name>/start. To make things easier you can use the SAP Gateway Client tool to test the services directly in SAP Business Warehouse using transaction /IWFND/GW_CLIENT. Log on to your SAP Business Warehouse system with the user you are using for the connection between SAP Data Hub and SAP BW.
First test the service /sap/bw/whm/backend/discovery and add an HTTP Request to it. The request is Header Name: accept and value: application/vnd.sap.bw.whm.discovery+json;version=1.0.0
Check the result. If all notes have been applied but you don’t have the required authorization you’ll receive a message with status code 403 “Forbidden”.
To find out which authorization is missing in this case, use transaction SU53 for your user. In this example, you don’t have the authorization for the S_BW4_REST authorization object.
To give a user authorization, a new authorization role needs to be created using transaction PFCG. Create a new role, go to the Authorizations tab and choose Change Authorization Data
Add the authorization object S_BW4_REST manually and enter the required data. As an example, enter the values which are shown from the authorization log in transaction SU53. As the POST activity is also required later on, choose both activities and enter the URI /sap/bw/whm/backend/discovery*. Then save and activate the authorization role.
After defining the role you need to assign it to the user who is used in the connection from SAP Data Hub. Then execute the service again in SAP Gateway client /IWFND/GW_CLIENT. This should work now.
Authorization Check for Service /sap/bw4/v1/monitoring/processchains
In the next step you can check if the process chain can be executed using the relevant web service. Please note that the uri can vary according to the SAP Business Warehouse release.
Go to transaction /IWFND/GW_CLIENT again. In case there is an entry under HTTP Request from the previous chapter, delete this line. Then set HTTP Method to POST and enter the following URI /sap/bw4/v1/monitoring/processchains/<name_of_the process_chain>/start. In the pop up window delete the default request and replace it with /sap/bw/whm/backend/discovery. Then execute the service.
If you don’t have the required authorizations an error message is displayed. You need to enhance and change the role you just created by making the required settings.
Once again, you can call SU53 to find out which authorizations are missing for your user.
In this example a minimal role is built using the following settings:
|S_RS_PC||RSPCAPPLNM||<Group of your Process Chain>|
|S_RS_PC||RSPCCHAIN||<Name of your Process Chain>|
With these authorizations the web service is executed successfully.
You can now also orchestrate the process chain from SAP Data Hub.
Transfer Data from SAP Business Warehouse to SAP Data Hub
With the Data Transfer operator, you can transfer data from SAP Business Warehouse to SAP Data Hub. The user which is used for the SAP Business Warehouse connection needs authorization to access the Info Provider or the Business Warehouse query. If you want to access different providers/queries with the same connection the user entered in the connection need authorization for all of the providers/queries.
Please take a look at SAP Note 2711139, which describes the limitations of the BW data transfer operator.
You have to migrate generated analytic and attribute views to calculation views as described in SAP Note 2236064.
Create a graph with a Data Transfer Operator as described here and then execute it. If it fails with a message like ”You do not have the authorization for component XX”, you have various ways of checking your authorizations.
In the following steps , please note that only the minimum authorization to read data from exactly one Info Provider or Query is shown. Every customer has its own authorization concept, and you need to know what best suits your particular requirements.
Authorization Check in SAP Business Warehouse
In SAP BW, use transaction SU53 to check which authorization is missing for a given user. As an example, user BWDATAHUB does not have authorization for Info Cube ZADSO361. A new role has to be created for authorization object S_RS_COMP.
To give authorization to this user, either a new role needs to be created or an existing needs to be enhanced using transaction PFCG. Under Authorization, add authorization object S_RS_COMP manually (or S_RS_COMP1 if you want to transfer the data from an SAP Business Warehouse query) and enter the required data. In this example, the exact values which are shown from the authorization log in transaction SU53 are used. In a real-world scenario however, you might define the role with a much broader scope.
In the example above ‘$$AZADSO361’ is a generated technical name denoting the default query on the corresponding InfoProvider ‘ZADSO361’. The same naming conventions can be used for any other InfoProvider.
After defining the role you need to assign it to the user who is used in the connection from SAP Data Hub.
Defining authorizations only for this role is not sufficient enough in SAP Business Warehouse. You also need to define the Business Warehouse Analysis Authorizations. To do this, go to transaction RSECADMIN and choose Authorizations Ind. Maint.
You need to provide values for at least for these four InfoObjects:
|0INFOPROV||InfoProvider||Your InfoProvider in our example ZADSO361|
|0TCAACTVT||Activity in Analysis Authorizations||Change or Display|
|0TCAIPROV||Authorizations for InfoProvider||Authorization for InfoProvider|
|0TCAVALID||Validity of an Authorization||Time frame of validity|
Save and activate the role. Then assign it to the user of the connection, thus returning to transaction PFCG and to the role you created in the previous step. Add authorization object S_RS_AUTH and the BW Analysis Authorization role that you created.
You can now trigger a data transfer from SAP Business Warehouse to SAP Data Hub.
The data is written successfully to an SAP Vora table.
Authorization Check in SAP BW/4 HANA for HANA External View Access
If you have an external HANA view or an external HANA query, the default access method is the HANA View rather than the InA protocol. In that case you need to grant select authorizations on the external view.
As mentioned in chapter Create Connection in SAP Data Hub, you need to add a HANA connection to your BW connection. The user of the HANA connection needs a select privilege on the HANA view you want to access. These privileges are created automatically during generation of the view, provided that the correct user mapping is maintained from a SAP Business Warehouse user to the underlying HANA user. For details about the generation of the privilege, see Authorizations for Generated HANA Views.
If there is no user mapping you can grant the privileges by using the SQL Editor from transaction DBACOCKPIT or HANA Studio and the following statement:
grant select on <view name> to <your user>
You can check if a user has the authorization for this view for example with the following statement:
SELECT * FROM (SELECT GRANTEE, GRANTEE_SCHEMA_NAME, GRANTEE_TYPE, GRANTOR, OBJECT_TYPE, P.SCHEMA_NAME, P.OBJECT_NAME, COLUMN_NAME, PRIVILEGE, IS_GRANTABLE, P.IS_VALID,V.VIEW_TYPE SUB_TYPE,V.IS_READ_ONLY FROM SYS.GRANTED_PRIVILEGES P JOIN SYS.VIEWS V ON (P.SCHEMA_NAME = V.SCHEMA_NAME AND P.OBJECT_NAME= V.VIEW_NAME) WHERE OBJECT_TYPE ='VIEW' union all SELECT GRANTEE, GRANTEE_SCHEMA_NAME, GRANTEE_TYPE, GRANTOR, OBJECT_TYPE, P.SCHEMA_NAME, P.OBJECT_NAME, COLUMN_NAME, PRIVILEGE, IS_GRANTABLE, P.IS_VALID,MAP(IS_USER_DEFINED_TYPE,'TRUE','TABLE_TYPE',OBJECT_TYPE) SUB_TYPE,'FALSE' IS_READ_ONLY FROM PUBLIC.GRANTED_PRIVILEGES P JOIN SYS.TABLES T ON (P.SCHEMA_NAME = T.SCHEMA_NAME AND P.OBJECT_NAME = T.TABLE_NAME) WHERE OBJECT_TYPE ='TABLE'union all SELECT GRANTEE, GRANTEE_SCHEMA_NAME, GRANTEE_TYPE, GRANTOR, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, COLUMN_NAME, PRIVILEGE, IS_GRANTABLE, IS_VALID,OBJECT_TYPE SUB_TYPE,'FALSE' IS_READ_ONLY FROM SYS.GRANTED_PRIVILEGES WHERE OBJECT_TYPE NOT IN ('TABLE','VIEW')) WHERE GRANTEE = ? AND GRANTEE_SCHEMA_NAME IS NULL
In addition to the HANA authorizations, the user also needs SAP Business Warehouse authorization for the SAP Business Warehouse rest services. Therefore, go to transaction PFCG and modify the role you created in the previous chapters (or create a new one). You need to grant the following authorizations:
Now that you finished the blog post you should have some knowledge about the authorization concept in SAP Business Warehouse, and be able to create a connection to an SAP Business Warehouse system in SAP Data Hub. Due to the clear separation of the scenarios we were able to create a unidirectional integration without the need for a connection from SAP BW/4 HANA to SAP Data Hub.
Going forward this integration allows us to create various scenarios that leverage the different capabilities of the two systems. The scheduling of BW Process Chains using the Data Hub allows a powerful orchestration and automation, especially if combined with the BW Process Type “Start SAP Data Hub Graph” which is available in SAP BW/4 HANA.