9 Replies Latest reply on May 8, 2014 10:30 AM by Francisco Amores

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



      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