SAP Knowledge Base Article - Public

2845637 - Account formula used with exception aggregation does not calculate for unbooked data even if the formula is valid in SAP Analytics Cloud (SAC)

Symptom

Inconsistent behaviour of Exception Aggregation in Account Formulas

Environment

  • SAP Analytics Cloud 2019.15

Reproducing the Issue

Create two accounts:

DUMMY_1 has formula "1" (constant value 1)

DUMMY_2 has the same formula "1" with the addition of "exception aggregation type" = SUM and "Exception Aggregation Dimensions" set to DATE;DIM1;DIM2

Even though the formula in this simple example is a constant it will not be calculated if the intersection of DATE, DIM1 and DIM2 does not contain data.

Cause

DUMMY_2 is defined with an exception aggregation

In the exception aggregation dimensions two out of the three dimensions are exceptionally aggregated, but one (DIM1 for instance) is on the display axis of a story and is in unbooked (or master) mode.

Generally speaking you cannot have a exceptionally aggregated constant formula in unbooked mode (or master) mode.

HANA's Multi-Dimensional Service (or MDS) which is responsible for processing the query aggregates values in the following fashion:

  1. It aggregates all dimensions that are not on the drill, or are not part of exception aggregation. This operation is always performed in booked mode.
  2. Then the formula is calculated for these cells in our internal cube structure. In this case formula is a constant 1.
  3. Then the exceptional aggregation is performed. If you have the exceptionally aggregated dimension on the drill, then no aggregation will be performed for that dimension unless if it is hierarchical, which in that case values are aggregated along the hierarchy (children values rolled up to parents).

The issue in this case is that the DIM1 dimension is on the drill, but in unbooked (or master) mode, and DIM2 dimension is removed, and thus it is aggregated away. So, MDS first generates a similar query (not identical to this though) as follows:

SELECT Account, SUM(measure), DIM1, DIM2, DATE

FROM your_datasource

WHERE .....

GROUP BY Account, DIM1, DIM2, DATE

But some combination of DIM1 dimension members and DIM2 do not exists in this result because they have no booked value in your fact table. Those are the tuples that you will get NULL in the results when step 2 of the process is executed.

Resolution

SOLUTION 1:

Turn off the unbooked mode for DIM1 dimension.

SOLUTION 2:

Remove DIM2 from list of dimensions that are exceptionally aggregated.

See Also

Your feedback is important to help us improve our knowledge base.

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO , KBA , LOD-ANA-PL , Planning , HAN-AS-INA-SVC , deprecated - please use HAN-DB-ENG-MDS instead , How To

Product

SAP Analytics Cloud 1.0