Forum Stats

  • 3,757,494 Users
  • 2,251,236 Discussions
  • 7,869,844 Comments

Discussions

overwriting an ICP Target

786797
786797 Member Posts: 83
edited Jun 9, 2014 4:12AM in Financial Data Management

Hi,

i need to overwrite Target ICP based on the HFM account ICP attribute.

While i could do a manual explicit mapping, this is not very handy nor dynamic.

I was thinking about creating a AftValidate script to overwrite the Target ICP.

Now i know how to retrieve the IsICP attribute from HFM and compare it in a loop to the ACCOUNTX in FDM, but what is the right way to overwrite a single value in an existing row?

i know how to add new records but i do not know how to update existing ones...so i thought something like the following could solve the issue, just i do not know the right syntax...

Set NewRec = DW.DBTools.fCreateRec()

  With NewRec

      .varKey = z

      .colFlds.update "ICP", ICP

  End With

  RES.PColRecs.Append NewRec

  End If

regards

Benoit

Tagged:

Answers

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown

    Hi,

    I would suggest building a SQL update statement for that.

    I would do it after mappings happen or just before data is going to be posted to TDATASEG (main table), so in the ImportAction script (subevent PostWorkToMainProcess).

  • 786797
    786797 Member Posts: 83

    Hi Francisco,

    that is the route i finally took before reading your post...but

    if i put the script in import action i get an access denied...anywhere else, the changes do not happen.

    i have to add that the user i am using is the admin for hfm and fdm and both are on the same server, in the same Oracle DB.

    maybe someone can tell me what is wrong with the script below

    '------------------------------------------------------------------

    'Created By: benoit rochegude

    'Date Created: 30/04/2014

    'Purpose: replace ICP if account in HFM is not ICP

    '------------------------------------------------------------------

    Dim cnSS 'ADO connection Object

    Dim strSQL 'SQL String

    Dim rs 'Source system recordset

    Dim rsAppend 'Hyperion FDM recordset

    Dim SQL_ICP

    Dim Sql_HFM

    Dim Sql_FDM

    Dim rssql_ICP

    Dim rssql_HFM

    Dim rssql_FDM

    Dim rssql_FDM_UPD

    Dim Sql_FDM_UPD

    Dim PerKey 'Period key

    Dim CatKey 'Category key

    Dim LocKey 'Location Key

    Dim SegKey 'Segment Key

    Dim WorkTable

    Dim HFMAcc

    Dim FDMAcc

    Dim FDMICP

    Dim HFM_ISICP

    LocKey= RES.PlngLocKey 'Location Key    

    SegKey = RES.PlngSegKey 'API.DataWindow.Reports.PlngSegKey 'Segment key

    WorkTable = "TdataSeg" & SegKey

    PerKey = CDate(RES.PdtePerKey) 'Period Key

    CatKey = RES.PlngCatKey 'Category Key

    Set cnSS = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")

    '******** Connect to HFM Database View  ********

    cnss.open "Provider=OraOLEDB.Oracle.1;Data Source= xxxx;User ID= xxx;Password=xxx"

    If StrEventName = "PostWorkToMainProcess" Then

    'If lngProcState = 12 Then

      mMon = Month(dblPerKey)

      mYear = Year(dblPerKey)

      PrevMon =Month(DateAdd("m", 0, Month(dblPerKey)))

      'Manual Date Selection

      'mMon = 9

      'mYear = 2011

      

      If mMon <= 9 Then

      mMon = "0" & mMon

      End If

      SQL_FDM = "SELECT ACCOUNT,ACCOUNTX,ICPX,PERIODKEY FROM " & WorkTable &" where to_char(PERIODKEY, 'mm')=" & mMon &" and to_char(PERIODKEY, 'YYYY')= " & mYear

      Set rssql_FDM = DW.DataAccess.farsFireHose(SQL_FDM)

      If Not rssql_FDM.BOF And Not rssql_FDM.EOF Then

      Do Until rssql_FDM.EOF

      HFMAcc = rssql_FDM.Fields("ACCOUNTX").Value

      FDMAcc = rssql_FDM.Fields("ACCOUNT").Value

      FDMICP= rssql_FDM.Fields("ICPX").Value

      '---- Insert Below the connection to HFM table

      SQL_HFM = "SELECT IsICP FROM EPM_HFM.xxx_ACCOUNT_ITEM WHERE LABEL = " & HFMAcc

      rs.Open SQL_HFM, cnss

      If Not rs.bof And Not rs.eof Then

      Do Until rs.eof

      '******** HFM Account Properties ********

      HFM_ISICP = rs.Fields("ISICP").Value

      '********** Find ICP ********

      If HFM_ISICP = "-1" Then

      Set rssql_FDM = DW.DataAccess.farsFireHose(SQL_ICP)

      If Not rssql_ICP.BOF And Not rssql_ICP.EOF Then

      HFMICP = rssql_ICP.Fields("ICPX").Value

      End If

      Else

      HFMICP = "[ICP None]"

      End If

      '********** Update Records to Database ********

      SQL_FDM_UPD = "UPDATE " & WorkTable & " SET ICPX = " & HFMICP & " where to_char(PERIODKEY, 'mm')=" & mMon &" and to_char(PERIODKEY, 'YYYY')= " & mYear

      Set rssql_FDM_UPD = DW.DataAccess.farsFireHose(SQL_FDM_UPD)

      Loop

      ' .close

      End If

      Loop

      End If

      '.close

      End If

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown

    Hi,

    couple of things:

    - If you build it in ImportAction Script, you cannot update TDATASEG table as this table does not have data yet. Data is still in the worktable. Therefore you will have to update the worktable (RES.pstrworktable)

    - As you are going to get ICP information from SQL query, I would suggest that you create a synonym in your FDM database to the table you are quering from HFM database.

    If you do this, you don't need to open any connection to external database and you can have a better performance UPDATE SQL Statement by using JOIN.

    Regards

  • 786797
    786797 Member Posts: 83

    ok fair comments.

    i was trying to do that first in AftValidate but i didnt get any results.

    i was also trying to avoid creating a view in the FDM DB, but this is not that dramatic if it works.

    other than this, the script should be ok right?

    regards

    Benoit

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown

    Run it :-)

    best way of doing is writing your results to debug txt file so you are sure about what is happening there.

    You don't have to create a view in FDM. What I was saying is that if FDM thinks your HFM data is in the FDM db you don't need to open a connection to HFM db to query the view. You can do it by creating synonym in FDM db to view you created in HFM db.

    Then you avoid going through each target account and running the view... you can just have one UPDATE which uses join of your FDM worktable and your "Synonym" view.

  • 786797
    786797 Member Posts: 83

    I think the right approach is to use the AftProcMap.

    no i am unsure how to use the StrDim of that event

    i need to play with AccountX and ICPX so would the following be correct?

    i guess StrDIM can be any dimension from the Workingtable so how to you say something like

    if StrAcctISCP=  clsHFMadapter.fIsAccountICP(ACCOUNTX) = 0 then

    tblWrkName= API.State.GstrActiveWorkTableName

    strSQL = "Update " & tblWrkName & " Set ICPX = '[ICP NONE]' where ICPX <> '[ICP NONE]' AND"

    strSQL = strSQL & " CATKEY = " & RES.PlngCatKey &" AND PARTITIONKEY = " & RES.PlngLocKey & " AND"

    strSQL = strSQL & " PERIODKEY = '" & API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod).strDateKey

    end if

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown

    In FDM, you can build your custom process in different places depending on your requirement.

    AftProcMap is executed after any dimension is mapped so you need to build your code for strDIM = "ICP" (assuming ICP is mapped after ACCOUNT)

    if clsHFMadapter.fIsAccountICP(ACCOUNTX) = 0 then

    ...


    as you are working with the worktable, you would not need to use keys in your SQL statement (CATKEY, PARTITIONKEY...) Data in the worktable will be for your current POV so no need to filter.

    Francisco Amores
  • 786797
    786797 Member Posts: 83

    Hi Francisco,

    i may try that in the future, but as of now, i got it to work with the importAction.

    thanks

    B.

This discussion has been closed.