SAP Analytics Cloud: enhancing planning with additional disaggregation modes!

[Alecsandra]

Planning sales by region or product line, workforce across the entire company, and expenses at the cost center level exemplify different levels of aggregation where planning can happen.  Although data is planned on a summary level, it needs to be broken down to a detailed level to ensure that plans can be operationalized.

In SAC, when data is entered or changed on such aggregated level, the value gets automatically disaggregated to the leaf members that aggregate up to it:

• Data entry on a booked member disaggregates proportionally with self-reference.
• Data entry on an unbooked member disaggregates equally. If the Unassigned member is part of the context, the value is booked to the Unassigned instead of equal disaggregation across all leaf members. 

What's New

However, there are business scenarios for which you require more control over disaggregation behavior. For example, you may wish to disaggregate coupons across stores based on sales or the training budget across teams based on each team’s size. To allow such disaggregation use cases to be performed on the fly, we are introducing a new disaggregation type - Reference to Another Measure. You will be able to use the weights of a sibling measure as a driver for disaggregation and choose whether to disaggregate the entire value or just the delta to the value entered.

How it works

To get started, you need to navigate to the Measures section of the Modeler, from where you can choose the disaggregation type that should be applied to your measure: Standard or Reference to Another Measure. The Reference to Another Measure disaggregation is available for the measures not configured with an exception aggregation type. Once Reference to Another Measure is selected, you will need to choose a measure as a reference from the drop down list - please note, calculated measures are not available for selection. Next, you can choose between Total Distribution and Delta Distribution to control the disaggregation mode further over the entire value entered or just the delta from the previous value.

Disaggregate using Reference to Another Measure – Total Value

Suppose you plan Coupons and wish to disaggregate Coupons across Products 1,2 and 3 based on the planned Sales. The products aggregate up to the parent node Product Group 1, where the data entry for Coupons is made.

The picture below showcases the difference between Standard and Reference to Another Measure disaggregation types using two different measures:

• Coupons (Standard), based on Standard disaggregation, disaggregate the value equally across products, with each receiving a share of 33.3%.
• Coupons (following Reference), based on Reference to Another Measure disaggregation, disaggregates the value based on the products' proportions calculated from the Sales measure. Product 1 receives a share of 33.3%, Product 2 16.6%, and Product 3 50% of the total value. 

Next, you can see that Sales (Reference) was adjusted: Product 2 will no longer be sold in Q1 (2024), which is expected to increase Sales for Product 1. This leads to a change in the Sales (Reference) product weights. However, the disaggregation of the Coupons (following Reference) is not automatically triggered when the weights of the reference measure change. The disaggregation is triggered only when a data entry at an aggregate level is made in the target measure, similar to the picture below where Coupons (following Reference) is decreased from 30 to 28. As a result, the new value gets disaggregated using the new Sales (Reference) weights. You can observe that Coupons (following Reference) get the value disaggregated only to Product 1 and Product 3. Product 2 gets booked with 0 as there is no longer a reference for disaggregation.

Disaggregate using Reference to Another Measure – Delta Value

But what if you want to retain the value disaggregated based on the initial weights and disaggregate only the incremental value using the new weights? Switching the disaggregation mode to Delta Distribution will allow you to disaggregate just the delta to the initial value instead of the total value. Starting from the initial example, the Sales (Reference) measure is increased by 100, and the weights of the products are adjusted: Product 1 gets 50% and Product 2 and 3 25% each. The Coupons (following Reference) measure is also increased by 12 at the parent level, which triggers data disaggregation. This time, the Coupons value is not disaggregated entirely to the affected cells using the new proportions. Instead, the difference in the value before (T1) and after (T2) the data entry is disaggregated, in this case, 12. The value before the input remains disaggregated as per the original weights.

The following table explains the results of the disaggregation using Delta mode.

T1		T2
Sales (Reference)	Coupons	Sales (Reference)	Coupons	Coupons - Delta Value
300	30	400	42 (30+12)	12
100  (33.3%)	10 (33.3%)	200 (50%)	16 (10 + 6)	6 (50%)
50    (16.6%)	5   (16.6%)	100 (25%)	8 (5 + 3)	3 (25%)
150  (50%)	15  (50%)	100 (25%)	18 (15 + 3)	3 (25%)

Attention to details

When the target (measure to be disaggregated) and reference measure are not symmetrically booked, the following behavior applies:

• If the values of the reference are unbooked, then there is a fallback to the standard disaggregation. In the future, we would like to allow users to choose whether the fallback behavior should be standard disaggregation or error.
• If the values of the reference are partially booked, then
  • The value is disaggregated to the leaf nodes according to the weights given by the reference measure values.
  • Unbooked reference values are handled as weight 0, if the target cell is booked.
  • If a reference cell is 0 (booked) and the target cell is unbooked, then the target cell stays unbooked.
  • If the sum of all reference weights is 0, then disaggregation is performed equally across all (in the reference) booked cells.

Conclusion

In summary, the following new features are available with SAC QRC1:

New disaggregation type, Reference to Another Measure, allows on-the-fly disaggregation using proportions from another measure. It comes with two modes:

• Total Distribution – disaggregates total value entered 
• Delta Distribution – disaggregates delta to the total value entered 

If you have additional disaggregation requirements required to enable your use cases, please use the Influence portal to submit your ideas!