Time-Dependent Hierarchy Freeze in SAP Analytics Cloud

Objective

When Finance transfers Master Cost Centers from one Function to another within the Cost Hierarchy, historical FTE and Employment Cost data should remain associated with the original Function and Sub-Function to ensure accurate analysis. At the same time, these transfers must be reflected as movements (transfers in/out) in FTE walks. This ensures that the closing position reconciles accurately with the opening position, clearly accounting for the reasons for change.

Problem Statement

The SAP Analytics Cloud does not currently support time dependency on the Hierarchy. This means that when Finance transacts a budget transfer by moving Master Cost Centre(s) out of one part of the Cost Hierarchy (i.e. “Function A”) to another part of the Cost Hierarchy (i.e. “Function B”), the SAC application:

Ignores which Functions and Sub-Functions previously held the original budget.

Shows all historical actuals and budgets as if they always belonged to Function B.

Makes it difficult to show the transfer between Function A to Function B.

Reports and dashboards showing FTE walks and Employment Cost walks by Function and/or Sub-Function do not reconcile.

Without Time Dependency on the Cost Center Hierarchy, it will not include Function A & Function B previous FTE budget or actuals:

Fig: FTE Walk of Functions without time dependency (pre-image)

Fig: FTE Walk of Functions without time dependency (post-image)

Solution Statement

To track the movement of Cost Centers between hierarchies, we will use a ‘Cost Center Transfer’ change reason. This will capture the transfer and allow it to be displayed in reports, showing the exact movement of cost centers between the nodes.

Fig: FTE Walk of Functions with time dependency

A separate model has been created in which all levels of the Cost Center hierarchy are treated as dimensions. This approach flattens the structure, allowing for the identification of hierarchy changes using transaction data. The transaction data—referred to as Drivers—is generated in the BW system. In this case, a logic has been implemented in BW that checks the master data to identify the month of the movement and generates the corresponding driver values.

Fig: Architecture for Time dependent hierarchy freeze models

Original Planning Model – this is the main model where all the planning data is captured and the dimensions in the model are:

Date – Fiscal Year, Quarter, Period Hierarchy.Version – Actual, DB (Budget).Cost Center – Cost Center with Hierarchy and the properties.Change Reason – Change Reasons for FTE changes (Growth, Efficiency, Offshoring, Cost Center Transfer, Opening and Closing).Audit Trail – Capturing the audit.

Drivers Model – this is the model where all the drivers are stored and the splitting data into suffix & original cost center calculation will be part of this model. The dimensions in the model are:

Date – Fiscal Year, Quarter, Period Hierarchy.Version – Actual, DB (Budget).Cost Center Hier – Cost Center with Hierarchy and the properties.Cost Center Level 1 – Level 1 nodes of the Cost Center hierarchyCost Center Level 2 – Level 2 nodes of the Cost Center hierarchyChange Reason – Change Reasons for FTE changes (Growth, Efficiency, Offshoring, Cost Center Transfer, Opening and Closing).Audit Trail – Capturing the audit.

Sample Cost Center Hierarchy:

Fig: Sample Cost center hierarchy

Sample Cost Center dimension master data:

DESCRIPTION

HIERARCHY

CC1

CC2

CC12345678

Function A

Sub-Function A

DD12345678

Function B

Sub-Function B

Here, CC1 and CC2 refer to the Cost Center Level 1 and Cost Center Level 2 nodes of the hierarchy, respectively.

Drivers play a key role in achieving this and those should be properly aligned with changes in the hierarchy. In the current scenario, these drivers are created in the BW system. From the drivers, we can identify the changes in the hierarchy as follows:

1 – No change-1 – Cost center changed the hierarchy 2 – Cost Center arrived in new node

Below are the sample drivers for Cost Center under function A & function B:

Cost Center

Cost Center Level 1

Cost Center Level 2

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

CC12345678

Function A

Sub-Function A

DD12345678

Function B

Sub-Function B

-1

DD12345678

Function A

Sub-Function A

Based on the example above, the cost center “DD12345678” was transferred from Function B to Function A in November. This means that “DD12345678” was part of Function B until October and then moved to Function A in November.

Cost Center

Change Reason

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Function A

CC12345678

Opening

20000

CC12345678

Closing

20000

Function B

DD12345678__DB25_1

Opening

2500

DD12345678__DB25_1

Cost Center Transfer

-2500

DD12345678__DB25_1

Closing

2500

Function A

DD12345678

Opening

2500

DD12345678

Cost Center Transfer

