FDMEE PSU2 released (11.1.2.3.200)

Hi all,
long time since last post so sorry if you miss it :-)

Two weeks ago FDMEE PSU2 (11.1.2.3.200) was released by Oracle

After reviewing all new features I must say there are some nice features that if you are new you will not notice but if coming from Classic FDM (like me) you will put your hands up in the air!!!

As I did with PSU1 post, I will show you most of the new features but at the moment you must trust me (or I must trust myself) as the new admin guide has not been published yet. It has just been published

Before starting, just to highlight that PSU2 requires an ODI Patch to be applied:

This patch was released to fix a bug related to ODI jobs getting stuck when large volumes were processed.
From now on I will assume you can install both patches with no assistance... Come on!! What I suggest, as always, is to perform backups before starting :-)

So now, what´s new?

Loading of ICP transactions into HFM Intercompany Transaction Module
FDMEE now supports loading ICP transactions into ICT Module. 
Old classic FDM users will remember about HFM having 2 adapters. There was one we were not using so often (FM11I...). I say that because I haven't seen many customers using it, at least in Europe.

How does it work? once you Enable ICP Transactions in the Target Application a set of additional dimensions will be added as attribute dimensions:

Then you can create an Import Format to import data and ICP transactions:

(and) a word of warning... you need to enable ICT Module in HFM :-)

Loading Cell Texts into HFM

Same approach as Classic FDM: you can load Cell Texts into HFM from FDMEE Memo Items.

Memo Items allows you to add notes to your imported data and attach documents to them (see post-it icon below). 

There is something I be will missing here which is the possibility of importing Memo Items from source files in an easy way. There were some custom solutions in Classic FDM so there should be in FDMEE...hopefully. Small bug with memo indentation :-)

If you want to load Memo Items as Cell Texts in HFM you will have to set the corresponding option in the Target Application settings:

When we export our data to HFM, Cell Texts will be also loaded from Memo Items:

We can check them in HFM:

Do you want different format for the text (i.e: remove Title...)? then change on your own risk HFM_LOAD.vbs script.

In Classic FDM, memo items could be imported from Excel Trial Balance files...did not check yet in FDMEE so please feel free :-)

Some notes... 

- Enhanced Cell Texts are not supported yet (you can build custom solution). Therefore only the "Default" Cell Text Label is created.

- Attachments in FDM Memo Items are not exported with the Cell Text neither links to them (the same was in Classic FDM)

New Application Options

- Load Method for HP/Essbase

  File: FDMEE exports data to a .DAT file and then load it into HP/Essbase

  SQL: FDMEE creates a Rule File in Essbase to load data from FDMEE database into HP/Essbase

- Add prefix dimensions when using Duplicate Members in Essbase across dimensions

  The dimension prefix is added during Export step for all dimensions [dim]@[member]

Import Format can be selected in the Data Load Rule definition for File-based source systems

In previous version it was using the import format assigned to the location.

Multi-Period Load!!!

This was one the features that was missed by most of the old FDMers... Multi-Load Activity (renamed in FDMEE to Multi-Period Load)

Looking at it, I can understand it took some time to re-implement it but here it is.

What is Multi-Period Load? Standard file-based loads in FDMEE focuses on one single period per file when loading into Target Applications (HFM, HP/Essbase). It does not matter if you import more than one file in one shot (as we see in old post) or one single file. With the new Multi-Period file-based loads we can import one single file having data for multiple periods in multiple columns. 

The file above for HFM shows data for three periods (three columns with balances, one for each period)

In Classic FDM, Multi-Load was a separate activity than loading from the workflow. Now it has been fully integrated in Data Load Rules.

Is that all? Not. In Classic FDM periods had to be contiguous. In FDMEE, you can have non-contiguous periods in the file.

To configure/load Multi-Period files:

1. Configure an Import Format with File Type = Multi-Period.

There is a new expression Columns=StartCol,EndCol that is used to specify field numbers of data columns in the file:

Did you notice anything special in the Import Format??? Yes!! Number of Fields column has been finally removed. It is automatically derived from your File Delimiter value.

2. Configure a Data Load Rule. The Import Format must be set to the Import Format for Multi-Period file type. Optionally you can assign which period is which column in case periods are not contiguous.

