Technical Articles
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
- 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.
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:
Landscape Options
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.
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 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.
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.
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
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 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:
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 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.
Thank you, Simon! Great article with a lot of detailed and helpful information.
Thank you, this is really helpful.
In the section about predictive analytics you've written that "The requested data is sent from your browser to SAP where the data processing occurs." What data do you mean by that? Metadata or the actual data like customer purchase history?
In other words: Do the actual algorithms run in the cloud or on my (onpremise) BW on HANA?
Hi Sebastian,
This is a good question and let me clarify that more in detail:
If you want to leverage your on premise environment you can still build your predictive models there and consume them from SAC.
If you have any more questions feel free to ask.
Thanks for your quick reply, that's what I've thought.
"If you want to leverage your on premise environment you can still build your predictive models there and consume them from SAC."
Please correct me, if i got this wrong: In the above case I can't use the SAC predictive component to build the models. I would do this, for example with HANA libraries like APL or PAL, in my on premise environment. Only the presentation and consumption of the results would take place in SAC.
This is correct. You would leverage APL/PAL for that and only consume the models in SAC for presentation purposes. However, you could use input parameters to control the model on the fly from SAC.
Thanks again. One last question (at least for now): Is it true that SAP offers a public and a private option/version of SAC? If so, what are the differences?
Yes that is true. With SAC private edition you will get a dedicated HANA system per tenant instead of a multi-tenant system. You can specify the sizing on your own whereas the public edition is sized automatically to fit the workload. Besides that both options have the same update schedule, security standard and SLAs. For the public edition you can also purchase additional storage if you require more.
To check the specifics please get in contact with your responsible sales contact. For the private edition a minimum of users might be required.
What about the planing component in SAC? Will (real) data also be transferred to the cloud in this case?
Hello Simon!
"Currently the live data connection from SAC to SAP BW is supported for the following BW versions:
Can you please explain this point?
Regarding the last point: do you mean that we can connect SAC to any version of BW in live mode?
Why do you mention the specific version and SP in point 1-3?
Thanks in advance
Evgeniy
Hi,
This means that SAC supports also BW on HANA and BW on any DB.
The mentioned version restrictions still apply.
Hi, thank you!
Does that mean that for example SAP BW 7.3 will work on all SPs?
It sounds a bit weird since the customer needs to have SAP BW 7.4 SP17+, but if the customer has SAP BW 7.3 it will work in all cases.
What is the reason to upgrade for them then?
No, 7.3 is not mentioned. You need the specified versions. The "on HANA / on AnyDB" refers to the deployment mode of BW.
Thank you, now it's clear.
Hi,
Do you know when the Live connection will be ready for Successfactors?
Many thanks!
Hi Jose,
For future features please refer to the official SAP roadmaps.
Thanks!
Hi Simon,
great blog! When will also planning using live data connection be possible?
Thank you!
Reni
Hi Reni,
Please follow the official roadmap to be updated as soon as possible: https://www.sap.com/products/roadmaps.html?search=Analytics%20Cloud&sort=title_asc
Best regards,
Simon
Hi Simon,
Thank you for your blog.
is it support the Data of SFSF Time management?
I couldn't find object of working time, employee time, timeoff.
Peace be with you.
Leo.