Forum Stats

  • 3,780,523 Users
  • 2,254,406 Discussions
  • 7,879,373 Comments

Discussions

Receiving "Data Access Error" during Export

Sahi
Sahi Member Posts: 27
edited Jun 3, 2014 4:04AM in Financial Data Management


HI All,

Im working on FDM11.1.2.1 and my scenario is like: In my source file, i dont have currency column but its there in my target system(i.e., Essbase). So in my Import Formats, i added a currency column and gave "0" as the field number and number of fileds and always my target currency would be "LCProd" so i provided it in the expression column. In the mappings section, for currency, we have "Like" type mapping with source and target as "*".

Now, in the workflow, im able to Import and Validate the data and i receive Orange fish there and during the Export, it throws an error message saying "Error: Data Access Error" but i receive orange fish eventhough.

When i retrieve data from Smartview connecting to the target Essbase Application, there is no data, even i append or replace during the FDM Export.

Please guide me on this.

Thanks

Sahi

Tagged:
«1

Answers

  • KKT
    KKT Member Posts: 1,128

    Hi ,

    with Currency dimension , you need to also Include one more dimension i.e HSP_rates and like you did mapping for Currency

    it should be Like - Rule defination as "*" and HSP_rates as HSP_InputValue.

    Kindly include this in your mapping as well.

    Thanks,

    ~KKT~

  • Sahi
    Sahi Member Posts: 27

    HI,

    Actually mine is a customised Currency application. Sorry, i missed this point in my post. Is there any other work around to resolve this issue?

    Please help

  • KKT
    KKT Member Posts: 1,128

    Can you please paste the logs over here so that we can get better picture.

    from    application -> Outbox-> logs

    Thanks,

    ~KKT~

  • Sahi
    Sahi Member Posts: 27

    Below is the log of the data file loaded:

    2014-06-02 05:13:39

    User ID........... Singhs5
    Location.......... 138
    Source File....... \\sssss\FDMData\FDMPLDev\Inbox\138\Testload_FDM.csv

    Processing Codes:
    ------------------------------------------------------------------------------------------------
    BLANK............. Line is blank or empty.
    ESD............... Excluded String Detected, SKIP Field value was found.
    NN................ Non-Numeric, Amount field contains non numeric characters.
    RFM............... Required Field Missing.
    TC................ Type Conversion, Amount field could be converted to a number.
    ZP................ Zero Suppress, Amount field contains a 0 value and zero suppress is ON.

    Create Output File Start: [2014-06-02 05:13:39]

    [Blank] - 

    Excluded Record Count..............0
    Blank Record Count.................1
    ------------------------------------------------------------------------------------------------
    Total Records Bypassed.............1
    Valid Records......................1
    Total Records Processed............2

    Begin SQL Server (BCP) Process (1): [2014-06-02 05:13:39]

    Processing Complete... [2014-06-02 05:13:40]

    Thanks

    Sahi

  • KKT
    KKT Member Posts: 1,128

    Thanks for the log.

    As you are entering column as currency and value "0" so it is ignoring that. Instead of adding zero Please add currency Member like "Local" in your data file and try to import and export again.

    Thanks,

    ~KKT~

  • Francisco Amores
    Francisco Amores Member Posts: 1,694 Bronze Crown

    This log is related to your import process.

    If you have a custom dimension "Currency" in your Essbase application:

    - You will have enable a custom dimension in FDM Essbase adapter for "Currency". For example use UD5 if it is not used yet.

    - You will have to configure your import format in order to import currency value in that dimension. As you don't have this information in your source file, you can create a line in your import format, assign field number 1 (set number of fields to 1 as well), and then in the expression either enter the literal you want to import (LCProd) or assign an import script that generates desired values.

    - Then after creating mappings for currency, importing, validating, and exporting...check your FDM Log. Also check outbox\logs folder for any error file.

    Regards

  • Sahi
    Sahi Member Posts: 27

    We are using ICP in FDM Essbase Adapter for Currency thus i think its not necessary to use UD5 here.

    Mapping is done with "Like" Type as mentioned above.

    I tried making the Currency filed number and number of fileds as 1 and exported the data, still same error.

    Below is the error from Outbox-->Logs:

    2014-06-02 07:26:56

    User ID........... Singhs5
    Location.......... 138
    Source File....... \\TRW-SQLDW01\FDMData\FDMPLDev\Inbox\138\Testload_FDM.csv

    Processing Codes:
    ------------------------------------------------------------------------------------------------
    BLANK............. Line is blank or empty.
    ESD............... Excluded String Detected, SKIP Field value was found.
    NN................ Non-Numeric, Amount field contains non numeric characters.
    RFM............... Required Field Missing.
    TC................ Type Conversion, Amount field could be converted to a number.
    ZP................ Zero Suppress, Amount field contains a 0 value and zero suppress is ON.

    Create Output File Start: [2014-06-02 07:26:56]

    [Blank] - 

    Excluded Record Count..............0
    Blank Record Count.................1
    ------------------------------------------------------------------------------------------------
    Total Records Bypassed.............1
    Valid Records......................1
    Total Records Processed............2

    Begin SQL Server (BCP) Process (1): [2014-06-02 07:26:56]

    Processing Complete... [2014-06-02 07:26:57]

    From Error Log:

    ** Begin Oracle Hyperion FDM Adapter for Essbase Runtime Error Log Entry [2014-06-02-07:27:46] **

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

    Error:

    Code..............10405

    Description.......Essbase API Procedure: [EsbReport] Threw code: 1001005 - 1001005 - [Mon Jun 02 07:27:46 2014]TRW-ESSDW01/PROD13/PRODLINE/Singhs5/Error(1001005)

    Unknown Member [[MISSING_VALUE]] in Report

    Procedure.........clsHPDataAccess.fValueGet

    Component.........upsES11XG4H

    Version...........100

    Thread............5056

    Identification:

    User..............Singhs5

    Computer Name.....TRW-FDMDW01

    Essbase Connection:

    App Name..........PROD13

    DB Name...........PRODLINE

    Server Name.......TRW-ESSDW01

    Connect Status.... Connection Open

    ** Begin FDM Runtime Error Log Entry [2014-06-02 07:27:46] **

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

    ERROR:

    Code............................................. -2147217900

    Description...................................... 'TO_CHAR' is not a recognized built-in function name.

    SELECT

    CatKey,

    PeriodKey,

    rptEntityStatus,

    rptLocation,

    rptName,

    rptDesc,

    rptMode,

    rptTitle,

    rptSubTitle,

    rptEntName,

    rptEntCat,

    rptEntPer,

    rptRuleSeq,

    rptRuleTitle,

    rptRuleDef,

    rptAcct,

    rptAcctDesc,

    rptAcctVal,

    rptTime,

    rptRuleStatus,

    UDVal1,

    UDVal2,

    UDText1,

    UDText2,

    UDText3

    FROM tDataCheck

    WHERE

    (CatKey = 13)

    AND (TO_CHAR(PeriodKey, N'YYYYMMDD') = N'20140331')

    AND (PartitionKey = 921)

    ORDER BY rptEntName, rptRuleSeq

    Procedure........................................ clsDataAccess.farsSnap

    Component........................................ upsWDataWindowDM

    Version.......................................... 1112

    Thread........................................... 4840

    IDENTIFICATION:

    User............................................. Singhs5

    Computer Name.................................... TRW-FDMDW01

    App Name......................................... FDMPLDev

    Client App....................................... WebClient

    CONNECTION:

    Provider......................................... SQLOLEDB

    Data Server...................................... TRW-SQLDW01

    Database Name.................................... FDM_FDMPLDev_DEV

    Trusted Connect.................................. False

    Connect Status.. Connection Open

    GLOBALS:

    Location......................................... 138

    Location ID...................................... 921

    Location Seg..................................... 10

    Category......................................... Actual

    Category ID...................................... 13

    Period........................................... Mar - 2014

    Period ID........................................ 3/31/2014

    POV Local........................................ False

    Language......................................... 1033

    User Level....................................... 1

    All Partitions................................... True

    Is Auditor....................................... False

    ** Begin FDM Runtime Error Log Entry [2014-06-02 07:27:46] **

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

    ERROR:

    Code............................................. -2147217900

    Description...................................... Data access error.

    Procedure........................................ clsAppServer.fPublishReport

    Component........................................ upsAppSv

    Version.......................................... 1112

    Thread........................................... 4840

    IDENTIFICATION:

    User............................................. Singhs5

    Computer Name.................................... TRW-FDMDW01

    App Name......................................... FDMPLDev

    Client App....................................... WebClient

    CONNECTION:

    Provider......................................... SQLOLEDB

    Data Server...................................... TRW-SQLDW01

    Database Name.................................... FDM_FDMPLDev_DEV

    Trusted Connect.................................. False

    Connect Status.. Connection Open

    GLOBALS:

    Location......................................... 138

    Location ID...................................... 921

    Location Seg..................................... 10

    Category......................................... Actual

    Category ID...................................... 13

    Period........................................... Mar - 2014

    Period ID........................................ 3/31/2014

    POV Local........................................ False

    Language......................................... 1033

    User Level....................................... 1

    All Partitions................................... True

    Is Auditor....................................... False

  • Francisco Amores
    Francisco Amores Member Posts: 1,694 Bronze Crown

    Hi,

    I would not use ICP, better custom dimension.

    It seems that you have an issues with your validation report:

    - Unknown Member [[MISSING_VALUE]] in Report. that error is due to a required dimension not being mapped so it's taking [MISSING_VALUE] by default. It could be because of using ICP although you would like to have a look to your TDATASEG table

    - 'TO_CHAR' is not a recognized built-in function name --> Have you customized the SQL query of the report. TO_CHAR is not valid for SQL Server, only for Oracle

  • Sahi
    Sahi Member Posts: 27

    Hi KKT,

    We are not supposed to change the source file shared by client. So whatever we do to load the file, needs to be done in the FDM alone.

  • Sahi
    Sahi Member Posts: 27

    HI,

    Please note that ours is an upgradation project, from 8.3.5 to 11.1.2.1.

    As u suggested, i disabled the ICP Dimension and activated the UD5 custom dimension for load and also updated the Import format and maps. But still no luck, when i retireve data thru Smartview, i observed that not only in the combination im loading the data thru FDM, data is getting cleared in the whole cube. This is really confusing, what is leading the complete cube clear in FDM is the question.

    Ona positive note, We raised an Oracle SR and they helped us resolve the "Data access error" (Solution - In workbench we need to import SQL standard reports as our database is SQL, earlier it was Oracle reports.) But still looks like the data is not getting loaded in Essbase although there is NO ERROR but "Check report" is coming BLANK, ZERO VALUES (attached).

    Error Log View:

    Error:

    Code..............10405

    Description.......Essbase API Procedure: [EsbReport] Threw code: 1001005 - 1001005 - [Mon Jun 02 16:39:27 2014]ESSDW01/PROD13/PRODLINE/singhs5/Error(1001005)

    Unknown Member [[MISSING_VALUE]] in Report

    Procedure.........clsHPDataAccess.fValueGet

    Component.........upsES11XG4H

    Version...........100

    Thread............4280

    Identification:

    User..............singhs5

    Computer Name.....FDMDW01

    Essbase Connection:

    App Name..........PROD13

    DB Name...........PRODLINE

    Server Name.......ESSDW01

    Connect Status.... Connection Open

    I searched on the Error code:1001005 and in one of the discussion board, found that this error occurs when Hyperion Reports and Hyperion Essbase get out of synch.

    Please suggest me on the same to move forward on the issue.

This discussion has been closed.