10 Replies Latest reply on Jul 3, 2012 4:38 PM by beyerch2

    API error in import script

    user8714121
      I am attempting to concatenate the result of a dimension lookup onto the source account string. I have attached the script below to the account dimension in the import format using a data pump import script.
      ____________________________
      'Function CCRule(strField, strRecord)

      Dim StrCC

      StrCC = (DW.Utilities.fParseString(strRecord,8,3,","))

      If StrCC ="" Then

      CCRule = StrField

      Else

      CCRule = StrField + "_" + API.SqlMgr.fMapItemLookup(800,"UD5",StrCC)

      End If
      End Function
      _______________________________________________________


      When i run this script i get the error "Object Required 'API'"

      Please let me know your thoughts on what i am doing wrong here.

      Thank you
        • 1. Re: API error in import script
          beyerch2
          #1 = You cannot use API items in Import scripts

          #2 = I am not 100% sure I understand what you are doing here, could you provide some sample data? There's probably another way to accomplish this.
          • 2. Re: API error in import script
            user8714121
            Thank you for your response. Our cost centers fall into three categories "C" "A" or "S" and the account mappings need to take these categories into consideration. Instead of concatenating the full cost center onto the account and mapping every combination I am hoping to append the category onto the account string. Below is some sample data of what i am hoping will occur.

            Original Data File:
            Account, CostCenter, amount
            123, C998, $10
            123, C778, $15
            232, C656, $20

            UD5 Map:
            Source, Target
            C998, C
            C778, S
            C656, C

            Data File after Script
            Account, CostCenter, Amount
            123_C, C998, $10
            123_S, C778, $15
            232_C, C656, $20

            Account map
            source, target
            123_C, hfmx
            123_S, hfmy
            123_A, hfmz
            232_C, hfml
            232_S, hfmm
            232_A, hfmn

            Please let me know if there is a better way to accomplish this. For user adoption purposes i would like to avoid conditional mapping if possible.
            Thank you again for you guidance.
            • 3. Re: API error in import script
              beyerch2
              and I assume there are more than 3 UD5 map records? :) It's also too bad the cost center type isn't just buried into the account with a unique ID character .......


              You should able to use :

              DW.DataAccess.farsKeySet(strSQL)

              To execute your own SQL statement against the DataMap table to get at the information .....
              • 4. Re: API error in import script
                user8714121
                Thanks, yeah, there are about 3000 cost centers and the "category" is only tracked offline in a spread sheet...making this a little more fun that it otherwise needs to be :) Thanks again for your help. I'll take a stab and that approach.
                • 5. Re: API error in import script
                  user8714121
                  I am running into the following error when i try the script below "Row cannot be located for updating. Some values may have been changed since it was last read" Please help. My desired end result is to concatenate the cost center type with the current account source string.

                  _____________________________________________________
                  Sub ImportAction(strEventName)
                  '------------------------------------------------------------------
                  'Hyperion FDM EVENT Script:
                  '

                  'Date Created:      7/2/2012 3:46: PM
                  'Change Log:
                  '
                  'Purpose:
                  '-----------------------------------------------------------------


                  If LCase(strEventName) = "postworktomainprocess" Then

                  ' Get work table
                       strWork = RES.PstrWorkTable

                  'Get the current location's profile

                  With API.POVMgr.fCheckLocProf(API.POVMgr.PPOVLocation)

                  lngLocID = .lngLocKey

                  'Location equals SAP_TB

                  If .lngLocKey ="800" Then


                       If RunCCRules(strWork) <> False Then
                            RES.PlngActionType = 2
                            RES.PstrActionValue = RES.PstrActionValue & "<big>Updated for missing cost center;</big>"
                       End If
                       
                       
                  End If
                  End With
                  End If
                  End Sub
                  '***********************************************************************
                  Function RunCCRules(strWork)

                  Dim strSQL
                  Dim rsSQL
                  Dim strCostCenters
                  Dim strCurrAccount

                  strSQL = "SELECT " & strWork & ".AccountX, tCostCenterType.HFM_CostCenterType, tCostCenterType.HFM_CostCenter FROM " & strWork & _
                                      " LEFT JOIN tCostCenterType ON " & strWork & ".UD1X = tCostCenterType.HFM_CostCenter" & _
                                      " WHERE ((" & strWork & ".PartitionKey = '" & RES.PlngLocKey & "' AND " & _
                                      strWork & ".PeriodKey = '" & RES.PdtePerKey & "' AND " & _
                                      strWork & ".CatKey = '" & RES.PlngCatKey & "'))"
                                      
                  'Get recordset matching account criteria
                  Set rsSQL = API.DataWindow.DataAccess.farsKeySet(strSQL)     

                  With rsSQL
                       If Not .EOF And Not .Bof Then
                            Do While Not .Eof
                  strCostCenter = Trim(.Fields("HFM_CostCenterType").Value)
                                 strCurrAccount = Trim(.Fields("AccountX").Value)
                                 If .Fields("HFM_CostCenterType").Value <> "" Then
                                      .Fields("AccountX").Value = strCurrAccount & StrCostCenter
                                 .Update
                                 Else .Fields("AccountX").Value = strCurrAccount
                  .Update
                            End If     
                                 
                                 .MoveNext
                            Loop
                       End If
                  End With

                  'Destroy the recordset object
                  Set rsSQL = Nothing
                       

                  End Function
                  ________________________________________________________________________

                  Edited by: user8714121 on Jul 2, 2012 4:39 PM
                  • 6. Re: API error in import script
                    beyerch2
                    Somethings about your code:

                    #1 - I think what you're trying to do is update ALL of the records at once, which would be most efficient?
                    #2 - I do not see anything that is creating the table 'tCostCenterType' ?
                    #3 - The update may fail because your query is joining two tables?? Not 100% sure if that would work, especially since I can't figure out where the one table comes from
                    #4 -

                    I might be over simplifying below, but I think that is what you want.

                    NOTE : Performance could be an issue with what I did below due to the querying against datamap table, though you could just do one select distinct to get all UD5 Src / Targ combinations, load it to a local array, and perform a search in the script.
                    Function CCRule(strField, strRecord)
                    
                    'Declare working variables
                    Dim strCC
                    Dim strSQL
                    Dim rsSQL
                    Dim strUD5Targ
                    
                    'Assign values
                    strCC = (DW.Utilities.fParseString(strRecord,8,3,","))
                    
                    if trim(strCC) < > "" then 
                    
                       'Query Data Map table for given location ("partition"), Dimension, and SrcKey
                       'NOTE: This assumes EXPLICIT mapping is happening.  If there is some type of 'LIKE' or 'BETWEEN'
                       ' more thought required here.
                       strSQL = "SELECT TargKey from tDataMap where PartitionKey = " & RES.PlngLocKey & " and DimName = 'UD5' and SrcKey = '" & strCC & "'"
                    
                       'Get recordset matching account criteria
                       set rsSQL = DW.DataAccess.farsFireHose(strSQL) 
                    
                       'Initialize Target UD5
                       strUD5Targ = ""
                    
                       'First record in recordset should be result we want since we limited with WHERE clause in query
                       'NOTE: in ADO.NET we can do a ExecuteScalar call which only returns ONE result which would be perfect.
                       'Would limit overhead of recordset, etc, etc, etc.......
                       'NOTE : This *IS NOT* efficient for large amounts of imported rows as we're executing MANY queries here...
                       '   If a lot of rows, perhaps a better idea is to read *ONCE* all of the distinct Src / Target UD5 values into an 
                       '   in-memory array and perform a search.....
                    
                       With rsSQL
                       If Not .EOF And Not .Bof Then
                         strUD5Targ = trim(.Fields("TargKey"))
                       end if 
                    
                       'Release memory
                       Set rsSQL = Nothing
                    
                       if strUD5Targ = "" then CCRule = strField else CCRule = strField & "_" & strUD5Targ
                    
                    else
                       CCRule = strField 
                    end if 
                    
                    End Function
                    Edited by: beyerch2 on Jul 2, 2012 5:33 PM

                    Edited by: beyerch2 on Jul 2, 2012 5:34 PM

                    Edited by: beyerch2 on Jul 2, 2012 5:35 PM
                    • 7. Re: API error in import script
                      SH_INT
                      Problem could be with this line

                      Else .Fields("AccountX").Value = strCurrAccount

                      try splitting over 2 lines

                      Else
                      .Fields("AccountX").Value = strCurrAccount
                      • 8. Re: API error in import script
                        user8714121
                        The provided script worked like a charm. Thank you very much for your help with this!
                        • 9. Re: API error in import script
                          user8714121
                          Thank you for your help with this. This is SIGNIFICANTLY better than the path i was going down.
                          • 10. Re: API error in import script
                            beyerch2
                            the only thing I would note is to watch the performance of that. There are ways to make it better if it 'sucks'.

                            Also, if it did answer your problem feel free to assign points. If I get to 1,000 points I get a free slinky or something. :-)