SAP Data Warehouse Cloud – a Chance to extend SAP BW/4 HANA
The Data Warehouse Cloud (DWC) is the latest Data Warehouse Product from SAP. As a Software as a Service Solution, it is based on the new HANA Cloud. The DWC should also be able to be a complete Data Warehouse solution in addition to actually working use cases such as self-service Data Preparation.
As a data warehouse solution, DWC must be able to integrate as many sources as possible.
After I’ve explained the connection to BW / 4HANA in one of my last posts, we would like to use this blog entry to show how a hybrid data model based on BW / 4HANA and DWC can be set up. I use a very simple data model which primarily should give an impression of the look and feel.
SAP is investing a lot in integrating both solutions (BW + DWC). The transfer of BW semantics from queries should also work via the “Model Transfer Connection”, which will be available soon. This means that semantics defined in SAP BW can be reused. The following recorded SAP WebSession gives a first look.
In the very simple scenario, there are two tables with transaction data, which contain sales and costs per retail area (in this case simply city).
The retail team would like to have a clear overview of how much profit is generated in a certain city per time period.
SAP BW/4 Data Model
In SAP BW / 4 HANA there is the following simple data model for the transaction data:
Integrative DWC & BW/4 HANA Scenario
In the fictitious very simple case, we imagine that SAP BW does not contain any master data for the cities. However, a department has a CSV file with the translation of IDs into descriptions of the cities. In addition, the “Profit” column should be calculated in the DWC based on the BW / 4 and CSV data. We have already created a table for the CSV file in the DWC.
1. Join of the Composite Providers
In order to integrate the data from the BW / 4 into the DWC, the connection to the BW / 4 must first be set up. If the connection is established, a data model can be started in the Data Builder regardless of the source of the DWC. In our case we create a graphical view:
Navigate to remote sources → select connection BW / 4 or HANA
Add both CompositeProviders to the Graphical View.
Hide the “InfoProv” and “Recordmode” columns for CP_EXP and CP_REV.
Check the join condition of the 2 CompositeProviders.
Hide the columns “City” and “Date” you have them 2-times.
Et voila: I’ve have connected two SAP BW / 4 HANA composite providers and joined them. Reporting is now possible.
2. Join the BW/4 HANA Data with the mapping table in DWC
We have stored the names of the cities in a DWC table which is filled from a CSV file (see here for details). We simply add this table “City_Names” to our graphical view:
Hide the last “City” column that only contains the abbreviations for the cities.
3. Creation of calculated Key Figure on top of the consolidated data model
The model still misses the key figure profit. For the implementation we create a new “Calculated Column”:
Attention: In order to be able to use the graphical view in stories, it must be deployed as an analytical data set.
We also hide the columns “Cost_Amount” and “Revenue”, since only the profit should be taken into account in the later evaluation.
Based on our very simple mixed data model, we can quickly visualize the data with the integrated story builder of the SAP Analytics Cloud (SAC):
The very simple example immediately shows the strength of the Data Warehouse Cloud. The modeling of data models is very quick and easy. The Data Warehouse Cloud is still very fresh on the market, but a use case can already be covered very well:
A company uses its SAP BW / 4HANA system as a central enterprise data warehouse that places great emphasis on governance and quality. For departments, however, a possibility is being sought to map flexible extensions and views based on this stable database. The Data Warehouse Cloud is very well suited for this. In the above example it was just a simple file enhancement. It is also conceivable to combine BW / 4 data with information from other systems.
The example also showed that no real metadata has been extracted from the BW / 4HANA system. It is more of a consumer view, in which the data can be loaded flat from the BW without any further knowledge of the metadata structures. With the upcoming Model Transfer Connection (see more details), the Data Warehouse Cloud will also have the ability to understand and transfer metadata already defined in SAP BW / 4HANA. In this way, calculated key figures from queries are understood, etc. This will be a big step forward.
The capabilities for transforming data will also be significantly expanded over the next few months with the data flow. This enables persistent data flows to be established. In addition to SQLScript, logic can also be implemented with Python in the future. (More details). I’am realy courious about that upcomming feature.
Both examples show that the Data Warehouse Cloud roadmap is full of new Features that will be published later this year.
This article has been published in german under this link.
Hello Christopher Kampmann
I like that article in addition to - https://blogs.sap.com/2019/11/21/data-intelligence-hub-connecting-the-dots-.../
Best Regards Roland