4 Replies Latest reply on Feb 12, 2013 5:44 PM by TreyD424

    Hyperion FDM and ERPi 11.1.2.2: ORA-12504 on Import with ERPI-FIN-E

    TreyD424
      Hi everyone,

      We are implementing Hyperion 11.1.2.2 FDM with HFM (Target Adapter = FM11X-G6-A) and ERPi (Source Adapter = ERPI-FIN-E) running on ODI 11.1.1.6. Backend databases are all Oracle DBs. We have been able to successfully configure ERPi and ODI & successfully run a Data Load Rule with all green checks in ODI Operator. We have also successfully tested the full FDM workflow with load to HFM using the traditional flat file method.

      We have also setup an FDM Location and Import Format (File Type = Adapter; Adapter = ERPI-FIN-E) for use with the ERPI Source Adapter. When kicking off an Import either manually via FDM Web or via Batch Loader, we are getting a grey fish, Information Bar with "Error: Import failed. Invalid Data or Empty content", and pop-up error dialog displaying "ORA-12504: TNS: Listener was not given the SERVICE_NAME in CONNECT_DATA.

      While previous versions of ERPi Source Adapter have the "ERPi Database Server/Service" option, ERPI-FIN-E does not have this option. Additionally, we have tested the TNSNames.ora files SID's with SQL Developer and SQL Plus and they all seem to be working.

      Has the "ERPi Database Server/Service" option moved somewhere behind the scenes? Another options or DBLink that needs to be setup somewhere else? Any thoughts or assistance would help.

      Thank you.

      Contents of ERPI-FIN-E.log is below:

      -----------------------------------------------------------------------------
      *** 1/22/2013 9:30:01 AM ***
      -----------------------------------------------------------------------------
      LoggedInUser = +redacted+
      Token = iZKY0Aj/AeTH9AV6F/k980xCFWDzrk5im3n2E0SnmNg6c/fNa/XjSfSU6+wemuIo3IQdzrUlhAj8
      M9J9SJpU5TvCibwJZaUJOh3nlA83UlfKOHpUC7oPGOqhedu+XIPbMC022Ftcb4LexFa/M1R6Rw1h
      4D7nbb0nLQv/viNdZyXzORrXzKD41ljWAnw/J/R84ZYt3yHyjxI2KBz6f7x9FKyfNnD5XJuR+50y
      klV8tjlLY6EvfKyiv/ecSanQuWeA1p2D8MBIFUftHtqgnbABXOEyRll4kT9yaPUaClpvLDaD7knt
      iBUSXU1fIOoKuHtrrD4XCvjMKcowWv/SnLIrbkn2HXotkKwqCulHx2is6yWnJfni96tZGvG8z4ds
      WRgm7tsJRV12Tp5zeNLSxZTZoK/5AMCdN5MC6u88G59prlI=
      ERPIdbConnString = Oracle://EPMD1.ges.+redacted+.com:1521/EPMD1
      m_ERPIdbUserID = ERPI
      ERPIdbPassword.Length = 6

      -----------------------------------------------------------------------------

      -----------------------------------------------------------------------------
      *** 1/22/2013 9:30:17 AM ***
      -----------------------------------------------------------------------------
      PeriodKey = 11/30/2012 12:00:00 AM
      PriorPeriodKey = 10/31/2012 12:00:00 AM
      Rule Name = DLR_1
      Execution Mode = FULLREFRESH
      executeRule...OK.
      -----------------------------------------------------------------------------

      -----------------------------------------------------------------------------
      *** 1/22/2013 9:30:17 AM ***
      -----------------------------------------------------------------------------
      System.Runtime.InteropServices.COMException (0x80040E14): ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
      at ADODB.ConnectionClass.Execute(String CommandText, Object& RecordsAffected, Int32 Options)
      at fdmErpiFin_E.clsDW.fTransferData(String strSQL, Int64& lngRecAffected)
      -----------------------------------------------------------------------------
        • 1. Re: Hyperion FDM and ERPi 11.1.2.2: ORA-12504 on Import with ERPI-FIN-E
          JeffJon
          You need to add an entry to the TNSNAMES.ORA file in the 32 bit Oracle client directory with the Host entry for what was used to configure the ERPI Database with the EPM System configuration Utility.
          • 2. Re: Hyperion FDM and ERPi 11.1.2.2: ORA-12504 on Import with ERPI-FIN-E
            TreyD424
            Below are the contents of the "E:\Oracle\product\11.2.0\client_32\network\admin" and "E:\Oracle\product\11.2.0\client_64\network\admin" folders on application server. EPMD1 = ERPi, HFMD1 = HFM, FDMD1 = FDM, EPMD5 = R12.

            Since we are able to connect via SQL Developer and SQL Plus to EPMD1, I believe these settings are OK, but open to additional checks if suggested.

            *****************
            EPMD1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = EPMD1.ges.+redacted+.com)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = EPMD1.ges.+redacted+.com)
            )
            )

            HFMD1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = HFMD1.ges.+redacted+.com)(PORT = 1522))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = HFMD1.ges.+redacted+.com)
            )
            )

            FDMD1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = FDMD1.ges.+redacted+.com)(PORT = 1523))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = FDMD1)
            )
            )

            EPMD5 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = tus1ebsdbsdin11.ges.+redacted+.com)(PORT = 15212))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = EPMD5.ges.+redacted+.com)
            )
            )
            • 3. Re: Hyperion FDM and ERPi 11.1.2.2: ORA-12504 on Import with ERPI-FIN-E
              JeffJon
              Create an Entry like the following:

              EPMD1.ges.redacted.com =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = EPMD1.ges.redacted.com)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = EPMD1.ges.redacted.com)
              )
              )
              • 4. Re: Hyperion FDM and ERPi 11.1.2.2: ORA-12504 on Import with ERPI-FIN-E
                TreyD424
                After discussing with Oracle Support, there were 2 issues that needed to be resolved:

                1) Since FDM and ERPi were installed on different Oracle database instances, a DBLink needed to be created between the 2 to successfully pass information between them

                2) Oracle Support sent us a "ERPI-FIN-E1" version of the ERPi Source Adapter that allows the use of DBLinks and reading data across tables. This is not supported by ERPI-FIN-E.

                Now we have integration using FDM and the ERPI-FIN-E1 Source Adapter.