Ques:
There is a report that consists of multiple calculated & restricted key figures.
They definitely slow down the query performances.
How are we going to optimize the calculations to improve the response time of the query?
Ans:
If the report consists of multiple calculated and restricted key figures, we can address various aspects to reduce the complexity and improve the response time. Here are a few strategies that we can work on:
– Moving all the calculations to the HANA layer (data layer) instead of the BW layer. We can also use calculated figures or formulas directly in the ADSOs which greatly reduces the computational time during the query execution.
– If the data model is built on the HANA database, then we can leverage the HANA in-memory calculation for performing all the computations as HANA is optimized for faster calculations.
– Let’s aggregate the data at the info provider level instead of performing the aggregation at the query layer to save processing time during the query execution.
-Use aggregated key figures as and when possible.
– Limit the use of calculated and restricted key figures in the query and try to replace them with pre-aggregated data at the info provider level.
– We can also replace the calculated or restricted key figures in the query with pre-calculated values from the data models.
– Ensuring proper query filtration optimization. We should ensure proper filters are applied on high cardinality fields to reduce the dataset size which is being processed by the calculations.
-Query Filter Optimization: Ensure that you use query filters efficiently. Apply filters on high cardinality (non-aggregated) fields early in the query to reduce the dataset size being processed by the calculations. This will allow the system to calculate fewer rows.
-Partitioning and indexing on the basis of time dimensions would be a good option for quick access to rows which are meeting the query selection criteria.
-Leverage the aggregation layer as and when possible.
-Avoid using complex formulas in the queries and use simple filters as and when possible.
-Monitor and optimize the data loads to ensure proper minimal latency in the data updates should be there.
By implementing these strategies, you can significantly optimize the performance of reports that include multiple calculated and restricted key figures in SAP BW HANA, reducing query response times and improving overall system efficiency.
The above Q/A – 8 is the the part of 15 Day – Real Time Scenerio Based Question/Answer Series – SAP BW/HANA Interview Preps.
Also, for your preps, you can checkout:
P𝘳𝘦𝘱𝘢𝘳𝘦 𝘺𝘰𝘶𝘳 #𝘚𝘈𝘗𝘉𝘞𝘏𝘈𝘕𝘈 𝘐𝘯𝘵𝘦𝘳𝘷𝘪𝘦𝘸𝘴 𝘸𝘪𝘵𝘩 𝘍𝘙𝘌𝘌 𝘚𝘈𝘗 𝘉𝘞 𝘘/𝘈 𝘚𝘦𝘳𝘪𝘦𝘴 – 10 𝘗𝘢𝘳𝘵𝘴 – 𝘌𝘢𝘤𝘩 𝘗𝘢𝘳𝘵 𝘩𝘢𝘷𝘪𝘯𝘨 5 𝘘𝘶𝘦𝘴𝘵𝘪𝘰𝘯𝘴 𝘸𝘪𝘵𝘩 𝘈𝘯𝘴𝘸𝘦𝘳𝘴 @ https://lnkd.in/gkCHXD5p
Get your 20 Real Time Scenerio Based Q&A Ebook – SAP BW/HANA to prepare for the Interviews well -> https://topmate.io/vartika_gupta11/1322400
Get your 𝐜𝐨𝐦𝐩𝐫𝐞𝐡𝐞𝐧𝐬𝐢𝐯𝐞 𝐥𝐢𝐬𝐭 𝐨𝐟 150 𝐒𝐀𝐏 𝐁𝐖/𝐇𝐀𝐍𝐀 𝐂𝐨𝐦𝐦𝐨𝐧 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬(Only Q’s) to prepare for the Interviews well -> https://lnkd.in/gXPUV2_k
Follow for #Interview Content – https://lnkd.in/gUdyEwEe