Building a complex Calculation View on top of other Calculation Views

[Former Member]

Hi Guys,

Do you have experience with building complex models using HANA Studio? I would like to hear your opinion about my case.

Currently we have a model running on a traditional relational DB, where we have raw fact data table and reference data tables.

The raw fact data is transformed in multiple steps, where intermediate results are stored in DB tables and are later used in the next steps.

After the last step of the processing we get output fact table, which is used as a basis for reporting.

Just to visualize that:

Raw_fact_data + reference_tab_1 (using join) + reference_tab_2 (using join) = intermediate_table_A

intermediate_table_A + reference_tab_3 (using join) = intermediate_table_B

intermediate_table_B + reference_tab_4 (using join) + reference_tab_5 (using join) = intermediate_table_C

etc.

result = intermediate_table_k

In HANA we would like to check if the same functionality could be achived "on-the-fly", so all the processing steps would be done during end user report execution.

Raw_fact_data + reference_tab_1 (using join) + reference_tab_2 (using join) = Calculation_View_A

Calculation_View_A + reference_tab_3 (using join) = Calculation_View_B

Calculation_View_B + reference_tab_4 (using join) + reference_tab_5 (using join) = Calculation_View_C

etc.

result = Calculation_View_k

What do you think of building a "cascade" HANA model, where Calculation Views are built on top of other Calculation Views joined with Column Tables? In each of the Calculation Views we have operations that are rather not well seen by HANA - Calculated Columns and some String operations.

We have about 20 quite complex steps, so I am worried about the performance of this non-staging approach. Some of the steps will be implemented using Graphical CV and some using SQLScript CV.

Additional question: is there any way to find the most time-consuming operation in these Calculation Views? With Explain Plan option it is not so easy in this case.

Best regards,

Leszek

[patrickbachmann · Accepted solution]

Hi Leszek,

At first glance, yes it looks like you can do your scenario via script type calculation view.  I don't see any glaring road blocks in your example.  I have done something very similar that performs calculations against a view and returns a couple of columns, then do a second pass against a source table, joining those results to yet another view and so on until I've had about 5 separate steps and then finally a CE_JOIN on all of my results.  In my case I don't have any performance issues.

As for the explain plan, have you tried looking below that at Visualize Plan? If you run this option and then you will see a high level summary flow and if you then execute that plan again you will get a lot more excellent detail.  You can see how many records are passed in each process etc

I hope this helps,

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

Thanks for your answer. The model I am buidling, is performing quite good now.

I had some performance issues due to usage of Calculation View (SQLScript type). It turned out that if you have output structure containing many fields in your scripted Calculation View (in my case 70) and you use SQL statements, not CE functions, then HANA cannot handle well field pruning. It passes too many fields and records between Calculation Views. I separated part of that logic that must be done using script and I limited the number of fields there. In the next step a Graphical Calculation View joins data coming from scripted CV with original fact table. The performance changed from 10minutes to 20seconds!

I tried to measure performance using Explain Plan, but it doesn't allow to drill into details of select from Calculation View, so it gives only very high level information. But Visualize Plan is a great tool, thanks for that.

Regards,

Leszek

[patrickbachmann · Accepted solution]

Hi Leszek, that's excellent!  And thanks for the update, that's very interesting indeed.  Glad to help.

-Patrick

[Former Member · Accepted solution]

Hi Lazek,

Your approach work pretty well, but if you build your lower level calculation views and pass input parameters and then if you decide to use that calculation view (which has input parameters) in a CE Function then youwill quickly run into the limitation on CE Functions. But if your Calc Views are not going to use any input parameters, then there wont't be any performance issues. I am doing very similar to what you are doing they work well.

Good luck,

Arthur.