Thursday, October 24, 2013

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!

Wednesday, October 23, 2013

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