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 11.1.2.4 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");

        System.setProperty("EPM_ORACLE_INSTANCE",

        "//localhost/d$/Oracle/Middleware/user_projects/epmsystem1");

       

        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();

       

        dataExtractOptions.setDatabaseOption(DATA_PUSH_OPTION.STARSCHEMA_CREATE);

        dataExtractOptions.setDelimiter(";");

        dataExtractOptions.setDSN("HFM_ETL_DEV");

       

        dataExtractOptions.setExtractFormat(DATA_EXTRACT_TYPE_FLAG.EA_EXTRACT_TYPE_STANDARD);

        dataExtractOptions.setIncludeCalculatedData(true);

        dataExtractOptions.setIncludeCellDesc(false);

        dataExtractOptions.setIncludeData(true);

        dataExtractOptions.setIncludeDerivedData(true);

        dataExtractOptions.setIncludeDynamicAccounts(true);

        dataExtractOptions.setIncludePhaseGroup(false);

       

        dataExtractOptions.setLineItemOption(DATA_LINEITEM_OPTION.EA_LINEITEM_EXCLUDE);

        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]}";

        dataExtractOptions.setMetadataSlice(metadataSlice);

        dataExtractOptions.setTablePrefix("TIEOUT");

       

        int sessionID = loadExtractOM.extractData(dataExtractOptions);

        System.out.println(sessionID);

        } catch (Exception e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

        } finally {

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

        sessionOM.closeSession(sessionInfo);

        }

        }

      }

       

       

      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");

          dataExtractOptions.setMapDbConnectInfo(mapDbConnectInfo);

           

          I just tried it out and it worked!

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

            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

                        dataExtractOptions.setMapDbConnectInfo(mapDbConnectInfo);

             

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

            http://docs.oracle.com/cd/E57185_01/HFMJD/constant-values.html

            1 person found this helpful