I recently done a review of several client sites that have done a technical upgrade to a HANA DB. This article will cover how to create a BI statistics query for report performance and calculate the report performance benefits of a HANA DB upgrade.
I’ve done a business intelligence survey of 20 clients sites and 45% have already done a DB upgrade to HANA and 80% of these will complete the upgrade by 2020.
The first step is to create a custom query on the Front-End and OLAP Statistics (Highly Aggregated) (0TCT_MCA1) multiprovider. Please note that this only holds statistics on BI queries built in query designer.
The following are the steps to create this report:
- Create a new query in the query designer off multiprovider: 0TCT_MCA1
- Add the following Global Filters
3. Add the following into Rows Section (Display as Key & Always suppress Zeros on results row).
Put row suppression on
4. Add the following into Columns section:
The following is the variable selection screen. If newly upgraded to HANA DB (say in the last 9 months), I suggest that you review data for the past 52 weeks (yearly snapshot).
If the HANA DB upgrade was older than 9 months, I suggest that you execute the report twice. Get a 6-month snapshot pre-HANA DB upgrade and a 6-month snapshot post HANA DB upgrade.
The following is the report output. The report will be split by Initial output (Int/Nav Flag = #) and navigation (Int/Nav Flag = X).
The following is the average performance improvement over all clients that participated in the review:
- 62% improvement on Initial output (split below)
- 85% improvement in data manager on initial output.
- 8% improvement in OLAP on initial output
- 65% improvement on navigation output (Split below)
- 88% improvement in data manager on navigation output.
- 3% improvement in OLAP on navigation output.
Please note that the figures could be potentially impacted by having lots of cache hits.
The following is the calculation that I followed on the statistics data provided by the above query. Obviously, I can’t provide specific client calculations but we can apply the above performance improvement percentages based off the following scenario
- 1 million executions on initial output for 52 weeks.
- 1 million executions on navigation for 52 weeks.
- Average execution time of 30 seconds on initial output pre-HANA upgrade.
- 21 Seconds in Data Manager
- 9 seconds in OLAP
- Average execution time of 15 seconds on navigation pre-HANA upgrade.
- 11 Seconds in Data Manager
- 4 seconds in OLAP
The following is the calculations based off this scenario:
Based off this scenario, 7,875 hours will be saved on users waiting for reports to either return data for initial or navigation outputs.
The range of savings for the clients that have participated has been from 4k to 11k hours per year. The saving really depends on the number of executions per year and the average execution time prior to HANA DB upgrade.
If you’ve upgraded your HANA DB, the above exercise provides a high-level overview of the performance benefits on query execution.
If you’re thinking of upgrading to HANA DB and you must justify the benefit, you can follow the above activity and use the average benchmarks of 62% and 65% on the initial and navigation respectively to do the performance calculation.
Okumaya devam et...