1 Reply Latest reply on Jul 4, 2012 4:13 AM by John A Booth

    FDM - Integration IMPORT script failed

    STH1980
      Hi,

      We are on 11.1.2.1.

      I am trying to create a import integration script which i took from the FDM admin guide...and i am trying to pull data from Oracle table...

      But i am getting error as "Data access error" and its in the below line when i try to execute the code form the workbench...but when i use the import button in the web its importing....*i beleive its somethign related to the STRWORKTABLENAME and somewhere i read that i need to declare the function FARSTABLE*...but i dont know how as i am pretty novice to that....can you please help me....

      *****
      Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
      *****


      also when i checked the error log i saw


      *** Begin FDM Runtime Error Log Entry [2012-07-03 07:06:27] ***
      -------------------------------------------------------------
      ERROR:
      Code............................................. -2147467259
      Description...................................... ORA-00903: invalid table name

      Procedure........................................ clsDataAccess.farsTableAppend
      Component........................................ upsWDataWindowDM
      Version.......................................... 1112
      Thread........................................... 20228




      Below is the complete code i am using

      Function SI_ActualLoad(strLoc, lngCatKey, dblPerKey, strWorkTableNam)*
      *+'------------------------------------------------------------------+*
      +'Oracle Hyperion FDM IMPORT Integration Script:+
      +'+
      +'Created By: admin+
      +'Date Created: 2012-06-01 01:19:02+
      +'strWorkTableName+
      +'Purpose:+
      +'strWorkTableName+
      *+'------------------------------------------------------------------+*
      Dim cnSS 'ADO connection Object*
      Dim strSQL 'SQL String*
      Dim rs 'Source system recordset*
      Dim rsAppend 'Hyperion FDM recordset*
      Dim monthvar*

      +'Initialize ADO objects+
      Set cnSS = CreateObject("ADODB.Connection")*
      Set rs = CreateObject("ADODB.Recordset")*
      monthvar=Month(Date)*
      +'Open Hyperion FDM work table recordset For appending+

      +'Set rsappend = DW.DataAccess.farsTableAppend(strWorkTableName)+
      +'Connect To Oracle database (our data source)+
      Dim strConn*
      strConn="Provider=ORAOLEDB.ORACLE.1;Data Source=EMDDS392:1521/DEVDBEMD;Database=DEVDBEMD;User id=xxxxxxx;Password=xxxxx"*
      cnss.open strConn*

      +'Create source query String+

      strSQL = "Select * "*
      +'strSQL = strSQL & "FROM STG_SAP_ACTUALS WHERE ACCOUNT NOT LIKE 'NA'"+
      strSQL = strSQL & "from EGETLDB.STG_SAP_ACTUALS where period =SUBSTR(TO_CHAR(SYSDATE-90,'MONTH'),1,5)"*
      +'Open source recordset+
      rs.Open strSQL, cnSS*
      +'Check For data In source system+
      If rs.bof And rs.eof Then*
      +'Give Error message+
      RES.PlngActionType = 2*
      RES.PstrActionValue = "No records To load!"*
      +'Assign Return value of Function+
      SI_ActualLoad = False ' Assign return value of function*


      Exit Function*
      End If*

      +'Loop through source records In database And append To+
      +'Hyperion FDM work table+
      If Not rs.bof And Not rs.eof Then*
      Do While Not rs.eof*
      rsAppend.AddNew*
      rsAppend.Fields("PartitionKey") = RES.PlngLocKey*
      rsAppend.Fields("CatKey") = RES.lngCatKey*
      rsAppend.Fields("PeriodKey") = RES.dblPerKey*
      rsAppend.Fields("DataView") = "YTD"*
      rsAppend.Fields("CalcAcctType") = 9*
      rsAppend.Fields("Amount") = rs.fields("GROUPCURRENCY").Value*
      rsAppend.Fields("Account") =UCase(rs.fields("ACCOUNT").Value)*
      rsAppend.Fields("Entity") = UCase(rs.fields("ENTITY").Value)*
      rsAppend.Fields("UD1") = UCase(rs.fields("SCENARIO").Value)*
      rsAppend.Fields("UD2") = rs.fields("VERSION").Value*
      rsAppend.Fields("UD3") = rs.fields("CURRENCY").Value*
      rsAppend.Fields("UD4") = rs.fields("TRANS_CURRENCY").Value*
      rsAppend.Fields("UD5") = rs.fields("ZONE").Value*
      rsAppend.Fields("UD6") = rs.fields("CATEGORY").Value*
      rsAppend.Fields("UD7") = rs.fields("SCHEDULE").Value*

      rsAppend.Fields("UD8") =UCase(rs.fields("DEPT_PRJCTS").Value)*
      rsAppend.Fields("UD9") =rs.fields("WBSELEMENT").Value*

      rsAppend.Fields("UD10") = rs.fields("DOC_DETAIL").Value*
      rsAppend.Fields("UD11") = rs.fields("BUSINESS_TRANSACTION").Value*
      rsAppend.Fields("UD12") = rs.fields("VENDOR_NAME").Value*
      rsAppend.Fields("UD13") =rs.fields("RECOVERYINDICATOR").Value*

      rsAppend.Fields("UD14") = rs.fields("TRANSAMOUNT").Value*
      rsAppend.Fields("UD15") = rs.fields("GROUPCURRENCY").Value*
      rsAppend.Fields("UD16") = rs.fields("SEGMENTNO").Value*
      rsAppend.Fields("UD17") = rs.fields("PARTNEROBJECT").Value*
      rsAppend.Fields("UD18") =rs.fields("PO").Value*

      rsAppend.Fields("UD19") = rs.fields("QUANTITY_UOM").Value*
      rsAppend.Fields("UD20") = rs.fields("DESCRIPTION").Value*
      rsAppend.Fields("DESC1") = rs.fields("ACCOUNT_DESCRIPTION").Value*
      rsAppend.Update*
      rs.movenext*

      Loop*
      End If*
      +'Give success message+
      RES.PlngActionType = 6*
      RES.PstrActionValue = "SQL Import successful!"*
      +'Assign Return value+
      SI_ActualLoad = True*


      End Function*
        • 1. Re: FDM - Integration IMPORT script failed
          John A Booth
          Per my answer on Network 54 you cannot test the script interactively in FDQM workbench. You must execute the integration script as part of the normal data load process.

          Per the FDM Admin guide: "Integration scripts can be run only by selecting Script from the File Type column of the Import Group grid (top grid) of the Import Formats screen".

          As I mentioned in my Network 54 post you can test the pure vbscript portions (without the FDQM specifics) to confirm that is working properly then add it to the integration script which will only run as part of a normal file import process.

          Regards,

          John A. Booth
          http://www.metavero.com