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

Inconsistent behaviour of Exception Aggregation in Account Formulas

• SAP Analytics Cloud 2019.15

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.

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.

SOLUTION 1:

Turn off the unbooked mode for DIM1 dimension.

SOLUTION 2:

Remove DIM2 from list of dimensions that are exceptionally aggregated.

• 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
• Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
• 2487011 - What information do I need to provide when opening an case for SAP Analytics Cloud?
• SAP Analytics Cloud > Learning > Guided Playlists
• SAP Analytics Cloud > Learning > Guided Playlists > Getting Support
• Need More Help? Contact Support or visit the solution finder today!

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

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