WHY HANA VIEW ARE GIVING BAD PERFORMANCE IN DASHBOARD THEN TABLE

[Former Member]

Dear Experts ,

I have created a dashboard on top of HANA view.

Scenario 1

HANA view is taking 3 seconds to fetch data in HANA studio .

When we create dashboard on that view, the dashboard  is taking around 50sec.

Report has around 10 queries.

Scenario 2

Then we insert data into a table from same above view .

HANA table is taking 10 ms to fetch data in HANA studio .

When we create the same dashboard on that table, the report is taking

around 4 Seconds.

Report has around 10 query. 

You may raise finger on dashboard tool. But by only converting view to table dashboard is giving good performance .

Please suggest what  should we use Table or View for reporting tool ????

Only performance matter to Users and that's why we have taken HANA.

Thanks

Anupam.

[pratik_doshi2]

Dear Anupam ,

When you use attribute view in calculation view, hana will first build the attribute view first it will process all the data from memory in attribute view and then only will proceed with the calculation view.

So in this case you should build functionality specific view. Try to put all the filter conditions at lower level.

Tell me you are using any attribute view and try with the above option.

Using tables in calculation will give better performance.

Regards ,

Pratik Doshi

[justin_molenaur2]

"When you use attribute view in calculation view, hana will first build the attribute view first it will process all the data from memory in attribute view and then only will proceed with the calculation view."

That's actually completely false.

Attribute views are not materialized in full every time an Analytic View is queried. On the contrary, only the joins/materialization required to fulfill the query are performed. I have rolled out Analytic views with 50+ attribute views joined to them, if every one was built every time the AV was used, that would be a disaster.

An attribute view is fully executed if you query it directly with SQL, but the behavior changes when used in an AV.

Quick observation in good faith of my statement.

Query an analytic view with one column from a joined attribute view (Material - MARA)

SELECT "PAPH1", SUM("VVREV")

FROM "_SYS_BIC"."Main.Analytics.CO/AN_COPA"

WHERE "PERIO_T" = '2013010'

GROUP BY "PAPH1"

Only one join is executed to the fact table

Query an analytic view with one column from a joined attribute view (Material - MARA), plus another column from a joined table (T179T) inside the attribute view (snowflaked dimension).

SELECT "PAPH1", "VTEXT_1", SUM("VVREV")

FROM "_SYS_BIC"."Main.Analytics.CO/AN_COPA"

WHERE "PERIO_T" = '2013010'

GROUP BY "PAPH1", "VTEXT_1"

Only two joins are executed; one to the snowflaked dimension and one to the fact table

So even though I am issuing a query against an AV with a very large number of dimensions, only what is needed is actually executed.

Happy HANA,

Justin

[former_member182302]

Did you mean that you have created a table and loaded the data from HANA View ( Which one Attribute/Analytic/Calculation View? )

If yes you are materializing the data into a table ( Something like materializing a aggregate ) that would obviously have performance benefits as it becomes a simple select when you compare to the query that should be used against a view to do a Run-time "Aggregation" right?

As Justin suggested please show more details to get help from SCN. There more you share , the better answer you get

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

Yes, I have created a table and loaded the data from HANA View

I have provide more information to Justin .

Please let me know what more information you want .

Thanks

[justin_molenaur2]

What dashboarding tool are you using?

You say the "report" has around 10 queries, are they all sourced from this same view? If not, have you managed to isolate the problematic query or are you sure it's the HANA view? What happens when you isolate them one at a time?

There inherently is overhead when passing to another tool (I assume BOE), but from the info you provided its near impossible to help, please provide more details.

Regards,

Justin

[Former Member]

Dear Justin,

All the 10queries of dashboard are on same view .

The  view is a HANA calc view with aggregation and calculation.

For this case Reporting tool is BOE itself . We have tested the same scenario on IBM Cognos 10 .

On Both reporting  Tool performance is nearly the same .

By only creating a table and loaded the data from same HANA View, we have seen significant improvement in dashboard performance .

Should we use table always instead of view for reporting purpose ?

Please let me know what more information you want?

Thanks

Anupam

[justin_molenaur2]

I would avoid another persistent table if you can.

You say that the 'query' executes in 3 seconds in Studio. Is this executing all 10 queries that are called from your dashboard or is this one single query that you are using for reference? Point being - if you see one query return in 3 seconds in Studio, and you have 10 queries in the dashboard I can only guess what happens (3x10 = 30sec plus BOE overhead) . Just make sure you are testing apples to apples when making statements like that.

You haven't told us any data volumes.

You have shown us any queries.

You haven't shown any details on the calc view.

Why do you have 10 queries on the same view in the dashboard? This seems like overkill.

I would take the following actions in this order

- Eliminate unneeded query calls where possible, can you consolidate?

- Determine which dashboard query is the longest running and attempt to optimize query

- From previous step, look for optimizations in the model itself. Have you considered all modeling optimizations?

Regards,

Justin