Calculation Engine (CE) Functions – Rest in Peace

I remember my initial encounters with HANA extremely vividly. One of them was a conversation with Paul Barker, who was an early HANA addict. We discussed building SQL models in HANA, and then the benefits of HANA Information Views. He was wide-eyed about the benefits of using HANA CE Functions, purporting insane performance increases.

What are CE Functions?

HANA Calculation Engine Functions (or CE Functions) are a mechanism introduced in HANA SPS02, which allow direct access to the HANA Column Store. They allow extremely precise control over how precise columns are projected, calculated and aggregated. In fact, they are a direct representation of how the original HANA Column Store modeled vector functions.

Why were CE Functions so fast?

They completely bypassed the SQL optimizer and meant that whatever you wanted to executed was run in the column store, and you always got the full performance of HANA. In the early days of HANA, SQL could be extremely inefficient and cause HANA to materialize large volumes of data in the SQL Engine, in row format. This caused big memory bubbles and 100-200x slower performance.

By comparison, CE Functions always ran in the column engine, and provided you were returning modest result sets, were highly efficient. They actually effectively compile into a language called L, which was HANA’s interim language, and is very similar to C++.

In 2011 and 2012, I very frequently coded high-performance aspects of a HANA solution in CE Functions.

What were the downsides to CE Functions?

CE Functions are part of the HANA SQLScript language, and as such they are an extension of ANSI SQL. That’s neat, but it also means that there aren’t many spare operators, and the syntax could get really funky. Sometimes you need a single quote (‘), sometimes double quotes (“). Sometimes double single quotes (”), or triple single quotes (”’). CE Functions are extremely picky!

What’s more, you have to specify every column, and with complex procedures, this means a lot of copy and paste, and activation of code. The editor wouldn’t give you much help, and the error messages were terse. In short, you have to be an expert SQL programmer and have a very good working knowledge of the HANA column store, to write good SQLScript with CE Functions.

But if you wanted performance, wow – this was HANA speed.

Along came the BW on HANA…

And so in 2012, along came “Project Orange”, or BW on HANA, with HANA SPS04. The HANA database had matured enough to work for SAP BW, but BW never used CE Functions. Instead, the BW team had their own proprietary way into HANA.

This soon turned out to be a limitation within BW, and the Calculation Scenario concept was born. Now, when you compile a BW object in BW 7.4, a HANA view is created, which can be accessed either via BW’s OLAP engine, or directly into the HANA database. The two are interchangeable, but CE Functions were way too restrictive for BW’s needs.

… shortly followed by Business Suite

Then in 2013 came Business Suite on HANA. The Business Suite makes extensive use of ANSI SQL via the OpenSQL interface, and it debuted with HANA SPS06. Due to the way the Business Suite is programmed, the HANA team had to make SQL much faster than it was before, and a ton of development went into the SQL Optimizer. As of HANA SPS06, there is often little different between SQL and HANA Information Views.

Let’s not forget HANA Live

HANA Live is programmed only in HANA Graphical Calculation Views. These had a habit of performing badly, if not designed very well, so the HANA development team put a ton of effort into making them perform great. They now compile into calculation scenarios, which are a XML representation of columnar projections and calculations.

What happened to CE Functions along the way?

Over half of SAP’s HANA customers use BW, and Business Suite is the fastest growing product by go-lives. Nether of these make any use of CE Functions and so they got extremely limited development attention and few new features.

Coupled with the fact that they are inflexible and hard to use, the other mechanisms – SQL, and Graphical Calculation Views, got all the attention. CE Functions didn’t get any further optimizations, and stayed the same since HANA SPS05. They didn’t get worse – the other options just got better and better.

As of HANA SPS07, we found that Graphical Calculation Views were always faster than CE Functions. In HANA SPS08, we find that SQL, Analytic Views and Graphical Calculation Views all have exactly the same explain plans in many cases. They all perform the same and it’s just a question of preference how you design things.

In fact, extremely complex views can be created as cascaded Calculation Views, and the view compiler will collapse and simplify the views and create a single optimized set of column projections, calculations, vector joins and unions as a single Calculation Scenario. This is truly the jewel in HANA’s crown.

CE Functions – Rest in Peace

The CE Function is an important part of HANA’s past, but there is no longer a reason to use them. They are less flexible, slower, and harder to program. We expect that in a forthcoming release of HANA, they will be deprecated and eventually de-supported.

The only time I use CE Functions is when the HANA SQL optimizer has a poor execution plan. Because CE Functions and SQL don’t share engines, it’s possible to control the HANA SQL optimizer using CE Functions. That’s an extremely unusual scenario these days.

Do note that the Scripted Calculation View still has its place – you can call out to Business Functions, Predictive Functions and other stored procedure objects in SQLScript. However the days of this are limited, because in HANA SPS09 it’s now possible to do some of this via the Graphical Modeler. I expect by HANA SPS10 or 11, the Scripted Calculation View will no longer be required.

So whilst they haven’t been officially deprecated yet, please celebrate their passing, and don’t use them any more.