Thoughts, ideas and solutions from a few EPM consultants.

Working with temporary folders in ODI

In the past I have (like I assume most ODI developers) setup local folders or network shares to house files that ODI needs to manipulate(modify, write). These folders and files have had various purposes. Sometimes it has been a staging location for a file copied off another system using SCP or FTP. Other times its been a repository for writing batch (DRM, EPMA) configuration files via Jython, or maybe its just a place to keep logs for metadata integration to Essbase. I always end up with a section of my documentation explaining the importance of setting up some folder on a server somewhere and giving the ODI user access to it.

If you hadn't noticed we have been playing with Jython and BeanScript more and more lately, this has lead to some unforseen insights into ODI. One that I might be able to ditch that last part of my documentation.

In my most recent project I have been writing small Jython scripts to create folders inside the temp folder (X:\Oracle\Middleware\user_projects\epmsystemX\tmp) if they don't already exist. Inside the folders I have been doing alot of the things I normally use network shares for like storing logs, the Essbase MetaData KM by default uses the temporary directory) and writing configuration files.

import os

def getPath(path, item):
    path = path.strip()
    if path[len(path) - 1:] == "\\":
    	path = path[:len(path) - 1]
    if item[:1] == "\\":
    	item = item[1:]
	return os.path.join(path, item)
 
 config = getPath(r"<?=java.lang.System.getProperty("java.io.tmpdir")?> ", "Config")
 if not os.path.exists(config):
 	os.makedirs(config)

This has been working great since this client uses several ODI agents per environment and has four different environments. Using this methodlogy I have been able to esure that each process can run from each agent (except the DRM agent since that is the only one that we can access the DRM batch client from for DRM automation). Until recently I had removed all dependacies on this type of shared folders until found a nagging inconvienience in ODI.

Our metadata imports from SAP come over in a file. Until recently I have been loading them directly over network from the file share on the SAP side into a Oracle table. Unfortunately we have a requirement that the file be loaded in order recieved into DRM, I have tried to do this mutiple ways (ROWNUM, Sequence, etc.) but for whatever reason the Sunopsis File driver isn't loading to the Oracle table in order. I have come to the conclusion I will have to write a process to number the rows in the file prior to load and since I don't want to manipulate the source file for obvious reasons I need to store it a temporary location. So I created a physical schema under the Generic File data source in ODI.

Share Folder

Then I used my method for copying files without variables across environments. And lastly I wrote some Jython to write the row number at the end of each line of the file.

When I was done I started to wonder if I really needed the shared folder. I figured maybe it was worth a shot to setup a new physical schema to point to the temporary path(<?=java.lang.System.getProperty("java.io.tmpdir")?>), it probably wouldn't work but might as well give it a go.

Temporary Folder

To my suprise when I made the changes to the logical schema it worked.

Copy File

The ODI KM translated it perfectly.

Code

Then ODI suprised me again by running the interface that loaded the file to the Oracle table successfully.

Import Cost Center

Code for Import

I am going to keep testing but if this continues to work it will be great. It eliminates some things the Oracle EPM (Hyperion) Administrator would normally have to remember when performing migrations or training new administrators.

P.S. Happy Cinco de Mayo