Friday, April 4, 2014

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 can later remove the explicit mappings...























Friday, March 28, 2014

FDMEE PSU510 released (

Just from the owen...
Some defects are fixed in this PSU:
We are still waiting for Oracle to publish the new admin guide where we can see more details about new features. In the README we can only see one:
  • Format Mask Mapping
FDMEE now supports the ability to specify a format mask for a target member. What is this?
This functionality is useful when designating the target member based on some part of the source member, an additional prefix, suffix, or replacement text for the target.
This feature is mainly thought for mappings where target members are defined as concatenated segments (like target profile dimension in ARM) although it can be also used for mappings where target is not delimited.
The format mask is available for the target member specification for all mapping types except Explicit.
I don't want to adventure before documentation is published I will leave this as standby.
But only 1 new feature??? just after digging deeper I noticed that there is a new ODI Scenario:

which tells me that we can now load metadata from SAP BW :-) The ODI Scenario is imported when patch is applied:
I know, it's weird that Oracle did not put it in the README...

If any of you are wondering... why metadata from SAP BW and not from SAP ERP?
My understanding is that to get the metadata from ERP we would need to go after many different tables and subsystems to get that information (large effort) As the market is mainly demanding metadata from SAP BW, here you are (you demand? you get?)

Which FDMEE version is showed in EPM Workspace?
So waiting for the admin guide where hopefully new Format Mask Mapping will be detailed.


Thursday, March 27, 2014

Strings in Jython - Part 1

Strings are one of the most common objects used when scripting. In this post I will introduce how to work with strings in Jython. Besides I will show two examples of parsing source files when our data contains string delimiters (' or ") to avoid errors when importing.

Strings in Jython
  • A string is a list of characters in order (letter, a number, backslash, quote...)
  • Jython recognizes as strings everything that is delimited by quotation marks (" " or ' ')
  • String/Array index starts at 0
  • We use [ ] to access characters in a string

  • Strings can be enclosed with single quotes 'mystring'
  • Strings can also be enclosed with double quotes "mystring"
  • Strings can also be enclosed in triple quotes when multi-lines are used
  • Substring is str[Start Position: Start Position + Length]
Raw Strings
Raw strings are used when we want to express strings that would be modified by escape sequence processing. They are very useful when using paths as literals. Raw strings are prefixed by r.
In the following example we want to print the new line character:
Operating with Strings
Although there are many functions and operators for Strings, the most common are:
  • Concatenation (+): combines 2+ strings into new string object
  • Repeat (*n): generates a string with n characters
  • split (): splits a string into a string array using the separator defined. If not separator is defined, whitespace is used
  • startswith/endswith: checks if a string starts or ends with a substring
  • strip: strip(), lstrip(), rstrip() are methods for removing any character from both ends of a string. If the character to be removed are not specified then whitespace will be removed. Same functionality as TRIM in VB.
  • join: takes a list of strings and joins them together with the calling string in between each element
  • Test: a string can be tested for truth value (T or F)
  • Replacing templates (%): the percent "%" character marks the start of the string to be included
    • %s used for strings
    • %d used for numbers
    • %f used for floating points

You can find more details about working with strings (functions, methods, operators...) in:

Array Slice Notation
We can use slice notation to access characters or substrings of a string.
They key point is to understand that :end represents the first value that is not in the selected slice.
So the difference between end and start is the number of elements selected.
The other key point is that start or end may be a negative number, which means it counts from the end of the array instead of the beginning.
You will have to take care with situations where there are fewer items than you ask for. Jython will not return an error but an empty list. On the other hand, if you just want to access a position by using [ ] then you will get an error if the position does not exist:
Example 1: my account description has apostrophe character
Let's say we have the following source file where our Description has apostrophe (single quote):
If we try to import the file we can see that any of the three rows are imported:
The process log shows exceptions raised while executing the import format to AMOUNT field:
It seems that our apostrophe is causing the issue. FDMEE thinks that the single quote delimits the data row when it is passed to the import format process and therefore it does not find any amount in the line:

EastSales;LaborCost;akaFDMEE's example;[ICP None];Comma_PDAs;NoCustomer;NoChannel;[None];1000

We can easily test it by removing the single quote to see how data is imported. But we don't want this :-)
We would like to parse the file so the amount is processed and rows are successfully imported.
What are the key points for our solution?
- Replace apostrophe by empty string when processing amounts
- Need to parse any field after the description having the apostrophe
The following script replaces the apostrophe (single quote) by empty string and then return the 9th column which is actually the amount:
This script can be assigned to the amount field in the import format:
If we execute the import with the new import format we can verify that columns are imported only until Description:
So the real issue is when importing the Description into FDMEE. Any of the fields which number is greater than the description one are imported. FDMEE thinks the apostrophe is delimiting the row so it does not find fields greater than 3.
We have to create one script for each dimension...and set field number to 1 for all of them.
Don't want to do all of this? I would suggest moving the description or the field having the apostrophe to the last column. In this way we don't need any parse script (including amount) in order to get amount imported :-) 
All data will be imported successfully: 

Example 2: all my columns are enclosed in double quotes
There are many situations where files are exported from source systems with the following format:
As comma is the delimiter and amount is using Euro notation (comma as decimal delimiter), all fields are enclosed in double quotes " " in order to have the correct number of fields.
Let's try to import this file and see the results:
We can see that double quotes are not stripped from other columns but amount.
There are many of them consists in stripping double quotes for each dimension. The following script will do that:
We can assign it to all columns so when we import data again all data looks good now:
Other solutions? give a try!