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 ************/
BEGIN
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:
SELECT
"DIM10", "DIM100", "DIM1000", "DIM1000000",
"KF1", "KF2"
FROM "_SYS_BIC"."devTest/MULTIIP"
('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)
but
"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).
Interlude
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 ************/
BEGIN
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:
SELECT
"DIM10","DIM100","DIM1000", "DIM1000000",
"KF1","KF2"
FROM FACT
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:
SELECT
"DIM10","DIM100","DIM1000", "DIM1000000",
"KF1","KF2"
FROM "_SYS_BIC"."devTest/MULTIIP"
('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?
No?
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!
Lars