Category Archives: SAP HANA

Eco-friendly slide recycling with HANA

Hey there!

I recently presented a short session on my experiences with developing and debugging non-classic-SAP HANA solutions at the Leading Insights conference in Melbourne (27./28.3.2017).

And since the slides are prepared in a way that they could be used without my talking as well, I thought, I might just post them. So, there they are on slideshare:

if you feel this is a shameless content plug, you’re probably right, but I got the feeling, that this might still be interesting to some readers of the “SAP HANA” tag.

Content-wise, this is what you can expect (just in case you don’t want to swap to page 2 which shows the table of contents):

  • an exciting example for a pure HANA development without any NetWeaver/ERP background
  • some practical tips for finding performance issues with SQL statements
  • and tidbits of performance gone bad and how to fix it.

There you go; now you know.


Make query hints great again…

In case you ever got frustrated by how 80’s like the SAP HANA hints infrastructure looks like, go and support my improvement idea on SAP Idea place: 

Go and upvote! Now! 🙂

[Update 23.1.2017]: After some of the folks that I really liked to review my idea reported that they don’t have access to it, I decided to put the idea text as a copy in here as well.
So here it is:

Currently (HANA 1 SPS12/HANA 2 SP0) SAP HANA supports the usage of query hints via information view parameters or – most commonly used – the WITH HINT query parameter.

Most of the times, the hints are used to avoid a specific bug in SAP HANA and are only applicable to a specific version/revision or application state.

For all practical matters, the hints don’t serve a functional purpose for any application but are necessary band-aids to enable the application to run successfully on SAP HANA.

As many bugs do get fixed with later SPS/revisions, the hints could and should be reviewed in order to allow the application leverage the formerly disabled feature again.

Right now, this means, that a developer/system operator has to manually document and keep track of all hints used in the system, the reason for the usage, the HANA revision the hint applies, maybe a incident numbers where the hint usage had been discussed and probably a target HANA version when the hint shouldn’t be required anymore.

This task is obviously not delivering a lot of value for any developer, so in reality, a hint, once introduced, will likely stay in place forever, as nobody remembers what it was used for and if it’s safe to remove it now.

The proposal is as follows:

Instead of a simple WITH HINT syntax, allow for a more comprehensive hint management that allows for identifying a specific statement (e.g. provide a custom statement identifier in a statement annotation or comment) and provide HANA function to specify a set of hints for any statement ID, including the already mentioned metadata.

Upon SQL query compilation SAP HANA would automatically match the statement ID against the maintained statement hints (this could be a single table structure) and include the hint into the statement execution.

The fact that this happened (and against which statement id hint setting this match had been performed) should be visible in the usual query execution analysis tools like EXPLAIN PLAN or PLANVIZ.

The proposed solution has the following benefits:

Application code does not need to be modified to add hints, as long as the statements can be uniquely identified.

The hint application can be controlled centrally, without the involvement of the original developer – essentially the hint can be applied


  • operations/support staff
  • The query compiler in HANA can automatically check for the valid HANA versions and ignore the hint for non-matching versions
  • The hints for a given installation can be transported like a configuration data set across multiple servers
  • The central list of statements with hints settings allows an efficient follow-up of HANA issues and – given an option to ignore the hint list during plan compilation – also allows for very efficient testing of whether a hint is still required or not.

I haven’t found an obvious way to contact support for the SAP Ideas Place, so my best bet is that in order to view any idea a registration is required.

  • Lars

Merging right. Some tests with data loading in HANA from Down Under.

Based on SAP HANA SPS 12 (HCP trial)

When Jody Hesch recently send me an email with a question about HANA, he received what everyone receives when sending email questions about HANA. The reply is a version of

“Usually I don’t do email Q&A as this simply doesn’t help with knowledge sharing.

Instead, I advise everyone to post the question in one of the HANA related forums
(SAP Community ,
or even stackoverflow ) so that the question and its answers are search- and findable.

That way everyone can benefit from this and you even might get faster and/or better answers than from just writing to me.”

I have not given up on my personal fight against GEIGOKAI and this it what it looks like in daily real-life.


You might be inclined to call this reply rude but it turns out that most people accept this very easily and manage to copy&paste their question into one of the forums – and changing culture is always a bit painful … drip drip drip

And just as many others, Jody managed to post his question for all to see, read and discuss here: Manually managing Delta Merge

While I was doing the due diligence of checking my own ideas against a HCP trial HANA instance (SPS 12.04), the whole explanation and the example got larger than what would be nice for a simple answer, which is why you now read this is a blog post format.

On loading data and merging

Ok, let’s get to it, then.
Jody’s question was “how can manually handling delta merge make a data load faster?”
To answer this I set up a small test case:

drop table merge_test;
     "AAA" NVARCHAR(20),
     "BBB" INTEGER ,
     PRIMARY KEY ("ID"));

declare ts_start timestamp = current_timestamp;
declare ts_end timestamp;
declare loops integer = 100;
declare j integer;
declare i integer;
declare with_commit integer = 1;

    truncate table merge_test;
    for j in 0 .. :loops do
        for i in 0 .. :loops*50 do
            upsert merge_test 
                values (:i, :i || ' - ' || :j, :i*:j)
                with primary key;

        end for;
        if with_commit = 1 then
        end if;
    end for;

    ts_end = current_timestamp;    
    -- now show some infos
    select seconds_between(:ts_start, :ts_end) as duration from dummy;
    select count(*) as num_rows, min(id), max(id) from merge_test;
    select column_name, memory_size_in_main, memory_size_in_delta, count, distinct_count
    from m_cs_all_columns 
    where table_name ='MERGE_TEST'
    and schema_name =current_user;
    select merge_count, read_count, write_count, last_merge_time
    from m_cs_tables
    where table_name ='MERGE_TEST'
    and schema_name =current_user;
    select start_time, motivation, execution_time, memory_merge, merged_delta_records, success
    from m_delta_merge_statistics
    where table_name='MERGE_TEST'
    and start_time >= :ts_start
    and schema_name = current_user;

What we have here is a simple table and a data loading simulation. The data gets inserted or updated via the UPSERT command and, if the parameter with_commit = 1 a commit is done after each iteration of the outer loop.
After both loops have been left, the code prints the runtime in seconds and selects some statistics from system views.