2500

DD12345678

Closing

2500

The walk of the FTE will look like the above table once the hierarchy freeze logic has been executed.

Hierarchy Freeze Logic

A story has been created in SAP Analytics Cloud using scripting capabilities, divided into two pages. The First page consists of logic for creating cost center nodes, which will initially reside in the old function and copying data from the original model to the other model where we have loaded the drivers. The Second page, based on the drivers will move out data from the original cost center to the newly created similar cost center node. In the above case, we have created the cost center with suffix _DB25_1. The number keeps on increasing if there are multiple movements of cost center from one function to another within a year.

For e.g. if the cost center hierarchy again changes within year a new node with suffix DB25_2, DB25_3, etc.., will be created, here DB denotes the budget version and 25 denotes the financial year and the number at the end denotes the number of times the function has been changed for that cost center.

To clarify the naming system:

DB: Denotes the budget version, ensuring that the cost center hierarchy is tied to specific budget plans.

25: Represents the financial year.

_1, _2, _3…: The numeric suffix represents how many times the function has been changed for that cost center in the given financial year. For example:

DB25_1: First movement of the cost center within the year.

DB25_2: Second movement, and so on.

This naming mechanism is extremely useful for tracking historical changes to cost centers and ensuring that data is consistently maintained, even as cost centers are moved across functions within the same fiscal year.

Page 1 – Cost Center Node Creation:

The new cost center with a suffix will be created whenever the drivers have the value -1, the number of -1’s within a year decides how much suffix nodes will be created for that cost center. The new node created will have same properties as of original cost center, with the only difference being the function assignment.

Sample SAC Table with Drivers :

Cost Center

Function

Sub-Function

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

DD12345678

Function B

Sub-Function B

-1

DD12345678

Function A

Sub-Function A

SAC Table After filtering Driver value as -1:

Cost Center

Description

Month

Cost Center Level 1

Cost Center Level 2

Drivers

DD12345678

Oct

Function B

Sub-Function B

-1

For the above logic, we have created a custom script written on a button to perform the task of creating cost center node.

A table is created with all the -1’s filtered, from this table the script reads the cost center and its properties to generate new nodes. For creating new node, we have created a script variable with type Planning model member and filled the variable reading table with all the cost center, its levels, hierarchy id and properties.

Fig: Script Variable Declaration

CC_MEMBER[0] = (
{
id:“DD12345678”,
description: “DD12345678”,
properties:
{
CC1:“Function B”
CC2:“Function B”
MONTH:“Oct”
},
hierarchies:
{
H1: {
parentId: “Function B”
}
}
}
);

Then, the updateMembers() API of the planning model is used to create a master data record for the above combination.

E.g.

PlanningModel_1.updateMembers(“<cost center dimension>”,CC_MEMBER);

On successful execution of the above line the master data record will be created and will be shown in dimension as below:

DESCRIPTION

HIERARCHY

CC1

CC2

CC12345678

Function A

Sub-Function A

DD12345678

Function B

Sub-Function B

DD12345678__DB25_1

Function A

Sub-Function A

Additionally, to assign the data to the newly created node, we use the Copy Data action to transfer data from the original model to the driver model. The value will be set to unassigned for cost center–level dimensions, as these do not exist in the original model.

Page 2 – Time Dependency Logic

This page holds the logic for aligning data according to hierarchy node changes.

Note: For each cost center, the data action must run as many times as it changes hierarchy during the year. In other words, the data action is cost center–specific and depends on the number of hierarchy changes.

We have created two tables: one to identify all the cost centers have “-1” as driver. Then applying that cost center filter from table 2 to table 1 to get all the combinations.

Table 1:

MonthCost CenterCost Center Level 1Cost Center Level 2DriversAprCC12345678Function ASub-Function A1AprDD12345678Function BSub-Function B1MayCC12345678Function ASub-Function A1MayDD12345678Function BSub-Function B1JunCC12345678Function ASub-Function A1JunDD12345678Function BSub-Function B1JulCC12345678Function ASub-Function A1JulDD12345678Function BSub-Function B1AugCC12345678Function ASub-Function A1AugDD12345678Function BSub-Function B1SepCC12345678Function ASub-Function A1SepDD12345678Function BSub-Function B1OctCC12345678Function ASub-Function A1OctDD12345678Function BSub-Function B-1NovCC12345678Function ASub-Function A1NovDD12345678Function ASub-Function A2DecCC12345678Function ASub-Function A1DecDD12345678Function ASub-Function A1JanCC12345678Function ASub-Function A1JanDD12345678Function ASub-Function A1FebCC12345678Function ASub-Function A1FebDD12345678Function ASub-Function A1MarCC12345678Function ASub-Function A1MarDD12345678Function ASub-Function A1

