Playing with HANA Parameters

The purpose of this blog was to share two end to end examples of passing parameters within HANA and using placeholders in SELECT statements.

I have created two scenarios:

1. HANA Table –> Table Function (with parameters) –> Calculation View (with parameters)
2. HANA Table –> Calculation View (with parameters) –> Procedure (with parameters)

The biggest lesson I learnt was to decide up front what you want to call your parameters and not to make any typos when creating them, I had misspelled one in the Table Function and it took me quite a while to fix as HANA Studio seemed to have a memory of the old parameter name and I struggled to get objects to activate as they were linked. This struggle could also be due to my HANA Studio version or even playing with parameters too late in the evening.

For both scenarios I have used the M_FISCAL_CALENDAR table from the _SYS_BI schema, it is a standard delivered table which can be filled with the “Time Generation” function within HANA Studio.

When displaying the contents of the table you will see the following columns relevant to our example: CALENDAR_VARIANT, FISCAL_YEAR and FISCAL_PERIOD. We have hardcoded the variant to V6 and will be using parameters to retrieve the dates for a specific Fiscal Year and Fiscal Period.

Scenario 1: HANA Table to Table Function to Calculation View

To start off I created a Table Function TF_DEMO_FISCAL with two input parameters: P_FISCYEAR and P_FISCPER. We then quite simply use the input parameters in the WHERE clause when selecting from the M_FISCAL_CALENDAR table.

The Table Function TF_DEMO_FISCAL can then be added to a projection node in our calculation view CV_DEMO_FISCAL. As our Table Function requires input parameters, I have created two input parameters: P_FISCYEAR and P_FISCPER on the Semantics of the calculation view.

Once the parameters are created select the projection node that contains the Table Function TF_DEMO_FISCAL so that the Output pane for the node is visible on the right hand side. The parameters that I created on the Semantics node are visible, right click on the “Input Parameters” folder and select “Manage Mappings”.

This allows us to map parameters coming from the Table Function (on the left) to parameters in the calculation view (on the right).

Save and activate the calculation view and then go to data preview. This will bring up the “Variables and Input Parameters” dialog where I can now select Fiscal Year 2018 and Fiscal Period 1.

As expected, the parameters were passed down to the Table Function and only the required data was retrieved i.e. 31 records for July.

At this point you can press the “Show Log” to see the SQL that was executed whilst showing you the data, this is useful as we can see that placeholders were used to pass parameters to the calculation view and we will use similar coding if we want to call the calculation view from other procedures.

Generated SQL:

SELECT TOP 5000″FISCAL_YEAR”, “FISCAL_PERIOD”, “DATE_SQL”, sum(“Count”) AS “Count” FROM “_SYS_BIC”.”system-local.private.dorothy8/CV_DEMO_FISCAL”(‘PLACEHOLDER’ = (‘$$P_FISCYEAR$$’, ‘2018’), ‘PLACEHOLDER’ = (‘$$P_FISCPER$$’, ‘1’)) GROUP BY “FISCAL_YEAR”, “FISCAL_PERIOD”, “DATE_SQL”

Scenario 2: HANA Table to Calculation View to Procedure

In the second scenario we pull table M_FISCAL_CALENDAR directly into the calculation view and still have the same two parameters: P_FISCYEAR and P_FISCPER.

Going to the Projection_1 node (which contains M_FISCAL_CALENDAR) we have applied three filters to the data:

1. A hardcoded filter on CALENDAR_VARIANT to V6

2. A parameter filter on FISCAL_YEAR setting it to P_FISCYEAR

3. A parameter filter on FISCAL_PERIOD setting it to P_FISCPER

Note that the parameter name is surrounded by $$.

Save and activate the calculation view and then go to data preview. This will bring up the “Variables and Input Parameters” dialog where I can now select Fiscal Year 2018 and Fiscal Period 1 and on execution the expected data is shown.

I then create a procedure called PR_DEMO_FISCAL in which we will select from the calculation view. The procedure has two parameters: P_FISCYEAR_PROC and P_FISCPER_PROC.

Note: The names need to be different to the parameter names in the calculation view. If you make the parameter names the same then it will not work and will give you the random error: “Could not create catalog object: argument type mismatch; Can’t use column expression as column view parameter:” Having said that, I consistently got the strange error, then I made the parameter names different and it started working, when I made them the same to test original problem it disappeared. No idea why! Thank you to a comment from Kostia Kharchenko (@kostyah) on 2015 post for solving this for me.

Then I executed the procedure and select Fiscal Year 2018 and Fiscal Period 1.

In this procedure I asked for the max date for selection and it correctly returns 31.07.2017