7 Replies Latest reply: Feb 10, 2014 3:49 AM by Francisco Amores RSS

FDM Integration script not selecting the FDM PoV to import data fo rgiven Period

User607524-OC Newbie
Currently Being Moderated


I have an issue with the below script when importing data, it's not using the FDM PoV but intstead importing all the data from SQL Table. Do i need to make a change for the Year it is FY14 in the table and FDM expects Apr- 2013. I don't use SQL so any script added to this will be appreciated.

If i have a Where clause = 'Apr' say then i get the data for that month.


SQL Table: Year and Date columns

FY14 | Apr

FY14 | May

FY14 | Jun


Import Script

Oracle Hyperion FDM IMPORT Integration Script:
'Created By:   admin
'Date Created:   2014-01-03 17:06:28
'Purpose:  To import PL and BS Actual data from ODS

Dim objSS   'ADODB.Connection
Dim strSQL    'SQL string
Dim strSelectPer 'FDM Period
Dim strSelectYear'FDM Year
Dim rs        'SQL Recordset
Dim rsAppend   'FDM tTB table append rs object
Dim recordCount

'Initialize objects
Set cnSS = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

'Get Current POV Period And Year And determine HFM Select Year
strSelectPer = Left((RES.PstrPer), 3)
strSelectYear = Right(RES.PstrPer, 4)
Select Case UCase(strSelectPer)
Case "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
  strSelectYear = strSelectYear + 1
Case "Jan", "Feb", "Mar"
     strSelectYear = strSelectYear
End Select

'Watch with this as it can cause looping
On Error Resume Next

'Connect to SQL database
cnSS.Open "Driver=SQL Server;Server=xxxxxxxx\xxxxxxx;Database=ODI_WORK_MARS;UID=xxxxx;pwd=xxx"

'Connect to SQL Server database
cnSS.CommandTimeout = 1200

'Keep the error message handling in for testing but will probably need  to write
'to a log if running in an overnight batch
'Error Handling
If  Err.Number <> 0  Then  
'  An exception occurred
RES.PlngActionType = 2
RES.PstrActionValue =  Err.Description & Chr(10) & "Unable to connect to SQL Data Source"
Set cnSS = Nothing
FinAllLocs_Conv = False
Exit Function
End If

'Create query String
strSQL = "Select * "

'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
'   RecordCount = 0
    'Loop through records and append to FDM tTB table in location's DB
If Not rs.bof And Not rs.eof Then
  Do While Not rs.eof

'Create the record 
   rsAppend.Fields("PartitionKey") = RES.PlngLocKey         ' Location ID
   rsAppend.Fields("CatKey") = RES.PlngCatKey          'Current Category ID
   rsAppend.Fields("PeriodKey") = RES.PdtePerKey         'Current Period ID
      rsAppend.Fields("DataView") = "YTD"            'Data View ID
   rsAppend.Fields("CalcAcctType") = 9            'Input data indicator
   rsAppend.Fields("Entity") = rs.fields("Entity").Value        ' Entity/Genpo ID
   rsAppend.Fields("Account")= rs.fields("Account").Value        'Account ID
      'rsAppend.Fields("UD1") = rs.fields("Account").Value        'Account/ID
      rsAppend.Fields("UD2") = rs.fields("Account").Value        'Account/ID
      rsAppend.Fields("ICP") = rs.fields("ICP").Value         ' Inter-Co/Destination
   rsAppend.Fields("Amount") = rs.fields("Value").Value        ' Data Value ID
  RecordCount = Recordcount + 1
End If

'Records loaded

RES.PlngActionType = 2
    RES.PstrActionValue = "SQL Import successful!   "  & RecordCount

'Assign Return value
SAP_HFM = True

End Function


  • Correct Answers - 10 points
  • Helpful Answers - 5 points