Table 2:

Measures

Drivers

CC1

Function A

CC2

Sub-Function A

Cost Center

Description

Date

DD12345678

Oct

-1

After filtering the Cost Center from Table 2 into Table 1 will look like below:

Month

Cost Center

Cost Center Level 1

Cost Center Level 2

Drivers

Apr

DD12345678

Function B

Sub-Function B

May

DD12345678

Function B

Sub-Function B

Jun

DD12345678

Function B

Sub-Function B

Jul

DD12345678

Function B

Sub-Function B

Aug

DD12345678

Function B

Sub-Function B

Sep

DD12345678

Function B

Sub-Function B

Oct

DD12345678

Function B

Sub-Function B

-1

Nov

DD12345678

Function A

Sub-Function A

Dec

DD12345678

Function A

Sub-Function A

Jan

DD12345678

Function A

Sub-Function A

Feb

DD12345678

Function A

Sub-Function A

Mar

DD12345678

Function A

Sub-Function A

Storing the unique combinations in an array, allowing us to pass them as parameters to the data action used for generating the FTE walk across different functions.

Additionally, an index array will be created to store the index where the hierarchy changes occur. This helps identify and pass the correct combinations to the data action. The unique combinations in the script will be stored as below:

Cost Center

Cost Center Level 1

Cost Center Level 2

DD12345678

Function B

Sub-Function B

DD12345678

Function A

Sub-Function A

This array stores combination on which we need to perform data transfers, and these combinations are passed as parameters to the data action.

The logic is designed to store the details in table into different variables. The system will pass the first combination data into node with Suffix (__DB25_1) and the second into the original cost center as the cost center only moved once in the hierarchy.

A check has also been added in the script to check if the data already exist in the same suffix cost center (i.e. if logic ran for the same cost center prior then no need to run/change anything) then skip the data action run for that suffix cost center. otherwise running data actions in the below order:

Copying previous year DB, Actual FTE data into the suffix cost center – Clearing the existing historical data for Actual and DB version and then copying data into the suffix node cost center and original cost center based on the month the hierarchy has changed. This data action is primarily divided into three steps mainly:Delete existing data of Previous year – using DELETE() function in advanced formula of data action deleting previous year data. Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL_TARGET%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DELETE()

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL_TARGET

Member/Measure

COST_CENTER

Leaf

Single

PY_SEL

Member/Measure

Date

FYQP

Any

