Working for customers, preparing training, conferences and the most important one, Francisco Jr running around, have been keeping me busy during the last months.
One of the presentations I've been working on is "Black Belt Techniques for FDMEE" (aka BBT for FDMEE). I thought it was interesting for people to know how to meet requirements of different complexity with some techniques which of course, aren't in the books :-)
Although I can't go into too much detail (I don't want to spoil the presentation), this is a foretaste of what you will enjoy if attending to Kscope17.
The Requirement
As you know, FDMEE can pull data from very heterogeneous source systems. Once data has been extracted, it has to be mapped into our target system (let's say HFM). Usually, people responsible of maintaining mappings (aka mappers) are more familiar with target system rather than source.
This is not always the case but it's a common scenario when financial departments are split. How often do you hear "Not sure about this, we need to ask our ERP guy..."?
Another common scenario is that ICP/Custom dimensions mappings use source ERP account as a driver either importing source account into source ICP/Custom dimensions or using Multi-dim/Conditional maps.
Have you have you ever asked to the mapper: Would it be easier for you if you could use the HFM account to define ICP/Custom dimension mappings rather than source account?
In my case, I always do. And what I found is that if they can define mapping using the target HFM account, maintenance tasks are much simpler and the number of mapping rules is highly reduced.
Of course, the immediate question is: Can we do that? Yes we can. How?
Lookup dimensions as a Bridge, that's the answer
Lookup dimensions can be used in FDMEE for different purposes. How can they help us to meet our requirement?
- They don't have an impact on target application
- We can define a #SQL mapping to copy our target values into other source dimension values including the lookup dimension itself
- We can define the order in which the lookup dimension mapped
Have a look at this: Source Account > Target Account > Lookup > Source C1 > Target C1
Did you understand above flow? Let's put some lights on it.
Let's start defining our lookup dimension "HFM Account":
In this example, we are going to use the lookup dimension to copy the target HFM account into the source lookup. For that purpose, we need to make sure that the lookup dimension is mapped after Account dimension. As you can see above, sequence number for the lookup is 2 while Account has assigned 1.
Besides, column UD5 of TDATASEG(_T) has been assigned to HFM Account (I could have used any other like UD10 so I leave some UDx free in case we have new HFM custom dimensions).
Copying the Target HFM Account into other/lookup dimensions
As for any other dimension, we can create maps for lookup dimensions. Our main goal is to copy a target value into other dimensions so why not using a SQL mapping?
The good thing of mapping scripts is that we can include multiple columns:
- Set target HFM Account to "Lookup"
- Set source HFM Account (UD5) to target Account (ACCOUNTX)
Done, our target account has been copied and it's now available as a source value in our lookup dimension.
A picture is worth than a thousand words
Let's create a multi-dimensional mapping to show how this works:
Mapping says: when source Product is "'001" and HFM Account is "Price" then target Product is "P7000_Phones"
Thanks to our lookup dimension we can use the HFM account as a source. Mapping rule is clear and easy to create. No need to change the SQL map we created, that's static.
What happens in Data Load Workbench?
- Source ERP account "10100" has been mapped to "Price"
- "Price" has been copied to source HFM Account
- Product has been mapped using source Product and HFM Account
At some point, I expect Oracle enhancing multi-dim maps to support target dimensions also so let's see another example where this approach is quite useful as well.
Another example: write-back to SAP using SAP accounts as Source in Explicit maps
In this case, we are extracting data from HFM in order to generate report files for SAP (write-back)
The requirement is to map SAP IFRS using SAP Accounts.
Following our approach:
- Map HFM Account to SAP Account
- Copy SAP Account to source SAP IFRS
- Map SAP IFRS based on SAP Accounts
Let's see the workflow:
As you can see, we have now copied the SAP Account into another dimension rather than the lookup. That allows us to create our Explicit mappings using SAP accounts in a very easy way.
Cloud-Friendly
Once nice thing is that this solution is cloud-friendly. Data Management for the Cloud allows creating lookup dimensions and #SQL mapping scripts so you can implement it if not using my loved on-premises FDMEE.
More BTTs soon!
