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

Hi,

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
Err.clear

'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"
Err.clear
Set cnSS = Nothing
FinAllLocs_Conv = False
Exit Function
End If

'Create query String
strSQL = "Select * "
strSQL = strSQL & " From ODI_WORK_MARS.dbo.TMP_HFM_ACTUAL_DATA_EXTRACT_TIN1 "

'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.AddNew
   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
  
                          
  rsAppend.Update
  RecordCount = Recordcount + 1
 
 
 
  rs.movenext
  Loop
End If
 

'Records loaded

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

     
       
  
'Assign Return value
SAP_HFM = True

End Function

Legend

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