The Data Load Rule must be executed from Data Load Rule page (not allowed from Data Load Workbench)

In this Data Load Rule, I have assumed:

- Column 8 (1st data column) has February-2006 data

- Column 9 (2nd data column) has April-2006 data

- Column 10 (3rd data column) has January-2006 data

As I have selected the non-contiguous periods in the Data Load Rule definition, I will not be asked for which periods I want data to be imported into. If your periods are contiguous, you leave Source Periods empty and you select period range in Data Load Rule as usual.

Once data is exported you can check how the export file looks like:

Source data has been pivoted so we only export one data column.

If you remember using Multi-load TEXT files with HFM data was exported in multiple columns but new way TDATASEG and POV are managed makes data to be always pivoted:

Note: Multi-Period is also supported for Excel TB templates. There are differences with Classic FDM:
- You can load to only one Location/Category
- You can load to multiple periods
- Period key is specified in the header

Open Batch for Multi-Period files

There is a new type of Batch for Multi-Period files which will be placed in \inbox\batches\openbatchml

Custom Scripts!!!
We have discussed about the different types of Scripts in FDMEE:

• Import Scripts: implemented to process source data when required (add prefix, concatenate different sub-strings, etc.). Available only in Jython.
• Event Scripts: implemented to respond to FDMEE events (BefImport, AftImport, BefValidate, AftValidate,etc. Available in Jython and VB.
• Mapping Scripts: used to build mapping logic to get target members based on multiple source/target column and IF THEN logic. Mainly when our mapping logic is so complex to be implemented with standard rules. Available in Jython and SQL

Now we have a new type of script which was already present (in a different way) in Classic FDM.

Custom Scripts can be mainly used to perform tasks such as importing data into auxiliary tables, copying files, sending an email from FDMEE, etc. Mainly any task supporting our integration process. Custom Scripts can be implemented in Jython or VB.

In Classic FDM there were two types of Custom Scripts:

- Custom Script for General Purpose: was mainly executed from FDM Workbench Client (Desktop Client).

- Web Script: were assigned to FDM Task Flows so they could be launched from the FDM Web Client.

As everything is web-based in FDMEE, there is no difference between General and Web. Also Classic FDM functionality like Menu Maker and Task Flows are not necessary any more. All these functionality are not encapsulated with new Custom Scripts.

In order to work with Custom Scripts:

1. Create Custom Scripts (Eclipse, Notepad++, etc.)

2. Create Custom Script Group(s)

3. Register Custom Scripts 

    For each Custom Script you create:

    - Assign a Name (similar to Task Flow)

    - Assign a Target Application

    - Assign it to a Custom Group (which be secured)

    - Optionally add run-time prompt parameters (the user can pass/select values when executing the script)

Example 1: Custom Script to show a custom message (no parameters)

We create the script from Script Editor. We have now a new tab for Custom Scripts:

When building Custom Scripts we follow the same approach as the other types. We first write code, check syntax, optionally test in our IDE for Jython/VB (Eclipse, Notepad++, etc.), and then paste into the script editor:

Note there are new FDMEE API functions available like the one used in this script.

Once the custom script is created, we will create a Custom Script Group and register our script (Menu Setup > Scripts > Script Registration):

From tab Custom Script Registration:

We can assign multiple scripts to the same group that can be secured later.

Once the script is registered, we can execute it from menu Workflow > Other > Script Execution:

Voilà!

Example 2: Custom Script to show a file (file name as input parameter)

Parameter types for Custom Scripts:

- POV Location

- POV Period

- POV Category

- POV Rule

- Query

- Static

Our script will show a file placed on the inbox folder:

We have created a different Custom Script Group for it. When the script is registered we add the following parameter for the file name:

The script can be executed as Example 1:

And the file is opened:

Some notes for Custom Scripts:

- Scripts are stored in /data/scripts/custom

- Custom Script Groups can be secured (I can say which FDMEE roles can see/run which groups)

- Custom Scripts cannot be scheduled at this moment

- Custom Scripts can be executed in Online or Offline mode

Batch Groups
The same way we have Report Groups and Custom Script Groups, we have now Batch Groups which let us group FDMEE batches and assign security to them:

A new setting has now been added for batches in Batch Definition page. Now we can assign a batch group to our batch:

Securing Report Groups, Batch Groups, and Report Groups
We can now define Role Security for Reports, Batches, and Custom Script groups. Therefore security for different roles is defined at group level:

High Availability

PSU2 has fixed several issues related to HA (i.e.: agent assignation). From PSU2, the system will automatically detect the FDMEE instance the User Interface is running and will use the ODI agent from the same instance to execute the process. As consequence, setting ODI Agent URL has been removed from System Settings for ODI:

SAP BW As source for Metadata Rules

I'm still on my way of confirming it. Not sure if it has been released or will be available in next update. And also preparing an entry for FDMEE integration with SAP BW.

SAP BW is not available as source for metadata yet. Maybe soon.

Other Enhancements:

- New System Setting for Check Report Precision

- We can select EPMA metadata load options in Metadata Rules (merge as move, merge as primary, and replace)

- A Custom Script can be called before and/or after batches are processed

So enjoy new PSU2 and feel free to share your feedback.

Talk to you soon.

Writing and Testing Python/Jython Scripts with Notepad++

Hi all,
I have something interesting for Notepad++ lovers.
You can write scripts and run them with Python Script plugin. I find this very useful for testing custom functions, string functions, etc.

You can install it from Plugin Manager in Notepad++:

Once it's installed and Notepad++ is restarted we will see the new plugin in the Plugins menu:

Creating a new Python script

You can create a new python script from menu Python Script > New Script.

 Then write your code and run it from menu Python Script > Run Previous Script

Using the Python Console

Besides writing a script with indentation, syntax check, colors, etc. you can also run the python console:

Running Jython Scripts

As you see above we were using Phyton... do you want to use Jython as FDMEE does?

You can create a Run Command in NotePad++ as follows:

java -jar E:\Oracle\Middleware\odi\oracledi.sdk\lib\jython.jar -i "$(FULL_CURRENT_PATH)" 

Save it and configure accelerator if you wish: 

You can then write scripts in Notepad++, set language to Python (Language > P > Python) save them as .py scripts, and run the command saved above. 

That will execute your script and show the result in the JVM:

Note that you have to indent yourself when needed. Would it be better that when I press Enter code is automatically indented if needed?  for example when using if statements :-)

