Friday, March 28, 2014

FDMEE PSU510 released (11.1.2.3.510)

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:

SAP_BW_LOAD_METADATA Version 001
which tells me that we can now load metadata from SAP BW :-) The ODI Scenario is imported when patch is applied:
SAP METADATA LOAD is not available yet.
I know, it's weird that Oracle did not put it in the README... Accidentally they left the ODI Scenario there but SAP METADATA LOAD will be available in next patch... (520?)

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.

Enjoy!

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: http://www.jython.org/docs/library/string.html

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 solutions...one 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!

Enjoy!



 

 

Tuesday, March 25, 2014

FDMEE PSU5 released (11.1.2.3.500)

I'm sure many of you were waiting this PSU to be released. You can take a look to John's blog.
It's here...together with the "Super Patch for all EPM Products" but (there's always a but)...nothing really new for FDMEE:
Only new certification for PSFT 9.2
Some bugs have been now fixed:

We will have to wait a bit more to see new features :-)


Monday, March 17, 2014

Bug & Workaround - SKIP not working in Logic Accounts

Hi all,

last week I got some feedback from a colleague saying that they tried using SKIP in Logic Accounts but it was not working as it was with Classic FDM.

Firstly, I would say that Logic Accounts have not been changed in FDMEE so they basically do the same as Classic FDM, creates additional data not coming from the source.

You have same operators but you will have to use Jython if you need to add any script to your logic accounts.

What about SKIP?
Skip is used to skip the calculation and therefore do not create the logic account when condition is met. In the example above the logic account will be only created if the current value is less than zero.

Let's see similar script in FDMEE using Jython:
We will multiply the current value of the logic account times 2 when it is less than zero. On the other hand, the logic account should not be created when CURVAL is equals or greater than zero. 

Bug
What happened if we execute the import?
Source Data:
Logic Accounts created:
Logic accounts are also created when CURVAL is greater than zero...not good :-)

If you take a look to the FDMEE process log you will notice how logic accounts are currently working.
Firstly, all logic accounts are generated. When I say all, I mean all:  

 INSERT INTO TDATASEG_T (
                    LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,ACCOUNT
                    ,ENTITY
                    ,VALID_FLAG
                    ,CURKEY
                    ,CALCACCTTYPE
                    ,ATTR13
                    ,AMOUNT
                    ,AMOUNTX
                    ,UD1
                    ,UD10
     ,DATAKEY
      )
      SELECT       LOADID
       ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,ACCOUNT
                    ,ENTITY
                    ,VALID_FLAG
                    ,CURKEY
                    ,CALCACCTTYPE
                    ,ATTR13
                    ,AMOUNT
                    ,AMOUNTX  
                      ,UD1
                      ,UD10
      ,TDATASEG_DATAKEY_S.NEXTVAL AS DATAKEY
      FROM (
                    SELECT LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                   
,'LA_UNITNEG' AS ACCOUNT                    ,ENTITY
                    ,'Y' AS VALID_FLAG
                    ,CURKEY
                   
,-1 AS CALCACCTTYPE
                    ,'LA_UNITNEG' AS ATTR13
                    ,SUM(AMOUNT) AMOUNT
                    ,SUM(AMOUNTX) AMOUNTX
                      ,UD1
                      ,UD10
        FROM TDATASEG_T
       
WHERE LOADID=996 AND (ACCOUNT IN ('Units'))        GROUP BY
                    LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,CURKEY
                    ,ENTITY
                      ,UD1
                      ,UD10
      ) LOGIC_ACCOUNT

2014-03-05 15:17:10,583 DEBUG [AIF]: Inserted 12 rows for logic account LA_UNITNEG
After logic accounts are generated, Jython function is executed for each logic account generated. We can see how an update statement is executed (CURVAL*2) for each logic account with negative balance (bold):

2014-03-05 15:17:10,605 DEBUG [AIF]: Executing function:
if 3598< 0:
 RESULT= 3598 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,609 DEBUG [AIF]: Executing function:
if 3689< 0:
 RESULT= 3689 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,641 DEBUG [AIF]: Executing function:
if -6988< 0:
 RESULT= -6988 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,645 DEBUG [AIF]:
         update TDATASEG_T set
         AMOUNT=-13976
         ,AMOUNTX=-13976
         where DATAKEY='96384'       

2014-03-05 15:17:10,648 DEBUG [AIF]: Executing function:
if 2999< 0:
 RESULT= 2999 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,651 DEBUG [AIF]: Executing function:
if 4064< 0:
 RESULT= 4064 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,654 DEBUG [AIF]: Executing function:
if 3874< 0:
 RESULT= 3874 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,657 DEBUG [AIF]: Executing function:
if -6254< 0:
 RESULT= -6254 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,662 DEBUG [AIF]:
         update TDATASEG_T set
         AMOUNT=-12508
         ,AMOUNTX=-12508
         where DATAKEY='96388'       
2014-03-05 15:17:10,664 DEBUG [AIF]: Executing function:

if 3455< 0:
 RESULT= 3455 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,667 DEBUG [AIF]: Executing function:
if 3291< 0:
 RESULT= 3291 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,671 DEBUG [AIF]: Executing function:
if 4678< 0:
 RESULT= 4678 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,674 DEBUG [AIF]: Executing function:
if 6874< 0:
 RESULT= 6874 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,677 DEBUG [AIF]: Executing function:
if 4157< 0:
 RESULT= 4157 * 2
else:
 RESULT = "skip"


So basically:
1. Generate all logic accounts
2. Update amount for logic accounts meeting conditions specified in our script
3. What about the rest of logic accounts which should be skipped?

Workaround
Although you can think in other workarounds based on your requirements, I will show you one which basically consists in hacking FDMEE a little bit :-)

We know that most of the FDMEE functionality is shipped by Oracle in ODI Scenarios. Logic accounts generation is implemented in scenario SCEN_COMM_LOAD_BALANCES_001.xml (E:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.3.00\workrep)
If we take a look to the code in the ODI Scenario we can see how codes jumps to the next Jython function (continue statement) when result equals "SKIP" (RESULT.upper() = "SKIP")
So actually FDMEE is not skipping the logic account as it was inserted in a previous step.
Any ideas? we could add a SQL statement in order to delete logic accounts from temporary table TDATASEG_T.
DELETE FROM TDATASEG_T WHERE DATAKEY=...
We have replaced continue statement by delete statement.
The new version of the scenario must be re-imported into the ODI work repository (INSERT_UPDATE mode). This action can be performed using ODI Studio.
After we import data again, we can see how the logic accounts are now correctly generated:
 
The FDMEE process log now shows the delete statement executed for each logic account that is skipped:

Hopefully Oracle will fix this issue in the patch (11.1.2.3.500?)

What we learnt?
- How logic accounts internally work (currently)
- Review code from ODI scenarios
- How to jump to next iteration in Jython (continue statement)

Enjoy!