07-14-2022 12:27 PM
Hi I want my UD3 value to be None for all BS and Revenue Accounts and then only look to the department to the 1:1 mapping if it is an expense. I created composite rules that have for an example the expression A#[1*]:UD3#[*] with an order of zero and then the 1:1 mappings with the order of one but it still finds the 1:1 rule first. Does the order only work within the Type and they always trigger 1:1 types first? If that is the case an recommendations?
PS we are currently doing this in the data source but we run into a problem when we have a new account that they have to reupload the file again so that it knows what the account mapping is. I figured if I put it in the UD3 transformation that the account transformation would have already happened.
Thanks in advance!
07-14-2022 02:37 PM
"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
07-14-2022 02:50 PM
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*].
07-14-2022 03:03 PM - edited 07-14-2022 03:08 PM
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.
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.
07-14-2022 03:39 PM
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.
07-14-2022 04:48 PM
I'm not sure if there is a way to do a -to-many style mapping. Everything we've done is a -to-one mapping. Hopefully someone else from the community can jump in and provide suggestions.
That said, typically there is some pattern that can be followed from the source system.
07-15-2022 06:04 AM
I suggest for the Account <> 7* use the Mask type * to * and then use a conditional BR something like below for the UD3 transformation.
Dim account As String = args.GetTarget("A#") Dim u3 As String = args.GetSource("U3#") If Not account.StartsWith("7") Return "None" Else Return u3 End If