Next, I ran three experiments.

The first two used the SAP HANA AUTOMERGE feature, where HANA decides when to perform a delta merge.
The third one followed the approach SAP BW uses for data loading: the AUTOMERGE is disabled and the target tables get merged after all data has been loaded.




5001        0       5000   

ID          1064                3733044                 505101  5001          
AAA         0                   17607386                505101  -1            
BBB         0                   5571068                 505101  -1            
$trex_udiv$ 232                 8889016                 0       0             
$rowid$     768                 15676428                505101  505101        

0           7           505101      2016-12-20 03:15:41.986


Easy to see, the whole thing ran for 386 seconds and managed to create a total of 505.101 record versions in the delta store.
Note that during this load NO delta merge had happened. This is because we never committed the transaction at all and the delta merge won’t “relocate” open update transactions to a new delta store.



5001        0       5000   

ID          723716              9132                    5001    5001          
AAA         741076              10052                   5001    5001          
BBB         711828              9132                    5001    5001          
$trex_udiv$ 273432              1912                    0       0             
$rowid$     1504                9132                    5001    5001          

5           30          505111      2016-12-20 03:40:22.85

2016-12-20 03:36:22.166 AUTO        54              FALSE           80016                   TRUE   
2016-12-20 03:37:22.24  AUTO        88              FALSE           90018                   TRUE   
2016-12-20 03:38:22.349 AUTO        119             FALSE           85017                   TRUE   
2016-12-20 03:39:22.49  AUTO        157             FALSE           85017                   TRUE   
2016-12-20 03:40:22.67  AUTO        186             FALSE           85017                   TRUE   

Here we see that with commits in between the whole thing only took 349 seconds.
As we’ve committed the transaction in between a couple of times, the automerge had a chance to be active – and did so roughly every minute (5 times).

Now, why is this variant a bit faster overall? The effort to find the currently active record versions grows with the number of versions in the delta store.
With the automerge in between, this number stayed consistently below 100.000 versions, while the first version had to face an increasing number of versions to check between round 100.000 and 505.101.


Finally the version of data loading that is similar to SAP BW’s approach.
I disabled the automerge via

alter table merge_test disable automerge;

and ran the test again:


5001        0       5000   

ID          1064                3733044                 505101  5001          
AAA         0                   17607386                505101  -1            
BBB         0                   5571068                 505101  -1            
$trex_udiv$ 232                 8810536                 0       0             
$rowid$     768                 15676428                505101  505101        

0           15          505101      2016-12-20 03:49:36.914



merge delta of merge_test;

2016-12-20 03:56:09.435 HARD        46              FALSE           5001                    TRUE   

With 325 seconds this is the fastest run.
Looking at the time the manual delta merge took, we find that it’s still faster than the last delta merge of the automerge example.

This is – again – due to the fact that now the are a lot less concurrent record versions in the table.
Note, MERGED_DELTA_RECORDS shows the number of valid (committed) records that have been moved from delta to main storage.
If I update the same record a hundred times before I commit, then I would only get one record merged into the main store.

Now, why do we see a better performance in this case?

To illustrate the resource usage during our loads, see the following diagrams:

In our first case, we see that the memory usage for the delta store as well as the CPU usage for the UPSERT gets larger over time.
Sometime after the last commit is done, an automerge gets triggered, leading to additional CPU and a lot more memory usage.
The result is a relatively well-compressed table (last little blue square).

Note that the number of uncomitted record versions (little orange squares) increases during the whole time.

With the second approach (commits in between and automerge on), we see that CPU and memory usage during the load stay moderate. Only when the merge is performed, memory and CPU usage increase and the whole process has to wait for the merge to switch over to the delta2 in order to continue.

Noteworthy here is that the resulting table can easily be larger than the table sizes produced by the other methods. This is because the column compression algorithms are determined during the first delta merge and won’t change that easily.
This can lead to a situation where the compression algorithms are determined on a subset of data that is not representative of the total (think of loading data with timestamps or dates in sequential order – the first merge may only see one specific date).

With the last option, we see a very similar pattern than in the first case.
The only difference here is that the final merge was manually triggered.

So with AUTOMERGE disabled and regular commits we get the best of everything:

  • no waits due to delta merges
  • no increased runtimes due to a high number of outdated uncommitted record versions
  • well-compressed table (since the compression optimisation can work on the whole data set).

This works well, as long as the resulting delta merge can be done within the available memory.
And even if other memory structures would need to be unloaded to allow for the delta merge to happen, then this would have to happen only once and not several times during the loading process.

Smarter merge

I mentioned that this third approach is similar to what SAP BW does but this is only half the truth.
Instead of manually sending a

merge delta of merge_test;

which gives the hard instruction to perform a delta merge now, SAP BW uses a “smart merge”.
The idea here is that instead of forcing a delta merge to let HANA evaluate whether a merge would be granted for, given the current memory statistics and given the merge decision functions.

With this flexible approach, the application does not need to care about the specifics of how to decide when a merge should take place, but can yet trigger HANA to take care about that.


Manually controlling the delta merge can improve performance for the load process as well as reduce the overall system load during the data loading, since multiple merges are avoided. Delta merges still are extremely resource intensive operations that can have a massive impact to the whole HANA system. Therefore it’s a good idea to have them done as seldom as possible.
This, in combination with a controlled approached to COMMITTING loaded data can, as shown above, have quite an effect to the runtime of the data loading.

There you go, now you know.

HANA in a pocket, a skull and some dirty hands on Linux

DISCLAIMER: this blog describes unsupported and unlicensed use of the SAP HANA software. I describe my personal try-out of a hardware/software combination. It’s not in any way meant to be used productively or recommended to use the software in the way I describe.

If you loose data, waste life-time, money, your good mood or damage anything while trying to do what I describe: that’s all on you and there’s no guarantee, warranty or liability whatsoever from SAP or myself.

The frustrating reality of ‘big machine computing’

Just like everyone else in the SAP HANA community, I really like having a private instance available for me.

Trying things out, practicing, researching solutions, working with sample data, checking system behaviour etc. are just so much easier than e.g. on a HCP instance or some “playground” instance on the company’s SAP HANA hardware.