Copy data to Cost Center combination of Previous year – using DATA() & RESULTLOOKUP() function fetching and storing data to target cost center (suffix & original cost center). Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DATA([d/AUDIT_TRAIL] = “#”, [d/COST_CENTER] = %CC_SEL_TARGET%) = RESULTLOOKUP()

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL

Member/Measure

COST_CENTER

Leaf

Single

CC_SEL_TARGET

Member/Measure

COST_CENTER

Leaf

Single

PY_SEL

Member/Measure

Date

FYQP

Any

Clearing data into original cost center – using DELETE() function in advanced formula of data action deleting data from original cost center for the month before suffix cost center data copied. Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DELETE()

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL

Member/Measure

COST_CENTER

Leaf

Single

PY_SEL

Member/Measure

Date

FYQP

Any

Running Data action for copying data according to drivers on the hierarchy nodes (suffix & original cost center) – this Data action is helping us in splitting data based on drivers into suffix & original cost centers.CONFIG.TIME_HIERARCHY = FISCALYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = ON
MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER_Hier] = (BASEMEMBER([d/COST_CENTER_Hier].[h/H1], %CC_SEL%))
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER_LEVEL_1] = (“#”)
MEMBERSET [d/COST_CENTER_LEVEL_2] = (“#”)

MEMBERSET [d/Z_D_HR_HCP_CHANGE_REASON] = (BASEMEMBER([d/Z_D_HR_HCP_CHANGE_REASON].[h/H1], “Growth”, “Efficiency”, “Insourcing”, “Transfer_All”), “Opening”, “Growth”, “Efficiency”, “Offshoring”, “Closing”)
FOREACH [d/Date] ASC
IF RESULTLOOKUP([d/AUDIT_TRAIL] = “HIERARCHY”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/Measures] = “FTE”) >= 2 THEN
// When Shifted to New node create Cost Center transfer node.
IF [d/Date] = %StartPeriod% THEN
ELSE
// Capturing closing values of movement of hierarchy into CC_TRANSFER – Cost Center Transfer Change Reason and target cost center
DATA([d/_AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%, [d/CHANGE_REASON] = “CC_TRANSFER”, [d/Date] = PREVIOUS(1, “MONTH”)) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/Date] = PREVIOUS(1, “MONTH”), [d/CHANGE_REASON] = “Closing”)
ENDIF
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”)
// If no change in the hieararchy
ELSEIF RESULTLOOKUP([d/AUDIT_TRAIL] = “HIERARCHY”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/Measures] = “FTE”) > 0 THEN
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP [d/AUDIT_TRAIL] = “#”)
ELSEIF RESULTLOOKUP([d/AUDIT_TRAIL] = “HIERARCHY”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/Measures] = “FTE”) < 0 THEN
// When Shifted to New node create Cost Center transfer node. FOR -1 Driver condition
IF RESULTLOOKUP([d/AUDIT_TRAIL] = “HIERARCHY”, [d/Measures] = “FTE”, [d/Date] = PREVIOUS(1, “MONTH”), [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1_PREV%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2_PREV%) != NULL AND RESULTLOOKUP([d/AUDIT_TRAIL] = “HIERARCHY”, [d/Measures] = “FTE”, [d/Date] = PREVIOUS(1, “MONTH”), [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1_PREV%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2_PREV%) < 0 THEN
// If Consecutive -1 Drivers found or hierarchy changed in consecutive months then
IF [d/Date] = %StartPeriod% THEN
ELSE
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%, [d/CHANGE_REASON] = “CC_TRANSFER”, [d/Date] = PREVIOUS(1, “MONTH”)) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/Date] = PREVIOUS(1, “MONTH”), [d/CHANGE_REASON] = “CC_TRANSFER”)
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%, [d/Date] = PREVIOUS(1, “MONTH”)) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/Date] = PREVIOUS(1, “MONTH”))
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%, [d/CHANGE_REASON] = “CC_TRANSFER”) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/CHANGE_REASON] = “Closing”) *-1
ENDIF
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”)

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL

Member/Measure

COST_CENTER_Hier

Leaf

Single

DATE

Member/Measure

Date

FYQP

Any

CC_LEVEL1

Member/Measure

COST_CENTER_LEVEL_1

Single

CC_LEVEL2

Member/Measure

COST_CENTER_LEVEL_2

Single

CC_LEVEL1_PREV

Member/Measure

COST_CENTER_LEVEL_1

Single

CC_LEVEL2_PREV

Member/Measure

COST_CENTER_LEVEL_2

Single

CC_TARGET

Member/Measure

COST_CENTER_Hier

Leaf

Single

StartPeriod

Member/Measure

Date

FYQP

Leaf

Single

Pushing data from Drivers model to original plan model – After splitting the data among the suffix & original cost center in driver model based on drivers we will be pushing the data for that plan version into original plan model. This data action has two steps:In first step we will be deleting existing records using advanced formula step –MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER] = (BASEMEMBER([d/COST_CENTER].[h/H1], %CC_SEL%))

DELETE()

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL

Member/Measure

COST_CENTER

Leaf

Single

DATE

Member/Measure

Date

FYQP

Any

In second step we will be using cross model copy step and mapping the common dimensions.At Last, Calculating closing for the changed cost centers:MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER] = (BASEMEMBER([d/COST_CENTER].[h/H1], %CC_SEL%))

Parameters:

Parameter Type

Measures/Dimension

Hierarchy

Leaf

Cardinality

CC_SEL

Member/Measure

COST_CENTER

Leaf

Single

DATE

Member/Measure

Date

FYQP

Any

After successful execution of above process in the given order, we will be able to achieve the FTE walk between the two functions, A and B with the details of change in total FTE between them. accurately calculate the FTE and its associated costs related to each function. The final output will be as follows:

Cost Center

Change Reason

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Function A

CC12345678

Opening

20000

CC12345678

Closing

20000

Function B

DD12345678__DB25_1

Opening

2500

DD12345678__DB25_1

Cost Center Transfer

-2500

DD12345678__DB25_1

Closing

2500

Function A

DD12345678

Opening

2500

DD12345678

Cost Center Transfer

2500

DD12345678

Closing

2500

Fig: FTE Walk of Functions with time dependency

Conclusion

This solution demonstrates how to implement a time-dependent hierarchy freeze in SAP Analytics Cloud without altering user-facing views or disrupting business workflows. It provides clarity on historical movements and supports accurate FTE and cost analysis across functions.

Feel free to provide your valuable feedback and welcoming question and suggestions for this article.

for more questions regarding the blog post please go through the below link:

https://answers.sap.com/questions/ask.html

References

Analytics Designer API Reference Guide:

https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/2024.3/en-US/index.html

ObjectiveWhen Finance transfers Master Cost Centers from one Function to another within the Cost Hierarchy, historical FTE and Employment Cost data should remain associated with the original Function and Sub-Function to ensure accurate analysis. At the same time, these transfers must be reflected as movements (transfers in/out) in FTE walks. This ensures that the closing position reconciles accurately with the opening position, clearly accounting for the reasons for change.Problem StatementThe SAP Analytics Cloud does not currently support time dependency on the Hierarchy. This means that when Finance transacts a budget transfer by moving Master Cost Centre(s) out of one part of the Cost Hierarchy (i.e. “Function A”) to another part of the Cost Hierarchy (i.e. “Function B”), the SAC application:Ignores which Functions and Sub-Functions previously held the original budget.Shows all historical actuals and budgets as if they always belonged to Function B.Makes it difficult to show the transfer between Function A to Function B.Reports and dashboards showing FTE walks and Employment Cost walks by Function and/or Sub-Function do not reconcile.Without Time Dependency on the Cost Center Hierarchy, it will not include Function A & Function B previous FTE budget or actuals:Fig: FTE Walk of Functions without time dependency (pre-image)Fig: FTE Walk of Functions without time dependency (post-image)Solution StatementTo track the movement of Cost Centers between hierarchies, we will use a ‘Cost Center Transfer’ change reason. This will capture the transfer and allow it to be displayed in reports, showing the exact movement of cost centers between the nodes.Fig: FTE Walk of Functions with time dependencyA separate model has been created in which all levels of the Cost Center hierarchy are treated as dimensions. This approach flattens the structure, allowing for the identification of hierarchy changes using transaction data. The transaction data—referred to as Drivers—is generated in the BW system. In this case, a logic has been implemented in BW that checks the master data to identify the month of the movement and generates the corresponding driver values.Fig: Architecture for Time dependent hierarchy freeze models Original Planning Model – this is the main model where all the planning data is captured and the dimensions in the model are:Date – Fiscal Year, Quarter, Period Hierarchy.Version – Actual, DB (Budget).Cost Center – Cost Center with Hierarchy and the properties.Change Reason – Change Reasons for FTE changes (Growth, Efficiency, Offshoring, Cost Center Transfer, Opening and Closing).Audit Trail – Capturing the audit.Drivers Model – this is the model where all the drivers are stored and the splitting data into suffix & original cost center calculation will be part of this model. The dimensions in the model are:Date – Fiscal Year, Quarter, Period Hierarchy.Version – Actual, DB (Budget).Cost Center Hier – Cost Center with Hierarchy and the properties.Cost Center Level 1 – Level 1 nodes of the Cost Center hierarchyCost Center Level 2 – Level 2 nodes of the Cost Center hierarchyChange Reason – Change Reasons for FTE changes (Growth, Efficiency, Offshoring, Cost Center Transfer, Opening and Closing).Audit Trail – Capturing the audit.Sample Cost Center Hierarchy:Fig: Sample Cost center hierarchy Sample Cost Center dimension master data:IDDESCRIPTIONHIERARCHYCC1CC2CC12345678CC12345678Function AFunction ASub-Function ADD12345678DD12345678Function BFunction BSub-Function BHere, CC1 and CC2 refer to the Cost Center Level 1 and Cost Center Level 2 nodes of the hierarchy, respectively.Drivers play a key role in achieving this and those should be properly aligned with changes in the hierarchy. In the current scenario, these drivers are created in the BW system. From the drivers, we can identify the changes in the hierarchy as follows: 1 – No change-1 – Cost center changed the hierarchy 2 – Cost Center arrived in new nodeBelow are the sample drivers for Cost Center under function A & function B:Cost CenterCost Center Level 1Cost Center Level 2AprMayJunJulAugSepOctNovDecJanFebMarCC12345678Function ASub-Function A111111111111DD12345678Function BSub-Function B111111-1 DD12345678Function ASub-Function A 21111Based on the example above, the cost center “DD12345678” was transferred from Function B to Function A in November. This means that “DD12345678” was part of Function B until October and then moved to Function A in November.Cost CenterChange ReasonAprMayJunJulAugSepOctNovDecJanFebMarFunction A CC12345678Opening200002000020000200002000020000200002000020000200002000020000CC12345678Closing200002000020000200002000020000200002000020000200002000020000Function B DD12345678__DB25_1Opening250025002500250025002500250000000DD12345678__DB25_1Cost Center Transfer -2500 DD12345678__DB25_1Closing250025002500250025002500000000Function A DD12345678Opening000000025002500250025002500DD12345678Cost Center Transfer 2500 DD12345678Closing000000250025002500250025002500The walk of the FTE will look like the above table once the hierarchy freeze logic has been executed.Hierarchy Freeze LogicA story has been created in SAP Analytics Cloud using scripting capabilities, divided into two pages. The First page consists of logic for creating cost center nodes, which will initially reside in the old function and copying data from the original model to the other model where we have loaded the drivers. The Second page, based on the drivers will move out data from the original cost center to the newly created similar cost center node. In the above case, we have created the cost center with suffix _DB25_1. The number keeps on increasing if there are multiple movements of cost center from one function to another within a year.For e.g. if the cost center hierarchy again changes within year a new node with suffix DB25_2, DB25_3, etc.., will be created, here DB denotes the budget version and 25 denotes the financial year and the number at the end denotes the number of times the function has been changed for that cost center.To clarify the naming system:DB: Denotes the budget version, ensuring that the cost center hierarchy is tied to specific budget plans.25: Represents the financial year._1, _2, _3…: The numeric suffix represents how many times the function has been changed for that cost center in the given financial year. For example:DB25_1: First movement of the cost center within the year.DB25_2: Second movement, and so on.This naming mechanism is extremely useful for tracking historical changes to cost centers and ensuring that data is consistently maintained, even as cost centers are moved across functions within the same fiscal year.Page 1 – Cost Center Node Creation:The new cost center with a suffix will be created whenever the drivers have the value -1, the number of -1’s within a year decides how much suffix nodes will be created for that cost center. The new node created will have same properties as of original cost center, with the only difference being the function assignment.Sample SAC Table with Drivers :Cost CenterFunctionSub-FunctionAprMayJunJulAugSepOctNovDecJanFebMarDD12345678Function BSub-Function B111111-1 DD12345678Function ASub-Function A 21111SAC Table After filtering Driver value as -1:Cost CenterDescriptionMonthCost Center Level 1Cost Center Level 2DriversDD12345678DD12345678OctFunction BSub-Function B-1For the above logic, we have created a custom script written on a button to perform the task of creating cost center node.A table is created with all the -1’s filtered, from this table the script reads the cost center and its properties to generate new nodes. For creating new node, we have created a script variable with type Planning model member and filled the variable reading table with all the cost center, its levels, hierarchy id and properties.Fig: Script Variable Declaration CC_MEMBER[0] = (
{
id:“DD12345678”,
description: “DD12345678”,
properties:
{
CC1:“Function B”
CC2:“Function B”
MONTH:“Oct”
},
hierarchies:
{
H1: {
parentId: “Function B”
}
}
}
); Then, the updateMembers() API of the planning model is used to create a master data record for the above combination.E.g. PlanningModel_1.updateMembers(“<cost center dimension>”,CC_MEMBER); On successful execution of the above line the master data record will be created and will be shown in dimension as below:IDDESCRIPTIONHIERARCHYCC1CC2CC12345678CC12345678Function AFunction ASub-Function ADD12345678DD12345678Function BFunction BSub-Function BDD12345678__DB25_1DD12345678__DB25_1Function AFunction ASub-Function AAdditionally, to assign the data to the newly created node, we use the Copy Data action to transfer data from the original model to the driver model. The value will be set to unassigned for cost center–level dimensions, as these do not exist in the original model.Page 2 – Time Dependency LogicThis page holds the logic for aligning data according to hierarchy node changes.Note: For each cost center, the data action must run as many times as it changes hierarchy during the year. In other words, the data action is cost center–specific and depends on the number of hierarchy changes.We have created two tables: one to identify all the cost centers have “-1” as driver. Then applying that cost center filter from table 2 to table 1 to get all the combinations.Table 1:MonthCost CenterCost Center Level 1Cost Center Level 2DriversAprCC12345678Function ASub-Function A1AprDD12345678Function BSub-Function B1MayCC12345678Function ASub-Function A1MayDD12345678Function BSub-Function B1JunCC12345678Function ASub-Function A1JunDD12345678Function BSub-Function B1JulCC12345678Function ASub-Function A1JulDD12345678Function BSub-Function B1AugCC12345678Function ASub-Function A1AugDD12345678Function BSub-Function B1SepCC12345678Function ASub-Function A1SepDD12345678Function BSub-Function B1OctCC12345678Function ASub-Function A1OctDD12345678Function BSub-Function B-1NovCC12345678Function ASub-Function A1NovDD12345678Function ASub-Function A2DecCC12345678Function ASub-Function A1DecDD12345678Function ASub-Function A1JanCC12345678Function ASub-Function A1JanDD12345678Function ASub-Function A1FebCC12345678Function ASub-Function A1FebDD12345678Function ASub-Function A1MarCC12345678Function ASub-Function A1MarDD12345678Function ASub-Function A1Table 2: MeasuresDrivers CC1Function A CC2Sub-Function ACost CenterDescriptionDate DD12345678DD12345678Oct-1After filtering the Cost Center from Table 2 into Table 1 will look like below:MonthCost CenterCost Center Level 1Cost Center Level 2DriversAprDD12345678Function BSub-Function B1MayDD12345678Function BSub-Function B1JunDD12345678Function BSub-Function B1JulDD12345678Function BSub-Function B1AugDD12345678Function BSub-Function B1SepDD12345678Function BSub-Function B1OctDD12345678Function BSub-Function B-1NovDD12345678Function ASub-Function A2DecDD12345678Function ASub-Function A1JanDD12345678Function ASub-Function A1FebDD12345678Function ASub-Function A1MarDD12345678Function ASub-Function A1Storing the unique combinations in an array, allowing us to pass them as parameters to the data action used for generating the FTE walk across different functions.Additionally, an index array will be created to store the index where the hierarchy changes occur. This helps identify and pass the correct combinations to the data action. The unique combinations in the script will be stored as below:Cost CenterCost Center Level 1Cost Center Level 2DD12345678Function BSub-Function BDD12345678Function ASub-Function AThis array stores combination on which we need to perform data transfers, and these combinations are passed as parameters to the data action.The logic is designed to store the details in table into different variables. The system will pass the first combination data into node with Suffix (__DB25_1) and the second into the original cost center as the cost center only moved once in the hierarchy.A check has also been added in the script to check if the data already exist in the same suffix cost center (i.e. if logic ran for the same cost center prior then no need to run/change anything) then skip the data action run for that suffix cost center. otherwise running data actions in the below order:Copying previous year DB, Actual FTE data into the suffix cost center – Clearing the existing historical data for Actual and DB version and then copying data into the suffix node cost center and original cost center based on the month the hierarchy has changed. This data action is primarily divided into three steps mainly:Delete existing data of Previous year – using DELETE() function in advanced formula of data action deleting previous year data. Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL_TARGET%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DELETE()
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SEL_TARGETMember/MeasureCOST_CENTERH1LeafSinglePY_SELMember/MeasureDateFYQPAnyAnyCopy data to Cost Center combination of Previous year – using DATA() & RESULTLOOKUP() function fetching and storing data to target cost center (suffix & original cost center). Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DATA([d/AUDIT_TRAIL] = “#”, [d/COST_CENTER] = %CC_SEL_TARGET%) = RESULTLOOKUP()
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SELMember/MeasureCOST_CENTERH1LeafSingleCC_SEL_TARGETMember/MeasureCOST_CENTERH1LeafSinglePY_SELMember/MeasureDateFYQPAnyAny Clearing data into original cost center – using DELETE() function in advanced formula of data action deleting data from original cost center for the month before suffix cost center data copied. Sample data action advanced formula code:MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER] = (%CC_SEL%)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %PY_SEL%))

