"Does the order only work within the Type and they always trigger 1:1 types first?"
Correct. The transformation rules sequential process the Types and then the order within each type. 1:1 mappings will always come before Composite.
Based on the information you provided it sounds like you could put your Expense+Department mapping as the Composite Rule and then put a Mask for everything else; from * to None. This way the expense account mappings get picked up first and everything else will fall to the mask rule.
Lastly, if I understand your PS correctly, I would strongly encourage you to avoid making transformations in the Data Source steps. Rather, make these changes in the Transformation Rule itself. There are certainly times where it makes sense to transform the data during sourcing, but you'll have better auditability and maintenance if you put those transformations in the Transformation Rules.
Hope this helps and follow-ups are welcomed. Cheers! -db
Bummer on the ordering. Thanks for the quick response. I had considered your idea but the end users can update for their new department mapping during the import and I am worried they will just add the 1:1 instead of the Expense+Department but let me give it a try. Question for you what is the syntax to have the source value in the composite mapping ie A#[7*]:?? where the ?? is the source value for department? I only figured out how to include the target value in the syntax with the A#[7*].
I'll assume your 7x ranged accounts are the expense ones. You generally have it correct in your original post. The format would be:
Rule Expression: A#7*:U3#YourSourceSystemValue
Target: select one of the UD3s per your dimension hierarchy
The YourSourceSystemValue can use the pattern matching (eg. 6*) for many-to-one mappings or you can do one-to-ones depending on your needs.
Edit:
Are the 7x accounts only used with your departments in your source system? If so, the 1:1 mapping is a viable option. You only need the composite if your source system does not adhere to those rules (a common problem we have as well). Just wanted to double check.
OK then I guess I need to ask the question the other way. How can I indicate the target account starts with 7 or can I not do this? When I gave the example of A#[7*]: the 7 was the OS target account not the source account.
The OS 7x accounts should be the only one with departments but the source account numbers are not consistent. So I am trying to figure out how to make all records that have a target account <> 7* to equal 'None' and then all records that have a target account that is 7* to do the 1:1 mapping.
