#FORMAT Mask Mapping for Target Values

It's good to see that FDMEE is being enhanced. For those who come from legacy FDM we were not used to new features since years!

As I described earlier in previous post, version 11.1.2.3.510 brings something new:
FORMAT MASK MAPPING

So what we have here? Think about the following example:

Source Account Value: 1001-2000-D123
Target Account Value: 11-Sales-123Cons

My source account has 3 different segments delimited by '-', and I want to define a mapping rule as follow (Power to the imagination!):

• Ignore 2nd and 3rd characters of 1st segment
• 2nd segment value starting with 2 indicates Sales so we have to replace the segment value by "Sales"
• Third segment indicates subaccount. We have to ignore first character and add prefix Cons to subaccount code

So first mapping rule I can think would be to create a mapping script? maybe new #SQL script?

Yup, it could be but what about the following target value?
#FORMAT("?##?-Sales-#???Cons","-")

What the h*** is that? 假借字 形聲字
Chinese? Let's give a try in FMDEE!

My first Format Mask Mapping
My source file will be a very basic one:

I have highlighted source account value: 1001-2000-D123

Let's create a mapping rule using new Format Mask Mapping as follow:

Note: source value? 1001-2* is fine for this example.

Let's run the Data Load Rule and check target account value:

We got the desired target account: 11-Sales-123Cons

Time for explanations:

#FORMAT(<Format Mask>,<Segment Delimiter>)

• #FORMAT - indicates mapping of type Format Mask in the target member
• <Format Mask>
• ? - Basically take from the source value the character in same position as ?
• # - Basically skip/drop the from the source value the character in same position as #
• Character -  Include character (or string) as it is
• * - Include all characters from source segment or source
• <Segment Delimiter> - Character used to delimit segments in source and target members. It must be the same character for both source and target. Delimiter is optional as source value may not represent delimited segments.

#FORMAT("?##?-Sales-#???Cons","-")

• 1st Segment mapping: ?##?
• Take 1st and 4th source characters. Skip 2nd and 3rd > 11
• 2nd Segment mapping: Sales
• Replace the entire source segment by "Sales" > Sales
• 3rd Segment mapping: #???Cons
• Skip 1st character, take 2nd, 3rd, and 4th, and add suffix "Cons"  > 123Cons

 When special characters are used in conjunction there are some particularities:

Use Cases
When integrating with ARM (Account Reconciliation Manager), we need to load data to specific ARM profile. A profile is defined by a combination of segments (Ex: Account-Company-Department). Profiles are defined as part of the ARM configuration based on business requirements.

However this new feature can be also useful for non-ARM integration either using or not delimiters as part of target member definition.

More examples
Here you have few more examples:

Have a look to mapping rules 05 and 06.

Let's say that we have XXABCDEYY as source value.

Rule 05 would return ABCDEYY. Why? When using "*" before "#", FDMEE will take all remaining characters. In other words, "*" overrides "#".

Rule 06 fixes this issue and remove prefix and suffix successfully,

I leave source and target accounts so you can analyze each mapping applied: 

To conclude, #FORMAT completes the three # mapping types for FDMEE: #FORMAT, #SCRIPT, and #SQL
Has it limitations? I'm sure once people start using it more use cases will come up. In any case this a very good starting point for sure.

Enjoy!

Validating mapping tables in FDMEE

One of the nicest enhancements in FDMEE is the capability of validating target members from mapping tables.
What is that? to better understand I will go back to Classic FDM...
Once upon a time...
When you created a mapping in FDM (I said created so not imported) and your target was explicit member (no wildcards), that target member had to be valid (exists in the dimension of your target application)
For example, I want to create a explicit mapping that maps source value BostonSales to target member EastSales. After typing manually EastSales in Target Entity then new row is added when we click Update Grid:

We could also have browsed for the target member instead of typing it which ensures our member is valid as we did not have type it avoiding potential typos:

But what happened if we had a typo? Let's type EsstSales:

Happens that after trying to update the grid we got an error as EsstSales is an invalid member of Entity dimension.

So after adding mappings, explicit target members were validated when updating the grid.

Obviously, mappings where target member had any wildcard character were not validated against target dimensions:

What about importing mappings? Classic FDM did not validate mappings (any type) when importing, only new mappings were added...

In the following example you can see how an explicit mapping for NYSales with an invalid target entity (EsstSales) is imported with no errors:

What about FDMEE?

Let's reproduce the same example in FDMEE:

 

Hmm, not validation error anymore...the new mapping rule was successfully added. It did not check my target entity EsstSales like Classic FDM did:

Wait a minute, what is that button labeled as Validate? Let's click on it :-)

So I can add mappings and then validate them by simply clicking a button, nice!

It will validate all target members in my mappings:

All? the same as Classic FDM, it will not validate target members having wildcard characters neither. The same as Classic FDM, obvious :-)

I can still add target members by browsing target dimensions:

I know, Descriptions are empty. As far as I know this is expected behavior as this functionality was not implemented yet. I know, weird.

Let's play a game: Find 1 difference. Easy one :-) 

Did you find it? I hope you have not wasted more than 1 minute :-) Any way I have to give you the solution, otherwise I cannot continue with this post!

You're right, Validate button is missing for Multi-Dimension mapping. Why? don`t worry, your explorer is OK, it's a bug.

So how I Validate my Multi-dimensional mappings?

I have been thinking in a workaround, just to play a little bit. In FDMEE you have now 2 import modes (Merge and Replace) and the option to validate mappings while they are imported (nice enhancement):

So I thought, why just create my mappings in TXT (or Excel) and then import them by selecting Validate option?

1. I created a multi-dimension mapping as follows:

2. Then I exported mappings for Entity dimension to TXT file:

We can see how mappings are exported to the text file:

3. I tried importing mappings for Entity dimension:

Select Merge and Validate options:

But file was imported with no errors!!!

4. Ok, then I thought... I will add an invalid mapping line to my file and import again (DummyMap):

As DummyValue does not exist it should raise a validation error:

Which one?

Ok, so the new line is validated but my multi-dimension mapping is not yet...

5. Let's give another try. I will change the target value for my dummy mapping to EsstSales:

After importing it again:

2 errors? Let's see them!

Woops, my multi-dimension mapping is now validated.

So what happened? to me the validation process takes all explicit target members (like EsstSales), validate them, and then mark as invalid rows any having an invalid target member. As our dummy mapping has an invalid target member, collaterally our multi-dimension mapping is marked as invalid too.

What do you think?

Do you have 100 multi-dimension mappings with explicit target member? create 100 explicit mappings, put all together in a file, import and validate...you can later remove the explicit mappings...

Enjoy!