Have you ever tried to expose a BW query with non-cumulative key figures as External SAP HANA View? Then, you probably bumped into the error message “Unkeep Attribute could not be removed”. This blog explains why the error message is raised and how it can be avoided under certain conditions.
Special credits go to Dr. Markus Sinnwell, SAP BW/4HANA development. He co-invented the concepts of non-cumulatives in SAP BW and SAP BW/4HANA, transformed them into code and wrote the initial version of this blog.
The error message “Unkeep Attribute could not be removed” is thrown when a time characteristic is used in the WHERE condition of a SELECT statement against an External SAP HANA View with non-cumulative key figures and this time characteristic is not in the field list of the SELECT and GROUP BY clause.
1. Non-cumulative key figures are not persisted but calculated at query runtime based on delta movements and reference points. Therefore, it is not possible to evaluate filter conditions on time characteristics before the non-cumulative key figures are calculated by the L node of the calculation scenario.
2. Because of (1) a filter on a time characteristic must be applied after the computation of the non-cumulative key figures.
3. Because of (2) the time characteristic on which a filter is defined must be processed by the L node of the calculation scenario as if it had been selected (as part of the field list in the SELECT and GROUP BY clauses).
4. All records which match the filter condition are then passed to the standard SQL/calculation scenario processing and there, the specified aggregation is used to aggregate records with the same “key” (according to the user specified SELECT and GROUP BY clause).
This would, in many cases, result in wrong query results because the special handling for aggregation of non-cumulative key figures over time is not considered in (4).
To avoid such issues, the generated External SAP HANA View uses the modeling property ‘Transparent Filter’ on all time characteristics. This in turn leads to the error message “Unkeep Attribute could not be removed” when a time characteristic is not part of the SELECT and GROUP BY clause but filtered and the Calculation Engine fails removing that time characteristic from the internal drilldown.
Under specific conditions the error message “Unkeep Attribute could not be removed” can be suppressed:
Queries with filter on time characteristic and ‘finer’ time characteristic added to GROUP BY
Assumed a query uses a time characteristic in the SELECT and GROUP BY clause which is ‘finer’ than the time characteristic on which the filter is defined. In this special scenario, there is no need for an aggregation over time after the application of the filter and therefore, the result of the query is correct.
A time characteristic A is ‘finer’ than a time characteristic B if for each value of A, it is possible to uniquely determine a single value of the characteristic B, e.g. 0CALMONTH is finer than 0CALYEAR, but 0CALWEEK is NOT finer than 0CALMONTH.
In the query execution we do not recognize this special scenario and therefore, even in this case, the query ends up in the above-mentioned error message. It is possible, however, to suppress the error by setting a parameter via the placeholder syntax as shown below in the sample queries.
Queries with filter representing a single value equal condition
Queries with a filter representing a single value equal condition might cause the error “Unkeep Attribute could not be removed” although the requirement is considered in the query i.e. the query uses a filter on a time characteristic and that time characteristic is in the SELECT and GROUP BY clause. In that scenario, the SQL optimizer may transform the GROUP BY clause internally and remove the characteristic from the internal GROUP BY. Thus, the error message is triggered.
As a workaround, in this scenario, the error message can be suppressed, either by adding a parameter via the placeholder syntax as shown below in the sample queries – or by adding the hint NO_GROUPING_SIMPLIFICATION to the query.
Disclaimer
The SAP HANA placeholder parameter (‘PLACEHOLDER’ = (‘ce_settings’,'{“disabled_patterns”: “39”}’)) is an internal parameter and may only be used in the context described here. Using such a parameter requires that
1. the described error situation has occurred and
2. you have verified that the query returns the expected result and
3. the parameter is not used as system-wide configuration but only in SELECT statements for specific queries. Using internal parameters outside of this given scope is not supported.
The InfoProvider contains a non-cumulative key figure ‘STOCK’ (with aggregation LAST) and a delta key figure ‘DELTA’ is assigned to ‘STOCK’.
The InfoProvider contains the records shown below in the inbound queue table. The requests in the InfoProvider have not been activated yet.
A query without filter on a time characteristic shows the expected result.
Then, we remove all records that do not match the filter condition. This step leads to:
Now, as the filter has been applied, we would remove the column 0CALMONTH as it is not in the field list of the SELECT and GROUP BY clause:
And finally, we would get the result of the query by aggregating all records with the same values for the GROUP BY characteristics using the given aggregation (sum(“STOCK”)):
Thus, the query would return a wrong result: STOCK= 280
By adding 0CALMONTH to the field list of the SELECT and GROUP BY clause, the query result is calculated like this:
Queries with a filter representing a single value equal condition might cause the error “Unkeep Attribute could not be removed” although the query uses a filter on a time characteristic and that time characteristic is in the SELECT and GROUP BY clause. In that scenario, the SQL optimizer may transform the GROUP BY clause internally and remove the characteristic from the internal GROUP BY. Thus, the error message is triggered.
Acknowledgement
Special credits go to Dr. Markus Sinnwell, SAP BW/4HANA development. He co-invented the concepts of non-cumulatives in SAP BW and SAP BW/4HANA, transformed them into code and wrote the initial version of this blog.
When is the error raised?
The error message “Unkeep Attribute could not be removed” is thrown when a time characteristic is used in the WHERE condition of a SELECT statement against an External SAP HANA View with non-cumulative key figures and this time characteristic is not in the field list of the SELECT and GROUP BY clause.
Why is the error raised?
1. Non-cumulative key figures are not persisted but calculated at query runtime based on delta movements and reference points. Therefore, it is not possible to evaluate filter conditions on time characteristics before the non-cumulative key figures are calculated by the L node of the calculation scenario.
3. Because of (2) the time characteristic on which a filter is defined must be processed by the L node of the calculation scenario as if it had been selected (as part of the field list in the SELECT and GROUP BY clauses).
4. All records which match the filter condition are then passed to the standard SQL/calculation scenario processing and there, the specified aggregation is used to aggregate records with the same “key” (according to the user specified SELECT and GROUP BY clause).
This would, in many cases, result in wrong query results because the special handling for aggregation of non-cumulative key figures over time is not considered in (4).
To avoid such issues, the generated External SAP HANA View uses the modeling property ‘Transparent Filter’ on all time characteristics. This in turn leads to the error message “Unkeep Attribute could not be removed” when a time characteristic is not part of the SELECT and GROUP BY clause but filtered and the Calculation Engine fails removing that time characteristic from the internal drilldown.
When can the error message be suppressed?
Under specific conditions the error message “Unkeep Attribute could not be removed” can be suppressed:
Queries with filter on time characteristic and ‘finer’ time characteristic added to GROUP BY
Assumed a query uses a time characteristic in the SELECT and GROUP BY clause which is ‘finer’ than the time characteristic on which the filter is defined. In this special scenario, there is no need for an aggregation over time after the application of the filter and therefore, the result of the query is correct.
A time characteristic A is ‘finer’ than a time characteristic B if for each value of A, it is possible to uniquely determine a single value of the characteristic B, e.g. 0CALMONTH is finer than 0CALYEAR, but 0CALWEEK is NOT finer than 0CALMONTH.
In the query execution we do not recognize this special scenario and therefore, even in this case, the query ends up in the above-mentioned error message. It is possible, however, to suppress the error by setting a parameter via the placeholder syntax as shown below in the sample queries.
Queries with filter representing a single value equal condition
Queries with a filter representing a single value equal condition might cause the error “Unkeep Attribute could not be removed” although the requirement is considered in the query i.e. the query uses a filter on a time characteristic and that time characteristic is in the SELECT and GROUP BY clause. In that scenario, the SQL optimizer may transform the GROUP BY clause internally and remove the characteristic from the internal GROUP BY. Thus, the error message is triggered.
As a workaround, in this scenario, the error message can be suppressed, either by adding a parameter via the placeholder syntax as shown below in the sample queries – or by adding the hint NO_GROUPING_SIMPLIFICATION to the query.
Disclaimer
The SAP HANA placeholder parameter (‘PLACEHOLDER’ = (‘ce_settings’,'{“disabled_patterns”: “39”}’)) is an internal parameter and may only be used in the context described here. Using such a parameter requires that
1. the described error situation has occurred and
2. you have verified that the query returns the expected result and
3. the parameter is not used as system-wide configuration but only in SELECT statements for specific queries. Using internal parameters outside of this given scope is not supported.
Sample Queries
The InfoProvider contains a non-cumulative key figure ‘STOCK’ (with aggregation LAST) and a delta key figure ‘DELTA’ is assigned to ‘STOCK’.
The InfoProvider contains the records shown below in the inbound queue table. The requests in the InfoProvider have not been activated yet.
| 0CALDAY (YYYY-MM-DD) |
0CALMONTH (YYYY-MM) |
0MATERIAL | DELTA |
| 2019-01-01 | 2019-01 | A | 100 |
| 2019-01-05 | 2019-01 | A | 10 |
| 2019-02-03 | 2019-02 | A | 30 |
| 2019-04-05 | 2019-04 | A | -20 |
Query without Filter on Time Characteristic
A query without filter on a time characteristic shows the expected result.
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
GROUP BY
"0CALMONTH",
"0MATERIAL"
Result:
| 0CALDAY (YYYY-MM-DD) |
0CALMONTH (YYYY-MM) |
DELTA | STOCK |
| 2019-01-01 | A | 110 | 110 |
| 2019-01-02 | A | 30 | 140 |
| 2019-02-03 | A | 0 | 140 |
| 2019-04-04 | A | -20 | 120 |
Query with Filter on Time Characteristic and Characteristic not added to GROUP BY
Assumed we would allow to add a filter on 0CALMONTH without adding 0CALMONTH to the field list in the SELECT and GROUP BY clause, the query result would be calculated like this:
SELECT
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
WHERE
"0CALMONTH" BETWEEN '201902' AND '201903'
GROUP BY
"0MATERIAL"
If we pushed down the filter through the L node of the calculation scenario which computes the non-cumulative key figures, then only the records matching the filter condition would be considered, in this example the record with date 2019-02-03. This would lead to wrong query results because also delta movements in 2019-01 must be considered to calculate the stock value. Therefore, it is not possible to push down the filter. We must apply the filter after the computation of the non-cumulative key figures.
To be able to filter after the computation of the non-cumulative key figures, we must add the filter characteristic to the internal drilldown of the intermediate result set.
Therefore, the first intermediate result after calculating the non-cumulative key figures is the same result that we have seen above, when selecting 0CALMONTH in the query:
| 0CALDAY (YYYY-MM-DD) |
0CALMONTH (YYYY-MM) |
DELTA | DELTA |
| 2019-01 | A | 110 | 110 |
| 2019-02 | A | 30 | 140 |
| 2019-03 | A | 0 | 140 |
| 2019-04 | A | -20 | 120 |
Then, we remove all records that do not match the filter condition. This step leads to:
| 0MATERIAL | 0MATERIAL | DELTA | STOCK |
| 2019-02 | A | 110 | 140 |
| 2019-03 | A | 30 | 140 |
Now, as the filter has been applied, we would remove the column 0CALMONTH as it is not in the field list of the SELECT and GROUP BY clause:
| 0MATERIAL | DELTA | STOCK |
| A | 30 | 140 |
| A | 0 | 140 |
And finally, we would get the result of the query by aggregating all records with the same values for the GROUP BY characteristics using the given aggregation (sum(“STOCK”)):
| 0MATERIAL | DELTA | STOCK |
| A | 30 | 280 |
Thus, the query would return a wrong result: STOCK= 280
The correct result for STOCK with aggregation LAST in the interval (2019-02,2019-03) is: 140
Query with Filter on Time Characteristic and Characteristic added to GROUP BY
By adding 0CALMONTH to the field list of the SELECT and GROUP BY clause, the query result is calculated like this:
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
WHERE
"0CALMONTH" BETWEEN '201902' AND '201903'
GROUP BY
"0CALMONTH",
"0MATERIAL"
As explained for the previous sample query, it is not possible to push down the filter through the L node of the calculation scenario which computes the non-cumulative key figures. And to be able to filter after the computation of the non-cumulative key figures, we must add the filter characteristic to the internal drilldown of the calculation scenario.
Therefore, the first intermediate result after calculating the non-cumulative key figures is the same result that we have seen above, when selecting 0CALMONTH in the query:
| 0CALMONTH | 0MATERIAL | DELTA | STOCK |
| 2019-01 | A | 110 | 110 |
| 2019-02 | A | 30 | 140 |
| 2019-03 | A | 0 | 140 |
| 2019-04 | A | -20 | 120 |
The second intermediate result after applying the filter is the final result. No additional aggregation is needed for this sample query:
| 0CALMONTH | 0MATERIAL | DELTA | STOCK |
| 2019-02 | A | 30 | 140 |
| 2019-03 | A | 0 | 140 |
This is the expected result for the key figure STOCK.
Query with Filter on Time Characteristic and ‘finer’ Time Characteristic added to GROUP BY
The query below does not fulfill the requirement as documented in SAP Note 2032830 about filters on time characteristics: the characteristic 0CALYEAR on which the filter is defined is not part of the SELECT and GROUP BY clause.
Therefore, the query would fail with error message “Unkeep Attribute could not be removed”.
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
WHERE
"0CALYEAR" BETWEEN '2019' AND '2020'
GROUP BY
"0CALMONTH",
"0MATERIAL"
However, as 0CALMONTH is finer than 0CALYEAR and 0CALMONTH is selected in the query, it is possible to use the following parameter to switch off the error. Note that the disclaimer above must be considered. In this special scenario, there is no need for an aggregation over time after the application of the filter and therefore, the result of the query is correct.
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
('PLACEHOLDER' = ('ce_settings','{"disabled_patterns": "39"}') )
WHERE
"0CALYEAR" BETWEEN '2019' AND '2020'
GROUP BY
"0CALMONTH",
"0MATERIAL"
Query with Single Value Equal Condition
Queries with a filter representing a single value equal condition might cause the error “Unkeep Attribute could not be removed” although the query uses a filter on a time characteristic and that time characteristic is in the SELECT and GROUP BY clause. In that scenario, the SQL optimizer may transform the GROUP BY clause internally and remove the characteristic from the internal GROUP BY. Thus, the error message is triggered.
As a workaround, in this scenario, either a parameter via the placeholder syntax can be used – or the hint NO_GROUPING_SIMPLIFICATION can be added to the query. Note that the disclaimer above must be considered when using the parameter.
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
WHERE
"0CALMONTH" = '201902'
GROUP BY
"0CALMONTH",
"0MATERIAL"
Workaround via placeholder syntax:
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
('PLACEHOLDER' = ('ce_settings','{"disabled_patterns": "39"}') )
WHERE
"0CALMONTH" = '201902'
GROUP BY
"0CALMONTH",
"0MATERIAL"
Workaround via database hint:
SELECT
"0CALMONTH",
"0MATERIAL",
sum("DELTA"),
sum("STOCK")
FROM <External SAP HANA View>
WHERE
"0CALMONTH" = '201902'
GROUP BY
"0CALMONTH",
"0MATERIAL"
WITH HINT( NO_GROUPING_SIMPLIFICATION )
Wrap-up
The error message “Unkeep Attribute could not be removed” is thrown for good reasons in most scenarios. However, there are a few exceptions to that rule and you know now how to suppress the error message if appropriate. Make sure though to observe the disclaimer above.
No comments:
Post a Comment