Take Your Data Warehouse to the Next Level
by Ingo Hilgefort, Global Product Evangelist, SAP Data Warehouse Cloud
To make good use of all the data streaming in from internal and external sources, companies must be able to organize and store that data in a way that enables optimal analysis, so that they can make the right business decisions. This is where data warehouses come in, which serve as a central repository of data for reporting and analytics across the enterprise.
To meet their data warehousing needs, many SAP customers have been longtime users of SAP’s well-known on-premise data warehouse solution, SAP Business Warehouse (SAP BW). However, with mainstream maintenance set to end for SAP BW 7.5 in 2027 (and at the end of 2020 for earlier versions), a number of SAP BW customers have been moving to SAP BW/4HANA, which is where SAP has been focusing its investment in new data warehouse features in recent years. In addition to being optimized for SAP HANA and providing the latest features and functionality, SAP BW/4HANA integrates with SAP Analytics Cloud, which enables access to sophisticated dashboards and visualizations, data enrichment capabilities, and self-service functionality.
This article outlines key best practices that can help SAP customers get the most from the integration between SAP Analytics Cloud and SAP BW/4HANA. It first looks at choosing the right type of connection for the integration, and why keeping SAP BW/4HANA up to date is critical. It then outlines recommendations for accessing and reusing the different asset types from SAP BW/4HANA in combination with SAP Analytics Cloud.
Choosing the Right Connection
SAP Analytics Cloud offers two options for the connection with SAP BW/4HANA: a live connection to SAP BW/4HANA or an import connection, where the information from SAP BW/4HANA is imported into SAP Analytics Cloud (in this case, the information is often referred to as “acquired data”). So, how do these options compare with one another?
When using a live connection to SAP BW/4HANA:
- A live model is created in SAP Analytics Cloud, where the SAP BW/4HANA data can be accessed directly — there is no replication of data, and the data does not leave the corporate network.
- The latest information will always be available for reporting, as the data is live and not based on a scheduled refresh.
- You can drill down from aggregated information into the lowest level of information available.
- There is no need to worry about any data-level security or analysis authorizations, because it is a live connection to the SAP BW/4HANA system that is simply reusing the already-existing security configuration.
- Users can leverage their existing SAP BW queries — including SAP BW queries with two structures — and they can access information that has not been available through standard SQL or MDX interfaces, such as time-dependent hierarchies.
- Queries can be executed in parallel (more on this later in the article when performance best practices are discussed).
When using acquired data via an import connection to SAP BW/4HANA:
- Access to all SAP Analytics Cloud features are provided via a local model
- Hierarchies will have to be re-created in the local model in SAP Analytics Cloud.
- Row-level data security will have to be re-created in the local model in SAP Analytics Cloud.
Comparing these two options clearly shows that the live connection provides better integration with SAP BW/4HANA and the most reusability of existing assets. However, the import connection option does have its place. For example, it might be useful for a scenario where users are looking to leverage a capability from SAP Analytics Cloud, such as the Smart Discovery feature, that isn’t yet supported by the live connection option.
Keeping SAP BW/4HANA Up to Date
When choosing the path of live connectivity between SAP Analytics Cloud and SAP BW/4HANA, it is important to understand that even though the integration has already existed for quite some time, regular updates and enhancements are still being made, and it is critical to check for regular updates on the SAP BW/4HANA side. In particular, keep in mind that:
- SAP is highly recommending that customers interested in the integration of SAP Analytics Cloud and SAP BW upgrade their system to SAP BW 7.5x or SAP BW/4HANA as soon as possible.
- SAP is recommending SAP BW 7.50 service pack 16 or SAP BW/4HANA 2.0 service pack 04.
- Customers should check the following two SAP Notes for further updates: 2715030 and 2541557.
Data Analyzer as a Self-Service Environment
For customers looking to leverage the live connectivity with SAP BW/4HANA, SAP developed the Data Analyzer product as part of SAP Analytics Cloud, which allows users to quickly open up an SAP BW query without needing to set up a model in SAP Analytics Cloud, giving users a simple and easy-to-use self-service environment for their SAP BW/4HANA data (Figure 1).
Figure 1 The Data Analyzer in SAP Analytics Cloud provides direct, self-service access to queries in SAP BW/4HANA
SAP BW/4HANA Query-Related Topics
A lot of customers think that because they are now looking to integrate with SAP Analytics Cloud, they have to create new SAP BW queries or make major adjustments to their existing SAP BW queries, but quite the opposite is true. The integration of SAP Analytics Cloud and SAP BW/4HANA was designed to reuse existing SAP BW queries without having to make any changes.
With support for existing SAP BW/4HANA queries built into SAP Analytics Cloud:
- Customers can continue to leverage their existing SAP BW queries, because a redesign is not necessary for the integration with SAP Analytics Cloud.
- Continue to follow existing best practices for SAP BW query design and keep using elements such as restricted key figures, calculated key figures, variables, and structures.
- Customers can leverage SAP BW queries for more complex calculations as they define and store those centrally as part of the SAP BW system, and then reuse them across all their SAP Analytics Cloud models.
- Leverage the analysis authorizations in SAP BW for data-level security and use a single sign-on (SSO) approach in combination with SAP Analytics Cloud so that there is no need to re-create data-level security.
Dimensions and Measures in SAP Analytics Cloud
When creating a live model in SAP Analytics Cloud to support the live connection to SAP BW/4HANA, there is a valuable opportunity that is often missed — SAP Analytics Cloud provides the option to rename SAP BW/4HANA dimensions and measures, and group dimensions together, for use in SAP Analytics Cloud visualizations.
For example, let’s say you have three dimensions in SAP BW/4HANA: Product Category, Product Group, and Product. With the integration between SAP BW/4HANA and SAP Analytics Cloud, you can combine these three dimensions into a group called Product in SAP Analytics Cloud, and use them as a tree control to help organize large queries into more consumable structures in SAP Analytics Cloud. The ability to rename dimensions and measures allows you to provide business users with more friendly terms.
Ensuring a High Level of Performance
When it comes to performance, the integration between SAP BW/4HANA and SAP Analytics Cloud has improved significantly.
The following is a set of recommended configurations that will help ensure a high level of performance for SAP BW/4HANA and SAP Cloud Analytics integrations:
- In SAP BW/4HANA, there is an option to set the read mode to “Query to read when you navigate” (transaction code RSRT).
- In SAP Analytics Cloud, the model opt ion “Optimize Story Builder Performance” pauses the automatic update on changes made in a chart or a table. Otherwise, modifications to a table or chart during the story design will send a request to the SAP BW/4HANA server for an immediate update.
- The SAP Analytics Cloud administrator has the option to enable parallel processing of SAP BW/4HANA queries (in the system configuration) so that SAP Analytics Cloud stories containing multiple queries will execute those in parallel.
- The query merging option for SAP Analytics Cloud stories (in the story properties settings) combines requests from SAP Analytics Cloud to SAP BW/4HANA to reduce the overall number of requests. For example, instead of requesting Revenue by Country for a table and Profit Margin by Country for a chart, query merging would combine these into a single request from SAP Analytics Cloud to SAP BW/4HANA to ask for Revenue and Profit Margin by Country.
- Customers should look at the option to enable HTTP 2.0 as part of their SAP BW/4HANA environment to allow for up to 12 parallel sessions. Details can be found here.
The ability to connect SAP Analytics Cloud directly to their existing SAP BW/4HANA investments provides SAP customers with a range of options. It allows organizations to reuse their existing work, build on top of existing best practices, and enrich existing SAP BW/4HANA assets with additional data and new capabilities from SAP Analytics Cloud, such as search to insight and linked analysis.
With the best practices outlined in this article, you will be well positioned to get the most out of this integration for your organization and take your data warehousing capabilities to the next level.