Forum Stats

  • 3,827,511 Users
  • 2,260,787 Discussions
  • 7,897,282 Comments

Discussions

how to clear zero values from HFM database.

LN
LN Member Posts: 8 Green Ribbon

Zero values were loaded in HFM (11.1.2.4), what is the best way to clear the zero values?

If clear the zero values by rule, that will be run every iteration when we hit the consolidation or calculation. Hence, Is there any impact to hfm database if rule runs frequently?

Note: in our env, dataloads are happening via ODI, no FDMEE and .dat file does not have zero value intersection. Zero values are being punched by business.

Tagged:

Answers

  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge

    Ultimately you have three possible solutions here.

    1) Load a .dat file with #NoData to the same intersections. That will clear the 0's out

    2) Build an adhoc grid and press the Delete key(or type #Missing) to clear the data.

    2) Create a rule that runs on calc. Personally, this is the approach I used since we had several users who were loading something like 80k rows of 0's for about 200 lines of data. I added a rule that deletes the data if abs(value) < 0.001 so that it also got fractional pennies.

    Yes, it runs every calc, but you can restrict it a bit so that it only runs on Entity Currency of the Base entities. Conceptually, there's no point deleting 0's at upper level entities and I would never delete a value from a Journal Entry(done it in the past, results are a mess)

  • LN
    LN Member Posts: 8 Green Ribbon

    Hi Jeo123,

    Thanks for your reply. Just wanted to double check on clearing the zero value via rule. Means there is no impact to hfm database and performance issues.

    Thanks

  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge

    Any calculation has "some" impact to performance and any change to data always impacts the database.

    In my experience, the impact is negligible, but I would encourage you to try it in a dev or test environment first to see how it impacts your application in particular.

    If it helps, here's the routine I built. I have a member list that I use because I don't want to clear things like allocations or exchange rates but you could just run this for all base accounts if that's not an issue for your application. The basic concept is open the data unit for the current entity as long as it's a base entity and then cycle through the data unit checking each intersection.

    Overall, the impact is pretty negligible especially compared to the impact we were seeing from having thousands of unnecessary 0's. But again, test this in your own application to determine the impact.

    If you find it too slow to run every time, you could also do something like creating a flag account where this only runs if you enable it by putting a 1 in the account, but again, that comes down to your preferences.


    Sub DataCheck()

    If pov_base and pov_entity <> "[None]" and pov_entity <> "FLAGS" and pov_value = "<Entity Currency>" Then

    Set DataUnit = HS.OpenDataunit("A{FinancialAccounts}")

    lNumItems = DataUnit.GetNumItems

    For i = 0 To lNumItems - 1

    Call DataUnit.GetItem(i, sAccount, sICP, sCustom1, sCustom2, sCustom3, sCustom4, dData)

    If (dData < 0.01 and dData > -0.01) Then ' and (HS.Account.IsBase("IncomeStatement",sAccount) or HS.Account.IsBase("BalanceSheet",sAccount) or HS.Account.IsBase("MfgCosts",sAccount)) Then

    sPOV = "A#" & sAccount & ".I#" & sICP & ".C1#" & sCustom1 & ".C2#" & sCustom2 & ".C3#" & sCustom3 & ".C4#" & sCustom4

    HS.Clear sPOV

    End If

    Next

    End If

    End Sub

  • LN
    LN Member Posts: 8 Green Ribbon

    Thanks for your information and rule. Can I know what were impacts happened to HFM database you had seen when it was implemented. Can you please share more insight on the database impacted stuff.

  • LN
    LN Member Posts: 8 Green Ribbon

    Sorry to bother you. Can you pls suggest which log do I refer to verify the calculation or consolidation timings.

  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge

    Every time you change a data point in the application there's an impact to the database. I can't speak to how doing this will impact your particular application.

    As for consolidation times, you can check task audit, but in general every time you run a consolidation it tells you when it starts and stops. Time it before the change, make the change, then time it after.

  • LN
    LN Member Posts: 8 Green Ribbon

    Thanks for your inputs

  • LN
    LN Member Posts: 8 Green Ribbon

    Hi Jeo123,

    The provided Sub DataCheck subroutine rule can be included in existed rules, and run the calculation / consolidation for every iteration or only one time run of the Sub Datacheck subroutine with out existed rules.

    Thanks in adv