Skip to Content

Different Output Composite Provider / Calculation View

Hi Experts,

i recognized that i get different outputs from a calculation view with a left outer join than from a composite provider with the same join. In the following screenshot you can see the left outer join of the dso tables ...D017 and ...D027. The join is a left outer join with four conditions as shown in the screenshot.

The following screenshot shows a composite provider with the same join definition as defined in the calculation view:

When i execute both scenarios i get different results in the measures. There is one measure "epqty" of the left table respectively left dso which shows different values. A measure "epanzfind" (which is of the right table respectively right dso) shows identical values in both scenarios.

Output calculation view:

Output Composite Provider:

The reason for the difference is that there are two rows in the right table d027 for which exists only one record in the left table d017. The measure of the left table is counted two times in the calculation view but only one time in the Composite Provider.

In the following screenshots there is a character "epchkcnt" added to the calculation view as well as to the composite provider which shows the difference on detailled Level:

Output Calculation View:

Output Composite Provider:

In the following screenshots the relevant table entries are shown:

As you can see for dataset with epchkcnt = 00001 there are two records in the right table and one record in the left table. For this record the measure "epqty" is counted two times in the calculation view and only one time (which is correct in my opinion) in the Composite Provider.

Do you know how to specifiy the calculation view in a way that it shows identical results to the Composite Provider.

Thank you in advance.

Kind regards,

Andreas

c20mg.png (115.1 kB)
scc5n.png (44.8 kB)
q2sxd.png (12.8 kB)
bacw1.png (2.2 kB)
kf5xc.png (5.8 kB)
umtc6.png (5.4 kB)
izgzh.png (29.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • May 04, 2017 at 01:49 PM

    Hi.

    Could you show the SQL Console code for both view? I suppose there's something on the FINDING column data for 00001 occurence..

    Hope to help

    Bye

    Add comment
    10|10000 characters needed characters exceeded

  • May 04, 2017 at 02:54 PM

    Hi Roberto,

    thank you for your answer. Can you just tell me how to show the SQL Console for the views? Sorry i'm very new to HANA.

    Kind regards,

    Andreas

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 10, 2017 at 03:38 PM

    Hi,

    Please check if following helps:

    https://looker.com/blog/aggregate-functions-gone-bad-and-the-joins-who-made-them-that-way

    Just a question - do you have "FINDING" column from right table in your composite provider? I can't see in your screenshot. You can remove this column in your calculation view while performing join and add it again via one more join at higher level.

    Thanks,

    Anup

    Edit 1: Note that there is something called "Dynamic Join" in the properties of join. If you set it as true, the fields of source tables are aggregated before applying the join (depending which columns are requested in the query - and I suspect your query is not requesting finding column so the join is not applied on this column in case of dynamic join - which I think is precisely done in case of composite provider)

    Add comment
    10|10000 characters needed characters exceeded