Absolutely. You can install Python Indent plugin to get this working:

 After enabling it you will get code indented.

Running FDMEE Jython Scripts

If you take a look to post Using PyDev for Jython Scripts you will remember about initialization code needed in order to get the context information from a FDMEE Load ID.

If you want to run Jython scripts in Notepad++ with fdmContext and fdmAPI objects you will have to include the initialization code in the script.

After some testing I noticed that same code working in Eclipse was not working in Notepad++. It does not find a suitable driver for Oracle connection :-\ 

After some googling and troubleshooting I got it working by switching to using the Oracle Call Interface (OCI) method described in this article. 

I still prefer PyDev/Eclipse but here you have more options :-)

Enjoy!

Jythoning with FDMEE - Dynamic Import Format (Part 1)

I recently used the following scenario to introduce one customer Jython scripting in FDMEE Event Scripts:

"We have two source files with different format (fixed and delimited). We have created one import format for each source file and have only one location to import data."

If you know FDM, you will be familiar with example above as it was also described in Classic FDM Admin Guide. Both classic FDM and FDMEE locations can have only one import format assigned...and then? do we have to change the import format dynamically based on which file we want to import? Yes and No.
No, you don´t have to do it. You will write the script and FDMEE will do it :-)
Yes, the import format will have to be changed dynamically so FDMEE knows how to read each type of file.

Let´s see how to build this solution in FDMEE  and how to apply some nice-to-know scripting.

Source Files
We have two source files with different format (fixed and delimited):

 

Import Formats

We have defined one import format for each file:

 

Location

Having one location only is a prerequisite for this post :-)

You can only assign one Import Format to the location at design time. 

Data Load Rule

Data Load Rules are defined at location level. We will only have one DLR:

With this configuration, the data load rule will only work if we import file COMMA4_WestSales_Jan06.txt. If we select the East file it will fail as the import format assigned to the location is configured for the West file.

