How to select data from analytic views efficiently

[Former Member]

Dear Experts,

I face a challenge to select data from analytic view efficiently. I have one analytic view which as 2 tables VBRK/VBRP(data foundation) and one attribute view linked for the variant configuration IBINVALUES IBIN IBSYMBOL.

If I write the select and in the WHERE statement I use field from VBRK/VBRP as filter I got the result in 1 second 1200 lines with the characteristic (VC) included.

Now my problem is that if I want to further restrict the result set and add a filter on the VC additionally to the WHERE statement, I got 212 lines in 9 seconds, I would expect that HANA would be faster. It seems that the entire attribute view for the variant configuration gets materialized before it is linked to the VBRK/VBRP tables.

Do you know any way how to fix this or build better the views?

I would like to have first the VBRK/VBRP select done and only for those result the attribute view should be executed using the additional filter on it.

SELECT "VBELN","VKORG", "Year","MILL", round(sum("FKLMG")) AS "FKLMG"

FROM "_SYS_BIC"."test_proc.procedure.mp.views/InvoiceData"

WHERE "Year" = '2013' and VKBUR = 'HUNG'

1200 lines in 1 second. Year and VKBUR is coming from VBRK/VBRP data foundation.

SELECT "VBELN","VKORG", "Year","MILL", round(sum("FKLMG")) AS "FKLMG"

FROM "_SYS_BIC"."test_proc.procedure.mp.views/InvoiceData"

WHERE "Year" = '2013' and VKBUR = 'HUNG' and MILL = 'HU'

212 lines in 9 seconds. The MILL field is coming from the attribute view.

thanks for your advice

Tamas

[former_member184768]

Hi Tamas,

Couple of points:

1) Can you please check the visual plan for your query and confirm that the filter for VBRK / VBRP is not pushed down and it is generating the join for the entire data set.

2) Can you please check if you can add a variable for MILL in the attribute view. Even if it would generate the filter in the where condition, but worth checking the performance impact.

3) In your first statement, the MILL is selected in the SELECT clause, which means the join to the attribute view is getting executed in first statement also. The visual plan for both the statements can help you identify how big is the result set is generated and where the execution time is increasing in the second statement.

With the information from the visual plan, I am sure it will be easy to identify the performance bottleneck and steps to be taken to improve the performance.

Regards,

Ravi

[Former Member]

Hi Ravi,

It is now bit better, the speed different is now only double. So one is running in 1.5sec, with MILL it is 3seconds instead of 9seconds.

1. I checked the visual plan for both of the selects and the WHERE statement is filtered/executed on the lowest level. No difference there

2. I tried with variable assigned to the MILL column in the analytic view already at modelling phase, but that slowed down the select. It was 3.5seconds the same result.

3. The visual plan was not showing too much different between the select execution.

     The column search has 3 steps Column view / aggregation / projection. Only different is that with the MILL removed from the select where statement we have 4 rows between the steps, otherwise 1.

The cost is 17,487%  in  both cases.

BR
Tamas

[former_member182114]

Hi Tamas,

If the visual plan is not showing too much difference, probably you forgot to "Execute" again after visual plan, see on this Lars blog how to do it.

http://scn.sap.com/community/hana-in-memory/blog/2012/12/20/show-me-the-timelines-baby

Don't know why we need this step (because we always need to execute it again), but it how it works today.

Regards, Fernando Da Rós