Import of dimension set rules
A couple of days ago I had a question. One guy had to generate dimension set rules for ledger accounts, based on information from an Excel file. The file contained 3 columns: the first one for ledger accounts, the second one for values of the first dimension (say, Departments), and the third column for values of the second dimension (say, Cost Centers):
'10510', '49', '0'
'10511', '49', '3'
'10600', '49', '0'
'10600', '49', '6'
'10600', '50', '0'
'10651', '49', '0'
'10651', '50', '0'
'10800', '49', '0'
'10811', '49', '0'
'10900', '49', '0'
and so on. As can be seen from the sample:
- only department 49 and cost center 0 are valid for the account 10510;
- only departments 49 and 50 are valid for 10600, while cost centers 0 and 6 are valid for department 49, and cost center 0 is valid for department 50;
- the accounts 10800, 10811 and 10900 have department 49 and cost center 0 as the only valid values
etc.
I have written a sample job that generates dimension set rules based on this information.
Pre-conditions for the job:
- “Dept-cc” dimension set is created, composed of Department and Cost Center dimensions.
- The dimension set has neither hierarchies nor rules created.
- All mentioned ledger accounts exist in the chart of accounts.
As a result of the job execution:
- Several dimensions set hierarchies will be generated. The number of hierarchies will be less than number of accounts, because some accounts have similar validation rules.
- The hierarchies will be updated and approved.
- There will be a dimension set rule collection created for “Dept-cc” dimension set.
- There will be several dimension set rules created – one per ledger account. The rules will be assigned to the rule collection.
The job may be found in the attachment.