Wednesday, October 25, 2017

FDMEE/Jython: Automating “REFRESH MEMBERS” with a Script

The Business Case:

You have just gotten your FDMEE data loads set up and running, including DataMaps that are dynamic based on the hierarchies (i.e. if the level 0 member value is a descendant of a given parent map it, otherwise IGNORE).  You discover the handy “Refresh Members” button on an FDMEE target application:

…but, as the administrator, you don’t want to have to log into the system and click that button once a day, every other day, or every hour. 

The Problem:

Oracle doesn’t give you an any way to schedule this “Refresh Members” function, because why would you ever want to automate that? Right?!  …Sigh…

A Solution:

…and here comes the FDMEE REST API to the rescue!! 

Now, I had been mulling over this particular question for nearly a year, and finally came back to it in the last couple of weeks after I ran across this Refresh Members thread in the Oracle community where John Goodwin put an example REST post for refreshing the members.  If you want a lot of great information regarding REST and PBCS, John has several great blog posts on the subject…

So, armed with this new information, I set out to find a way to write the jython script to submit the REST request.  After digging around some python documentation, I did the right kind of search and came across one of Francisco’s REST API posts.  As always, he provided some great codes snippets to pretty much write the entire script (Using On-Prem Planning REST API to Import Metadata).  Of course, we aren’t using Planning, but we get a very good idea of how the script needs to go…

I’ll just pause here to say a huge “THANK YOU!” to these guys.  They have helped me so much with their insight and knowledge!

A lot of the following will be very close to Francisco’s post, with a couple of changes.

Steps to run the “REFRESH MEMBERS” Job

1.      Import  modules:

Note the first import for DwgObject, which is another gift from Franciso.  In one of his other blogs he talked about how to decode passwords from the ODI repository, so I have created a dummy data server in ODI to house my credentials to use for the authentication step, rather than a hardcoded UID and password.

2.      Establish variables and build the REST URL:

3.      Set up the “Payload” or “Data” for the request:

This is where I had some fun trying to figure out what was going on.  All the information seemed to indicate that the “urllib.urlencode()” was required.  However, I continued to receive a HTTP Error 400 – Incorrect Syntax error until I just passed the raw payload string. Once I commented out the urlencode line, voalà!  Job submitted successfully!

4.      Set the Authentication.  Again, I use the method of decoding credentials from ODI for this.

5.      Open the Request, add the Headers, and add the payload

There are a couple of methods for adding the payload/data and therefore causing the request to be a POST request.  The following seemed nice and clean:

The debug log entries for the request objects were very helpful in diagnosing my issues with getting the script to work properly.  The response may be parsed for different information like ProcessID and Status of the request.  Based on the status, the sky is the limit on where to go next.  You could even make this into a custom function to call from the BefImport event.

Hooray!  A working “REFRESH MEMBERS” jython script utilizing the REST API for FDMEE!  Hope this is helpful to you and good luck!


  1. Great script - thanks for sharing. I use the encryptpassword batch file for passwords and then decrypt the files that it creates rather than saving them in ODI or FDMEE. It's much easier to transfer between environments and is standard FDMEE functionality.

    1. How are you calling encryptpassword batch file from Jython script ?

    2. encryptpassword is not called from the Jython script. It is used to save the userid/password for the environment and then that information is decrypted. As an example, I use encryptpassword to create files called DBUSER and DBPW and the Jython scripts decrypt the details from these files. I do the same for each environment with the relevant connection info. It means I don't have to save any connection info in the scripts.

  2. Hi,

    I tried your above script but I received an error urlopen error(111, ‘connection refused’).
    Can you please tell why am I getting this error.


    1. Hmm... Seems like it may be an issue with credentials or the URL you are trying to use. What release are you on?

    2. Hi Jonathan,

      We use version for fdmee. Tried the same url using boomerang and it is working. I tried the same using epmautomate but it didn’t work.


    3. Hi,

      After a few changes to url it is working now only for the authentication, but when I am adding the body part then it is throwing me the following error:
      TypeError: wrap(): 1st arg can’t be coerced to byte []


  3. Hi, Thanks for posting this script.
    I am trying to refresh members from PBCS application to HFM target application using this script.
    I have set vServer and vPort to point to the cloud PBCS server. When I ran the script in FDMEE, the process details shows that the script has run successfully but there isn't another process for refresh members. However when I look in PBCS, the refresh members has run and shows in the process list.
    My question is which server and port should I use in vServer and vPort?
    on premise server:port or PBCS cloud?

    Thanks for your prompt answer.



    1. Hi Bash,

      The refresh members function in FDMEE/DM only updates metadata in the FDMEE database for the application it is submitted on for validation purposes within FDMEE/DM. It doesn't update hierarchy in a target application. You will likely need a different process for that (i.e. DRM/EDMCS, FDMEE MetaData rules, etc.).

  4. Hello Jonathan,
    Thanks for getting back to me on this question and thanks for clarifying.
    I am trying to update the metadata in the on-premise FDMEE database for the target cloud application.
    I have set vServer and vPort in this script to point to the cloud.
    When I run the script from on premise FDMEE, I can see that the process to update dimension members for each dimension is successfully launched in PBCS jobconsole BUT when I look at the process details in on premise FDMEE, the script is showing as successfully executed but I do not see the process details that shows that members have been refreshed as I see it in PBCS cloud.
    This brings the question as to whether I am using the correct server and port in the script or should i be pointing to the on premise server (vServer:vPort).

    Thanks and regards


    1. Ah, ok... I think the URL and Port should be your on-prem one. The connection to the cloud target app will be controlled in the target app setup.

      Best Regards,