Live Data Connection: SAP BW/4HANA to SAP Analytics Cloud

Simon Kessler

Posted by Simon Kessler on

SAP Cloud Platform | HANA | Analytics Consultant

More by this author

This blog post will cover the usage of the live data connection in SAP Analytics Cloud (SAC) to SAP BW/4HANA and SAP BW. To help you with your implementation you will learn about:

  • Architecture of a live data setup
  • Data privacy
  • Predictive features
  • Performance measurement and improvement

The cloud based analytics solution is a fully integrated SaaS platform which offers business intelligence, planning and predictive features. It allows you to discover, visualize, plan and predict all in one single product.

By being a cloud based solution SAP Analytics Cloud requires no installation to use. Just start up your browser and begin exploring your data and for mobile analytics an iOS App is available.
A flexible licensing allows to easily scale the application as your user base grows.

On top of SAC you can run the Digital Boardroom which smartly reuses the stories (reports in SAC are called stories) you built in SAC and turns them into great, interactive presentations.

Connecting to Data Sources

SAC can connect to a multitude of data sources. The connections are differentiated by the two categories live data connection and import data connection. While the import data connection allows to replicate data to the cloud from following sources:

  • SAP BW
  • SAP BPC
  • SAP Universe
  • SAP ERP
  • SQL Database
  • SuccessFactors
  • OData Services
  • SAP Hybris Cloud for Customer
  • SAP Hybris Cloud for Customer Analytics
  • SAP Business ByDesign Analytics
  • Concur
  • Salesforce (SFDC)
  • Fieldglass
  • Google Drive
  • Google BigQuery System

The live data connection can connect to a remote system without replicating data but querying it directly:

  • SAP HANA
  • SAP S/4HANA
  • SAP BW, BW/4HANA
  • SAP Universe

Live Data Connections

As this post targets to cover mainly the functionality which is available with the live data connection I will first start to explain how the architecture for this setup looks like, what the requirements are and finally lose some words about data privacy.

Customers today prefer the live data connection over the import data connection due to its simplicity in terms of maintenance. No need to maintain a replication process, data stays on premise, existing authorization and metadata can be reused.

Architecture

SAC is a cloud solution and therefore we need some integration mechanism to let SAC talk to your on premise BW/4HANA system. All requests from SAC to the BW/4HANA system are triggered by the browser runtime via XML HTTP Requests (XHR). Sounds simple and reasonable? Yes, but we should not forget about Same-Origin-Policy (SOP):

“The same-origin policy restricts how a document or script loaded from one origin can interact with a resource from another origin. It is a critical security mechanism for isolating potentially malicious documents.” (Mozilla)

What does this mean for our planned setup? If we access SAC on a public URL like https://example.eu1.sapbusinessobjects.cloud then the browser is not allowed to make requests to the BW/4HANA system at e.g. https://bw4h.example.com.

Architecture without a reverse proxy

Thus, we need a way to overcome the Same-Origin-Policy (SOP) which disallows a browser to make XMLHttpRequests to another domain (BW/4HANA). The workaround in one of my previous blog posts was to use a reverse proxy. This is working fine and was until recently the recommended setup. But with Wave 20 we got the support for Direct Live Data Connections to BW/4HANA. As the name suggests no middleman (reverse proxy) is required anymore. SAC can now talk directly to BW/4HANA.

But wait.. Didn’t we say it is not possible because of the SOP? Yes, but there is this great feature called Cross-Origin-Resource-Sharing (CORS):

“CORS (Cross-Origin Resource Sharing) is a system, consisting of transmitting HTTP headers, that determines whether to block or fulfill requests for restricted resources on a web page from another domain outside the domain from which the resource originated.

The same-origin security policy forbids “cross-domain” requests by default. CORS gives web servers cross-domain access controls, which enable secure cross-domain data transfers.“

(Source: Mozilla)

Now that sounds exactly like what we need! So CORS is adding some HTTP headers from the server’s (BW/4HANA) response that tell the browser “Hey, it is safe and okay to access my domain as long as you are from <domain-of-SAC>”. Among others the Access-Control-Allow-Origin header is sent. It must contain the SAC domain so the browser knows it is safe to access this server from the SAC domain. You could call this kind of a trust setup. Keep in mind that as with the reverse proxy setup the data flow is only happening between the browser and your BW/4HANA system. If both are in the corporate network your data never leaves your network.

