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











21 comments:

  1. how did you expose the objects inside of the FDM API? I'm trying to write a BefImport script that writes a file with Periods in the Rows (like most files from a Relational DB) into the Open Interface table and trying to find the equivalent of the old DW.DBTools.fCreateRec object.

    ReplyDelete
    Replies
    1. Hi Paul,
      if you want to insert data into the Open Interface Table from a flat file you will have to create a script so you read data from the file and insert into the table. You don't need any API function to create a record. YOu have an example of how to insert data into a table using Jython in Page 263 of Admin Guide.
      Any way, why would you like to do that? Do you want to import only data from your active POV? You can then create an import format for that with no need to loading before into the OI table.

      Rgds

      Delete
  2. I have a file with multiple periods in the rows that I want to load. In the old days I just created a custom dimension for Period and turned off the Period as a Global member on the workbench and then loaded them all at once through a single POV. If there is an OOTB way to do this I would love to know, still trying to get a handle on the new FDM logic...

    ReplyDelete
    Replies
    1. Are loading to HFM or HP/Essbase? unfortunately that procedure cannot be followed in FDMEE. There are different ways of approaching it. You could use a lookup dimension and then export it, concatenate with other dimension and use Essbase rule file to split, etc. You can also work on your own risk and alter database table to have a custom dimension Period_Custom, make it mappable, and exported to the DAT file...If you find table for application dimensions you will notice how it works.

      Delete
    2. If HFM, you can also adjust the HFM_EXPORT action script.. as it is still available

      Delete
  3. Hi Francisco,
    can you look at the function below and tell me whether you notice anything wrong with it? I am attempting to write an import script using Jython in FDMEE. The script is very simple but I'm having a hard time assigning the value from a given field to a variable.

    dr_str = fdmAPI.fParseString(strrec,30,11,",")

    Thanks,
    Clint

    ReplyDelete
    Replies
    1. Hi, sorry for the delay.

      THat is VB code. You will need to use Jython syntax.

      you can use something like strrec.split(",")[10]

      Regards

      Delete
  4. Is there any Jython syntax that is equivalent to the vb API farsSnap function? I want to connect to a custom table with the FDM database.

    ReplyDelete
    Replies
    1. Hi,
      you have a sample script in the admin guide which shows how to connect to SQL database in order to import data into Open Interface Table.

      Regards

      Delete
  5. I know that it's useless complaining but why on earth did Oracle choose Jython for FDMEE scripting. Language in itself so undocumented, then even more undocumented regarding FDMEE appliance. Strange.

    ReplyDelete
    Replies
    1. Interesting point especially as essentially jython is python and there is loads of documentation and examples out there for that! Also there is the freely available jython reference online.

      VBScript was never going to be a realistic option once Oracle moved to a platform independent version of the software.

      Delete
  6. Srx, your comment has really struck a chord with me.

    ReplyDelete
  7. Hi, this fishing with FDMEE blog rocks it's been really helpful so far.

    I have a question, is there a way to also dynamically change the set of validation rules for a single location ? I have an scenario in HFM for 2 different GAAP's and was thinking of creating only one location, however the validations change a lot depending on the GAAP the user is loading. I wouldn't want to create another location for every entity because the client has about 100 of them, instead I wanted to use your idea of assigning the Validation Rule depending on the name of the load file

    Is this possible?? I was going through the API but didn't find anything regarding validation rules

    Any help would be highly appreciated

    ReplyDelete
    Replies
    1. Hi, do you still check questions on your blog ??

      Delete
    2. HI, you would have to change it via database.
      There is no API for that yet.

      Delete
    3. Thanks for the quick reply, do you know which table has this info ?? also at what point of the process should I change it ?? before import ?? before check ?? and when should I change it back ???

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Thanks for your help do you know at what point should I change the validation rule back ??? Because Im not pretty sure wether the table with the validations is populated before the check or after it

      Delete
    2. In the BefImport Script test if the Location being loaded is the appropriate GAAP (not sure how you specify this variable, Import Format or Location Name or is it by the data). update the tpovpartition table when import is initialized to the appropriate Validation Entity, Validation Rule, Validation group.

      Delete
  9. Hi, I'm having a problem with my check report and would like to understand which script is executed after Consolidate.py, the one invoking the check step (and the check report), after some days looking at the logs I can't seem to find the answer

    Thanks in advanced

    Thanks a lot

    ReplyDelete
  10. Hi Francisco, If one dynamically changes the import format, what happens with the load rule ?? Shouldn't we change the import format there as well ??

    ReplyDelete

Thanks for feedback!