FDMEE and PBJ, together hand in hand

Do you know Jason Jones? I guess you do but in case you don't, I'm sure you may have been playing around with any of his developments.

Personally, I've been following Jason since years. I remember what I thought when I went to one his presentations in Kscope: "This guy really knows what he says and has put lot of effort helping the EPM community. Definitely an EPM Rock star."

One day, I found something quite interesting in his blog: PBJ. I thought it could be very useful to improve and simplify something that I had already built using a different solution. Why not then using something he was offering to the community as open-source? It was good to me and also good to him. I guess that seeing something you've built is useful for others, must make you proud.

When I told him that I was going to integrate FDMEE on-prem with PBCS using PBJ, he was very enthusiastic. The library was not fully tested so I made sure I was providing continuous feedback. Some days ago he published about our "joint venture". Now it's time for me.

FDMEE Hybrid Integrations
We have already covered Hybrid integrations in some posts.
In a few words, FDMEE on-prem PSU200+ can be used to extract/load data from Oracle EPM Cloud Services (E/PBCS, FCCS so far).

I suggest you also visit John's blog to know more about hybrid integrations in FDMEE:

• FDMEE Hybrid and the REST Part 1
• FDMEE Hybrid and the REST Part 2
• FDMEE Hybrid Update Part 1
• FDMEE Hybrid Update Part 2

PBJ - The Java Library for PBCS
REST Web Services, what's that? I  let you google and read about it. For us, REST is how EPM Cloud Services open to the external world. Oracle provides different REST APIs for the different EPM CS.

Luckily, Jason has gone one step further. He built a Java Library to use the REST API for PBCS:

PBJ is a Java library for working with the Planning and Budgeting Cloud Service (PBCS) REST API. It is open source software.

Why would we need PBJ in our solutions? Currently hybrid integrations have some missing functionality like working with metadata among others. For example, we recently built a solution in FDMEE to load exchange rates from HFM into PBCS.

FDMEE was offering seamless extracts from HFM. Rates are data in HFM but not in PBCS. They are treated as metadata. We used REST APIs for PBCS from FDMEE scripts which was working perfectly. However, we built the code using modules available in Jython 2.5.1. That gave rise to much head-scratching... Working with HTTP requests and JSON was not an easy task.
We noticed everything was much easier from Python 2.7 (Jython 2.7) but nothing we could do here as we were stick to what FDMEE can use :-(

TBH, we had a further ace up our sleeve: building our own Java library but we delayed this development for different reasons. It was then that PBJ appeared :-)

Why reinventing the wheel? PBJ is open-source and makes coding easier. We can collaborate with Jason in GIT and he is quite receptive for feedback given.

Using PBJ from FDMEE Scripts
When I first started testing it, I noticed that there were multiple JAR dependencies which had to be added to the sys.path in my FDMEE script. That was causing some conflicts with other Jars used by FDMEE so Jason came up with an uber-JAR:

uber-JAR—also known as a fat JAR or JAR with dependencies—is a JAR file that contains not only a Java program, but embeds its dependencies as well. This means that the JAR functions as an "all-in-one" distribution of the software, without needing any other Java code. (You still need a Java run-time, and an underlying operating system, of course.)

One of my concerns was the fact that FDMEE uses Java 1.6. That's usually a problem when using external Jars from FDMEE scripts. Luckily, PBJ is also built using Java 1.6 so the current versions of FDMEE and PBJ are good friends.

Before using any PBJ class we have to add the Jar to the sys.path which contains a list of strings that specifies the search path for modules:

# -------------------------------------------------------------------
# Add library path to sys.path
# -------------------------------------------------------------------
import sys
import os.path as osPath

# list of jar files
listPBJdep = ["pbj-pbcs-client-1.0.3-SNAPSHOT.jar"]

# Add jars
pbjDepPath = r"E:\PBJ\uber_jar"
# Debug
fdmAPI.logInfo("Adding PBJ dependencies from %s" % pbjDepPath)
for jar in listPBJdep:
    pbjPathJar = osPath.join(pbjDepPath, jar)
    if pbjPathJar not in sys.path:
        sys.path.append(pbjPathJar)
        fdmAPI.logDebug("PBJ dependency appended to sys path: %s" % jar)

Once the Jar file is added to the path we can import the different classes we want to use:

