Skip to main content
Perspective:
Perspective
20 minute read

Hybrid Modelling in SAP BW

Project Background

Client is multinational enterprise information technology company that provides products and services geared toward the data center such as servers, enterprise storage, networking, and enterprise software. Purpose of the project was to move existing Classical BW reports that shows near real time data to real time data reporting using SLT and BW4HANA inbuilt capabilities. This is one of the best approaches to use BW Query and composite provider (BW4HANA objects) as BW shell will add flexibility to do more things. 

With Classical BW systems, Business needs to wait at least half a day for BW reports to get refreshed. However, SLT with BW4HANA system capabilities data get refreshed as soon as data enters Transactional systems and available for reporting. Thereby, supports your business users from Finance, Order Management and Delivery to make their tactical decisions on a day-by-day basis  

Problem Statement
For any SAP development, we follow a 3-tier architecture (presentation layer, application layer, and database layer), where complex formulas or logic are executed in the application layer rather than the database layer. BW developers didn’t have the option to run their report logics directly on the database and were reliant on BW modelling, where data needs to persist (physically store) at many layers before being exposed to BW queries. This caused a lot of performance issues and data redundancy in the past hence we need an alternate BW approach that not only help us reduce physical storage but also ensure better performance

Key Terms 

  1. Info object – Smallest Unit in SAP BW. Info objects are used to store Master data (something which do not change frequently)
  2. HANA Calculation View – Virtual data objects that helps you create information view on data base itself. It helps calculate data on the fly to cut down unload and reload phases to zero when it comes to business-related changes in data staging or extension/reduction in a data model. It also keeps your operation costs for SAP HANA at a minimal level because less data in an SAP HANA database means less expense for licensing. Also, the virtual aspect of this model ensures there is no redundant data lying at multiple places in BW 
  3. Composite Provider / HCPR – Virtual BW data object that helps you union or join more than one info providers (on which you can create reports) to design complex business scenarios. A Composite Provider is an Info Provider, which combines data from several analytic indexes or from other Info Providers (by Join or Union) and makes this data available for reporting and analysis. UNION and JOIN operations are executed in HANA and not on application server, thereby increasing the model efficiency and faster execution time
  4. BW Query – BW4HANA based query to create data models or queries 
  5. SLT – SAP Landscape Transformation is a trigger-based data replication method in HANA systems in real time
  6. ADSO – Advanced Data Store Objects is the primary persistent object used in data modelling 
  7. AFO – SAP Analysis for Office commonly known as AFO is an Excel add-in (reporting tool) that helps you analyze data and helps in decision making
  8. PSA – Persistent Staging Area which stores raw data from ECC / S4
  9. DSO – Data Store Object which stores detailed data
  10. Info Cube – stores Aggregated data
  11. Multiprovider / Infoset – helps in joining multiple DSO / cubes with union and join capabilities

Pre-Requisites

  1. SLT setup between S4 and B4 systems so we have live data as soon as it refreshes in source system
  2. Models such as Calculation View, Composite Provider and BW query formerly known as Bex Query
  3. Analysis for Office tool for reporting purpose
  4. Minimum System requirement: SLT - SAP 7.3 SP 10, HCPR – SAP 7.4 SP 5, BW Query – SAP 7.4 SP 9, BW4HANA Info objects – SAP BW 7.5 SP 0
Architecture 


Classical BW Architecture

  1. S4 / ECC unfiltered data is stored as is in PSA tables (First level of Persistent data)
  2. DSO stores data in detailed form such as regional data from AMS, APAC, Europe etc (Second level of Persistent data)
  3. Cubes stores data in aggregated form (Third level of Persistent data)
  4. Virtual capabilities of Multiprovider / Infoset helps us combine data from Multiple sources / object types such as Planned / Actual (Non-Persistent data)
  5. BW Query consumes data from Virtual provider and help us create query (non-Persistent data).


Hybrid Modelling Architecture

  1. S4 HANA tables are replicated to BW4HANA data base through SLT
  2. Calculation Views are created on BW4HANA data base to leverage in memory concept and faster data retrieval
  3. Calculation based models are then consumed to BW4HANA through Composite provider for further cleansing and transformation
  4. Composite Provider data is then consumed to create BW Query. AFO is used as reporting tool

 

Available Options

In this process Design logic is implemented in S4 / ECC systems which is then replicated in BW system. Once the Data Source is replicated in BW system, Persistent flow is created in BW system (Info objects, DTP, transformation, DSO / Cubes, Multiprovider / Infoset, Query)

Pros: 

  • With BW, you get permanent data storage as opposed to transactional data that could be purged and disappear
  • Users can drill through the data across various time periods and do projections for the future accordingly, which generally isn’t easy to do in a transactional database.

Cons:

  • It has multiple layers of redundant data and transforming data which slow done the data processing work
  • It takes too much time for data to reach the destination of reporting layer from source as it must pass from multiple layers
  • Downtime is required to clear ECC / S4 accumulated data
  • Drop / reload of data based on history data required
  • Reporting can only be done at Reporting level

In Hybrid process we use the HANA database capabilities which is not the case in prior option. Data models are virtually created on HANA data base which are then exposed to Composite provider and then BW query is created on top of it. 

