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:

1. “Dept-cc” dimension set is created, composed of Department and Cost Center dimensions.
2. The dimension set has neither hierarchies nor rules created.
3. All mentioned ledger accounts exist in the chart of accounts.

As a result of the job execution:

1. 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.
2. The hierarchies will be updated and approved.
3. There will be a dimension set rule collection created for “Dept-cc” dimension set.
4. 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.