This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
 

 Return to Index  

FDM - Integration IMPORT script failed

July 3 2012 at 8:42 AM
No score for this post
Sun 
from IP address 203.99.193.189

 
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)

*****

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Responses

  1. Re: FDM - Integration IMPORT script failed - Anonymous on Jul 3, 11:24 AM
  2.  
  3. Test it in Excel - John A. Booth on Jul 3, 11:31 AM
    1. Re: Test it in Excel - sun on Jul 3, 11:52 AM
      1. Review user logs - John A. Booth on Jul 3, 11:57 AM
        1. Re: Review user logs - sun on Jul 3, 12:07 PM
          1. Script - John A. Booth on Jul 3, 12:25 PM
            1. Re: Script - sun on Jul 3, 12:35 PM
              1. Re: Script - sun on Jul 4, 8:55 AM
                1. Re: Script - sun on Jul 5, 9:27 AM
                  1. Batch Loader and Task Manager - Jeff McAhren on Jul 5, 9:52 AM
                    1. Re: Batch Loader and Task Manager - sun on Jul 5, 11:03 AM
                  2. Couldn't run the import integration script - Yam on Jul 18, 4:03 AM
                    1. Re: Couldn't run the import integration script - Anonymous on Jul 27, 7:14 AM
     
 Copyright © 1999-2014 Network54. All rights reserved.   Terms of Use   Privacy Statement  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.