9 Replies Latest reply: May 8, 2014 5:30 AM by Francisco Amores RSS

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

    User607524-OC

      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