Flexible Data Modelling with SAP S/4HANA and SAP BW/4HANA
Author – Stefan Beekman, SAP Analytics Solution Architect, NTT DATA Business Solutions UK
Introduction
I am an SAP Analytics Solution Architect working in the Analytics Consulting team of NTT DATA Business Solutions UK. In this blog, I want to share with you some first-hand practical experiences and examples of the flexible modelling options that BW/4HANA and S/4HANA have to offer.
This blog doesn’t go into the depths of how to configure/build HANA or BW/4HANA models, for which there are excellent blogs available out there. Instead, it focuses on how I implemented virtual data models (using the Smart Data Access capability) to deliver value very quickly to the customer on a project. It shows how data virtualisation can serve two purposes:
– Support an agile delivery approach.
– Provide real-time analytics.
I start with explaining the customer scenario and requirements. After that I will take you through the iterative approach that was taken in order to deliver this solution and what modelling options were used. Finally I end with some conclusions and provide references for additional information.
Hopefully this blog will spark some new ideas on how to do data modelling and approach a reporting project.
Scenario
The Customer Service department of my customer needed real-time insights from different stages of the sales and delivery process so they were able to better answer customer’s questions based on the most recent information from their S/4HANA system. This included information such as open customer orders, yet to be shipped orders and billing status for those. By having access to the latest information they wanted to improve their service levels.
As with many BI and analytics requirements, the requirements were blurred at the start. Rather than going through detailed requirements gathering workshops before starting the solution build, an agile approach was adopted. We wanted to deliver a first version of the report as quickly as possible and take an iterative approach to extend it further.
The main architectural design considerations were as follows:
1. Build the solution entirely in BW4/HANA using BW data modelling techniques.
2. Build solely in S/4HANA using Core Data Services (CDS) views.
3. Or a combination of the two.
Because of the new technologies being introduced at this customer, we were asked to explore and demonstrate the modelling options in both BW/4HANA and S/4HANA.
Additionally, the customer had the requirement to make the report available in both the Fiori Launchpad and in BusinessObjects Analysis for Office. This was because different parts of the organisation were used to different reporting tools due to historical reasons.
Solution Approach
Below I will describe the steps followed in each iteration and what techniques were used.
First Iteration
As BW/4HANA was our first choice as a data platform to deliver complex reporting, we decided to use this as the basis for the first iteration. Together with the key business user, first, we identified the main database tables and high-level requirements. We then exposed these tables in BW/4HANA using Open ODS views. This can be done very quickly using a Smart Data Access (SDA) connection from BW/4HANA to S/4HANA. Schematically this looks like this:
SDA is a technology, which enables remote data access as if they were local tables in an SAP HANA database without data replication. It is based on local virtual tables that map to an existing object at the remote data source site. In the model above, it enables reporting on BW/4HANA data models, while the data stays in S/4HANA. This means that the data is not physically transferred to BW/4HANA. Note that even though the examples here are for a Data Warehouse in BW/4HANA, this could equally be applied to “Native” Hana Data Warehouse use cases as well.
The process of exposing a table with an Open ODS View (OOV) using an SDA connection is a matter of a few clicks and BW/4HANA will generate the OOV based on the table definition in ECC or S/4HANA.
By using this technique and joining all the tables using a Composite Provider, I quickly had my first data model available for iterative prototyping with access to more than 1300 (!!!) fields. Depending on the complexity of the joins in BW/4HANA, this process is a matter of hours rather than days.
Under the bonnet, BW/4HANA uses a local virtual table (on the BW database) that points to a persistent database table in the source system, in this case in S/4HANA. There are two ways to create these virtual tables in BW/4HANA:
1. Manually in HANA Studio using the HANA data modelling perspective. Using this technique, the virtual table is manually created on the HANA database in BW/4HANA.
2. Alternatively, you let BW/4HANA automatically generate the virtual table, when it generates the Open ODS View object in the BW application.
We used option two because it is less effort and all modelling is conveniently done in one place, in the BW application.
Please note, that for this option to work, the SAP BW <SID>-user needs to have the required HANA privileges, which can be granted using the following command:
Second Iteration
After a couple of iterations of adding additional tables, changing the joins and adjusting logic in the BI query, we agreed on the final reporting requirements. With the introduction of Core Data Services (CDS) views, this is now the preferred way to build data models in S/4HANA and surface any data. By using CDS data models are defined and consumed on database server rather than on application server, which has obvious performance advantages.
Therefore, we decided to build the final data model using CDS views in S/4HANA and then consume this model in both SAP BO and Fiori to cater to two different user groups.
Schematically this looks like this:
Note: BW consultants might notice the various levels of CDS views, similar to the modelling in BW/4HANA where we separate the functionality in different data warehouse layers!
This way of modelling everything in S/4HANA was possible in this case, because the requirement of the customer only required reporting on the most recent data in S/4HANA. No historical data was required.
Future Iterations
The requirements for this specific report could be met by doing all data modelling directly in S/4HANA. However, the customer could already foresee more complex requirements where virtual data and persisted data models need to be combined in BW/4HANA. So the next thing that we want to pursue is to combine virtual and persisted models. This requirement pivots the solution back to BW/4HANA, as not all data can be modelled virtually.
For example if:
– Historical data is only available in the data warehouse and not in the source system anymore.
– Periodic snapshots of the data need to be stored in the data warehouse.
– The source system does not support virtual data access.
Schematically, this would look like this:
Conclusion and Findings
When comparing these two modelling options (BW/4HANA vs. S/4HANA), we can conclude the following.
Option 1: Modelling in BW/4HANA Using Composite Providers
Pros:
– Access to Info Objects, however, note that navigational attributes become display attributes when assigning a field to an Info Object. You cannot use a navigational attribute for navigation unless you include them in the CDS view.
– Access to the rich functionality of BW/4HANA query designer such as BW (exit) variables, unit and currency conversion, etc.
– Use the graphical interface of BW/4HANA instead of the script interface.
– Very quick and agile: views get generated in BW/4HANA based on source definition.
Cons:
– Only access to technical field names, not the descriptions. This doesn’t look good in the report and would require additional maintenance of field labels in BW/4HANA or mapping to Info Objects.
– Depending on what is supported by SDA, some functionality might not be pushed down to the HANA database. This requires performance testing.
Option 2: Modelling in S/4HANA Using CDS Views
Pros:
– Use the full power of the HANA database for calculations.
– Access to lots of annotations for rich functionality (see links below for more information).
– Rich syntax help and type-ahead functionality in the HANA IDE.
– Reusability: a Consumption CDS view can easily be converted to Data extraction CDS view by adjusting the annotation in case the requirements change and data needs to be extracted to BW/4HANA. Furthermore, one CDS view can both be consumed in BW/4HANA and Fiori.
Cons:
– Scripted interface. In complex scenarios, it can become hard to keep the overview and will require lots of coding.
– BW/4HANA cannot consume CDS views with parameters as data provider.
Performance
It is worth mentioning that in both scenarios, the models can be tested, analysed and debugged using the transaction RSRT2. In BW/4HANA, you can use the BI query, and in S/4HANA the CDS view can be used as long as the correct Analytics annotations are used.
Interestingly enough, the performance of both models was very similar. I tested both the BW/4HANA query and the CDS view using RSRT2 and checked the statistics. To run a query against about 4 million records took about 1.6 sec in both scenarios.
In S/4HANA, reading the ~4 million records (aggregated to ~400 records) took about 1.6 seconds:
When running the BW/4HANA query on top of SDA, the database read time was similar. In fact, for this specific execution (exact times vary slightly from execution to execution), it was even a bit faster!
Note that in this case, we cannot see how many records were selected in the HANA database, we can only see how many records (~100) were passed from S/4HANA to BW/4HANA through the SDA connection:
So, an important conclusion from this is that SDA does not add a noticeable performance penalty!
I would like to conclude with mentioning that the customer received this agile approach of delivering the analytical solution very positively, and more scenarios will be implemented using a similar approach.
Final remarks and further reading
Some final points to consider:
– Please always check whether your SAP license supports this kind of modelling.
– When using Hana Data Base (HDB) 1.0, there is a significant disadvantage because every time a change is made to the source view or table, the remote table in BW/4HANA will need to be recreated.
– When using HDB2.0, any change to the source view or table is automatically synchronised with the remote table in BW/4HANA. This will hugely reduce development and maintenance effort.
For more details on configuring SDA and which databases are supported, please refer to the SDA section of the HANA Administration Guide or consult these two SAP Notes:
– 1868209 – SAP HANA Smart Data Access 1.0 Master Release Note
– 2352696 – SAP HANA Smart Data Access 2.0 Master Release Note
Finally, I would like to share the following links that contain some useful information:
– How to use ABAP CDS for Data Provisioning in BW
– ABAP Core Data Services – Best Practice Guide
– CDS views: HowTo use in BW contexts
– Official SAP Help on CDS Annotations
If you would like to know more about how the techniques described in this blog can help your organisation to get more value out of BW/4HANA in less time, please get in touch!
Author: Stefan Beekman