Jython Scripting in FDMEE It's Not That Scary Tony Scalese Edgewater Ranzal
About Edgewater Ranzal
15 Years 700+ clients 1000+ projects
Focus Services People Methodology Customers Partnership
We offer a full spectrum of EPM/BI Services Financial performance, Legal, Segment & Mgmt Reporting, Financial Close HFM Optimization, Performance Lab SOX Compliance Support Installation, Upgrades, Migration, System Monitoring, Backup and Recovery, Disaster Recovery, Load Testing, Hardware Sizing, Exalytics Benchmarking
Business Intelligence
Consolidation
Strategic Finance, Planning, Budgeting, Forecasting, Workforce Planning, Capital Planning, Project Financial Planning
Enterprise Planning Costing & Profitability Mgmt
Infrastructure
Data Integration, Financial Data Management, Data Warehousing, Master Data Management &DRM, ETL Services, Automation
Dashboards & Scorecards, Financial Analytics & Reporting, Operational Analytics, What-if Analysis, Query & Reporting, Visual Exploration
Data Services
Project Management
Training & Support Services
Key Teach Course Delivery: Planning, Essbase, Financial Reporting, Smart View, HPCM, HFM, FDM, DRM, OBIEE Custom Training Delivery: Process & Reporting
HPCM Standard & Detailed Models, Waterfall Allocations, Activity Based Costing, Customer, Product & LOB Profitability
Project/Program Mgmt, EPM Road Maps, Application Reviews, Business Requirements, Process Change, Documentation
Support Services – Infrastructure & Application Support Contracts
Agenda
Jython Overview Configuring Eclipse FDMEE Scripting Configuration
Let’s Get It Out of the Way
Why Jython and not VBScript? ● Jython is the strategic direction of Oracle development ● Jython is platform independent ● The API supporting Jython scripting will continue to be enhanced ● Jython error handling is more elegant and robust
Jython Basics
Python programming language written in JAVA Syntax is more sensitive than VBScript Variable data type is critical; declaration is implicit Comment characters: # or ‘’’’’’ import statement allows easy reuse of code Far more robust error handling Familiarize yourself with camelCase Not that different than VBScript
Script Format
Import JAVA classes import java.sql as sql
Utilize Try...Except try: Code to execute except: Error handling
Commonly Used JAVA Classes
Sys, Traceback: Can be used to get detailed error information including line in script producing error Java.sql: Used to interact with remote databases Smtplib: Used for email generation
Jython String Manipulation Method
Functionality
count(Substring,[Start],[End])
Number of times substring occurs
endswith(Suffix,[Start],[End])
Boolean result; does string end with suffix
find(substring,[Start],[End])
Returns position where substring is found
isdigit()
Boolean result; is string all numbers
isspace()
Boolean result; is string all spaces
lower()
Puts still in all lower case, similar to lcase
replace(old,new,[count])
Replaces text in string
split([separator],[max split])
Returns a list, string is divided by separator
startswith(prefix,[Start],[End])
Boolean result; does string start with prefix
strip([characters])
Removes specified character(s) from string
upper()
Puts still in all upper case, similar to ucase
zfill(width)
Returns numeric string filled left with zeros
Jython Statements Jython
VBScript
If Then
if x == 100: elif x == 200: else:
If x = 100 then Elseif x = 200 then Else End if
Case
No native statement; utilize dictionary or If Then statements
Select Case x Case 100 Case 200 Case Else End Select
Jython Iteration Jython
VBScript
Do Loop
while x<100: Do something x += 1
Do while x < 100 Do something x = x+1 Loop
For Loop
for x in range(100): Do something
For x = 0 to 100 Do something Next
Jython Mapping Data Type List
Jython
VBScript
lListName = [‘Elem1’,’Elem2’,’Elem3’]
aArrayName = Array(“Elem1”,”Elem2”,”Elem3”)
for elem in lListName: print elem
Dictionary
dictName = {} dictName[‘Tony Scalese’] = 36 dictName[‘Opal Alapat’] = 39 for sKey,sItem in dictName.iteritems(): print sKey + ‘\’s age is ‘ + str(sItem)
For each strElem in aArrayName msgbox strElem Next Set dictName = CreateObject(“Scripting.Dictionary”) dictName.Add “Tony Scalese”, 36 dictName.Add “Opal Alapat”, 39 For each strKey in dictName.Keys msgbox strKey & “’s age is “ & dictName.Item(strKey) Next
JAVA Runtime Environment (JRE)
If a JRE is not installed, be sure to install the 64 bit JRE (install using defaults) ● http://www.oracle.com/technetwork/java/javase/dow nloads/jre7-downloads-1880261.html
Jython Download
Download Jython 2.5.1 from http://www.jython.org/downloads.html ● Do not use a more up to date version!
Jython Install
Select English Next Accept the EULA Next Select Standard Next Leave the default C:\Jython2.5.1 directory Next May be prompted that the directory is created, click OK and Next again Leave Current as JAVA home directory Next Click Next on the Summary tab, install will begin Click Next on the ReadMe and then Finish
Jython Install Confirmation
Confirm the C:\jython2.5.1 directory exists Confirm jython.jar file exists
Download Eclipse
Download Eclipse Standard from http://www.eclipse.org/downloads/
Extract Eclipse
Extract the download to the C:\ directory Open the C:\Eclipse folder and launch eclipse.exe
Workspace
Specify a default workspace
Configure Eclipse General Options
Expand General Editors and click Text Editors Change the options: ● Display tab width: 2 ● Insert spaces for tabs: Checked ● Show Print Margin: Checked ● Show Line Numbers: Checked
Install PyDev
Select Windows Preferences Expand Install/Update Click Available Software Sites Click Add Name: PyDev Location: http://pydev.org/updates/ Click OK
PyDev Install
PyDev Install
Select Help Install New Software Click PyDev from the Work With drop down Check PyDev Click Next Click Next Accept the EULA and click Finish
PyDev Install
Put a check next to the certificate Click OK Click Yes to restart Eclipse after the install
Set PyDev Options
Windows Preferences Expand PyDev and click Editor Change tab length to 2
Set PyDev Interpreter
Interpreter – Jython Click New ● Interpreter Name: Jython2.5.1 ● Browse and select Jython.jar from C: drive
Click OK
Set PyDev Interpreter
When prompted, leave defaults and click OK
Creating a Project
Close the Welcome Screen Right click the Package Explorer New Project
Creating a Project
Expand PyDev and select PyDev Project Click Next
Creating a Project
Name the Project Change the project type to Jython Click Finish Click Yes when prompted
Organize Code
Right click the Project Select New Folder ● Name the folder src ● Repeat and create as lib
Import JAR Files
Right click lib folder and select Import Expand General and select File System
Import FDMEE JAR
From Directory: lib directory of FDM ● %hyperion_home%\Products\FinancialDataQuality\li b
Check the aif-apis.jar Click Finish
Update Python Path
Right click the project (ER_FDMEE) Select Properties Click PyDev – PYTHONPATH Click Add jar/zip/egg Expand the project and click the lib folder
Creating Modules
Add new PyDev module ● Right click src folder ● New PyDev module
Creating Modules
Specify a name Click Finish When prompted for a template, select Module main and click OK
Testing Module
Add print “hello” Run the script ● Run as Jython Run
hello shows in the console window when successful
Testing FDMEE API Usage
Add the following text to the module: ● import com.hyperion.aif.scripting.API as API
Save the module Type API(). on a new line. A list of available methods will display
Import Scripts
The equivalent of Import – Data Pump scripts in FDM Classic Import Integration scripts have been replaced by the Open Interface adaptor and the BefImport event script Import scripts are used to manipulate data during the import process Execute during an import process when assigned to the import format used by the location and data load rule Every line in the data file is evaluated Avoid the temptation to overuse import scripts ● Parsing strings that cannot be parsed by an import format ● Temporary variables storing header information ● Mapping data should be done in maps, not import scripts
Import Script Syntax
Define the script name ● def ScriptName (strfield,strrecord):
Write script actions ● aField = split(strrecord,”-”) ● sCostCenter = aField[1]
Return result to function ● return sCostCenter
Notice the colon at the end
Sample Import Script def EBS_CostCenter(strField, strRecord): Notice the sCC = strField colon at the end if strField.count(“.”) > 0: aSegments = strField.split(‘.’) sCC = aSegments[2] return sCC
Temporary Variables (RES.pVarTempx)
No longer limited to 5 Declare a variable before the function Designate the variable as global
strDept = “” def Nvision_Entity(strfield,strrecord): if strrecord[1:5] == “Dept:”: global strDept strDept = strrecord[6:9] return strfield
Using Temporary Variables
Simply return temporary variable in “assign” script
def Nvision_C1(strfield,strrecord): return strDept
Mapping Scripts
Similar in concept to varValues Be careful of overuse due to performance impacts Enter #SCRIPT to activate script section of maps fdmRow.getString(“FieldName”) fdmResult = “Mapped_Result”
Event Scripts
Execute during defined events within the application Fewer than FDM Classic, notably StartUp, POVChanged Scope needs to be well defined to ensure process only executes when needed No need to declare function ● def BefImport(): Not needed
Events – Before and After Event
Description
FDM Classic Equivalent
Import
Executes any time the import process is executed
ImportAction, FileImport
Calculate
Before & After Logic groups and maps processing
Calculate
ProcLogicGrp When logic groups are assigned
ProcLogicGroup
ProcMap
Executes ONCE during mapping application
ProcMap
Validate
Validate workflow step
Validate
ExportToDat
Export workflow step
ExportToDat
Load
Export workflow step
Load
Consolidate
Export workflow step
Consolidate
Check
Check workflow step
Check
Custom Scripts
Custom scripts are used to extend the functionality of FDMEE Custom script are organized in groups ● Scripts can be assigned to multiple groups
Custom scripts are registered ● Similar to Menu Maker specifying a caption ● Parameters can be added to make a script more dynamic
Custom scripts can executed from web UI No need to declare function ● def ER_OutputData(): Not needed
Custom Script Registration
Create a group ● Logical grouping that a user can select ● ●
By Region: North America, EMEA, Asia Pacific By Purpose: Data Exports, Budget, Actuals
Register script to group ● Add parameters if applicable
Executing Custom Scripts
Select Group Select Script & click Execute ● Specify parameters if applicable
Select Execution Mode and click OK
Development Mode
Used to test code without actually impacting the data in FDMEE 4 sections: 1. 2. 3. 4.
Initialize dev mode Populate context Custom code Clean-up actions
Once code is tested, sections 1, 2 & 4 can be removed, handled natively by the application
FDM Context
Each execution includes a collection of information
Commonly Used
Occasionally Used
Rarely Used
APPID CATKEY CATNAME CHKSTATUS EXPSTATUS IMPSTATUS INBOXDIR LOADID LOCKEY LOCNAME OUTBOXDIR PERIODNAME PERIODKEY PROCESSSTATUS SCRIPTSDIR VALSTATUS
EXPORTFLAG EXPORTMODE FILEDIR FILENAME IMPORTFLAG IMPORTFORMAT IMPORTMODE MULTIPERIODLOAD RULEID RULENAME SOURCENAME SOURCETYPE TARGETAPPDB TARGETAPPNAME
BATCHSCRIPTDIR EPMORACLEHOME EPMORACLEINSTANCEHOME
Initialize Development Mode import java.math.BigDecimal as BigDecimal import java.sql as sql import com.hyperion.aif.scripting.API as API fdmAPI = API() conn = sql.DriverManager.getConnection("jdbc:oracle:thin:@server:port:SID", "user", "password"); conn.setAutoCommit(False) fdmAPI.initializeDevMode(conn); print “Connected”
Populate Context fdmContext = fdmAPI.initContext(BigDecimal(LoadID)) print “Location Name: “ + fdmContext[“LOCNAME”]
Clean-up Action
fdmAPI.closeConnection();
Useful FDM APIs
getPOVLocation(LoadID), Category, StartPeriod, EndPeriod: Returns key, not text executeDML(query,param_list) getCustomScriptParameterValue(loadID,param): Load ID is optional executeQuery(query,parameters): Returns recordset showCustomFile(FilePath): Display file showCustomMessage(message) logFatal(message), Error, Warn, Info, Debug
Integrating Data from Relational Sources
The BefImport event script is used in conjunction with the Open Interface adaptor to import data from relational source This approach replaces import integration scripts from FDM Classic Check out Mike Casey’s presentation - Using the Open Interface Adapter in FDM Enterprise Edition 11.1.2.3 - on 6/25 from 2-3 for a deep dive into the setup
BefImport Script Sample - Setup import java.text.SimpleDateFormat as SimpleDateFormat import java.sql as sql strPOVPer = fdmContext["PERIODKEY"] strYear = SimpleDateFormat("yyyy").format(strPOVPer) lngMonth = SimpleDateFormat("MM").format(strPOVPer) strMonth = SimpleDateFormat("MMM").format(strPOVPer) batchName = "MatchDataLoadRuleParam"
BefImport Script Sample – Build Query insertStmt = """ INSERT INTO AIF_OPEN_INTERFACE ( BATCH_NAME ,YEAR ,PERIOD ,PERIOD_NUM ,CURRENCY ,COL01 ,COL02 ,AMOUNT ) VALUES ( ? ,? ,? ,? ,? ,? ,? ) """
BefImport Script Sample – Run Query #Connect to source DB sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@Serv er:Port:SID","User","Password"); #Build SELECT statement to get data to insert selectStmt = "Select * from table \n" selectStmt = selectStmt + "Where 1=1 \n" #Run the query to get the data stmt = sourceConn.prepareStatement(selectStmt) RS = stmt.executeQuery()
BefImport Script Sample – Import Data #Loop through the data and import it into the Open Interface table while(RS.next()): params = [ batchName, strYear,strMonth,lngMonth, "USD", RS.getString("Account"), RS.getString("Entity"), RS.getBigDecimal("Amount") ] fdmAPI.executeDML(insertStmt, params, False) fdmAPI.commitTransaction() #Close the connection RS.close() sourceConn.close()
Questions
