Hi All,
I am creating an FDM interface to fetch data from Oracle RDB and push it to an essbase database. I am under the impression that if you have to pull data from RDB you need to create an integration import script . So I have created an Import Integration script based on the example given in the FDM admin Document.
The Script is as below:
Function PSIP_TEST(strLoc, lngCatKey, dblPerKey, strWorkTableName)
'------------------------------------------------------------------
'Oracle Hyperion FDM Integration Import Script:
'
'Created By: admin
'Date Created: 2/18/2014 6:00:33 PM
'
'Purpose:
'
'------------------------------------------------------------------
Dim cnSS 'ADODB.Connection
Dim strSQL 'SQL string
Dim rs 'Recordset
Dim rsAppend 'tTB table append rs object
'Initialize objects
Set cnSS = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName)
'Connect to Oracle database
cnss.open "Provider=OraOLEDB.Oracle.1;Password=PSIP_TST;Persist Security Info=True;User ID=PSIP_TST;Data Source=PSIP"
'Create query string
strSQL = "SELECT YEAR,COST_CENTRE,PROGRAMME_CODE,ACCOUNTS,PROJECT_CODE,AMOUNT FROM VW_PSIP_EBUDGET_ESTIMATES"
'Get data
rs.Open strSQL, cnSS
'Check for data
If rs.bof And rs.eof Then
RES.PlngActionType = 2
RES.PstrActionValue = "No Records to load!"
Exit Function
End If
'Loop through records and append to tTB table in location’s DB
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.PlngCatKey
rsAppend.Fields("PeriodKey") = RES.PdtePerKey
rsAppend.Fields("DataView") = "YTD"
rsAppend.Fields("CalcAcctType") = 9
rsAppend.Fields("Account") = rs.fields("ACCOUNTS").Value
rsAppend.Fields("Entity") = rs.fields("COST_CENTRE").Value
rsAppend.Fields("UD1") = rs.fields("COST_CENTRE").Value
rsAppend.Fields("UD2") = rs.fields("PROGRAMME_CODE").Value
rsAppend.Fields("UD3") = rs.fields("PROJECT_CODE").Value
rsAppend.Fields("Amount") = rs.fields("Amount").Value
rsAppend.Update
rs.movenext
Loop
End If
'Records loaded
RES.PlngActionType = 6
RES.PstrActionValue = "Import successful!"
'Assign Return value
SQLIntegration = True
End Function
When I run the Script in Script editor I get the following error :
Error: An error occurred running the script:
-2147467259 - Data access error.
At Line: 19
If I see in the Error log it is as follows:
ERROR:
Code............................................. -2147467259
Description...................................... ORA-00903: invalid table name
Procedure........................................ clsDataAccess.farsTableAppend
Component........................................ upsWDataWindowDM
Version.......................................... 1112
Thread........................................... 41900
Line nu. 19 is this : Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName) and therefore I thought that may be when I run the script in the Script editor maybe the value of the variable "strWorkTableName" does not get populated that is why it gives error .
So I ran the Import stage in the Workflow and Got the following error :
Error: Import failed.Invalid data or Empty content.
and there is no entry in the error log.
I am lost , please need your help .
One more thing I would like to ask is kindly please validate if my connection string is correct which is :
cnss.open "Provider=OraOLEDB.Oracle.1;Password=PSIP_TST;Persist Security Info=True;User ID=PSIP_TST;Data Source=PSIP"
I read that FDM is a 32 bit product and therefore will require a 32 bit oledb connection provider.
FDM here is installed on a 64 bit machine and the provider here used in the connection string is 64 bit.
Please help , my work here has come to a stand still . Kindly help