DELETE()
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SELMember/MeasureCOST_CENTERH1LeafSinglePY_SELMember/MeasureDateFYQPAnyAnyRunning Data action for copying data according to drivers on the hierarchy nodes (suffix & original cost center) – this Data action is helping us in splitting data based on drivers into suffix & original cost centers.CONFIG.TIME_HIERARCHY = FISCALYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = ON
MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/COST_CENTER_Hier] = (BASEMEMBER([d/COST_CENTER_Hier].[h/H1], %CC_SEL%))
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER_LEVEL_1] = (“#”)
MEMBERSET [d/COST_CENTER_LEVEL_2] = (“#”)

ELSE
// If consecutive -1 are not found then run below code
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%,
[d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”)
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%,
[d/COST_CENTER_Hier] = %CC_TARGET%, [d/CHANGE_REASON] = “CC_TRANSFER”) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/CHANGE_REASON] = “Closing”) *-1
ENDIF
ELSEIF RESULTLOOKUP([d/Z_D_HR_HCP_AUDIT_TRAIL] = “HIERARCHY”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/Measures] = “FTE”) = 0 THEN
// When Shifted to New node create Cost Center transfer node.
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”) * 0
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%, [d/CHANGE_REASON] = “CC_TRANSFER”) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”, [d/CHANGE_REASON] = “Closing”) * 0
ELSE
DATA([d/AUDIT_TRAIL] = “HIER_FINAL”, [d/COST_CENTER_LEVEL_1] = %CC_LEVEL1%, [d/COST_CENTER_LEVEL_2] = %CC_LEVEL2%, [d/COST_CENTER_Hier] = %CC_TARGET%) = RESULTLOOKUP([d/AUDIT_TRAIL] = “#”)
ENDIF
ENDFOR
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SELMember/MeasureCOST_CENTER_HierH1LeafSingleDATEMember/MeasureDateFYQPAnyAnyCC_LEVEL1Member/MeasureCOST_CENTER_LEVEL_1NANASingleCC_LEVEL2Member/MeasureCOST_CENTER_LEVEL_2NANASingleCC_LEVEL1_PREVMember/MeasureCOST_CENTER_LEVEL_1NANASingleCC_LEVEL2_PREVMember/MeasureCOST_CENTER_LEVEL_2NANASingleCC_TARGETMember/MeasureCOST_CENTER_HierH1LeafSingleStartPeriodMember/MeasureDateFYQPLeafSinglePushing data from Drivers model to original plan model – After splitting the data among the suffix & original cost center in driver model based on drivers we will be pushing the data for that plan version into original plan model. This data action has two steps:In first step we will be deleting existing records using advanced formula step –MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER] = (BASEMEMBER([d/COST_CENTER].[h/H1], %CC_SEL%))

