WELCOME TO



1143000 Impact of joining on calculation column of calculation view on HANA view performance in HANA 1.0 SPS12 Applies to: This article applies to understand the impact of join on calculation column of calculation view on the HANA view performance in HANA 1.0 SPS12Summary: This document aims at discussing the effect of calculation column of calculation view on join performance. The join operation in HANA view is important operation, SAP has improved the performance of join operation from its earlier version. However, this important operation needs to be used carefully otherwise it may result in huge performance degradation of calculation model in HANA. The general best practice guideline of any transformation to be handled at ETL layer e.g. SLT or BODS, however if it is not possible for any obvious reason then we need have better strategy to handle the same in calculation view. However, before we have better strategy, we need to have better understanding what is going wrong if we use join operation on calculated column and what would be the impact. Any operation in HANA view handled by different engines like calculated column is handled by calculation engine and only then it goes to join engine. This may result in adverse performance of the calculation view in terms time of execution and memory consumption. This article would provide a reference or insight on performance of join operation on calculated column in HANA calculation view.Author: Dipanshu RoyCreated on: 1st, November 2019Email id:dipansro@in.00 Impact of joining on calculation column of calculation view on HANA view performance in HANA 1.0 SPS12 Applies to: This article applies to understand the impact of join on calculation column of calculation view on the HANA view performance in HANA 1.0 SPS12Summary: This document aims at discussing the effect of calculation column of calculation view on join performance. The join operation in HANA view is important operation, SAP has improved the performance of join operation from its earlier version. However, this important operation needs to be used carefully otherwise it may result in huge performance degradation of calculation model in HANA. The general best practice guideline of any transformation to be handled at ETL layer e.g. SLT or BODS, however if it is not possible for any obvious reason then we need have better strategy to handle the same in calculation view. However, before we have better strategy, we need to have better understanding what is going wrong if we use join operation on calculated column and what would be the impact. Any operation in HANA view handled by different engines like calculated column is handled by calculation engine and only then it goes to join engine. This may result in adverse performance of the calculation view in terms time of execution and memory consumption. This article would provide a reference or insight on performance of join operation on calculated column in HANA calculation view.Author: Dipanshu RoyCreated on: 1st, November 2019Email id:dipansro@in.Table of Contents TOC \o "1-3" \h \z \u BUSINESS REQUIREMENT PAGEREF _Toc522955473 \h 3OVERVIEW PAGEREF _Toc522955474 \h 3PROCESS & TOOLS USED; PAGEREF _Toc522955475 \h 3SOLUTION: PAGEREF _Toc522955476 \h 3CONCLUSION: PAGEREF _Toc522955477 \h 12REFERENCE: PAGEREF _Toc522955478 \h 12BUSINESS REQUIREMENTHANA modelers frequently need to use join operation. However using join in calculation view may result in costly choice sometimes unknown to most of the developers as join is basic operation of SQL. There are basic or general guideline of different join and how it operates faster based on different types of available join operation. However this article is not going to focus on type of join operation rather try to show the impact of join on calculated column.The performance of join is measured in two different dimensions i.e time of execution and memory consumption of the model while executing the same in HANA. This measurement in two different is very important in HANA sometimes tuning in one dimension result bad performance in other dimension OVERVIEWThe measurement of performance will be based on HANa model involving following,HANA model for SAP material batch attributes generation based primarily on tables MCHA, INOB, AUSP and CABNHANA model performance when join is on calculated column between INOB and MCHAHANA model performance when join is not on calculated column between INOB and MCHA. The calculation is done in SLT layerJoin performance is measured both in time of execution and memory consumption of execution. PROCESS & TOOLS USED;HANA 1.0 SPS12 is used for this demonstration. Basic knowledge of database concept and skills are required. One should be familiar SQL operation, HANA Studio, HANA modeling using calculation view and performance tools available in HANA studio. SOLUTION:The following is the demonstration of how to measure the performance of model and the impact of join operation on calculated column versus the non-calculated column where calculation has been taken care in SLT layer. Following are the different SAP tables used for the model. The model S_BATCH_ATTRIBUTES_LOAD is a real model in one of SAP HANA project where I have worked on. The MCHA (SAP batch table) is having approximately 78 mil recordsThe INOB (SAP bridge table for internal number and object data) is having approximately 79 mil recordsThe AUSP (SAP characteristics value table) is having approximately 3.5 billion recordsThe CABN (SAP material characteristic table) is having approximately 6K recordsThe model S_BATCH_ATTRIBUTES_LOAD is developed based on joining these tables. However, the join between MCHA and INOB tables is on calculated columns. The model(HANA view) with join between calculated columns of tables MCHA and INOB is being executed within 1 min 27 secs The same model without join between calculated columns of tables MCHA and INOB is being executed within 24 secs i.e. 72% faster than model with join on calculation column. The model with join between calculated columns of tables MCHA and INOB is consuming 58.5 GB of memory for displaying 200 records in HANA Studio. The same model(HANA view) without join between calculated columns of tables MCHA and INOB is consuming 10.1 GB of memory for displaying 200 records in HANA Studio. This is almost 6 times less memory consumption by the model without join on calculated column.While investigating the execution plan of the model, the join node between INOB and MCHA without join on calculation column is taking 0.2 sec to execute join operationWhile investigating the execution plan of the model with join on calculation column, the join node between INOB and MCHA is taking 31 secs to execute join operation, which 155 times faster execution.CONCLUSION:The above HANA model and performance metrics clearly show the adverse effect of using join on calculated column. The model without join on calculated column is 72% faster and consuming 155 times less memory. This difference in time and memory will reduce with more volume of data, however it will have different effect on the model reducing overall performance of the same. It can safely be concluded transforming or calculation in view should wisely be mixed with SQL operations and should be carefully used in HANA models. REFERENCE:SAP HANA Modeling Guide: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download