Pros: 

  • No Physical storage of data and hence, no data redundancy
  • No Downtime required
  • With HANA data base we use code push down capabilities and hence logic execution is very fast
  • No monitoring process required to load data between stages and hence lesser resource / user maintenance
  • Reporting can be done at any levels.

Cons:

  • This process will not work in case of History / Snapshot data

Solution Overview

Calculation Views and Composite provider helps us implement Mixed modelling in BW4HANA where a model from Native HANA (Calculation views) is exposed to BW object (Composite provider). 

Calculation views aids in implementing logic directly on data base layer, thereby reducing execution time by eliminating the need to push the data to application layer. It helps calculate data on the fly to cut down unload and reload phases to zero when it comes to business-related changes in data staging or extension/reduction in a data model. Unlike Classical BW systems there is no need to clear delta queues whenever there is a change in logic which facilitates zero downtimes during go live. 

Composite Provider helps us combines data from several analytic indexes (Calculation Views in our Scenario) or from other Info Providers (by Join or Union – Info objects in our Scenario) and makes this data available for reporting and analysis. UNION and JOIN operations are executed in HANA and not on application server. CP also help us implement complex logic such as Key and Text display for field, Attributes of Info object.

Process Steps

  1. In the Client environment, BW4 report should be executed using AFO tool
  2. Input User selections when prompted, this would help us restrict data from getting picked from S4 system
  3. Input entered through User Selections would then traverse in reverse order from Query -> CP -> CV
  4. Data is then picked up based on logic implemented in CV. It is further restricted based on logic implemented in CP and Query
  5. When data is pushed using Conventional approach (CV -> CP-> Query), it takes a lot of time to search relevant data as Linear search algorithm is applied and value help field executes implemented logic each time to pull list of values
  6. When CP fields are associated with Info objects, an integer value is assigned to each master data value in an Info object. Thereby, value search help access is much faster
  7. If the data is loaded for first time or new data is loaded, then SID values are generated for all those newly created data traversing through Info object

Benefits

  1. No Physical storage of data and hence, no data redundancy – We have developed 30 BW reports for which classical system would require at least 100 Persistent objects as compared to 5 Persistent objects in Hybrid modelling. Therefore, a total of 95% memory saving
  2. No Downtime required
  3. With HANA data base we use code push down capabilities and hence logic execution is very fast – With HANA Database, code execution happens directly on DB layer instead of application layer (classical BW) and thereby saving a lot of time of data transfer between DB and Application layers
  4. No monitoring process required to load data between stages and hence lesser resource / user maintenance – With no persistent data load process from S4 to B4, we do not need any resource to monitor these loads and thereby reducing FTE utilization by 50%
  5. Improved performance – With HANA Data base using code push down concept, in memory and column storage ensures faster delivery of data from Data base to Application layer and analysis capabilities that were previously unavailable due to limited hardware performance. This is 3600 times faster than hard disk operations
  6. Real time data as compared to near real time data in classical BW systems
  7. FTE Reduction – We would need to create a minimum of 6 objects (Data source in S4 and B4, PSA, DSO, Cube, MP / Infoset, Query) in classical BW system as compared to 3 objects (CV, CP, Query) in Hybrid modelling for BW report and thereby reducing FTE utilization by 50%

Comparison of Persistent objects in Classical BW vs Hybrid Modelling: We have compared the persistent objects below data based on 30 BW objects implemented so far.

Comparison of Data storage levels in Classical BW vs Hybrid Modelling: We have compared the layers difference below data based on 30 BW objects implemented so far. 


Accomplishments and Lessons Learnt

  1. By implementing this solution, we have reduced the amount of memory utilization by 95%
  2. Reduced User Maintenance – This flow would not only help support team with housekeeping activity but also Business users get data in real time. It helps us reduce FTE utilization by 50% 
  3. Lesser storage and hence lesser redundancy of data – Classical BW would require at least 3 levels of persistent data as compared to Hybrid modelling which is virtual and just need 1 level of data storage at Data base layer. Therefore, a total of 66% reduction in data redundancy
  4. Improved performance in executing complex process and larger volumes of data due to code push down concept of HANA DB capabilities – With HANA Data base using code push down concept, in memory and column storage ensures faster delivery of data from Data base to Application layer and analysis capabilities that were previously unavailable due to limited hardware performance. This is 3600 times faster than hard disk operations
  5. No Downtime required – No Data loading plan, no user lock and hence highest system availability, no waiting time for a planned resales window

Sponsors:

 

Authors:

Recommendations

Enterprise Technology & Performance

Learn how our enterprise technology solutions will help your company achieve maximum impact and value through word-class technology strategies and processes. 
Services

See your world in digital and transform transformation | Deloitte Global

Digital doesn’t sit still, so neither can your business. To thrive in today’s ever-changing digital world, digital transformation is imperative. But there are many ways to do it. The one constant is your role, as CEO, and the need for your direct involvement.
Perspective
7 minute read

Skills-based organizations

The most fundamental building block of work—the job—could be hampering many organizations. Instead, many are now applying skills-based models to meet the demand for evolving business priorities.
Research
5 minute read