Dimension Combination Rule in SAC
In this blog, I will introduce how to use the new function Dimension Combination Rule to allow data input and publish only on valid member combination across dimensions. The feature has been released in version 2020.07.
A typical user case is organizational dependencies when defining financial master data; for example: one cost center belongs to one profit center. With Dimension Combination Rule, user is able to define organizational dependency.
Assuming in the planning model, there are Cost Center dimension and Profit Center dimension. User would like to define member combinations between Cost Center and Profit Center dimension.Cost Center and Profit Center have the below dimension members. CC1 belongs to PC1, CC2 belongs to PC2, and so on.
Another user case is sales strategy that some products are only allowed to be sold in some locations. Hence there should be relationship between Product dimension and Location dimension.
Next, taking cost center/profit center case, I will show you how to create the Dimension Combination Rule, and how the planning uses Dimension Combination Rule.
How to define Dimension Combination Rule?
- Open SAC model, switch on Validation Rule in the model preferences. The Rules tab is added to the model maintenance page after turn On the toggle.
- Open the Cost Center dimension and create a new custom property named “ProfitCenter”(it’s not required to name property as ProfitCenter. Any name is ok). And then maintain the allowed combination values of Profit Center for each cost center
- After saving the master data, go to Rule tab of the model. Create a new Rule, select Cost Center as Reference dimension. In Matched dimension setting, select “ProfitCenter” from the attribute list and match it to Profit Center dimension. Then, save the rule.
When defining the rule via dimension attribute, hierarchy node is NOT supported. Member used in combinations are treated as leaf members.
Unassigned (#) member
Unassigned (#) member is always valid with allowed combinations.
Let’s take above rule as example.
Also, CC1, CC2, CC3 has allowed combination with Profit Center dimension, then we will also generate Unassigned (#) of Profit Center as allowed combination with CC1, CC2, CC3.
You may notice CC4 is not a valid member with any Profit Center. So, Unassigned (#) member of Profit Center is not a valid member with CC4 .Same for PC4 which is not valid with any cost center, hence unassigned (#) member of Cost Center is NOT a valid member with PC4.
Enter Data in Table
In the SAC story, add Cost Center and Profit Center in the story row and use unbooked mode to show all the base members in the table.
Then, you would get Data Entry restriction when trying to input data into the table.
- Enter data into (Unassigned, PC1/PC2/PC3/Unassigned) is allowed. Enter data into (Unassigned, PC4) is NOT allowed.
- Enter data into (CC1, PC1/PC2/Unassigned) is allowed. Enter data into (CC1, PC3/PC4) is NOT allowed.
- Enter data into (CC2, PC2/Unassigned) is allowed. Enter data into (CC2, PC1/PC3/PC4) is NOT allowed.
- Enter data into (CC3, PC2/PC3/Unassigned) is allowed. Enter data into (CC3, PC1/PC4) is NOT allowed.
- Enter data into (CC4, PC1/PC2/PC3/PC4/Unassigned) is NOT allowed.
Besides entering data into base member directly, disaggregate data also follow the dimension combination rule.
- If Cost Center or Profit Center does not added in the table row or column, entered data will go to Unassigned member firstly for Cost Center or Profit Center as before.
- If Unassigned member is excluded for Cost Center or Profit Center in this case, data will only be disaggregated to allowed combination.
- If user enters data into parent node of the Cost Center or Profit Center, disaggregation to leaf nodes will follow the rule “booked data have higher priority than unbooked data.”
- For the parent node which is booked (some leaf nodes have data), the value will only be disaggregated to the valid booked combinations. For example,(CC1,PC1) are valid combination while (CC1, PC3) are invalid combination. Both of them have booked data 100. Enter data 300 on their parent node (CC1, All PC) will only be disaggregated to allowed combination (CC1, PC1).
- For the parent node which is unbooked (none leaf nodes have data), the value will only be disaggregated to the valid unbooked cells. For example, (CC1, PC1/PC2/PC3/PC4) are all unbooked cell. Enter data on 200 on their parent node (CC1, All PC) will only disaggregated to allowed combination (CC1, PC1/PC2).
- In case the disaggregated leaf nodes are all invalid (for both unbooked and booked cases above), there will be error message. For Example, un-allowed combination (CC1, PC3) have booked data 100. Enter data 200 on its parent node (CC1, All PC) will get an error message and change data is restricted.
Currently, not all the functions use dimension combination rule.
There are still chances that invalid data is created/modified in private version, These invalid data changes are NOT allowed to be Published to public version.
When click Publish Data button to publish the change to a public version, Dimension Combination Rule will be checked again. User will have the choice to cancel the Publish and go back to story to correct data on invalid combinations, or go ahead with Publish then invalid data changes will be lost.
- Data Import does not know dimension combination rule, hence there is chance that invalid data is imported to model. This might not be a big problem, since most Import case is importing ACTUAL data into SAC, not Plan data.
- Max. three rules are allowed in one model.
- Max. two dimensions are allowed in one rule in 2020.07. (From 2020.10, the limit will be extended to three dimensions.)