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 @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
Nice Blog
Very Informative!!!!
Useful Information