DELETE()
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SELMember/MeasureCOST_CENTERH1LeafSingleDATEMember/MeasureDateFYQPAnyAnyIn second step we will be using cross model copy step and mapping the common dimensions.At Last, Calculating closing for the changed cost centers:MEMBERSET [d/AUDIT_TRAIL] = (“#”)
MEMBERSET [d/Measures] = (“FTE”)
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/FYQP], %DATE%))
MEMBERSET [d/COST_CENTER] = (BASEMEMBER([d/COST_CENTER].[h/H1], %CC_SEL%))

If(RESULTLOOKUP([d/CHANGE_REASON]=”CC_TRANSFER”) < 0) THEN
DATA([d/CHANGE_REASON] = “Closing”) = 0
ELSE
DATA([d/CHANGE_REASON] = “Closing”) = RESULTLOOKUP([d/CHANGE_REASON] = “CC_TRANSFER”)
ENDIF
Parameters:IDParameter TypeMeasures/DimensionHierarchyLeafCardinalityCC_SELMember/MeasureCOST_CENTERH1LeafSingleDATEMember/MeasureDateFYQPAnyAnyAfter successful execution of above process in the given order, we will be able to achieve the FTE walk between the two functions, A and B with the details of change in total FTE between them. accurately calculate the FTE and its associated costs related to each function. The final output will be as follows:Cost CenterChange ReasonAprMayJunJulAugSepOctNovDecJanFebMarFunction A CC12345678Opening200002000020000200002000020000200002000020000200002000020000CC12345678Closing200002000020000200002000020000200002000020000200002000020000Function B DD12345678__DB25_1Opening250025002500250025002500250000000DD12345678__DB25_1Cost Center Transfer -2500 DD12345678__DB25_1Closing250025002500250025002500000000Function A DD12345678Opening000000025002500250025002500DD12345678Cost Center Transfer 2500 DD12345678Closing000000250025002500250025002500Fig: FTE Walk of Functions with time dependency ConclusionThis solution demonstrates how to implement a time-dependent hierarchy freeze in SAP Analytics Cloud without altering user-facing views or disrupting business workflows. It provides clarity on historical movements and supports accurate FTE and cost analysis across functions.Feel free to provide your valuable feedback and welcoming question and suggestions for this article.for more questions regarding the blog post please go through the below link:https://answers.sap.com/questions/ask.htmlReferencesAnalytics Designer API Reference Guide:https://help.sap.com/doc/958d4c11261f42e992e8d01a4c0dde25/2024.3/en-US/index.html Read More Technology Blog Posts by Members articles

#SAP

#SAPTechnologyblog