Basic rule of thumb on any system development of a 2 tier architecture, is that complex formula or logic be push down to the Database rather than the application layer. As Databases are designed to handle more complex formulas. In the SAP environment this was commonly practiced in ABAP programming but not in the Legacy BW (none HANA). As most BW report logics were ran in the application layer and high complex computation were processed in BW’s ETL. BW Developers didn’t have the option to run their report logics directly on the Database & was reliant on BW modeling. This caused a lot of performance issues & Data redundancy in the past.
The Pushdown approached became common and popular in BW when HANA Modeling was introduced. Highly complex Formulas/Logic in the BW Reports are recommended to be done in HANA Modeling to take advantage of HANA’s full potential (Hybrid Model).
But since the Hybrid Model (BW + HANA modeling) is a new approach in BW. A lot of BW developers still experience Performance issues despite running their report in BW4HANA. BW developers tend to develop reports the same way they did in the Legacy BW, which are no longer efficient.
Creating Hybrid models in BW4, if not careful can easily result to “Data Leaks” which causes the performance issues. “Data Leaks” is a term where unnecessary data is processed through the HANA or BW Architecture but is not needed in the report. Eventually the excess data spills over through the Model until the report is generated.
Below are 4 steps you can follow to Optimize your Hybrid BW4HANA Model (BW + HANA Modeling)
1. Avoid using using complex formulas in the BW Query.
BW developers often create their queries the same way (old fashion way) and embed their formula & logic in the BW Queries. Although there are formulas in the query that BW automatically pushes down to HANA to help the processing. But it’s not clear which formula does that. So if possible it’s safer to put all the complex computation in HANA through the Calculation Views rather than in the BW Query (good candidate are the Boolean Operators). BW Queries run in the Application Layer, if a logic is NOT push down specially if it’s a complex one, a report can easily experience performance issues. Although your HANA DB can pass the data quickly to the BW query (Application Layer) for further processing, the Application Servers are not as powerful as your HANA servers. thus creating a bottle neck on your report generation.
2. All Query Filters / Restriction must be push down to HANA.
Most BW developers forget to apply the filter restrictions in HANA (through Input Parameters) which are applied in the query. Since there are no filter/restrictions in HANA, whatever tables you joined or data requested will all be processed by HANA & pumped into the BW application layer. This may cause your HANA DB to run out of memory or you’ll still experience performance issue as the HANA DB will process all the data when you only needed a portion of it based on the filters.
3. Watch out for those Left Outer Joins & “Dangling Projections”.
Multiple Left Outer Joins has been a frustration for many BW developers in the past. As BW Infosets could only handle 1 Left Outer Join. This was specially needed when joining multiple Tables (ex. SD – Order – Delivery – Invoice) and the only way to do it before in Legacy BW was to do it in ABAP and have everything in 1 big table. HANA modeling enabled BW developers to do multiple Left Outer Joins. But it’s way is a bit different.
In an “SQL Join” it’s commonly known that the “LEFT OUTER JOIN” & “WHERE” statement will act as the filter to restrict the data needed to be processed. So once an SQL Join statement is executed it will filter the data simultaneously and only process what is needed. In HANA Modeling, joining different Projection in a Calculation View maybe a little different. When you create a Projection in your Calculation VIEW, you’ll have to indicate the “WHERE” clause through the Calculation VIEW’s Input Parameters (in relation to #2). Make sure that all Projections have Input Parameters and don’t rely on the Joins to further filter your data.
But if the dataset needed to be joined doesn’t have the same filter or restriction needed. There are a few methods that can be done to execute a perfect Left Out Join
◉ Attribute – You can use the Reference Column in the Input Parameters if there’s another table that can be used as reference, to further limit the data to be processed.
◉ Table Function – HANA Modeling enables developer to go the old fashion way of coding everything in SQL. For very complex requirements Table Function may be needed. Table function allows you to create complex Join statements in SQL in 1 Projection in your HANA model without worrying about dangling Projections. Table Function can be embedded on your HANA Model similar to a Projection.
4. Always check your your models for “Data Leaks”
◉ HANA to Query – Observe the performance of your reports and queries against the HANA Models. If you’re having issues with your queries/report but your Calculation VIEWS are running fine (using the same parameters) then revisit # 2. Your Query filters may not be pushed down.
◉ HANA Model – Visualize the Calculation VIEWS. This will enable you to see how much rows are being processed by each projection. The example below shows that 45M records were processed but was eventually dump when it went through a JOIN (in relation to #3)
BW’s Objects has been known for its stability in Data Warehousing & ETL functions but BW objects alone still lack the Reporting flexibility needed to take full advantage of HANA’s power. HANA modeling enables BW developers to push down the complex formulas/logic without the need of putting them in the ETLs (Traditional BW modeling). This also reduces the cost by avoiding data redundancy. Creating Hybrid Models enables BW developers take advantage of each tools strength (BW + HANA).
Although Hybrid Models are a lot different from the BW Legacy Architecture, they’re easy to learn and easy to master. If you’re new to BW4HANA but familiar with the old BW tools, you’ll only need to change the way you develop your Queries/Reports and familiarize yourself with HANA Modeling Tools. Don’t forget to always carefully analyze your structures within BW or HANA to determine the reports/models’ performance.
No comments:
Post a Comment