SAP Analytics Cloud: Live Data Connection to SAP BW/4HANA
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
UPDATE: Please also checkout my newest blog post about an alternative setup without reverse proxy: https://blogs.sap.com/2017/11/28/sap-analytics-cloud-live-data-connection-to-sap-bw4hana-using-cors-and-sso
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 SAC 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
- OData Services
- SAP Hybris Cloud for Customer
- SAP Hybris Cloud for Customer Analytics
- SAP Business ByDesign Analytics
- Salesforce (SFDC)
- 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.
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.
External vs Internal Access
The solution is to introduce a reverse proxy (Apache) which acts as a single entry point to SAC and BW/4HANA. A single URL will be used to access SAC (normal redirect to SAC). The BW/4HANA system will be available on the reverse proxy under a path, e.g. “/BW4H”. All requests to this path are redirected from the proxy to the BW/4HANA system:
Beneficial in this setup is that you can easily restrict the access to the BW/4HANA system by making your reverse proxy only available on the internal corporate network. Thus your users have two choices: access SAC on the public URL from outside the corporate network but without access to BW/4HANA or use the internal URL in the corporate network with full access to BW/4HANA:
We now have a basic understanding of the architecture and which components we need in our landscape. Let’s have a look into the options we have in setting up our landscape in regards to productive and development environments. To access the BW system in SAC a connection needs to be created. This connection points to a path relative to the domain on which SAC was accessed. During your setup you may ask yourself the questions: How many SAC tenants do I need? How to connect the reverse proxy with SAC? How many reverse proxies do I need?
To answer these questions I will show you several options which should fit every budget and requirement (please note that for the sake of simplicity only a two-tier environment will be considered. The approach shown is also valid for a three-tier environment):
Single-Tenant-Single-Proxy: This setup uses one SAC tenant (productive) and one reverse proxy. On the reverse proxy there will be two paths defined: /BW4H_DEV and /BW4H_PROD. Depending on the path the requests are redirected to the BW/4HANA development or productive system. On SAC the according connections have to be created (let’s call them BW4H_DEV and BW4H_PROD) which use the defined paths.
Transferring content in SAC from DEV to PROD requires you only to change the data source of an SAC model from BW4H_DEV to BW4H_PROD. Be aware that this setup affects the uptime of stories for the end users. When switching to development all stories which were productive before cannot be used anymore as they will from now on use the model which connects to the BW/4H development system. First when you switch back the data source to BW4_PROD they are usable again. As often multiple stories are built on a single model this setup is not recommended.
Single-Tenant-Multiple-Proxy: To overcome the limitations we faced with the single proxy setup we can introduce another proxy (could be another physical instance or just another domain on the same proxy).
Now we will have two domains which point to the same SAC tenant but the path redirects to the productive or development BW/4HANA system. In SAC only one connection exists: BW4H which uses the path /BW4H. What have we gained from this? Depending on from which URL SAC is accessed (https://dev… or https://prod…) the relative path points to the BW/4HANA development or productive system.
To develop new stories based on new queries the development URL is used. When the development is finished the BW/4HANA artifacts can be transported to the productive system and without any further changes the stories in SAC can be used from the productive SAC URL to fetch the productive BW/4HANA data. At the same time the story can be published/shared. Simple and elegant 😉
Multiple-Tenant-Multiple-Proxy: The most advanced setup uses multiple SAC tenants and multiple domains. Thereby multiple fully isolated environments are created. It is recommended to use the same namespace in all environments and the same connection name. To transport content the export and import functionality of SAC is used. Connections and models can be transported as well.
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 must be activated on the BW/4HANA system and it is recommended to use the Apache 2.4.x HTTP server as a reverse proxy.
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.
How to Setup BW/4HANA Connection
- Install Apache HTTP Server (installation guide available)
- Configure reverse proxy server
- Activate InA Service in BW
Make sure to also set the Reverse Proxy Host parameter in the admin interface of SAC to the proxy host (Note: There is currently a bug present which prevents from setting this parameter: KBA 2541884). Otherwise your users will receive emails with the public URL and not the reverse proxy URL which is required to access the BW/4HANA data source.
Finally you can create the BW/4HANA connection in your system:
- Go to “Connection”
- Create “Live Data Connection” – SAP BW
- Fill in the details of the system and make sure to enter the correct path
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)
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):
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 Chrome and 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:
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.
I have shown you how to set up the live data connection to SAC to avoid replicating data. You have learned about the different landscape options and that data flows directly from your BW/4HANA system to your browser without touching the cloud storage. Also some predictive features were shown and how to utilize them. Finally the measurement and improvement of performance was explained and some tipps 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.