DataSphere Modeling: Boosting Reporting Performance

Recently, I started a new project at a client moving into the DataSphere world. What surprised me was that nobody had looked into performance tuning. Moreover, the solution to bad performing reports was to just snapshot the final layer. This approach worked, but with the number of solutions growing and the snapshots becoming heavier, problems started occuring up to the point where flows failed.

At ONE, we value thourough investigation, so we activated the out-of-the-box expensive statements monitoring capabilities of SAP HANA to pinpoint the root causes.

I'll share a real-life allegory as a father of two small children. On weekends, my kids wake up before me and my wife, and a few months ago, they asked to set the breakfast table on their own. We gladly agreed, and they eagerly got to work. We could hear them discussing, planning, and pulling items from cupboards and drawers.

After a very long time they came to call us for breakfast and we found a table filled with all sorts of plates, some items for breakfast, but also candy, pots and pans and lots of clutter which indeed was related to dining, but not really breakfast-type material. What my children didn’t do, was filtering, they just brought in everything which was in the eating space. Although it was funny, we could say that we as end-client were not 100% happy, since it took quite some time to build their result, and additionally we had to clean up the table quite a lot to get to the output we actually required.

While you now may laugh about what happened at my house, the same actually took place at this client. In none of the models created so far, input parameters were present (sic). In the models where filters were set, they were set at the very last layer and in many cases nearly all fields were propagated from any table to the last layer.

Furthermore, the models based on S/4HANA CDS views were overly generic, combining all kinds of datasets such as contracts and sales data.

Addressing the pain points and re-evaluating the bottlenecks Addressing all of the above pain points significantly reduced the memory consumption of the models, but this was just the first step. Once these were taken care of, we reevaluated the performance and found more room for improvement. ‘Case’ statements caused a lot of row level processing, large source data tables were projected multiple times and unions of datasets happened in suboptimal places.

Lastly, we identified a case where a mix of SQL scripting and graphical calculation views caused suboptimal processing. It is known that mixing these types of scenarios can hamper performance quite a bit. Here, we decided that partial snapshotting was the better solution.

What should you take away from this? Most importantly, there is often not one silver bullet. Take your time to use the tools SAP provides to analyze your models and re-evaluate after improvements have been made. The ExplainPlan and PlanViz are easy enough to use. Focus on low hanging fruit first; are your filters close to the source? Can you apply input parameters? Can you specify your models to avoid over-generalization? Do you really need all those 100+ fields in your reporting model?

Then, focus on the complex things. Are you mixing SQL Script and Graphical views? Are your Unions in the right place? Do you really need that complex case statement?

All of the above should help you build leaner models, which will make for better supportable solutions. It sounds logical to make large generic models, but it often leads to over complexity.

Finally, know that snapshotting has its place. It is not inherently bad but should be used when other options don’t work out.

Are you having performance issues in your models, but are you having issues getting to the bottom of it? We are here to help.

As a sidenote, our children still make the breakfast table in weekends, and even though they know certain items don’t belong, they still place them occasionally as for them it is off course hilarious to see our – played – reactions to it.