An Improvement of 72x with HANA and Design Studio
We did a great PoC on the last month and I would like to share with you the results and what we did.
The POC history
Customer have on their environment SAP HANA installed with one BW productive system (BW on HANA) since 2013. Since the initial implementation, they were able to see many improvements on performance of many BW queries, DSOs and cubes. But, one of their most important reports, accessible per more than 1,000 users was not with a good performance with a performance of around 6 minutes to refresh the data
This generate a large negative noise 😡 against all the company like:
“SAP HANA doesn’t work”
“After the migration of BW to SAP HANA, the performance of my report didn’t change significantly”
“SAP HANA is not what I expected”
With that in mind, we did a meeting with the customer and schedule a small assessment. During our assessment we find some issues related to modeling best practices that were affecting the performance:
- The union of 4 cubes resulting in a data set of almost 1 million rows;
- Combination of ABAP Code on this SAP BW Query to convert some currencies and also to restrict data based on one Table (They are not using the Standard Analysis Authorizations);
- Combination of two hierarchies structures on the same query/report;
- Usage of Web Intelligence to one demand that could be fit with Design Studio;
- Execution of many queries in Web Intelligence;
- Many calculations done on report level
Technically speaking, this is what we find on their BW system when we start our assessment:
The POC Challenge:
With that in mind and after show these issues to customer, they challenge us: “OK SAP. We want this report running in 5 seconds or less.”
What and how we did:
Before thinking about how technically we could improve the performance of this report, we started talking and engaging the customer IT area to break some paradigms like: “Why you don’t do this directly on HANA?” or “Did you try to use a different tool?”. The business area also got involved during all process, to test if the report is ok in terms of usability and also if the values are ok.
These teams was a critical success factor, as they helped a lot validating some features of security, transformations, filters etc and also validating the data when we developed the HANA Views and also the dashboard.
We decided then to develop this same logic structure directly on SAP HANA, accessing the Calculation Views of each cube automatically created by SAP BW and modeling there all transformations.
As a front-end tool, we decided that the best would be Design Studio, as don’t generate any MicroCube and it’s also optimized to read HANA Views and the usage of Parent-Child Hierarchies.
It was the customer requirements for this dashboard:
- Good Performance (5 seconds)
- Hierarchies (parent-child)
- Custom Row-Based Security (based on one table)
- Currency and Unit of Measure Conversion
- Drill down to more granularity (never possible with WebIntelligence)
After some weeks of hard work, we showed for this customer all of these functionalities based on HANA Views and the result was impressive:
Reduction of time from 6 minutes to less than 5 seconds (4,8 seconds on the best execution).
An improvement of 72X!!
To have a better understanding of how much this improve the customer operation and how this number is representative, let’s imagine that you could improve the time spend of a trip from São Paulo to New York that takes around 10 hours in 72 times. You would arrive in New York in only 8,5 minutes.
After this job, the customer was satisfied and convinced that SAP HANA is really fast and really saw that with SAP HANA they can have an improvement on their process by increasing the BI adoption against all the company
The customer also saw that the problem was not on HANA, but in the way they did their BW query and also on the Front-End tool that they are using.
good story. Sadly the images loading from SAP Jam are not working (Access denied). 🙁
Thank you Michael. It's now solved.
a quite interesting blog and great results! 😎
Of course I got a couple of question if I may.
Quote "Design Studio, as don’t generate any MicroCube"
I believe that Webi's MicroCube technology is one of the most misunderstood features of the product. It enables Webi to generate reports at different levels of granularity on the fly without the need for communicating with the backend, be that SAP HANA DB or anyDB, thereby reducing Network traffic. But now to my many questions:
A) Why was a Drill-Down with WebIntelligence not possible?
B) How many local report fromulas were used in the poor performing Webi document I wonder?
C) I would also be interested in learning more about why Webi's MicroCube technology had any negative performance impact in this customer scenario, please.
D) Are there more details on the steps you all took to improve the performance?
Some things to avoid are obvious such as:
E) How did you all deal with the multiple hierarchies? Was that just a poor report design to pull in multiple hierarchies or?
F) Was there a re-design of the BeX queries involved as well?
G) Is there a more detailed breakdown of the runtimes available (HANA, OLAP/BeX, Webi, etc.) before and after the performance tuning?