Skip to Content
Technical Articles

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:

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 



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] = "#")   




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.




You must be Logged on to comment or reply to a post.