Ever since BW on HANA had been introduced, I read many articles discussing the improvement that it can deliver. These articles provided these conceptual experiences that resulted in specific benefits, features and overall improvements. I recently had the experience to execute a true POC that compared a BW on Oracle system with a BW on HANA environment. This was the first time I was able to turn the conceptual into practical and I wanted to share these results with you. Though the system that I was working with was not terabytes, I think I was able to clearly quantify the HANA benefit. Let me know what you think and I hope that you enjoy.

Key Benefits

Company A is currently reviewing the capabilities to implement BW on HANA.  The main reasons to implement this tool are the following:

• Improve end user data accessibility – A few teams are limited with the data sets that they are trying to execute.  The current data model has been effectively implemented but one of the main reasons for the deficiencies is the way the tool is currently used.  End Users are executing queries that are returning over a million plus rows and have complex restricted and calculated key figures.
• Reduce development life cycles – Due to consistent changes as the system matures, we need to be able to reduce the development life cycles
• Reduce data model – The Company A data model has become very complex over the last few months and it continues grow in complexity.
• Future development – As the data model continues to grow, the volume will also continue to expand. Leveraging BW on HANA will allow the database to be scalable moving forward.  Not only does it provide the in-memory capabilities but it will also provide the following:
  • Scalability – As Company A introduces additional functional areas, we will not have an immediate concern for performance implications.
  • Predictive Analytics – In the future, Company A may implement both a fully functional planning solution and predictive capabilities.  BW on HANA provides the extended and improved capabilities with the integration with the Predictive Analytical Library
  • Real Time Operational Reporting –– With the enterprise solution, HANA Live can be introduced to support real time operational reporting
  • BW real time extraction – With BW 7.4, SLT will support real time (trigger based) data extraction.  Please note, not all DataSources provide these capabilities but this will continue to grow until the main capabilities are supported.

The following methods were measured to determine the immediate improvements between BW on an Oracle Database and BW on a HANA Database:

• DSO Activation times
• InfoCube Load Times
• InfoCube Size
• Query run times

In order to successfully achieve the above measurements, the following systems, transactions and tools were used:

• Data Model Benchmarks
  • BWP vs. BWX
    • Please note that these systems were a snapshot at a specific time so the data sets are not the exactly the same
  • Transaction RSA1 – This transaction is used for all data modeling executions.  These benchmarks use SAP Best Practices in order to create a Pulse Check to compare the systems
• Query Runtime
  • Business Explorer Analyzer (BEx) – This is the tool that leverages Microsoft Excel and is embedded
  • RSRT – This is the embedded BW tool that leverages the application server and mimics the execution within the Web

Data Model Benchmarks

The following prerequisites were executed prior to all benchmarks:

• Optimize all BW on HANA InfoCubes
• Optimize all BW on HANA DSO’s (new DSOs that were copied from original source DSO)
• Creation of improved Data Model

BW on Oracle (BWP)

The following measurements are 2 of the larger DataStore Objects within BWP.  We are documenting how long it takes to activate a DSO.

The following measurements are the 4 main InfoCubes that are leveraged within the Sales and Distribution functional area.  These are the main sources for reporting for both the Finance and Demand Planning teams. None of these InfoCubes have aggregates but 2 of them are compressed within a 7 day time period (every 7 days).

BW on HANA (BWX)

Since the BW on HANA System is not connected to the PRD system, we are unable to e51xtract from this system directly into BWX.  In addition, we delete the Persistent Staging area on a weekly basis.  In order to compare the above data loads, we have replicated the DSOs and InfoCubes, which will be sourced from the above Data Targets. This will not provide an exact comparison but similar since the structures are the same.  As stated below, the number of records will be less in our BW on HANA System.

BWX DSO’s

BWX InfoCubes

Comparisons

In the below DSO Activation comparisons, you can see they are all greater than or equal to 95%.

Please note***we are loading DSO1 into DSO2, while DSO1 is loaded from 2 different sources so there is more records that need to be aggregated together.

In the below InfoCube comparisons, you can see how changes to the data model will be reduced with maintenance time with BW on HANA.  The load times have decreased by >50% in all cases and in some instances at least 90% better

Query Runtime