Yes, there are always ways to work around this but e.g. for me this means I have to live with average round trip times of ca. 320ms. That’s certainly not too bad and definitively works for most use cases. Unfortunately the server is not “mine”.

So, if I e.g. wanted to reboot the server for any reason, well, folks probably wouldn’t be too happy …

Also, this server really is only available when I can logon into our corporate network in one way or another. And this turns out to be a limitation that is easily hit.

Customer visit with no immediate WIFI logon?

Nope, no way to access the SAP HANA system then.

Cable provider outage in the home office?

Yep, that SAP HANA server is running fine, far away – without any chance to be used by me.

In short a somewhat local SAP HANA installation would be a really nice thing. Especially since I am not permanently working with tons of data, but rather look into the development/admin aspect of things.

Is there a way or what?

A few weeks ago, I was on a customer visit in San Francisco and the development team there came up with a valid and interesting question: how to provision developers with private SAP HANA instances?

With a private instance, each developer could mess around with his system without impacting anyone else. This is pretty much a standard approach for development nowadays, so the request is rather reasonable.

SAP internally there exist multiple approaches to this problem.

From “small” SAP HANA hardware under the developer’s desk (actually quite expensive machines, that we would have called “workstations” back in the day) to spawn-on-demand-systems on virtual servers: there’s a flavor for everyone.

Unfortunately all that sort of screams: high initial investment.

Not great for smaller teams or individuals who would want to get deeper into SAP HANA development/administration without access to “allowed” hardware.

Coincidentally I read Mr. Atwood’s blog post and was intrigued: this Intel Skull Canyon NUC sounded awesome.

Even though I am not a gamer and the last time I assembled a PC myself was somewhat around 2000 this looked really interesting to me.

The system runs on an Intel i7 quad-core processor and allows up to 32GB of RAM.

That’s certainly enough to run and operate a small SAP HANA instance.

Here’s a picture of this nice piece of technology:


Cutting the story short

I ordered that thing online together with two 16 GB DDR4 strips and an old/slow but relatively cheap IBM SSD with 480GB capacity.

All in all the bill for this was around 1400 Australian Dollars – not too bad for a system way more powerful than my MacBook Pro.

Of course: this doesn’t come with any display or input device (well, in fact the online shop I ordered from had a special and chipped in a Logitech K400 plus for free 😉 . You have to have a monitor to use this thing.


A few days later the whole pack arrived and I was really surprised by the weight of it: easily below one kilogram.

I’ll skip the whole un-boxing and assembly part, but will tell you: it’s easy.

The RAM modules just need to be clicked into place and the SSD installation requires to loosen and tighten a single screw. Definitively doable for a hardware n00b like myself.

The more complicated part – again: for me, others might just do this without blinking – was to put an operating system onto the new machine.

I didn’t buy a Windows license, since the whole purpose of this buy was to eventually run SAP HANA on it, so I went for OpenSuse Linux (Leap Distribution). Creating a boot-able USB thumb drive is relatively easy.

The only changes to this tutorial I did was

  • using ‘pv‘ to get an idea on the progress of the data transfer (installed via ‘brew‘) – yes, I used a Mac for this.
  • using a blocksize of 1M instead of the 4k mentioned in the tutorial.


The whole command set then looked like this:

sudo zsh

pv /path/to/downloaded.iso | dd of=/dev/rdisk2 bs=1M


Armed with this USB thumb drive stuck into one of the 4 USB ports, I simply started my “skullbox” and the OpenSuse installation screen appeared.

Again, I’m not the Linux expert on the installation side of things, so I basically when with most of the default settings and clicked through.

One reboot after that, I had OpenSuse running on the new machine.

Meanwhile in the kitchen

As TV chefs would say “… meanwhile I prepared something else …” which translates here into the download of the SAP HANA installation files from SAP Service Marketplace (Yes, you do need to have paid access to this).


The installation files come in a big multi-part RAR archive with Windows Auto-Extraction, which means, that the first part is an .EXE file.Unpacking on Linux however is not a problem; all you need is the UNRAR tool (if all fails ‘zypper install should fetch this).


The rest of the installation is fairly well documented, and only small additional changes were required to make the installation work on this NOT SUPPORTED operating system:


a) PNG Library couldn’t be loaded, and the hdblcmgui woudln’t start.

Fixed by installing an older version via zypper install –oldpackage libpng12-0′


b) nameserver couldn’t be started due to missing SSL libs.The trace file listed

Can’t load ‘/playfield/hana_inst/51050838/DATA_UNITS/HDB_SERVER_LINUX_X86_64/instruntime/’ for module Net::SSLeay: cannot open shared object file: No such file or directory at line 193.”


Fixed by ‘zypper install libssl*’ and

ln -s /lib64/ /usr/

ln -s /lib64/ /usr/lib64/


(as seen here)After that the installation went through without further troubles.

The looooong fork

I have enabled the option for remote administration in OpenSuse, which means that the system now can be accessed via VNC.

For my Windows 10 based work laptop I find that MobaXTerm makes it really simple to access the Linux box – it even has a plugin that contains a VNC client.

For the Mac, there is actually a VNC client build in (I’m using El Capitan): just start “Screen Sharing” or put vnc://hostname:port into the Safari address bar and off you go…


After all of this (and some more wrestling with the Linux network setup – I still barely have a clue on that, but it seems to work now…)

I can now access my “HANA skullbox” via:

  • WIFI connection at homeoffice
  • drect CAT5 cable connection between my work laptop and the NUC

which are exactly the scenarios I wanted to support.

Starting a HANA Studio via X-Server works too:


But that’s neither fast nor pretty.

For working with the system I prefer using the local HANA Studio or the Web UI.

Of course I used ‘memory allocation limit’, but to have some fun, I cranked it up to the total 32 GB of installed RAM; so far no functional problems with this 😉

All of this setup happened after hours over maybe three evenings. Someone with more Linux know-how would probably be done in way less time.

So far I’m quite happy with this setup and I’m eager to try out what things work and where the hard limit of usability for this micro-HANA lies.

OK, that’s it for now.

AGAIN: this is a completely unsupported setup. No support, warranty, maintenance or endorsement is provided by SAP or myself for this.

There you go – now you know!



SAP HANA community continues to grow and so does the moderator team

Hello HANA community!

As of last week, we are happy to have a new moderator in our team: Michael Healy