Entering the credentials all the time is also not very convenient. Therefore we have to enable SSO as well. For that we will use SAML and two Identity Providers. Why would we use two IdP? Well, if possible you should always go with one IdP only. But some landscapes may require you to use one master IdP and another which acts as a proxy. SAP IAS allows you to be setup as a proxy. It will basically pass through all authentication requests to your corporate IdP (e.g. Azure AD). This setup is totally possible with SAC and BW/4HANA. In this blog post we assume that you want to setup trust between BW/4HANA and Azure Ad. SAC will have a trust relationship with IAS where IAS is acting as a proxy for Azure AD.

In the next chapters we are going to check out what is required to set this all up.

SAP BW/4HANA setup

First of all you have to ensure that the information access (InA) service is activated and you are running SP4 at least. Then follow the excellent description of Firas: https://blogs.sap.com/2017/11/08/enable-bw-direct-live-connections-in-sac/. You must perform the steps mentioned under the section “Netweaver 7.4+”.

I will quote the relevant steps for easier reference here:

“On your BW system, create a file somewhere (ex: /usr/sap/<SID>/SYS/profile/cors_rewrite), then add it to icm/HTTP/mod_0 as the following:

icm/HTTP/mod_0 = PREFIX=/,FILE={path_to_cors_rewrite_file}

The file should hold the following content:

if %{HEADER:isSACOriginAllowed} = true
	setHeader isSACOriginAllowed false

