Building a complex Calculation View on top of other Calculation Views
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
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
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.