Michael lives and works in Dublin, Ireland and works in the SAP Digital Business Services group helping customers around the globe with their SAP HANA implementations.

When he’s not solving support incidents he is busy writing well liked documents on SAP HANA troubleshooting, such as

Troubleshooting SAP HANA Authorisation issues

Troubleshooting SAP HANA Delivery Units and HANA Live Packages issues. (HALM).

Troubleshooting Hanging Situations in HANA

Troubleshooting SAP HANA High CPU Utilisation

And if there’s still time left he’s also answering question in the forum.

What an engagement! 😎

His efforts had been recognized with the Topic Leader award 2015/2016 in the SAP HANA and In-Memory Computing space.

All this was not enough for him and he seemed quite happy when I asked him if he would consider joining the moderator’s team – lucky us!

Here’s the whole team at a glimpse:

Vancouver, CAN Dublin, IRE Passau, GER Melbourne, AUS
Lucas Oliveira Michael Healy Florian Pfeffer Lars Breddemann

For the more visually inclined readers, here’s a nice and colorful map view (click for the website):


All in all the moderator team now spans all time zones and covers most areas of SAP HANA technology. It also consists of Top Contributors, SAP Press Authors and SAP HANA Distinguished Engineers (no SAP Mentors yet, but who knows what the future holds… ), so I’d say the space is in a good position to enable informed and interesting discussions and knowledge exchange.

From my end of the world (pin “C” on the map 🙂 ) thanks to Lucas, Michael and Florian for their great work!

Cheers from Down Under,



On multiple mistakes with IN conditions

Based on SAP HANA SPS 11

Update 1.6.2017

Retesting the APPLY_FILTER() approach on rev. 112.07 showed that it is now possible to simply hand APPLY_FILTER() an IN condition with a list of integers.
This approach for the workaround of the general limitation on multiple input parameter values for scripted calculation views is also shown in SAP note  “2315085 – Query with Multi-Value Parameter on Scripted Calculation View Fails with Incorrect Syntax Error.

Therefore, if you are using a current version of SAP HANA and still develop/use scripted calculation views, it is not recommended to use the CE-function approach I explained in this blog post. The limitation that this approach worked around has been fixed in SAP HANA.

Dear readers

there is a long-standing modelling problem with SAP HANA calculation views:

Using multiple input parameters to filter data similar to the SQL IN predicate.

This discussion Handling multi value input parameters can be taken as a comprehensive example.

It seems so straight forward at first and so practical.

Once the input parameter is defined the data preview tool built into SAP HANA Studio or your reporting client of choice can read the meta data for it and present the user with a nice UI dialog to specify values.

Something as fancy as this:


Now, the way that this works is rather counter intuitive.

For graphical calculation views, there are a couple of nicely written blog posts available, like Using Multiple Values in Input parameter for filtering in Graphical Calculation View but it seems that scripted calculation views did simply not want to be as flexible.

For those, rather clunky (and not very well performing) solutions had to be built to make it possible at all, (see SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter or How to process and use multi-value input parameter in a scripted view in HANA)

Either the solution involved dynamic SQL or some form of parameter string mangling with loops and pseudo-dynamic temporary result set constructs.

Other approaches proposed to avoid the problem altogether and use multiple parameters (instead of one multi-valued parameter).

Developer arrogance driving solution finding…

The last time I read one of those discussions (yesterday) I thought:


“This cannot be the right solution. There must be some easier way to do it!”

So arrogance got the better of me – HA! It cannot be that difficult. (It’s so cheesy that for once Comic Sans is a fitting choice).

I dare to guess that nearly every developer had that feeling every now and then (if not, I would have a hard time finding a good explanation for so many drastically underestimated development efforts…)

Attacking the problem

My first impulse was to use the APPLY_FILTER() function, but I soon learned what many others probably discovered before: it doesn’t solve the problem.

The reason for that is the way APPLY_FILTER() works.

It takes the table variable and your filter string and constructs a new SQL statement.

For example, if your table variable is called vfact and your input parameter selection was 1, 2 and 5 your scripted calculation view could look like this:

/********* Begin Procedure Script ************/
  vfact = select * from fact;
  declare vfiltD10 nvarchar(50); -- this is a temp variable to construct the filter condition
  vfiltD10 = ' "DIM10" IN ( ' || :IP_DIM10 || ' )';
  var_out = APPLY_FILTER (:vfact, :vfiltD10);
END /********* End Procedure Script ************/

This compiles fine and if you try to run it with some parameters you are greeted with a surprise:

     "DIM10", "DIM100", "DIM1000", "DIM1000000",
     "KF1", "KF2"
        ('PLACEHOLDER' = ('$$IP_DIM10$$','1,3,6')) ;

Could not execute ‘SELECT “DIM10”, “DIM100”, “DIM1000”, “DIM1000000”, “KF1”, “KF2” FROM “_SYS_BIC”.”devTest/MULTIIP” …’ in 373 ms 962 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] “_SYS_BIC”.”devTest/MULTIIP/proc”: [130] (range 2) InternalFatal exception: not a valid number string ‘1,3,6’

Not only is this error annoying, but it’s FATAL… shudder!

After some investigation, I found out that the input parameter not only provides the digits and the separating commas but also the enclosing single-quotes.

Nothing easier than getting rid of those:

  vfiltD10 = ' "DIM10" IN ( ' || replace (:IP_DIM10 , char(39), '')  || ' )';

With this, the single-quotes get easily removed (39 is the ASCII value for the single quotes and the CHAR function returns the character for the provided ASCII code – this just makes it easier to handle the double-triple-whatever-quotation syntax required when the single-quote character should be put into a string).

Of course, seeing that we have not yet reached the end of this blog post, you already know: that wasn’t the solution.

The problem here was not only the quotation marks but also that  SAP HANA does not parse the string for the input parameter value. The result for the filter variable is that we do not get the condition

  actual condition          ===> syntax structure

  “DIM10” IN ( 1, 3, 6)     ===> X IN ( c1, c2, c3)


  “DIM10” IN ( >’1, 3, 6′<) ===> X IN ( c1 )

So even when we remove the quotation marks, we still end up with just one value (I enclosed this single value in >’ ‘< for easier distinction).


