Rules and Metadata Version in HFM

HFM Audit

Let me ask you a few questions:
• How do you know what metadata file has been loaded into your Production system?
• How do you know it’s the same file you loaded into QA?
• How do you know the Rules file is the correct one for your metadata?
• What Rules file was used to calculate a particular subcube?

You probably already have procedures in place for making sure that all of the above can be checked. The point is there is nothing in the application that actually gives you that information. In this blog I want to talk about a way of storing the Rules and Metadata versions in the application itself. Here are some of the benefits. You can:
• Check that the correct Rules/Metadata files have been loaded.
• See if the Rules and Metadata versions are in sync.
• Ensure that the same Rules/Metadata is loaded in different environments
• See when the Rules/Metadata changed

Have a look at the screenshot below. There are two accounts; one captures the Rules Version and the other the Metadata Version. The format of the data is VersionNumber.DDMMYY and they must be the same for the Rules and Metadata. You can see that in March there is a difference between the Rules and Metadata:

You can format the version number however you like; the only restriction is it must be a number of some sort. My preferred format is VersionNumber.DDMMYY (as above). You could have it so that the Version Number and the date have to be the same or the Version Number can be different but the date must be the same. Either way it’s a very easy check to make sure that the Rules and Metadata are in step. This, of course, is in a single application and when you load the Rules and Metadata files into a different application or environment, the values should be the same.

And here is how you do it.

Metadata

In the Metadata, create two accounts to hold the Rules and Metadata Versions. You could also create one account and have a custom analysis. Set the Account type to Balance, IsCalculated to Yes and the number of decimal places to 6. For the Metadata Version account, add the VersionNumber.DDMMYY to a UserDefined field (in our case UD1). If you are using phased submissions, set the Submission Group to 0 (you could have a separate member for each phase if you wanted to but I think that’s a bit extreme).

Rules

In the Rules file, create a constant at the top of the file so that it’s easy to find and set it to the value of the Rules Version e.g.:

Const cRULES_VERSION = "23.071214"

And in Sub Calculate set the Rules Account to be equal to the constant:

HS.Exp "A#Rules = " & cRULES_VERSION

Set the Metadata Account to be equal to the value in its UD1 field:

HS.Exp "A#Metadata = " & HS.Account.UD1("Metadata")

You might want to review what members of the Value dimension this executes for. I think Entity Currency and Entity Curr Adjs is enough.

Now whenever you calculate, the data is stamped with the Rules and Metadata versions. I have also implemented for some clients a date and time stamp for the Calculation of Rules which is again quite simple.

Conclusion

I know this involves manual steps because you have to maintain the version numbers in the Rules and the Metadata but it’s not going to take more than 5 minutes once it’s set up. It’s of benefit whether you have Classic or EPMA or a mixture because you can easily check that two applications have the same Rules and Metadata. And it keeps auditors happy because they can see that there is a means to check the migration of the application from Development through to Production.

OnDemand Rules in HFM

In Version 11.1.2.2 OnDemand Rules were introduced. This is the ability to execute a ‘Calculate’ type Rules procedure from a web form. They are documented in the Administrator Guide for V11.1.2.3:

On-demand rules are used in Data Forms, and enable you to run a subset of calculations to quickly see the results in the data form. All HS functions that can be used in Sub Calculate (but no others) can also be used in OnDemand rules.

This means that you can run a section of the Rules file without running the complete calculation. This feature comes from Planning where calculations bring in data from, for example, Workforce Planning and I have struggled to see how it could be of any use for HFM. In Planning you are dealing with discrete blocks of data held in different plan types while HFM has just the one Chart of Accounts. The calculation time for a single entity in HFM is usually less than a minute so the benefit of being able to calculate just a subset when there are dependencies on the data is doubtful. Oracle’s QMR and Tax Provision applications both use OnDemand Rules but you need to design the application carefully if you are going to use OnDemand Rules for copying data. If the OnDemand Rules are not called by the Calculate routine, any intersections that are updated must not be calculated because they will be cleared when Calculate is next executed. Admin Guide again:

Also note that unlike Sub Calculate, data previously written to an IsCalculated data-point is not cleared when an On-demand rule is run.

There is no point in changing calculated data in OnDemand Rules because it will be overwritten when Calculate is executed. If you change the data in the subcube when executing OnDemand Rules, the calculation status changes. So this leaves us with either changing input data or running a process that does not actually change the data such as extracting data. You could use OnDemand Rules for creating a starting point for Budget by copying in data from a previous period or year. If you use SmartView for data input, a common problem is that zeroes are saved as input data. It occurred to me that the OnDemand Rules could be used for administration tasks such as removing input zeroes. A simple OnDemand Rule can be used to clear intersections where the data value is zero:

Sub OnDemand_Clear()
Dim objDataUnit
Dim numItems, numItemCounter
Dim strAcc, strC1, strC2, strC3, strC4, strICP, numData

'Open a dataunit for all the data in the subcube
Set objDataUnit = HS.OpenDataUnit("")
'get the number of records in the dataunit
numItems = objDataUnit.GetNumItems()
'Read through each record
For numItemCounter = 0 To numItems - 1
'Get the data value for the record
numData = objDataUnit.Item(numItemCounter).Data
'If the data value is zero
If numData = 0 Then
'Get the member information
strAcc = objDataUnit.Item(numItemCounter).Account
strICP = objDataUnit.Item(numItemCounter).ICP
strC1 = objDataUnit.Item(numItemCounter).Custom("C1")
strC2 = objDataUnit.Item(numItemCounter).Custom("C2")
strC3 = objDataUnit.Item(numItemCounter).Custom("C3")
strC4 = objDataUnit.Item(numItemCounter).Custom("C4")

'Clear the intersection
HS.Clear "A#" & strAcc & ".I#" & strICP & ".C1#" & strC1 & ".C2#" & strC2 & ".C3#" & strC3 & ".C4#" & strC4
End If
Next

End Sub

Notice the name of the procedure begins with OnDemand_. You can choose whether to call these OnDemand procedures from the Calculate procedure or not.

Now you can add this OnDemand Rule to a web form:

You can either type in the OnDemand Rule name or click on the selection box.

Notice you can have more than one OnDemand Rule on the same web form.

Now when you open the web form you will get an extra icon on the toolbar in the Calculate section:

And it is also available from the right-click menu:

OnDemand Rules are not of much value as a means of executing a small portion of the Rules file. However, they can add value if they are used for occasional admin or data tasks.