Although Hyperion Financial Management (HFM) has a screen for clearing data, I have always preferred the option of clearing data with a file. For those not familiar with the technique, it is simply loading a data file with the data fields for each record replaced with the string 'NODATA'. When loaded using the Merge method, the file clears the target data without leaving behind hard zeros.
In this blog post, you'll learn how to use this alternative — dare I say, "better" — method for working to clear data in HFM.
The biggest advantage of clearing data with a file is that it allows you to surgically remove individual data points, right down to specifying each custom dimension member. The Clear Data screen, on the other hand, is limited to clearing all records for a Scenario, Year, Period, Entity and Account combination.
This method also scales well to large data-clearing operations and will produce the same results as the Data Clear screen. I frequently use it during development to ensure everything has been removed from a target scenario before loading new data.
Finally, I like that clearing with a file allows me to verify ahead of time the records being cleared and (not infrequently) reload exactly what was removed if results are not as expected.
Creating a NODATA File
The easiest way to create a NODATA file of more than a few lines is with a regular expression. A regular expression is an advanced pattern matching function commonly used in programming, but also supported for Find and Replace operations in most text editors. The example below is for Notepad++, but the steps will be similar for other editors working with Textpad or UltraEdit.
Starting with a data file of records you want to clear:
1. Open the data file in the text editor.
2. Open the Replace dialog (Search --> Replace or Ctrl-H in Notepad++.)
3. In the Find field enter the following:
;[\d \.\,\-eE]+$
4. In the Replace field enter the following:
;NODATA
5. Change the Search Mode to Regular Expression
6. Select Replace All
The data field for all records in the file will be replaced with the text string 'NODATA':
Before:
After:
7. Save As to preserve the original file. (You were going to do that anyway, right?)
8. Load to HFM using the Merge method.
What the Regex Does
The regular expression matches from right to left, so is not affected by the number of columns in the data file. The regex will replace the entire string after the rightmost semicolon on each line with the text 'NODATA' if the string after the semicolon is composed only of the following characters:
- Any number (0 to 9)
- A space (' ')
- A decimal point ('.')
- A comma (',')
- A minus sign ('-')
- The letter 'e' (uppercase or lowercase)
The 'e' is included on the off chance that some of the data values are in scientific notation.
To learn more about HFM 11.1.2.4, watch the webinar replay...
