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.