# -------------------------------------------------------------------
# Import section
# -------------------------------------------------------------------
from com.jasonwjones.pbcs.client.impl import PbcsConnectionImpl
from com.jasonwjones.pbcs import PbcsClientFactory
from com.jasonwjones.pbcs.client.exceptions import PbcsClientException
import time

We are now ready to connect to our PBCS instance.

Example: loading new Cost Centers into PBCS
I have created a custom script in FDMEE to keep it simple. The script is basically performing the following actions:

1. Import PBJ Jar file
2. Connect to PBCS
3. Upload a CSV file with new Cost Centers
4. Execute a Job to add new Cost Centers

Our CSV file with new metadata is simple, just three new members:

PBJ has class PbcsClientException to capture and handle exceptions. You can use this class in addition to Python's one:

try:
    # Your code...    
except PbcsClientException, exPBJ:
    fdmAPI.logInfo("Error in PBJ: %s" % exPBJ)
except Exception, exPy:
    fdmAPI.logInfo("Error: %s" % exPy)

Connecting to PBCS

We just need 4 parameters to create a PBCS connection:

# -------------------------------------------------------------------
# Credentials
# -------------------------------------------------------------------
server = "fishingwithfdmee.pbcs.em2.oraclecloud.com"
identityDomain = "fishingwithfdmee"
username = "franciscoamores@fishingwithfdmee.com"
password = "LongliveOnPrem"

Note: just working with Jason to use encrypted password instead of hard-coded one. I'll update this post soon.

Creating the PBJ Client (PbcsClient)
PBJ can be seen as a PBCS client built in Java so next step is to create the Client object:

# Create client
clientFactory = PbcsClientFactory()
fdmAPI.logInfo("PbcsClientFactory object created")
client = clientFactory.createClient(connection) # PbcsClient
fdmAPI.logInfo("PbcsClient object created")  

With the client object we can upload the file with new metadata to the PBCS Inbox/Outbox folder. This is done with uploadFile method:

# Upload metadata file to PBCS Inbox
csvFilepath = r"E:\FDMEE_CC\FDMEE_CostCenter.csv"
client.uploadFile(csvFilepath)
fdmAPI.logInfo("File successfully uploaded to PBCS Inbox")

The file is then uploaded to PBCS so the Job can process it:

Creating the Application object (PbcsApplication) 
Once file file is uploaded we need to create an application object to import new metadata. In my case, my PBCS application is DPEU.

# Set PBCS application
appName = "DPEU"
app = client.getApplication(appName) # PbcsApplication

Executing the Job and Checking Job Status

I have created a Job in PBCS to upload new cost centers from a CSV file (PBJ also supports zip files):

One thing you need to know about REST API is that they are called asynchronously. In other words, we need to check the job status until it is completed (or predefined timeout is reached).

So we first execute the job by calling method importMethod and then check job status with method getJobStatus. The status will be checked every 10 seconds while the job is running.

In order to check the job status we need to know the job id. This is done with getJobId method:

# Execute Job to import new metadata (Cost Center)
result = app.importMetadata("FDMEE_Import_CC") # PbcsJobLaunchResult
fdmAPI.logInfo("Result: %s " % result)
        
# Check Job Status and loop while executing (may add timeout)
jobStatus = app.getJobStatus(result.getJobId()) # PbcsJobStatus
fdmAPI.logInfo("Job status: %s " % jobStatus)
statusCode = jobStatus.getStatus()

while (statusCode == -1):
    time.sleep(10) # sleep 10 seconds
    jobStatus = app.getJobStatus(result.getJobId()) # PbcsJobStatus
    fdmAPI.logInfo("Job status: %s " % jobStatus)
    statusCode = jobStatus.getStatus()
        
# Show Message
if statusCode == 0:
    fdmAPI.showCustomMessage("New Cost Centers added!")
else:
    fdmAPI.showCustomMessage("Some errors happened! %s" % jobStatus)

Once the job is completed we can see the results in the PBCS Job console:

Job was executed with no errors. By navigating to the Cost Center dimension we can see the new hierarchy added:

I have also added some code to write debug entries in the FDMEE process log. This is always useful and can help you to find and fix issues easily:

Conclusion and Feedback
In this post, my main goal has been to show you how to use PBJ library in FDMEE. I'm sure this can be very useful to implement different requirements for hybrid integrations.

Jason did a great job and the ball is now in our court. The best way of contributing is to keep testing PBJ and provide feedback.
Let me highlight that PBJ is not his only project. There are few others that you can check in his site.

Enjoy FDMEE and PBJ together hand in hand!

Share this post