SAP Analytics Cloud – Generating Transactions from Master Data Attributes

[sukanya_krishnan]

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:

Employee Grade	Unit Rate (Prop)
EG001	90
EG002	67
EG003	91
EG004	70
EG005	80

Transaction Data:

Project	Employee Grade(Prop)	Unit Rate	Effort	Cost
Project1	EG002	67	160	10720
Project2	EG005	80	144	11520
Project2	EG004	70	160	11200

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

Project	Employee Grade (Prop)	Unit Rate	Effort	Cost
Project1	EG002 --> EG003	67 --> should change to 91	160	10720
Project2	EG005 --> EG001	80 -->  should change to 90	144	11520
Project2	EG004	70	160	11200

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