The 'Keep Flag' property -- How does it work? Why does it work?

[cgwaters]

Within my calculation views I had been encountering problems properly aggregating the measures, when certain attributes were excluded from the result set. An SAP consultant recommended setting the attributes' KeepFlag property to True; that resolved the problem, but I'm not sure why.

The help feature within HANA Studio doesn't mention the Keep Flag property. I haven't been able to find much information about this property online, either.

states:"The Keep flag will always add [the specified] columns in the group by clause even if they are not selected in the query." This is a good start; however, is there any additional documentation regarding this property?

[Former Member · Accepted solution]

Hi Chris,

The only additional documentation I know of (which I think is sufficient - there's not much else to the "Keep Flag" property) is found in the HANA Data Modeling guide, found at help.sap.com/hana_appliance.

You can set the Keep Flag property to True for attributes in an aggregation node, to force those 
columns to be retrieved from the database even when not requested in the query. In other words, 
you are including those columns into the group by clause even if you do not select them in the 
query. To set the Keep Flag property to True for attributes in an aggregation node, choose an 
attribute in the Details Panel, and in the Properties Panel set the Keep Flag property to True .

[cgwaters · Accepted solution]

Thanks, Jody. That explanation is helpful. So should Keep Flag be set to True for all of the columns that the user could potentially exclude from the result set? To illustrate: Given the following sample data:

PRODCODE   LOCATION   QUANTITY

12345      A                 1

23456      B                10

34567      C               100

12345      A                 1

If the result set excludes LOCATION, the aggregated results are incorrect; i.e.,

PRODCODE   QUANTITY

12345             1

23456            10

34567           100

The second occurence of 12345 / A / 1 is inexplicably excluded. Setting LOCATION's Keep Flag property to True restores the second occurrence--resulting in the correct quantities; i.e.,

PRODCODE   QUANTITY

12345             2

23456            10

34567           100

Of course, calculation views typically have many more attribute columns. Should the Keep Flag property be set to True for every one of the columns that the user could potentially exclude from the result set?

[Former Member · Accepted solution]

Hi Chris,

Can you check if Quantity comes from a base table, or from another model? If it comes from another model - is it defined as a measure or dimension in that model? And in your current CalcView - is it defined as an attribute or a measure in the semantics node? The reason I ask is that HANA will aggregate dimensions incorrectly - it only considers unique values. (Side note: I plan on blogging about this and other common pitfalls in the near future.) So please double-check from source table to final model semantics node, that attributes and measures are classified correctly.

I would be hard pressed to believe HANA is returning incorrect aggregation results from a CalcView for a simple aggregation. You most certainly should not have to enable "Keep Flag" for all of your attributes. The point of "Keep Flag" is to ensure that calculations before aggregation (or at some intermediate level of calculation) are calculated correctly.

[former_member184768 · Accepted solution]

Hi Chris,

I agree with Jody here. The 'Keep flag' property is not expected to be used for a simple aggregation scenario, but exception aggregation where some calculation is expected before the measures are aggregated.

Depending on your requirement, you can also look into the option of pre-calculating the values and persist it in the underlying table. That will help from the performance perspective also.

Regards,

Ravi

[rajarshi_muhuri · Accepted solution]

Ravindra/ Jody : sorry to hijack this thread

I have a situation where I need counts not to aggregate , can exception aggregation help .

I have posted the question on

[former_member204768]

Hi Chris,

I am too late... but replying just in case you are still interested to know.

Go to SAP Note  1764658

Read, "HanaCEInstantiationProcess.pdf" from the attachment section

[rajarshi_muhuri]

That SAP note has a mine of good information