Sap Hana Table Function Recursive Logic

[filiperebollo1986]

Hi Experts,

I am trying to develop a recursive HANA 2.0 Table Function in order to dynamically calculate a lot of metrics, based on a configuration table, having dependencies between each other.

Let´s say the configuration table is filled as follows:

I am trying to create a recursive function because I don´t know how many levels of dependencies this table will hold.
Let´s say the tomorrow I will create a IND6 based on IND4 and IND5, in this case, without using recursion, I would have to create another UNION in my function to get the dependencies and calculated it and therefore I would have to add as many UNIONS as calculation levels (BECAUSE of the dependencies).

Would it be OK to create a table function reading it self?

I tried the following code and it´s working OK, but my concern is to run a more complex code on production and get some errors.

Bellow is the testing code:

FUNCTION TF_RECURSIVE (IP_COUNT INTEGER) 
    RETURNS TABLE
    (   "INDICATOR" VARCHAR(100),
        "TYPE" NVARCHAR(100),    
        "VAL" NVARCHAR(100),
        DEEP_LEVEL INTEGER
    )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN
--DECLARE _COUNT INTEGER = 0;
   CONFIG =  
        Select INDICATOR, TYPE, COMPOSITION, VAL from 
        ( select *, 
       SUBSTR_REGEXPR('(?<=^|,)([^,]*)(?=,|$)' IN CONFIG.COMPOSITION OCCURRENCE "SERIES"."ELEMENT_NUMBER" GROUP 1) "VAL"
  from CONFIG,
       SERIES_GENERATE_INTEGER(1, 1, 10 ) "SERIES" -- replace 10 with your max. number of values in CSV-Field
            )
WHERE VAL IS NOT NULL;
IF :IP_COUNT <= 10 THEN
IP_COUNT := :IP_COUNT + 1;
RETURN 
    
 SELECT A.INDICATOR, A.TYPE, A.VAL, MAX(B.DEEP_LEVEL) + 1 AS DEEP_LEVEL  FROM TF_RECURSIVE(:IP_COUNT) A
        LEFT OUTER JOIN TF_RECURSIVE (:IP_COUNT) B
        ON A.VAL = B.INDICATOR
        GROUP BY
        A.INDICATOR, A.TYPE, A.VAL
    UNION ALL
SELECT A.INDICATOR, A.TYPE, A.VAL, (CASE WHEN A.TYPE = 'PRIMARY' THEN 0 ELSE NULL END) AS DEEP_LEVEL  FROM :CONFIG A
        LEFT JOIN :CONFIG B
        ON A.INDICATOR = B.VAL
        GROUP BY 
        A.INDICATOR, A.TYPE, A.VAL;

END IF;

END;

[lbreddemann · Accepted solution]

"I tried the following code and it´s working OK, but my concern is to run a more complex code on production and get some errors.'"

I would share the same concerns.

This approach is needlessly complex and likely error-prone.

SQL and SQLScript for that matter are languages for manipulating the data in the database (and managing the database of course). What those languages are not very good at is handling "meta"-logic, i.e. code to create templates or examples of how things should operate.

Building a "flexible" or "dynamic" processing logic on top of the - by design - static language constructs of SQL is a proven sure-fire way to pain, misery and disappointment. Another example for this is "One True Lookup Table" (OTLT) and its many different cousins.

Instead of trying to implement the dynamic solution in SQL/SQLScript, why not do what countless BI/Analytics-tool vendors did and create a software that keeps track of the dynamic definitions of the metrics and then creates explicit, static SQL representations of those?

That way, the resulting SQL will be much easier to understand and very likely much better performing.

[filiperebollo1986 · Accepted solution]

Hey Lars,

You are totally right and I am already feeling the symptoms of a wrong choice. Also the OTLT article you´ve mentioned is amazing, it describes exactly what I am walking trough.

Anyway, lesson learned!!

By the way, I´ve just watched your "[SOT213] If your SQL reads like Assembler you're doing it all wrong" and it is amazing as well.

Thanks a lot for your time.

If you allow me I have one more question:

I´ve seen in your presentation many snippets for creating time frames such as '< 30 days', 'Between 30 and 60 days', '> 60 days', 'Year to Date', 'Previous Month' and so on. Wouldn´t it be easier/correct/best practice/effortless bringing these abstractions to the front end? As well as creating calculated columns, restricted key figures, aggregated key figures, conversion, or any other metric?

Filipe

[lbreddemann · Accepted solution]

Thanks again, Filipe!

For those interested in the mentioned SAP Online Track session, here is the link: https://www.lbreddemann.org/sap-online-track-2020/.

And since I already answered your additional questions on LinkedIn, I post them here as well for the benefit of others:

"thanks a lot for the positive feedback on SOT talk. It's amazing to hear that this is useful to developers like yourself!

Concerning the question... you know that I have to say "it depends" right? 🙂 Going off your profile, I am guessing you work with SAC a lot. That means, that you care about that the HANA data models support the requirements of the SAC stories well, are general enough to support a range of stories, and perform well enough to allow interactive analysis. These requirements sometimes don't go together easily. For the presentation I chose to implement the different day-ranges in the query itself - assuming only little further processing of the result set would be required.

I did include potential further changes (if my memory serves me well), where those day-ranges weren't part of the result anymore and that may be a better approach, for cases where additional filtering or a flexible setup of the day-ranges is required.

Another aspect is how flexible (in terms of how often something should be changed) those measures need to be. Very often, the main effort in change happens in the early solution phase, where requirements change a lot and the solution needs to be changed a lot too. Later, for many SAC scenarios, there is only little need for flexibility. I once worked on a project that computed medical keyfigures based on a standard formal keyfigure declaration (effectively an XML file describing the formula). The project build a parser for the XML that extracted the key figure logic and then created - for each keyfigure - a set of table functions and views in HANA. To get the results of the keyfigures, all one had to do then was to call the top-most table-functions with the appropriate filter variables. As the result set structure was stable, no further changes to the front end were required. And whenever the keyfigure definition got adjusted, the table-functions would just be recreated.

So, you see, it really "depends" on the specific requirements and what you and your team are technically able to deliver. The solution I mentioned above was a multi-person team with at least one PhD in Computer Science... not something that "one just cobbles together on a weekend".

If my guess about the SAC requirement is correct, then my tip would be to ensure that - the data model on HANA is absolutely clear to you (how it works, why it is the way it is, etc.) - it is clear what part of the SAC model needs what data and where it gets it from the HANA data model - the result sets produced in HANA and send to SAC are small(!)

I cannot overstate the importance of the last point - it is by far the single most issue I have seen with bad performing queries in HANA: too much data sent to the client.

Anyhow, I usually don't do Q&A on LinkedIn (since it's just 1:1 and not available to others) but I do hope this helps you a little bit."