if %{HEADER:ORIGIN} regimatch ^(http(s)?:\/\/)?{SAC_HOSTNAME} [AND]
if %{PATH} regimatch (\/sap(\(.*\))*\/bw\/ina\/*) [AND]
if %{REQUEST_METHOD} regimatch (GET|POST|OPTIONS)
setHeader isSACOriginAllowed true
 
if %{HEADER:isSACOriginAllowed} = true
begin
  setResponseHeader Access-Control-Allow-Origin %{HEADER:ORIGIN}
  setResponseHeader Access-Control-Allow-Methods GET,POST
  setResponseHeader Access-Control-Allow-Headers x-csrf-token,x-sap-cid,authorization,mysapsso2,x-request-with,sap-rewriteurl,sap-url-session-id,content-type
  setResponseHeader Access-Control-Max-Age 600
  setResponseHeader Access-Control-Expose-Headers x-csrf-token,sap-rewriteurl,sap-url-session-id,sap-perf-fesrec,sap-system
  setResponseHeader Access-Control-Allow-Credentials true
end
 
if %{HEADER:isSACOriginAllowed} = true [AND]
if %{REQUEST_METHOD} stricmp OPTIONS
begin
  regRewriteUrl ^/(.*) /sap/public/ping
  removeResponseHeader Set-Cookie
  removeResponseHeader Expires
end

kindly replace {SAC_HOSTNAME} with your SAC host name(s) (including port if none standard), you may also adapt the pattern to meet your requirement (http or https or both ..).

Finally you have to restart your ABAP system.”

Now BW/4HANA will send CORS headers when the request is coming from the SAC domain. If you are using a Web Dispatcher in front of BW/4HANA then please perform the aforementioned step on the Web Dispatcher instead of on the BW/4HANA server. Always the last node (which is “nearest” to SAC) must be configured to send the CORS headers.

This is technically everything we need to use the direct live data connection. But as we also want to use SAML SSO some more steps need to be performed.

First ensure that the InA package (/sap/bw/ina/service/v2) or a higher-level package is configured for SAML authentication with your chosen IdP.

To enable SAML SSO from SAC to BW/4HANA we must provide some dummy HTML file which is used to authenticate the user and follow the SAML HTTP redirects. The setup is straight forward and documented in detail in the SAC manual:

  • Enter transaction code in BW/4HANA: SICF.
  • Enter Service Path/sap/bw/ina, and then press Enter.
  • Under default_host > sap > bw, right click ina, then choose New Sub-Element.
  • In Service Name, enter auth.
  • Add a description.
  • Open the Handler List tab, and enter ZCL_DUMMYAUTH_SERVICE
  • Save and return to the main menu.
  • Enter transaction code: SE24.
  • Enter Object TypeZCL_DUMMYAUTH_SERVICE, select Create, and then select Save.
  • Go to the Interfaces tab, and add IF_HTTP_EXTENSION, plus a description.
  • Go to the Methods tab, and add the following information:
    • MethodIF_HTTP_EXTENSION~HANDLE_REQUEST
    • LevelInstance Method
    • VisibilityPublic
    • Description: Add a description
  • Double click on IF_HTTP_EXTENSION~HANDLE_REQUEST and add the following code:
method IF_HTTP_EXTENSION~HANDLE_REQUEST.
    DATA:
          html_content TYPE string.

    html_content = '<html><script type="text/javascript">window.close();</script></html>'.
    server->response->set_cdata( data = html_content ).
endmethod.
  • Select Save.
  • (Optional) Check if the auth package is installed.
    Open the following URL in your browser: https://<Your_ABAP_System>/sap/bw/ina/GetServerInfo?sap-client=<Your_Client_ID>. Make sure you are redirected to your IdP login page, and that you do not get 404 page after login. Replace <Your_ABAP_System> with your ABAP system host, and <Your_Client_ID> with your SAP BW client ID.

As you can see the HTML file does nothing except closing the window. This is required as SAC will trigger this URL (/sap/bw/ina/auth). As this URL is SAML protected the browser will be redirected to the IdP. The IdP will then recognize that you are already authenticated (when logging in to SAC) and have a session. So your browser follows the redirects by the IdP and finally the HTML content is delivered which closes the pop up.

Browser setup

Your browser (Chrome) must allow 3rd party cookies and pop ups from the SAC domain. This can be easily configured in the Chrome settings menu:

 

SAP Analytics Cloud setup

Ensure that you have setup trust between your IdP (in this case IAS) and SAC. This can be configured in the SAC admin panel: Enabling SAML Single Sign-On (SSO).

In case your IdP delivers the User ID in mixed case (e.g. KesslerSimon) then you cannot use the user id as the user attribute. In SAC all user ids are uppercase and the comparison is case sensitive. If this is the case for you then choose Custom SAML User Mapping which allows you to enter case sensitive attributes (here: the user id). In SAML you have a NameID attribute which identifies a user in the system. This attribute is compared against SAC and if it matches the user is allowed to access. That’s why we use custom SAML user mapping which enables SAC to compare this NameID attribute (coming from your IdP) against a custom attribute in your SAC user list. When using this configuration the user id in SAC (first column in the Users menu) is not relevant anymore for the IdP and can contain any string.

Now we can proceed to setup the connection to BW/4HANA:

  1. Create connection “Live”
  2. Specify the BW/4HANA domain
  3. Specify the client
  4. Select SAML
  5. Press OK

 

Now a pop up window should appear and immediately close. That’s it! Now you can create models from your direct live data connection to BW/4HANA.

Requirements

Currently the live data connection from SAC to SAP BW is supported for the following BW versions:

  • SAP BW/4HANA SP4+
  • SAP BW 7.4 SP17+
  • SAP BW 7.5 SP7+
  • SAP BW on any DB / on HANA

The information access (InA) service and SAML must be activated on the BW/4HANA system.

Data Privacy

SAC runs in SAP data centers with one of the highest data protection standards. Therefore your data is safe even when you import it. As we are using in this post the live data connection I want to highlight that SAC stores no data from BW in the cloud. Only metadata like filter values, dimension and key figure names may be stored.

Each data access request is going straight from your browser to your BW/4HANA system.Therefore the data does not even pass the SAC cloud. It all happens locally in your browser runtime.

Troubleshooting

When you experience any issues please first try to clear your browser cache or perform the steps in a Chrome incognito window to rule out any old sessions getting in your way.

Feature Support

The live data connection currently supports a subset of all features. The documentation lists the limitation more in detail. There is also a helpful support matrix available which shows which BW/4HANA features are supported. Also keep an eye on the relevant KBAs to stay up to date on any issues (use the component LOD-ANA-BI in the support launchpad)

Predictive Features

SAC offers multiple predictive features and some of them are also available by using the live data connection. Now how does this work? The requested data is sent from your browser to SAP where the data processing occurs. You have to opt-in into this feature, by heading to System – System Configuration – Enable Time Series Forecast and Smart Grouping on Live Data Models.

Smart Grouping can be used for Bubble and Scatterplot charts. It will cluster your data points into groups based on similarities in the data. Select a supported chart, go to the Builder and activate Smart Grouping on the bottom. You can adjust the number of groups and the group label. By default the clustering algorithm will also take into account any tooltip measures from the chart.

Time Series Forecast allows to forecast the value of a time series with an algorithm (automatic) which doesn’t consider seasonality effects or with triple exponential smoothing which better fits seasonality in the data. The forecast can be easily enabled on the attached menu of a chart (select a chart to bring this up):

It will also show the upper and lower confidence bounds. Be aware that this only works with the chart type Time Series which requires a time dimension. When going to the advanced menu the number of forecast periods can be adjusted and you can add additional forecast input to improve the quality (which must have data beyond the main data series’ time range). The quality can checked out when you click on the Forecast link in the chart details (right below the chart title):

Performance Measurement

Created a great story and want to further improve the performance? Then you need to know how to analyze the performance of your SAC setup! We first must distinguish between SAC frontend performance and BW/4HANA query performance. Both areas may be subject to improvements during your investigation.

To measure SAC frontend performance we can utilize a built in timing feature. It can be switched on by modifying the application URL. Let’s assume your SAC URL to view a certain story is:

https://sac.example.com/sap/fpa/ui/tenants/001/app.html#;view_id=story;storyId=4B43D159F6F0B785F10000000B78A232

You now have to add the following URL parameter: STORY_PERFORMANCE_LOGGING. The URL should like this (notice the ?STORY_PERFORMANCE_LOGGING part):

https://sac.example.com/sap/fpa/ui/tenants/001/app.html?STORY_PERFORMANCE_LOGGING#;view_id=story;storyId=4B43D159F6F0B785F10000000B78A232

Now you can fire up the Chrome Developer Tools (F12), open the Console and filter by StoryPerformanceLogging:

Another approach is to use the Performance tab in the Chrome Developer Tools. Normally the frontend is not the bottleneck as it is a lightweight SAPUI5 application. Often the queries have more room for optimization as they might lack the fine tuning. Queries can be checked in the BW/4HANA directly but it is also possible to get some runtime information from SAC without BW/4HANA knowledge.

Getting information about BW/4HANA query execution time is quite easy by using again the Chrome Developer Tools. Make sure to switch to the Network tab and press the Record button on the left side:

Now reload the page with F5 (Chrome page reload). Filter the result by GetResponse and you will get a list of HTTP requests which are made to the BW/4HANA system. Each chart will trigger one request / query execution. On the right side you will see a waterfall chart which allows you to easily spot long execution times of a query.

When you found a request which seems to take a bit too long just click on the request line to open a more detailed view. On the right pane select the Timing tab:

This tab shows you some statistics like:

  • Queueing: How long the request had to wait in a queue until it was executed
  • Waiting (TTFB): Time to first byte, tells how long it took until a response from BW/4HANA or the proxy server was received.

When the TTFB looks rather high than it is worthwhile to further investigate this request. Navigate to the Preview tab in the right pane, which shows the JSON response in a nice tree view. Expand the nodes PerformanceData  Measurements – 0 (ABAP BICS: Get Rs Data). This node shows you how many seconds it took to execute the query on the BW/4HANA side. In our example the response time is already quite good:

Performance Improvement

As you have now learned how to measure performance it is time to learn how you can improve the performance. I don’t consider BW/4HANA queries in this blog post as there is already plenty of information available on how to tune these. Let’s rather have a look at the SAC story design.

Do not stuff too many charts on a single page. Each chart triggers one query execution, so think about your story flow and what information really makes sense on one page. This will also make it easier for your colleagues to quickly grasp the main purpose of your report and avoids information overflow. Maybe some of your KPIs can be grouped together and displayed in a single Numeric chart.

Sometimes multiple charts can be replaced by a single table. This results in a minimization of requests. The trick is to create a canvas page and to position a single table. Choose all the measures and dimensions you need in your report. Select the first column (any cell) and open the Styling tab (next to the Builder). Press the to add multiple new columns. Now add as many columns and rows as you need to build your layout. Copy and paste each data cell (measure) with CTRL-C and CTRL-V to the appropriate location in the table. This will create a cell reference. These references are more stable than formulas and even stick to the correct source when the table is sorted or a key figure added/removed.

Finally make sure to set all the cells of the actual data (should be on the right side now) invisible by setting the font and cell color to transparent (opacity 0%). Last but not least create some boxes and other styling and move it behind the table to make your report look nice.

Please also avoid to clutter the pages with too many page filters. They will slow down the page rendering. As a rule of thumb I would stick to a maximum of five filters for most use cases. It also depends on how many items a filter has. Filters with thousands of items will considerably slow down the page loading.

Images are nice on a story and make it more vivid. But they will also impact loading time. If you are going to use a lot of images always ensure they are shrunk and optimized to your story. There is no need to include 4K images if it is scaled down to a 20×40 pixel image in the story.

Some other improvement areas are on the PC/connection side directly. Use a strong WiFi signal or a wired network connection to rule any internet connection issues out. Check if you have the latest Chrome browser and enough free RAM on your machine. Sometimes it helps to close some tabs on Chrome to free up some memory.

Conclusion

I have shown you how to setup the live connection to BW/4HANA without a reverse proxy by using CORS. Additionally we have set up SAML SSO for convenient query consumption. Also some predictive features were shown and how to utilize them. Finally the measurement and improvement of performance was explained and some tips were given.

I hope you enjoyed my blog post and it will help you to get started with SAC! If you have any questions feel free to come back to me.

References

VN:F [1.9.22_1171]
Average User Rating
Rating: 5.0/5 (1 vote cast)
Live Data Connection: SAP BW/4HANA to SAP Analytics Cloud, 5.0 out of 5 based on 1 rating

192 Views