There were 2 approaches taken in order to execute queries:

• Executing the reports against the current architecture
• Creating a more efficient architecture and recreating a similar query to see performance improvements

With BW on HANA, there are areas that improvement will be realized but other areas that it can’t be realized. All improvements will be recognized within the database.  After the data is moved from the backend database to the frontend database, the

Within the query runtime, we want to measure some of the following metrics:

OLAP Related events

Front-End related events in detail

The first query that was executed was Query XXXX, which is a report which supports the Team X. 

The following were used:

• Transaction RSRT
• HTML
• No Cache
• Track Statistics

The navigation that occurred within this report is the following:

• Drill Across on Calendar Year/Month
• Drill Down on Material

Query XXXXX

The following were used:

• Transaction RSRT
• HTML
• No Cache
• Track Statistics
• Selection Criteria – 0CALMONTH Range – 01/2014 – 12/2014
• Created a Y query in both systems using the original Query XXXXX as the baseline
  • Characteristics included in the initial layout are the following:
    • Sold-To – Sales District
    • Sold-To – Customer Group
    • Sold-To – Industry
    • Sold-To Party
    • Sold-To – Broker
    • Material – Brand
    • Material
    • Calendar Month (Columns)
    • Key Figures Included are the following:
      • Restricted Key Figure 1
      • Restricted Key Figure 2
      • Restricted Key Figure 3
      • Calculated Key Figure 1
      • Restricted Key Figure 4
      • Restricted Key Figure 5
      • Restricted Key Figure 6
      • Restricted Key Figure 7
      • Calculated Key Figure 2
      • Calculated Key Figure 3

     

In the below 2 tables, you can see the aggregated access to the main tables in BWP and BWX.  Within BWP, you will see both F and E Tables since there are compressed and uncompressed tables that need to be read. Within BWX, since there are no dimension or fact tables, this is skipped and the read time is drastically lower. This provides a huge improvement within the database access and the amount of time that it takes to start rendering the data.  This provides further explanation on why it took 11 minutes in BWP vs. 4 minutes in BWX.

Scenario 1:

Query: XXXXXX

Variable Values: 01/2014 – 03/2014

Key Figures to be included are the following:

• Restricted Key Figure 1
• Restricted Key Figure 2
• Restricted Key Figure 3
• Restricted Key Figure 4
• Calculated Key Figure 1

Execution Steps:

• Execute Report in RSRT
• Drill Down on Free Characteristic 1
• Drill Across with Key Figures
• Filter Key Figures not required
• Sort Restricted Key Figure 4
• Filter Hierarchy on NODEX
• Drill down on Item
• Swap Item with Characteristic 2
• Delete Filter on NODEX and switch to NODEY
• Start back with NODEX and Free Characteristic 1
• Select Free Characteristic 2
• Filter down by Free Characteristic 3 on Free Characteristic 2
• Complete

BWP – 6:14 minutes (368 seconds)

BWX – 2:40 minutes (144 seconds)

1. 60.8% improvement

In the above screen shot, you can see the substantial improvement within the Data Manager.  As SAP has stated that if the Data Manager time duration is longer than all of the other events, SAP HANA can greatly improve the overall performance

Execution Steps:

The time that is listed is the overall duration that it took to get to that step.  It does not mean the amount of time it took that step to execute.  The final execution is the overall duration for the end to end processing.

Improved Architecture:

One of the benefits of BW on HANA is to reduce the overall data model, remove duplication and simplify the loading process.  In order to do this moving forward, Company A will be required to review the overall data model that has been currently created for the implemented modules.  In order to determine if there is a significant performance improvement, we created a quick remodel of a few of the objects (testing purposes only and will not include the complete data set).

Our main objective is to remove the InfoCube layer and leverage the DSO layer for all reporting. A MultiProvider will still be the reporting layer in order to create the necessary unions.

A few notes on the below stats:

• The number of objects in the MultiProvider were less
• Only the data that is used in the report were included in the data targets
• SPO was used against the Forecasting data by version which may not be feasible within production.  SPO capabilities may still be used in production but by year

As you can see the largest improvements occur in the beginning when the database is accessed. After this, the improvements start to level off because the application server is working through the most of the tasks while the database is accessed less.