FDM - Integration IMPORT script failed

FDM - Integration IMPORT script failed

Sun
Sun

July 3rd, 2012, 12:42 pm #1

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
Quote
Share

Anonymous
Anonymous

July 3rd, 2012, 3:24 pm #2

Hi any one pls.....
Quote
Share

Joined: April 4th, 2008, 8:06 pm

July 3rd, 2012, 3:31 pm #3

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
Do two things.

1) First test the extract portion in Excel just writing out to a flat file. FDQM is really bad at reporting good errors and you cannot debug within workbench.

2) For the second half put in a very basic integration script that just adds some rows manually.

Use 1 and 2 to figure out where your issue is. Then carefully put them together and run that way.

Hope this helps,

John A. Booth
http://www.metavero.com
Quote
Like
Share

sun
sun

July 3rd, 2012, 3:52 pm #4

Thanks for you reply John.

Actually, i was able to load data from a flat file...but since our data is in oracle data i am trying to do the same import process using the script which is available in our fdm admin guide....but its throwing error at the line Set rsappend = DW.DataAccess.farsTableAppend(strWorkTableName)

any help is much appreciated.....
Quote
Share

Joined: April 4th, 2008, 8:06 pm

July 3rd, 2012, 3:57 pm #5

Try to review the user logs and see if it shows anything useful.

I would also suggest trying a very simple script that loads a few records as I mentioned before.

Failing all of that you can log a ticket however as you are doing custom code effectively they may or may not be of assistance.

The FDQM interface is pretty unforgiving so you may have to seek some on site consulting as well if all else fails.

Regards,

John A. Booth
http://www.metavero.com
Quote
Like
Share

sun
sun

July 3rd, 2012, 4:07 pm #6

i got the below error...

*** 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


and FDM stops and highlight the below line..

Set rsappend = DW.DataAccess.farsTableAppend(strWorkTableName)
Quote
Share

Joined: April 4th, 2008, 8:06 pm

July 3rd, 2012, 4:25 pm #7

I don't think you can run an integration script interactively like that -- you will need to set it up to run as it really runs. Running it interactively won't pass the proper parameters. Which is why it's not happy with a null table name.

Regards,

John
Quote
Like
Share

sun
sun

July 3rd, 2012, 4:35 pm #8

Thanks for your reply John...

but i tried to search for the infortmation everywhere but went in vain ....not sure ..but am a novice in this fdm part....

any help is much appreciated....any guidelines from u......
Quote
Share

sun
sun

July 4th, 2012, 12:55 pm #9

Hi John,

i managed to run the script successfully by creating a temporaty work table using fWorkTableMake.....and the script is executing till the end where i am displaying a message box....

but when i goto the web client to check whether the import is happned or not..it was showing the below one....

Error: An error occurred importing the file.
Detail: Object required: 'API'
At line: 24
Stacktrace:
upsOBJDMw.clsImpProcessMgrClass.fLoadAndProcessFile(strFilename[String], strLoc[String], strCat[String], strPer[String], blnMerge[Boolean])
Hyperion.FDM.Pages.ImportPage.buttonImport_Click(sender[Object], e[EventArgs])


any idea on this?
Quote
Share

sun
sun

July 5th, 2012, 1:27 pm #10

Hi John,

I amable to successfully perform import, validate, export , load process using the custom integration script in FDM......i.e. i wrote a script named ACtual_load under the import folder in the workbench....and this code includes everythign from import to load..


now i want to know how i can schedule this one? i saw upshell.exe but am not seeing any difference there when i execute from the command prompt...it just finishes and no idea whether it actually executed/executing...

can you please help me,.....
Quote
Share


Confirmation of reply: