Skip to Content

This blog is to explain a common problem which occurs when we try to add an alphanumeric column as a measure (with ‘COUNT’ aggregation) in graphical views.

Problem Description


For example, if we have two views – VIEW_1 and VIEW_2, each having 2 alphanumeric columns “A” & “B”.

And if we are trying to get a count of distinct values of column “B”, grouped by the unique values of column “A”, we would normally build a graphical view, similar to the one shown below.

intiial_flow.PNG

View_1.PNGview_2.PNG

In this case lets assume that we have only two unique values (‘X’ & ‘Y’) for COLUMN “A”, across VIEW-1 & VIEW-2.


From an SQL perspective, we would expect HANA to perform the following query


SELECT A, COUNT(B) FROM

(

     SELECT A, B FROM VIEW_1

     UNION

     SELECT A, B FROM VIEW_2

)

GROUP by A;

But the graphical view data preview will always give the following result, whatever values are present in the views.

DATAPREVIEW.PNG

This result is definitely wrong.

Reason

The HANA optimizer, whenever possible, tries to always push down the aggregation/filters to a lower level node to reduce the number of rows transferred across levels/nodes.

So, in our case, HANA optimizer tries to push the ‘COUNT’ aggregation down.

/wp-content/uploads/2014/10/flow_553216.png

And the following property setting – “ALWAYS AGGREGATE RESULT” if set to ‘TRUE’, always enforces a final aggregation in the semantics node.

This wouldn’t be a problem for aggregation types like SUM, MAX and MIN.

Ex:  SUM ( SUM(A,B,F), SUM(C,D) ) is same as SUM(A,B,C,D,F)  or

        MAX ( MAX(A,B,F), MAX(C,D) ) is same as MAX(A,B,C,D,F)  or

        MIN ( MIN(A,B,F), MIN(C,D) ) is same as MIN(A,B,C,D,F)

BUT COUNT ( COUNT(A,B,F), COUNT(C,D) )  => COUNT ( 3, 2 ) => 2, which is definitely wrong vs COUNT(A,B,C,D,F) => 5

pre_aggre_true.PNG

So what actually gets executed, in the graphical view is the following query –

SELECT A, COUNT(B) AS B FROM

(

     SELECT A, COUNT(B) AS B FROM

     {

          SELECT A, B FROM VIEW_1

          UNION

          SELECT A, B FROM VIEW_2

     }

)

GROUP by A;

Resolution:

I guess the resolution is evident by now 🙂

For COUNT aggregation on alphanumeric values, switch “ALWAYS AGGREGATE RESULT” to ‘FALSE’. to get accurate results,

always_aggre.PNG

or even simpler, use the COUNTER feature provided by HANA  🙂

Regards

Ajay

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. rajesh kumar

    HI Ajay,

    Nice document

    i have created one table in HANA and now i want to create a Counter for the same.

    can you please help how to achieve this

    Regards

    Raj

    (0) 
  2. rajesh kumar

    Hi Ajay

    thanks for the quick post,

    i didnt created any Views on top of my table.

    so without view we cont go for creating counter and we con’t get the output panel in the right side.

    please correct me if im wrong.

    Regards

    RAj

    (0) 

Leave a Reply