2 Replies Latest reply on May 3, 2016 12:17 PM by HVilminko

    HFM Java API - Extract data to Database example

    Rodrigo Radtke Souza

      Hi all! I'm writing a Java code against the new HFM API to extract HFM data to an Oracle database and I would like to know if you have any kind of example that you could provide me. I did some testing already as you can see in the code below. This code connects fine to my HFM application, but it throwns a "Unrecognized database driver." error. I do suspect that this error happens because I do not set the mapDbConnectInfo object in my code, but I could not find any example anywhere saying what are the correct values that I should pass to this object creation, so if you have any example on this Java component, it would be much appreciated as well.


      If I try to extract HFM data to a text file, my code works fine. My problem only relies on extracting data to the Oracle database, but as I said, it is probably because I need to somehow set mapDbConnectInfo object as well.


      import java.util.Locale;

      import oracle.epm.fm.common.datatype.transport.DATA_EXTRACT_TYPE_FLAG;

      import oracle.epm.fm.common.datatype.transport.DATA_LINEITEM_OPTION;

      import oracle.epm.fm.common.datatype.transport.DATA_PUSH_OPTION;

      import oracle.epm.fm.common.datatype.transport.DataExtractOptions;

      import oracle.epm.fm.common.datatype.transport.SessionInfo;

      import oracle.epm.fm.common.exception.HFMException;

      import oracle.epm.fm.domainobject.application.SessionOM;

      import oracle.epm.fm.domainobject.loadextract.LoadExtractOM;

      import oracle.epm.fm.hssservice.HSSUtilManager;


      public class HFM_Extract {


        public static void main(String[] args) throws HFMException {

        String ssoToken = null;

        SessionOM sessionOM = null;

        SessionInfo sessionInfo = null;


        try {

        System.setProperty("EPM_ORACLE_HOME", "//localhost/d$/Oracle/Middleware/EPMSystem11R1");




        ssoToken = HSSUtilManager.getSecurityManager().authenticateUser("admin", "hyperion");

        sessionOM = new SessionOM();

        sessionInfo = sessionOM.createSession(ssoToken, Locale.ENGLISH, "HFM_DEV", "FINCAPP");


        LoadExtractOM loadExtractOM = new LoadExtractOM(sessionInfo);

        DataExtractOptions dataExtractOptions = new DataExtractOptions();















        String metadataSlice = "S#ACTUAL_USD.Y#2014.P#Sep.W#YTD.E{[Base]}.V#<Entity Currency>.A{[Base]}.I{[Base]}.C1{[Base]}.C2{[Base]}.C3{[Base]}.C4{[Base]}";




        int sessionID = loadExtractOM.extractData(dataExtractOptions);


        } catch (Exception e) {

        // TODO Auto-generated catch block


        } finally {

        if (sessionOM != null && sessionInfo != null)







      Error code:


      //             Financial Management Data Extract

      //  Date: 4/1/2016 8:42:07 PM

      //  User: admin@Native Directory

      //  Source DSN: HFM_ETL_DEV

      //  Source Financial Management Application: FINCAPP

      //  Destination: TIEOUT

      //  Extract Type: Financial Management Standard Star Schema

      //  Options: Create New / Replace Existing

      //  Include Data: Yes

      //  Dynamic Accounts: Yes

      //  Calculated Data: Yes

      //  Derived Data: Yes

      //  Cell Text: No

      //  Phased Submission Group Data: No

      //  Line Item Details: No





      Processing status change: (-2147220956)

      4/1/2016 8:42:07 PM : Status : Processing completed with errors

      Data extract completed with errors for Application FINCAPP using prefix TIEOUT.  Error code is: -2147220956  :  (-2147220956) (Unrecognized database driver.)

        • 1. Re: HFM Java API - Extract data to Database example
          Rodrigo Radtke Souza

          Just got a response from Oracle SR. We need to add the following code to make it work:


          Map<String, String> mapDbConnectInfo = new HashMap<String, String>();

          mapDbConnectInfo.put("dbTypeProperty", "ORACLE");

          mapDbConnectInfo.put("HOST", "HFM_ETL_DEV"); // tnsnames.ora alias

          mapDbConnectInfo.put("dbUserName", "ST1_HFM_ETL");

          mapDbConnectInfo.put("dbPassword", "xxxxxxxxxxxxx");



          I just tried it out and it worked!

          • 2. Re: HFM Java API - Extract data to Database example

            Thank you for sharing this Rodrigo! I sure hope that the holes in the HFM API documentation will be filled at some point. I got my extraction code working with MS SQL Server using the following connection properties:


                        mapDbConnectInfo.put("dbTypeProperty", "MS_SQL_SERVER"); // Database server type

                        mapDbConnectInfo.put("HOST", "dbserver.test.local"); // DB server hostname

                        mapDbConnectInfo.put("dbPort", "1433"); // DB server port

                        mapDbConnectInfo.put("dbName", "EPM_HFMEXTRACT"); // Database name

                        mapDbConnectInfo.put("dbUserName", "hfm_ext"); // SQL login/user

                        mapDbConnectInfo.put("dbPassword", "xxxxxyyyyyzzz"); // Password for SQL login



            You can find additional properties (for DB2 for example) in the API constants documentation:


            1 person found this helpful