The different syntax structures pointed out above are easily overlooked also in standard SQL. Often developers do not fully realise that an IN condition with 3 parameters is structurally different from an IN condition with 2 or 4 parameters.

Whenever the number fo parameters of the IN condition changes, the statement is effectively a new statement to the database, requiring new parsing and optimisation and also allocating its own space in the shared SQL cache.

This is another detail that ABAP developers do not need to worry about, since the

SAP NetWeaver database interface gracefully splits up IN-lists into equal chunks and recombines the result set automatically. See this ancient piece SAP Support case “FOR ALL ENTRIES disaster” for more details.

One approach to avoid this issue can be to use temporary tables instead of the IN condition. Especially when parsing/query optimisation is taking a long time for your application, this might be an approach worthwhile to implement.

Back to the main topic though!

So, the “obvious” approach of using APPLY_FILTER() does not help in this case.

Is it possible that it is just not possible to take multiple input parameter values into an IN list? But graphical calculation views can do it – and rather easy.

And in this observation laid the key for the solution. What is different between graphical and scripted calculation views?

Right, graphical calculation views do not produce SQL for the boxes we draw up.

Technically speaking it replaces them with Plan Operators – very much similar to the abandoned CE_-functions.

Do you see where this is heading?

Yes, indeed. The solution I found works with CE_-functions.

Oh, how very heretic!

May the performance gods get angry with me for making the SAP HANA execution switch engines…

But first, let’s look at the solution, shall we?

/********* Begin Procedure Script ************/
     vfact = select * from fact;
  var_out = CE_PROJECTION(:vfact,
                     [ "DIM10", "DIM100", "DIM1000", "DIM1000000"
                     , "KF1", "KF2" ],
                      'IN ("DIM10", $$IP_DIM10$$)');
END /********* End Procedure Script ************/

Easy to see, this approach mimics the filter approach for graphical calculation views.

To not over complicate things I only used the CE_PROJECTION function for the filter part – everything else is still in efficient, familiar SQL.

Important to note is that this works only, when the input parameter is referenced with the $$<name>$$ format.

Also important to recall is that the complete filter expression needs to be provided as one string enclosed in single quotation marks ( ‘ <filter expression goes here> ‘ ).

OK!“, you may say, “this works, but now you broke the holy rule of CE_-functions damnation. The performance of this surely is way worse due to the implicit engine change!

Well, let’s have a look into this!

First the explain plan for the SQL based statement:

     "DIM10","DIM100","DIM1000", "DIM1000000",
where DIM10 IN (1,3,6) ;

OPERATOR_NAME   OPERATOR_DETAILS                                         EXEC_ENGINE SUBTREE_COST

COLUMN SEARCH   FACT.DIM10, FACT.DIM100, FACT.DIM1000, FACT.DIM1000000,  COLUMN       1.645529062

                FACT.KF1, FACT.KF2                                                  

                (LATE MATERIALIZATION, OLTP SEARCH, ENUM_BY: CS_TABLE)              

  COLUMN TABLE  FILTER CONDITION:                                                   

                (ITAB_IN (DIM10))                                                   

                FACT.DIM10 = 1 OR FACT.DIM10 = 3 OR FACT.DIM10 = 6       COLUMN    

Now the scripted calculation view version:

     "DIM10","DIM100","DIM1000", "DIM1000000",
        ('PLACEHOLDER' = ('$$IP_DIM10$$','1,3,6')) ;

OPERATOR_NAME   OPERATOR_DETAILS                                         EXEC_ENGINE SUBTREE_COST

COLUMN SEARCH   FACT.DIM10, FACT.DIM100, FACT.DIM1000, FACT.DIM1000000,  COLUMN       1.645529062

                FACT.KF1, FACT.KF2                                                  

                (LATE MATERIALIZATION, OLTP SEARCH, ENUM_BY: CS_TABLE)              

  COLUMN TABLE  FILTER CONDITION:                                                   

                (ITAB_IN (DIM10))                                                   

                FACT.DIM10 = 1 OR FACT.DIM10 = 3 OR FACT.DIM10 = 6       COLUMN     

See any difference?


That’s right, there is none. And yes, further investigation with PlanViz confirmed this.

SAP HANA tries to transform graphical calculation views and CE_-functions internally to SQL equivalents so that the SQL optimizer can be leveraged. This does not always work since the CE_-function are not always easy to map to a SQL equivalent, but a simple projection with a filter works just fine.

Now there you have it.

Efficient and nearly elegant IN condition filtering based on multiple input parameters.

There you go, now you know.

Have a great weekend everyone!



Modelling Learning Double Action or two things I just learned about modelling in SAP HANA SPS 11

Far fetched…


A colleague asked me over a year ago (2015 and SPS 9 … sounds ancient now, I know) whether it is possible to leverage information models in a different SAP HANA instance via SDA (Smart Data Access – look it up in the documentation if you didn’t know this yet).

