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