Technical Articles
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.
Note | Description |
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
Name | Description |
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
Name | Description |
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 |
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:
Authorization Object | Name | Value |
S_BW4_REST | BW4_URI | /sap/bw/whm/backend/discovery* |
S_BW4_REST | BW4_URI | /sap/bw4/v1/monitoring/processchains/* |
S_BW4_REST | BW4_HTTP_M | POST, GET |
S_RS_PC | ACTVT | 16 (Execution) |
S_RS_PC | RSPCAPPLNM | <Group of your Process Chain> |
S_RS_PC | RSPCCHAIN | <Name of your Process Chain> |
S_RS_PC | RSPCPART | Runtime |
S_BTCH_NAM | BTCUNAME | <Background User> |
S_BTCH_JOB | JOBACTION | RELE |
S_BTCH_JOB | JOBGROUP | * |
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:
Object | Description | Value |
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:
Authorization Object | Name | Value |
S_BW4_REST | BW4_HTTP_M | GET |
S_BW4_REST | BW4_URI | /sap/bw4/v1/catalog/infoproviders/* |
Conclusion
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.
Hi,
Thanks for the Investigation in this Topic, I will add this to the Blog - DataHub Implementation with the SLC Bridge
Best Regards Roland
Hi,
I'm trying to follow the steps, however, I have a problem since in SICF, under BW4, I don't have any nodes at all. My system is BW4HANA 2.0 with Support Pack 001. May I know how to activate or make the services under BW4 node available? I check your SAP Notes listed, all of them are valid only for BW4HANA 1.0.
Regards,
Wira
Hello Wira,
I just double checked: There are no sub-nodes under "bw4" in SICF. Just activating the node itself will be sufficient. To be precise the following three paths need to be active in SICF:
default_host –> sap -> bw -> whm;
default_host -> sap -> bw4
default_host -> public -> sap -> icf -> logoff
Kind Regards,
Thorsten
to be precise ...
default_host -> public -> bc -> icf -> logoff
best regards Roland
Hi Roland,
Just wanted to understand if the method applies to also connecting SAP BW 7.50 SP05 as some of the sicf services are missing in SAP BW system.
Thanks,
Vipin
Hello Vippin,
in case of BW 7.50 the path default_host -> sap -> bw4 is not existing. There are compatibility replacements in the other services that are listed in the blog. E.g. /sap/bw4/v1/monitoring/processchains/<name_of_the process_chain>/start is replaced by
/sap/bw/whm/backend/run/processChain/start/<name_of_the process_chain>
Regards,
Thorsten
Hi Thorsten,
Thanks for this blog. I'm trying to connect our BW system on NW 7.5 SP5 to Data Intelligence service in SAP's cloud. The BW system is located on-premise. Can I get some directions on the ways of establishing a connection from cloud to on-prem in the scenario of Data Intelligence.
I did create a destination pointing to the BW system in the SAP cloud platform cockpit (where the Data Intelligence service resides) and also created a connector entry in the SAP Cloud connector. I keep getting the following error
Status code from adapter response: 400
For the HANA DB connection, I get the error
Error at connecting adapter for checking status: ESOCKETTIMEDOUT
It would be great, if I get some help.
Thanks,
Ragav
Hello Ragav,
Although I can understand that this scenario may be relatively common, the scenario you described exceed the scope of this blog entry.
I would ask you to open a ticket on the component for Data Intelligence, as I personally haven't worked with the cloud connector yet.
Regards,
Thorsten
Thank you very much for the update Thorsten. Appreciate you getting back soon. I've done that and I'm awaiting a response from SAP.
I've raised the message in the component "Data Intelligence – Operations (CA-DI-OPS)" is this still good? Should I reach out to them through anyother? Please advise.
Regards,
Ragav
Hi Thorsten
thank you for this great explanation. You described very well how to load data from SAP BW, but is there also a way loading data into SAP BW and SAP BW/4Hana?
Regards
Christopher
Hello Christopher
one thing you can always do is to write into a database as an intermediate step and add the corresponding table as a data source. You could for example write directly into the HANA database of your BW/4 system and then leverage its corresponding capabilities.
As to my knowledge writing into BW without a "detour" is currently in development. I personally however don't have exact facts as I'm not involved in the development.
Regards,
Thorsten
Hey,
thanks for the write up - very helpful!
which table would you need to reference, if you wanted to pull a full list of "who has access to what" - including all the accounts and entitlements they have - the access control list (ACL), for audit purposes
Hello Jamie,
Thank you.
Your question exceeds the scope of this blog post. The post is related to the technical administration of the connection. In case you have questions with regards to audit purposes I suggest to open a question on SCN.
Regards,
Thorsten
Hello Thorsten Schneider,
Good Blog, Nice Info
Do we have any operator or is there any way to load the data to BW Objects(ADSO)?
Advance Thanks
Hello Pavankumar,
one thing you can always do is to write into a database as an intermediate step and add the corresponding table as a data source. You could for example write directly into the HANA database of your BW/4 system and then leverage its corresponding capabilities.
As to my knowledge writing into BW without a “detour” is currently in development. I personally however don’t have exact facts as I’m not involved in the development.
Regards,
Thorsten
Hello,
In my case, this is the sap/bw/whm/backend/discovery service which is missing on my BW4.
Didn't find any relevant notes on the subject (all the notes in this blogs have been implemented or was not implementable).
Anyone with an idea ?
Hello Frederic Cincet,
please check from the BW/4 System with the Report BW4_REGISTER_RESOURCE, if you can call the Discovery Service successfully. the Service is not available from tx. SICF
Best Regards Roland
Thanks a lot Roland.
I can call this discovery service from the BW4_REGISTER_RESOURCE. However, I get an error from my pipeline when executing it:
Error during communication with the BW backend: Server returned HTTP response code: 500 for URL: https://xxx:yyy/sap/bw/whm/backend/discovery
Do we speak of the same discovery service ?
Hello, Yes.
Check the Blog - https://blogs.sap.com/2019/11/21/data-intelligence-hub-connecting-the-dots-.../
at the End of the Blog, you will find additional SAP Notes for BW/4 and Data Intelligence.
The Document - SAP First Guidance – complete functional scope (CFS) for SAP BW/4HANA contains additional Information to activate the Gateway on BW/4 via Task List.
Best Regards Roland
Hi Roland,
Thanks again.
Which part is this which describes the configuration of the gateway ?
I'm only aware of the list SAP_BW4_SETUP_SIMPLE which was run (not by me) probably a few years ago.
Note that I can read a BW data providers with the INA service. The pb occurs when switching the access method to Hana external view.
Regards,
Frederic
Ok, the problem was linked to the note 2675224 which was indicated as implemented though it was not.
Had to manually implement a few notes too on the CL_BW4_DISCOVERY (as the SNOTE was not able to identify the position of all the correction instructions).
Hi Roland,
We're on BW 7.5 SP20 on HANA and though the majority of the services are registered in BW4_REGISTER_RESOURCE, just two of them (discovery and infoproviders) provide a response when we call them. For the rest we get a error message that the re respective class is missing. For example, when queries service is called, we receive the below error:
"Resource CL_BW4_REST_CATALOG_QUERIES does not implement any supported interface".
Can you please advice whether all the registered services shall be available in our BW version and it's only the matter of implementing the missing classes?
Thank you and regards,
Irina
Hello Irina Lichkova,
this seems to be a new error, which is not reported, yet nor anything found on the Knowledge Base Search. Please open an incident at SAP for that.
Best Regards Roland
ps: see also the comment about Rules of engagement ...
Thank you Roland. An incident to SAP is raised already.
Ah, that's the one with no answer, yet ... 😉
Exactly 🙂
Hello Thorsten,
Very nice explanation on SAP DI to BW4HANA connection.
Currently I am working to extract data from BW4HANA 2.0 to Azure data lake using SAP Data Intelligence( version 2215.17.26) which is on AWS cloud.
My scenario is, delta extraction after initial load for BW objects of type Info object ,Composite Provider and ADSO.I have successfully created the DI to BW4HANA connection using connection type BW with https protocol but when I try to use ABAP ODP reader I am not able see the defined connection in the configuration of ABAP ODP reader .So I try to create to a flow using Data Transfer operator but extraction mode delta is disabled for composite provider.
Could you let me know if DI supports deltas for composite providers ,ADSO and Info objects ? If not please let me know the alternate.
Thanks ,
Sirisha
Hello Sirisha,
As far as I remember the ODP read operator requires a connection of type "ABAP". The BW Connection is used only for Process Chains and "BW-Native" Objects like Queries etc.
I haven't worked in that area for some time, so I hope this answer is sufficient for you to implement your use-case. If not you may use one of the other channels to ask the colleagues that are currently in this topic (open a ticket)
Best Regards,
Thorsten