How we use a different Import Format at run time?

Here is where event scripting takes place. We can capture the different steps of the FDMEE Workflow and change their behavior. So, my first idea would be to change FDMEE´s behavior before the file is imported. Can I do that? Yes :-)

Which is the event script we have to adjust? BefImport is the answer.

Let´s start with "How do I know which Import Format is configured for the source file I am importing?"

If you looked carefully, I have defined file names so the prefix COMMA4_X matches the import format name. Is that mandatory? Obviously not but it simplifies the process.

• COMMA4_EastSales_Jan06.txt > COMMA4_EastSales
• COMMA4_WestSales_Jan06.txt > COMMA4_WestSales

Ok, I know which import format for which file but... how can I get the file name in an event script?

FDMEE automatically initializes the data load workflow context information prior to invoking

the Import, Mapping and Event scripts. In Classic FDM, file name was passed as an input parameters. FDMEE event scripts do not use input parameters for context values as they can be easily obtained when coding.

The fdmContext object is initialized with the list of properties listed below. The properties can be accessed by referencing as fdmContext["<PROPERTY NAME>"]

Properties of fdmContext: APPID, CATKEY, CATNAME, EXPORTFLAG, EXPORTMODE, FILENAME, IMPORTFLAG, IMPORTFORMAT,   IMPORTMODE, IMPSTATUS, INBOXDIR, LOADID, LOCKEY, LOCNAME, MULTIPERIODLOAD, OUTBOXDIR, PERIODKEY, RULEID, SCRIPTSDIR,  SOURCENAME, SOURCETYPE, TARGETAPPDB, TARGETAPPNAME.

So we can get the file name by using fdmContext["FILENAME"]

Cool, I know how to get the name of the file being processed but... how can update the import format for the DLR being processed?
I have good news for classic FDMers. The FDMEE object model has been simplified so forget about API, RES, and DWH objects, and all functions/subs being overlapped. We now have only the object fdmAPI to access all functions in Jython/VB .net. Today we will be focused on Jython.

You can take a look to FDMEE Admin Guide to see the list of current functions being supported (A Sub in Jython is a function returning type Void). We can access these functions using the syntax                             fdmAPI.<function name>
Ok, you already took a look? then you saw the following function:
     updateImportFormat(String pImpgroupKey,BigDecimal pLoadId)

So I have the ingredients but... how do I put all together? although there are different coding we can use, let me show the following:

Line 17: firstly we will be executing the BefImport script only for specific location as we may have more than one. Our location is COMMA_DynImpFormat.

Line 19: we store the file name in variable filename

Line 22: we have to pass the import format name as the first parameter, and the Load Id as the second.

We can build the import format name by concatenating the prefix "COMMA4_" and the second field of the file name which is delimited by underscore.

So we need to get the 2nd field of the file name... let´s split the string by using "_" as delimiter:

filename.split("_") --> array of substrings

Position 0: COMMA4

Position 1: EastSales or WestSales depending on file being imported

Position 2: Jan06.txt

By using string[x] we can get any element of the array. One new concept for FDM Scripters will be the Phython Slice Notation. I suggest you to visit http://docs.python.org/2/tutorial/introduction.html for more details.

The code you see above is how it will look in PyDev (Eclipse). When we copy and paste into BefImport event script it will look like:

Is that all?

Yes for the moment. Wait wait... in FDMEE you can configure if event scripts are executed for specific target applications so we need to enable them for our HFM application COMMA4DIM:

Ok, so is that all?

Yes for the moment. We will add some more functionality to this script in next entries :-)

Note: we could have also taken the first two fields of the file name to get the import format name or use other solutions. Try yourself other solutions :-)

Note: all predefined API and variables are defined using the fdm prefix, so do not use fdm to define custom variables and conflicts to avoid conflicts.

Note: changing dynamically the import format does not mean we are changing the import format assigned to the location, we are just changing it for the current data load rule being processed. If you check the import format assigned to the location, it will remain the same.

Jython: Learnt Lessons 

- Using fdmContext object

- Using fdmAPI object

- Using IF conditional statement

- Strings in Jython (positions start at 0)

- Using split function

- Using array slice notation