SAP Analytics Cloud – Generating Transactions from Master Data Attributes

As SAC consultants, there might be situations where we have to migrate from other planning technologies to SAC. There would be lot of situations where we might have to define process in SAC, which is different from existing ones. Like in SAC, changing dimension’s property will not affect the transaction data automatically if attribute value is leveraged as part of transaction. Let us consider below example

Master Data:

Transaction Data:

Here Unit Rate would be maintained as property of Employee Grade dimension and same would have to be leveraged to calculate cost of projects. Therefore, it is mandate to create a transaction entry linking Project and Employee Grade and associating the unit rates accordingly. This can be configured using SAC’s Advanced Scripts

Scenario 1:

One time history load to create transactions for all projects. Post load, if unit rate property of employee grade is changed, same should reflect in transaction.

Step 1: Copy unit rate from property of employee grade to unassigned project member

FOREACH [d/ Employee Grade] 

@F=ATTRIBUTE ([d/ Employee Grade]. [p/Unit Rate]) 

DATA ([d/Measure] ="Unit Rate", [d/Project] ="#") = @F 

@F=0 

ENDFOR

Step 2: Traverse through projects for each employee grade in model OR selected employee grades during maintenance to copy from unassigned project member to right project member

FOREACH [d/Employee Grade]

FOREACH [d/Project]

IF [d/Project].[p/Employee Grade] = [d/Employee Grade] THEN

DATA ([d/Measure] = "Unit Rate", [d/Project]=[d/Project.[p/ID], [d/Employee Grade] = [d/Project].[p/Employee Grade]) = RESULTLOOKUP ([d/Measure] = "Unit Rate", [d/Project] = "#")   

END IF

END FOR

END FOR

Scenario 2:

If as part of project’s master data maintenance, employee grades included in projects are modified. Transaction data should get modified accordingly. Considering same example

In above example – as part of Project master data, employee grade is maintained. If project owner modifies the grade associated with project, the transaction should get modified accordingly and hence cost should get changed accordingly. Same can also be implemented using Advanced Scripting

DELETE ([d/Measure] ="Unit Rate", [d/Project] =%ProjectID%)

DATA ([d/Measure]="Unit Rate",[d/Project]=%ProjectID%,[d/Employee Grade]=%Grade%) = RESULTLOOKUP([d/Measure]="Unit Rate",[d/Employee Grade]=%Grade%)

This way – complexity from business user perspective would be reduced because they do not have to input or maintain separate transactions for unit rate. Rather master data is loaded and maintained and click of data action creates/modifies the transaction accordingly.

Regards,

Sukanya