Forum Stats

  • 3,837,697 Users
  • 2,262,286 Discussions
  • 7,900,365 Comments

Discussions

HFM Data Extract Fails when exporting to Database

Chris Badenhorst
Chris Badenhorst Member Posts: 63 Red Ribbon
edited Jan 17, 2019 11:38AM in Financial Consolidation

Hi

We are trying to export metadata and data from our HFM application to an Oracle database. I've successfully created the DSN and when clicking on 'Test Connection', it is Successful.

However, when running the data extract, we get an error:

Processing status change: (-2147220797)

12/5/2018 9:08:25 AM : Status : Processing completed with errors

Data extract completed with errors for Application xxx using prefix xxx.  Error code is: -2147220797  :  (-2147220797) (An error occurred in creating an ODBC connection.)

Below is an extract of the xfm.odl  log:

[errorCode: 800402B5] [srcException: NotSpec]
[errType: 1] [dbUpdate: 2] [11.1.2.4.207.5298] An unknown error occurred in an
ODBC object. [[1 record: SQLSTATE = 08001; NATIVE ERROR = 0; MSG =
[DataDirect][ODBC Oracle Wire Protocol driver]Connection refused. Verify Host
Name and Port Number.]]

[errorCode: 800402B5] [srcException: NotSpec]
[errType: 1] [dbUpdate: 2] [11.1.2.4.207.5298] An unknown error occurred in an
ODBC object. [[1 record: SQLSTATE = 08001; NATIVE ERROR = 0; MSG =
[DataDirect][ODBC Oracle Wire Protocol driver]Connection refused. Verify Host
Name and Port Number.2 record: SQLSTATE = 08001; NATIVE ERROR = -1; MSG =
[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to
connect to destination]]

[errorCode: 800402C3] [srcException: XfmExc] [errType:
1] [dbUpdate: 1] [11.1.2.4.207.5298] An error occurred in creating an ODBC
connection. [[08001]]

[errorCode: 800402C3] [srcException: XfmExc] [errType:
1] [dbUpdate: 1] [11.1.2.4.207.5298] An error occurred in creating an ODBC
connection. [[Failed to create DB connection.]]

[errorCode:
800402B5] [srcException: NotSpec] [errType: 1] [dbUpdate: 2]
[11.1.2.4.207.5298] An unknown error occurred in an ODBC object. [[1 record:
SQLSTATE = HY000; NATIVE ERROR = 0; MSG = [DataDirect][ODBC Oracle Wire
Protocol driver]Cannot open TNS Names file.]]

[errorCode:
800402C3] [srcException: XfmExc] [errType: 1] [dbUpdate: 1] [11.1.2.4.207.5298]
An error occurred in creating an ODBC connection. [[HY000]]

Our HFM version is 11.1.2.4.206.5291 Our DB is MS SQL. We have confirmed that all IPs and Ports are open on the firewalls.

Any suggestions of what the cause might be?

Thank you.

Chris.

Tagged:

Best Answer

Answers

  • Chandra Bhojan-Oracle
    Chandra Bhojan-Oracle Posts: 198 Employee
    edited Dec 6, 2018 6:28AM

    The cause of the issue is "Cannot open TNS Names file".

    Please check if the EA schema Database added to the TNSNames.ora file which is under userprojects\config\dbclient folder in HFM Server.

    Also in the Windows environment Path variable is set to the correct client folder and that has the tnsnames.ora

    Are you able to connect to the EA schema using UDL file?

    Thanks,

    Chandra

  • Chris Badenhorst
    Chris Badenhorst Member Posts: 63 Red Ribbon
    edited Jan 2, 2019 1:51AM

    Hi Chandra

    We don't have the TNSNames.ora file. My understanding is that this will only be created if you are running HFM on an Oracle DB. Is my understanding correct? We are running HFM on MS SQL.

    Chris.

  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Jan 4, 2019 11:01AM

    Even though you're running HFM on MS SQL Server, I believe (based on your previous posts) that you are trying to export HFM data to an Oracle database though I could be mistaken. Is that correct? Are you trying to export data to a flat file, or an extended analytics schema? The "ORA-12203: unable to connect to destination]]" error is generated by either an Oracle database client or server.

  • Rajesh CP-Oracle
    Rajesh CP-Oracle Posts: 102 Employee
    edited Jan 4, 2019 11:17AM

    What was the db type chosen during the DSN configuration?

    There  will not be any tnsnames.ora file for SQL Server.

  • Chris Badenhorst
    Chris Badenhorst Member Posts: 63 Red Ribbon
    edited Jan 5, 2019 4:24AM

    Hi Chris

    Yes, we are trying to export HFM data to an Oracle database star schema.

    Could the issue be that we didn't install Oracle DB client on the servers?

    Chris.

  • Chris Badenhorst
    Chris Badenhorst Member Posts: 63 Red Ribbon
    edited Jan 5, 2019 4:26AM

    Hi

    The DB type chosen is Oracle. We don't have the tnsnames.ora file on our Development server or previous Production server and the extract worked. On our new Production servers the extract doesn't work. Could it be that we have to install Oracle DB client?

    Chris.

  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Jan 8, 2019 9:09AM

    Yes. HFM needs a way to communicate with the Oracle database, via the Oracle DB client.

  • Chris Badenhorst
    Chris Badenhorst Member Posts: 63 Red Ribbon
    edited Jan 17, 2019 11:38AM Answer ✓

    Hi

    We have resolved the issue by opening the Financial Management Server ports on the firewall (9091, 9092, 10001, 10020).

    Chris.