The scenario in mind here was a SAP BW on HANA system reading data from a Suite on HANA system and using the SAP HANA live content (, installed there.

The Open ODS feature of SAP BW on HANA was to be used here as it allows reading from tables and views exposed via SDA in the local SAP HANA instance.

Now this idea sounds splendid.

Instead of having to manually build an extractor or an data export database view (both of which can be extensive development efforts), why not simply reuse the ready made content of SAP HANA live for this?

As usual the proof of the pudding is in the eating and as soon as it was tried out a severe shortcoming was identified:

    ('PLACEHOLDER' = ('$$userNameFilter$$', 'USER_NAME= LARS'))
Could not execute 'select * from "LARS"."IMACCESS_LBPB/SCV_USERS"('PLACEHOLDER' = ('$$userNameFilter$$', 'USER_NAME= ...'
SAP DBTech JDBC: [7]: feature not supported:
Cannot use parameters on row table: IMACCESS_LBPB/SCV_USERS: line 1 col 22 (at pos 21)


I just created an Information Model similar to the ones provided with the SAP HANA Live content including the heavily used Input Parameters to enable the model to be flexible and reusable (and also to allow filter push-down) but SAP HANA tells me:


“Nope, I’m not doing this, because the PLACEHOLDER syntax only works for information views and not for ‘row tables’.”

This ‘row table’ part of the error message stems from the fact that SAP HANA SPS 9 showed SDA tables as row store tables. This also means that all data read from the SDA source gets temporarily stored in SAP HANA row store tables before further processed in the query.

One reason for doing that probably was that the mapping from ODBC row format to column store format (especially the data type mapping from other vendors DBMS) was easier to manage with the SAP HANA row store.

Having said that, when accessing another SAP HANA system, such format mapping surely should be no problem, right?


And in fact there is an option to change this: the parameter “virtual_table_format” in the “smart_data_access” section on of the indexserver.ini:

= Configuration

Name                     | Default

  indexserver.ini          |       

    smart_data_access      |       

     virtual_table_format  | auto 

This parameter can be set to ROW, COLUMN or AUTO (the SPS 11 default value, automatically using the right format depending on the SDA adapter capabilities).

For more on how “capabilities” influence the SDA adapter behavior, check the documentation.

Back last year I wasn’t aware of this parameter and so I couldn’t try and see if, after changing the parameter, the query would’ve worked.

Anyhow, like all good problems the question just popped up again and I had an opportunity to look into this topic once more.

“Smarter” at last…

And lo and behold, with SAP HANA SPS 11 the PLACEHOLDER syntax works like a charm even for virtual tables.

SELECT -- local execution ---
     sum("KF1") AS "KF1",
     sum("KF2") AS "KF2",
FROM "_SYS_BIC"."devTest/stupidFactView"
    ('PLACEHOLDER' = ('$$IP_FACTOR$$','34'))
WHERE "D10_VAL" = 'DimValue9'
and "D100_VAL" = 'DimValue55'


D10_VAL     D100_VAL    KF1         KF2         CC_KF1_FACTORED

DimValue9   DimValue55  -1320141.70 525307979   -44884817     

successfully executed in 352 ms 417 µs  (server processing time: 7 ms 385 µs)

successfully executed in 356 ms 581 µs  (server processing time: 8 ms 437 µs)

successfully executed in 350 ms 832 µs  (server processing time: 8 ms 88 µs)

OPERATOR_NAME       OPERATOR_DETAILS                                         EXECUTION_ENGINE

COLUMN SEARCH       ‘DimValue9’,




                     TO_BIGINT(TO_DECIMAL(SUM(FACT.KF1), 21, 2) * ’34’)






                        SUM(FACT.KF2)                                        COLUMN

    JOIN            JOIN CONDITION:

                    (INNER) FACT.DIM100 = DIM1000.ID,

                    (INNER) FACT.DIM10 = DIM10.ID                            COLUMN

      COLUMN TABLE                                                           COLUMN

      COLUMN TABLE  FILTER CONDITION: DIM1000.VAL = n’DimValue55′            COLUMN

      COLUMN TABLE  FILTER CONDITION: DIM10.VAL = n’DimValue9′               COLUMN


See how the SPS 11 SQL optimisation is visible in the EXPLAIN PLAN: since the tables involved are rather small and only two dimensions are actually referenced, the OLAP engine (usually responsible for STAR SCHEMA queries) didn’t kick in, but the execution was completely done in the Join Engine.

Also notable: the calculated key figure was reformulated internally into a SQL expression AFTER the parameter value (34) was supplied.

This is a nice example for how SAP HANA does a lot of the query optimisation upon query execution.

If I had used a placeholder (question mark – ?) for the value instead, this whole statement would still work, but it would not have been optimised by the SQL optimizer and instead the calculation view would’ve been executed “as-is”.

Now the same statement accessing the “remote” view:


SELECT -- SDA access ---
     sum("KF1") AS "KF1",
     sum("KF2") AS "KF2",
FROM "DEVDUDE"."self_stupidFactView"
    ('PLACEHOLDER' = ('$$IP_FACTOR$$','34'))
WHERE "D10_VAL" = 'DimValue9'
and "D100_VAL" = 'DimValue55'


D10_VAL     D100_VAL    KF1         KF2         CC_KF1_FACTORED

DimValue9   DimValue55  -1320141.70 525307979   -44884817     

successfully executed in 351 ms 430 µs  (server processing time: 12 ms 417 µs)

successfully executed in 360 ms 272 µs  (server processing time: 11 ms 15 µs)

successfully executed in 359 ms 371 µs  (server processing time: 11 ms 914 µs)

OPERATOR_NAME           OPERATOR_DETAILS                                                       EXECUTION_ENGINE

COLUMN SEARCH           ‘DimValue9’, self_stupidFactView.D100_VAL,





  COLUMN SEARCH         SUM(self_stupidFactView.KF1),




                        (ENUM_BY: REMOTE_COLUMN_SCAN)                                          ROW

    REMOTE COLUMN SCAN  SELECT SUM(“self_stupidFactView”.”KF1″),




                        FROM “_SYS_BIC”.”devTest/stupidFactView”

                            ( PLACEHOLDER.”$$IP_FACTOR$$” => ’34’ )  “self_stupidFactView”

                        WHERE “self_stupidFactView”.”D10_VAL” = ‘DimValue9’

                        AND “self_stupidFactView”.”D100_VAL” = ‘DimValue55’

                        GROUP BY “self_stupidFactView”.”D100_VAL”                               EXTERNAL


Because of the mentioned parameter setting, SAP HANA now can create a statement that can be send to the “remote” database to produce the wanted output.

Note how the statement in the REMOTE COLUMN SCAN is not exactly the statement we used: the aggregated columns are now the first in the statement and the parameter syntax used is the new “arrow”-style syntax (PLACEHOLDER.”$$<name> $$” => ‘<value>’). This nicely reveals how SDA actually rewrites the statement in order to get the best outcome depending on the source systems capabilities.

For a better overview on what happens in both scenarios please look at this piece of ASCII art in awe 🙂

|[ ]| = system boundaries

local statement execution

|[SQL statement ->    Information view -> Tables +]|


|[       RESULT < ——————————-+]|

SDA statement execution

|[SQL Statement -> Virtual Table -> SDA connection ->]| — ODBC transport –> |[ Information view -> Tables +]|


|[       RESULT < ———————————–]| <– ODBC transport — |[–<  RESULT <—————+]|

For more on SDA, BW on HANA and how both work together have a look here:

And while there, don’t miss out on the other “new in SPS 11”- stuff (if not already familiar with it anyhow)

The Web, Stars and the importance of trying things out

For the question discussed above I of course needed to have a test setup ready.

Creating the SDA remote source was the easiest part here, as I just created a “self” source system (BW veterans will remember this approach) that simply pointed to the very same SAP HANA instance.

In order to emulate a proper SAP HANA live view I needed to create an Information model with Input Parameters, so I thought: easy, let’s just quickly build one in the Web based development workbench.

So far I’ve done most of the modelling in SAP HANA studio, so I took this opportunity to get a bit more familiar with the new generation of tools.

I wanted to build a classic Star-Schema-Query model, so that I could use the Star Join function.

From SAP HANA Studio I knew that this required calculation views of of FACT and DIMENSION to work.

Not a problem at all to create those.


A CUBE type view for the fact table


One of the Dimension type views

I then went on and created a new calculation view of data type CUBE and checked the WITH STAR JOIN check box.


Next I tried to add all my FACT and DIMENSION views to the join, but boy was I wrong…


Clicking on the ➕ button should allow to add the views.


But no option there to add the fact view into the STAR JOIN node – while adding dimension just worked fine:


Now I had all my dimensions in place but no way to join them with fact table:


After some trial and error (and no, I didn’t read the documentation and I should have. But on the other hand, a little more guidance in the UI wouldn’t hurt either) I figured out that one has to manually add a projection or aggregation node that feeds into the Star Join:


Once this is done, the columns that should be visible in the Star join need to be mapped:

And NOW we can drag and drop the join lines between the different boxes in the Star Join editor.


Be careful not to overlook that the fact table that just got added, might not be within the current window portion. In that case either zoom out with the [-] button or move the view around via mouse dragging or the arrow icons.


After the joins are all defined (classic star schema, left outer join n:1, remember?) again the mapping of the output columns need to be done.


Here, map only the key figures, since the dimension columns are already available in the view output  anyhow as “shared columns“.


For my test I further went on and added a calculated key figure that takes an Input Parameter to multiply one of the original key figures. So,nothing crazy about that, which is why I spare you the screen shot battle for this bit 😉 .

And that’s it again for today.

Two bits of new knowledge in one blog post, tons of screenshots and even ASCII art – not too bad for a Monday I’d say.

There you go, Now you know!


*click* – *click* – *doubleclick* and nothing happens

Today’s tidbit is one of those little dumb things that happen every now and then and when I think: “Great, now this doesn’t work… WTF…?”

Usually that’s a bit frustrating for me as I like to think that I know how stuff works around here (here, meaning my work area, tools, etc.).


So here we go. Since the SAP HANA Studio is currently not “an area of strategic investment” and a the Web based tools are on the rise, I try to use those more often.

I even have the easy to remember user-friendly URL (http://<LongAndCrypticNodeName.SomeDomainname.Somethingelse>:<FourDigitPortNumber>/sap/hana/ide/catalog/) saved as a browser bookmark – ain’t I organized! 🙂


And this thing worked before.

I have used it.

So click on the link and logon to the instance and get this fancy “picture” (as my Dad would explain it to me –  everything that happens on the screen is a “picture”, which is really helpful during phone-based intra-family help-desking…):



Pic 1 – The starting ‘picture’, looking calm and peaceful… for now


Ok, the blocky colors are due to GIF file format limitation to 256 colors, but you should be able to see the important bits and pieces.


There is some hard to read error message, that I choose to ignore and click on the little blue SQL button and then … nothing happens.

I click again and again as if I cannot comprehend that the computer understood me the first time, but no amount of clicks yields to open the SQL editor.

What is going on?

Next step:


Do the PRO-thing…

… open Google Developer Tools

… delete session cookies and all the saved information.

… Logon again.


Lo and behold, besides the much longer loading time for the page, nothing changed.


Great. So what’s else is wrong? Did the last SAP HANA upgrade mess with the Web tools?

Pic 2 – wild clicking on the button and visually enhanced error message indicating some bad thing


Luckily, that wasn’t it.

Somewhere in the back of my head I remembered, that I had a couple of browser extensions installed.


Now I know what you’re thinking: Of course it’s the browser extensions. That moron! Totally obvious.

What can I say? It wasn’t to me.


Pic 3 – there’s the culprit, the root cause and trigger for hours of frustration


It just didn’t occur to me that e.g. the Wikiwand browser extension that I use to have the Wikipedia articles in a nicer layout would install a browser wide hook to the CTRL+CLICK event and that this would prevent the Web tools to sometimes not open.

After disabling this (there’s a settings page for this extension) the Web tools resumed proper function.

Good job!


So is the Wikiwand extension a bad thing? No, not at all. There are tons of other extensions that do the same.


While I would really like to demand back the precious hours of my life this little mishap took from me, I assume that this request would be a bit pointless.

To me, at least, this experience, leaves me with the insight, that I clearly thought to simplistic about the frontend technology we use today. Web browsers are incredible far from a standard environment and controlling what the end user finally sees is not easy (of really possible).


Ok, that’s my learning of the day.






the error message “Could not restore tab since editor was not restorable” not only seems to be a tautology, but also had absolutely nothing to do with the problem in this case.


HANA dude analyses Python error and this happened next… (plus: something with Andy Warhol)

Fresh out of the Christmas/New Year vacation I yesterday received this question from one of my colleagues:

Hi Lars

I am trying to set up a demo to show MDC at FKOM. I am trying to configure MDC via the Python script method – although I have an issue where I am missing a file – the error from the convertMDC.Py script says “no module named ConfigMgr.Py”

I remember you demoed this method at the Architecture Summit in Sydney – did you come across this issue?”

(He was talking about this nice event and the Multi Database Container feature of SAP HANA.)

Now as I was on my way to see the very nice Andy Warhol – Ai Weiwei exhibition currently on display in Melbourne, I couldn’t logon to my test system to look into the issue.

Due to my “root-cause-analysis reflex” from my stint in SAP support, I followed up today and was able to verify my initial hypothesis: there must be a search path setting not quite right.

These are the steps I took:

1. check the file:

The first two lines in this Python file are

import os, time, sys, subprocess, getopt
import ConfigMgrPy

which is where the error message comes from.

Python is trying to load the listed modules but fails with the ConfigMgr module.

Checking the Python documentation I figured out that there are a couple of places Python looks for the modules.

One option to specify these places is the environment variable PYTHONPATH. Therefore I checked this variable on my system:

2. check PYTHONPATH environment variable:


Reformatting this string gave me this list:


Nice, but didn’t really gain a lot of insight here…

3. Checking the path from within Python:

In hindsight this should have been the first thing to do, but hey, I am thinking and learning on my feet here…

> python
Python 2.7.10 (sap:1, Jul  6 2015, 10:21:20)
[GCC 4.7.2 20130108 [gcc-4_7-branch revision 195014]] on linux2
Type "help", "copyright", "credits" or "license" for more information.
Traceback (most recent call last):
File "/etc/pythonstart", line 7, in <module>
import readline
ImportError: No module named readline

I decided to ignore this error message. Not sure why this happens on my system!

From the Python documentation I knew that I need the sys module for further investigation, so I load this and print the current search path via sys.path:

>>> import sys
>>> sys.path
['', '/usr/sap/T07/SYS/global/hdb/custom/python_support', '/usr/sap/T07/HDB07/exe/python_support', '/usr/sap/T07/HDB07/dewdftzldc05', '/usr/sap/T07/HDB07/exe', '/usr/sap/T07/HDB07/exe/testscripts', '/usr/sap/T07/HDB07/exe/Python/lib/', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/plat-linux2', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-tk', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-old', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-dynload', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/site-packages']

Ok, so there we find all our PYTHONPATH folders as well as some other folders that seem to belong to the Python installation.

4. Load the missing module

Now I thought: “Hey, lets try and load this module manually!

>>> import ConfigMgr

Ok, no error message so far.

Then the following should work!

>>> dir (ConfigMgrPy)
['CUSTOMER', 'Configuration', 'ConfigurationException', 'HOST', 'LayeredConfiguration', 'READONLY', 'RemoteConfig', '__doc__', '__file__', '__name__', '__package__', 'createUUID', 'expandProfileVars', 'getDaemonInfo', 'getLinkInfo', 'getRootPath', 'sapgparam', 'stringToBool']

Geeze, I am such a great Python hacker… *cough* 😐

But where does the module come from?

Easy as pie:

>>> ConfigMgrPy
<module 'ConfigMgrPy' from '/usr/sap/T07/HDB07/exe/'>

5. ALL WRONG – this only works on a system where you DON’T have the problem!

Clearly, my fancy schmanzy root cause analysis would not have helped my colleague, because on his system Python didn’t find the module in the first place.

So what to do in this case?

I’d say, looking for the ConfigMgrPy file or folder or whatever Python loads for modules might be a good idea.

Since there is no hint in the Python documentation that ConfigMgr belongs to the standard Python stuff, I have to assume that it belongs to SAP HANA.

Which limits the search area for the object to the SAP HANA installation folders.

On my system that would be the /usr/sap/<SID>/ folder.

Searching for files on Linux always makes me google for examples and this is what I came up with this time:

find -L /usr/sap/T07 -name 'ConfigMgr*'

So far so good, but what’s the -L for?” you might ask.

When trying this thing out on my system, I didn’t use the -L immediately and found… nothing at all.

Only after trying a couple of name pattern that definitively should have yielded some hits it dawned on me that there might be something else preventing the find program to find the files.

And in fact, find does not follow symbolic links in files systems by default.

Looking into the installation folder on my system I can see that symbolic links are what we use for SAP HANA:

ls -la /usr/sap/T07
total 16
drwxr-xr-x  4 t07adm sapsys 4096 Apr 15  2015 .
drwxr-xr-x 17 root   sapsys 4096 Dec 10 18:30 ..
lrwxrwxrwx  1 t07adm sapsys   22 Apr 15  2015 HDB07 -> /hana/shared/T07/HDB07

Means: without the -L option, find doesn’t go down this path.

Having figured this out, find of course gave me what I was looking for:

find -L /usr/sap/T07 -name 'ConfigMgr*'

And with this information my colleague could have modified the PYTHONPATH variable on his system to fix the problem (which he wrote me is what he has done meanwhile anyhow…).

There you go – now you know.

Happy New Year everyone and have a great 2016!


Changes, News and Annoucement

Dear all

Since there had been quite big changes happening to me (which will affect my engagement here on SCN) I thought I pen down a few lines about that.

Relocation (again)

Looks like I am not the person to stay put in one place for a long time.

But after my move from Austria to Germany in 2013 it was time for me to pack up everything again and board the plane with a one-way ticket to stunning Melbourne, Australia.

Besides the obvious change in my awake-and-working-time zone there’s this view when having a walk during lunch break:

😎 I love that!

My new (corporate) home: Custom Development

This relocation happened with the support and endorsement of SAP, the company I’ve been working for the last 12 years.

My current job here is actually not in SAP HANA development or support or anything like that, but on the other side of the solution building process.

SAP Custom Development is a software development organization within SAP. Sort of a sub-marine team 🙂

Where the large SAP standard software development builds highly standardized, reusable and to-be-configured solution platforms, we’re dealing with the customer requirements that need a different kind of care.

We do the customer specific innovation and deliver software just for that single customer.

Just like a tailer-made suite or a bespoke pair of shoes.

This is very different from the standard business but extremely exciting as we get to look into all the stuff our customers are interested in that is just not possible with the standard solution. We get to use the latest and greatest platforms and processes from SAP – everything from design thinking, over Fiori or Big Data IoT technology. And we go back to the colleagues from standard development to align with them on features and road maps.

The outcome is high quality software that does exactly what you need and fits seamless into your SAP/IT landscape including support and maintenance.

I guess it’s obvious why I find this interesting.

Come and see me!

While I likely will have less time now to answer a lot of questions in the SAP HANA spaces (SAP HANA and In-Memory Computing, SAP HANA Developer Center) I don’t give up on knowledge sharing.

As TechEd’s are happening a bit far off from the Australian continent, there’s going to be a smaller more local event, that still covers the important topics and speakers.

I’ll have the pleasure to step in line with some awesome presenters (Bjoern Goerke, Thorsten Franz or Rich Heilman to just drop a few names) and will deliver some lectures.

If you happen to be in Sydney around 24/25. November 2015 and you’re interested to meet and talk to all those famous folks 🙂 follow the link and register!


Ok, that’s for today.

Enjoy your weekend folks!