Authors : molly_207 , sridhar.sundaram2
The use case in this blog post relates to scenarios where capabilities of SAP Analytics Cloud is extrapolated to meet specific uses cases which are not available as standard functionality in an account-based(Classic) planning model.
1. Sum Product Custom Calculation and use Days in Month in Account Formulas
2. Overcome constraints of Validation rules.
3. Load data for non-company code currencies in account-based model.
Use case 1:
1. Overcome Data/ Time restrictions on Quarter, Half Year and Year
2. Achieve SUMPRODUCT exceptional aggregation for Quarter & Fiscal Year based on days of the month.
In standard SAP Analytics Cloud when we use exception aggregation on Account members, Sum product functionality is not available as standard and new exception aggregation methods cannot be introduced.Date/Time dimension is system driven and cannot be altered .This introduces a limitation where one cannot store different representation of data at parent level(quarter /year) compared to months .(month being the lowest granularity). Below illustration explains the use case in more detail
1. Plan values entered in Unit of barrels per month interval in the Input Forms in account-Volume in Barrels
2. In SAP Analytics Cloud story the Quarter and Yearly values on the parent hierarchy would roll up to Sum / Average based on the standard available exceptional aggregation configured.
3. Create a member named “DAYSINMONTH” to determine the No. of days in a month inclusive of Leap Year consideration, using the below with an member formula as below with exception aggregation as Sum.
IF([d/Date].[p/MONTHDESC]=("Oct", "Dec", "Jan", "Mar", "May","Jul", "Aug"), 31,
IF([d/Date].[p/MONTHDESC]=("Nov", "Apr","Jun","Sep"), 30, IF([d/Date].[p/MONTHDESC]="Feb",
IF([d/Date].[p/YEAR]=("2020"OR"2024"OR"2028"OR"2032"OR"2036"),29,28))))
4. Volume in Barrels per day is calculated using account-based formula as
Volume in Barrels per day = Volume in Barrels / No. of days in month
5. Expected result is to calculate as a sum product i.e. for Quarter instead of arithmetic average.
6. In SAP analytics Cloud story, per the behaviour of the hierarchy roll-up the formula configured at the account level does the sum product as shown below as the formula is applied at Quarter & Year level.
7. To achieve the above result set, No exception aggregation definition needs to be set for the Account member where this formula is maintained, while the underlying accounts used in calculation have a aggregation setting as SUM.
Use case 2:
Overriding Validation rules
To avoid the Planning data to be entered at a wrong combination, Validation rules are set up.
Ex: Cost Center – Profit Center- Segment.In a typical revenue planning scenario, we tag the Revenue accounts to Profit Center and Segments and Cost Center is tagged to # or Unassigned in a Revenue Model.
For Opex planning, we tag the Cost Accounts to Cost Center, Profit Center, Segment. This is done to arrive at P&L at profit Center, Segment & Company Code level.
However if we set up the validation rule based on attributes, there is one shortcoming where you cannot post data to CC #- Unassigned , any PC combination , as # is not considered as legitimate member by SAP Analytics Cloud Validation rule .
The above limitation is mitigated as explained in the below scenario involving data movement between 2 models .
In this case, a temporary member named CC_NONE where the rule definition against (No Members) is set up, and this facilitates data load in Profit Center and CC_NONE combination.
Define copy rule, to copy the data between CC_NONE to # and Delete the CC_NONE data using sequence of Data Action step ensuring data is intact for planning and reporting.
Use case 3:
Handle non company-code currency data load in Classic model.
When data is loaded into an account-based planning model in SAP analytics Cloud where currency is enabled in Company Code dimension, it is defaulted to store company code currency.
Often there is a need to load data in a different currency than the company code currency.
In this example, Prices needs to be loaded in US dollars while the company code currency is GBP.
The solution is to create a temporary company code with USD as the company code currency and load the data. Maintain the exchange rates for GBP to USD. Alternatively, a generic dimension type currency can be used, if the requirement is to load multiple currencies.
Run a copy package between the temporary company codes in Default currency instead of local currency and then delete the temp company code values
Data Action is defined as below which then can be triggered in Story used to copy the data to company code currency and delete the temporary data in USD.
The solution hacks detailed above are use cases handled during project implementation to overcome challenges encountered due to inadvertent technical constraints
For more info on upcoming product releases and innovations, please refer to
https://saphanajourney.com/sap-analytics-cloud/product-updates/
The use case in this blog post relates to scenarios where capabilities of SAP Analytics Cloud is extrapolated to meet specific uses cases which are not available as standard functionality in an account-based(Classic) planning model.
1. Sum Product Custom Calculation and use Days in Month in Account Formulas
2. Overcome constraints of Validation rules.
3. Load data for non-company code currencies in account-based model.
Use case 1:
1. Overcome Data/ Time restrictions on Quarter, Half Year and Year
2. Achieve SUMPRODUCT exceptional aggregation for Quarter & Fiscal Year based on days of the month.
In standard SAP Analytics Cloud when we use exception aggregation on Account members, Sum product functionality is not available as standard and new exception aggregation methods cannot be introduced.Date/Time dimension is system driven and cannot be altered .This introduces a limitation where one cannot store different representation of data at parent level(quarter /year) compared to months .(month being the lowest granularity). Below illustration explains the use case in more detail
Sample Data set and the required calculation
1. Plan values entered in Unit of barrels per month interval in the Input Forms in account-Volume in Barrels
2. In SAP Analytics Cloud story the Quarter and Yearly values on the parent hierarchy would roll up to Sum / Average based on the standard available exceptional aggregation configured.
3. Create a member named “DAYSINMONTH” to determine the No. of days in a month inclusive of Leap Year consideration, using the below with an member formula as below with exception aggregation as Sum.
IF([d/Date].[p/MONTHDESC]=("Oct", "Dec", "Jan", "Mar", "May","Jul", "Aug"), 31,
IF([d/Date].[p/MONTHDESC]=("Nov", "Apr","Jun","Sep"), 30, IF([d/Date].[p/MONTHDESC]="Feb",
IF([d/Date].[p/YEAR]=("2020"OR"2024"OR"2028"OR"2032"OR"2036"),29,28))))
4. Volume in Barrels per day is calculated using account-based formula as
Volume in Barrels per day = Volume in Barrels / No. of days in month
5. Expected result is to calculate as a sum product i.e. for Quarter instead of arithmetic average.
6. In SAP analytics Cloud story, per the behaviour of the hierarchy roll-up the formula configured at the account level does the sum product as shown below as the formula is applied at Quarter & Year level.
7. To achieve the above result set, No exception aggregation definition needs to be set for the Account member where this formula is maintained, while the underlying accounts used in calculation have a aggregation setting as SUM.
Use case 2:
Overriding Validation rules
To avoid the Planning data to be entered at a wrong combination, Validation rules are set up.
Ex: Cost Center – Profit Center- Segment.In a typical revenue planning scenario, we tag the Revenue accounts to Profit Center and Segments and Cost Center is tagged to # or Unassigned in a Revenue Model.
For Opex planning, we tag the Cost Accounts to Cost Center, Profit Center, Segment. This is done to arrive at P&L at profit Center, Segment & Company Code level.
However if we set up the validation rule based on attributes, there is one shortcoming where you cannot post data to CC #- Unassigned , any PC combination , as # is not considered as legitimate member by SAP Analytics Cloud Validation rule .
The above limitation is mitigated as explained in the below scenario involving data movement between 2 models .
In this case, a temporary member named CC_NONE where the rule definition against (No Members) is set up, and this facilitates data load in Profit Center and CC_NONE combination.
Define copy rule, to copy the data between CC_NONE to # and Delete the CC_NONE data using sequence of Data Action step ensuring data is intact for planning and reporting.
Use case 3:
Handle non company-code currency data load in Classic model.
When data is loaded into an account-based planning model in SAP analytics Cloud where currency is enabled in Company Code dimension, it is defaulted to store company code currency.
Often there is a need to load data in a different currency than the company code currency.
In this example, Prices needs to be loaded in US dollars while the company code currency is GBP.
The solution is to create a temporary company code with USD as the company code currency and load the data. Maintain the exchange rates for GBP to USD. Alternatively, a generic dimension type currency can be used, if the requirement is to load multiple currencies.
Run a copy package between the temporary company codes in Default currency instead of local currency and then delete the temp company code values
Data Action is defined as below which then can be triggered in Story used to copy the data to company code currency and delete the temporary data in USD.
The solution hacks detailed above are use cases handled during project implementation to overcome challenges encountered due to inadvertent technical constraints
For more info on upcoming product releases and innovations, please refer to
https://saphanajourney.com/sap-analytics-cloud/product-updates/
- SAP Managed Tags:
