us-analytics.png
Ask an Expert

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.

What's New in HFM 11.1.2.4? Watch the Overview and Demo


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

02.jpg

6. Select Replace All

The data field for all records in the file will be replaced with the text string 'NODATA':

Before:
04.jpg

After:
05.jpg

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...

HFM-11.1.2.4

Ask an EPM/BI Advisor

If you're here, you've got questions — and we've got answers. Book your consultation to ask us about any range of topics, including:

  • Evaluating EPM or BI technologies
  • Comparing on-prem vs. cloud
  • Planning upgrades and migrations
  • Estimating project costs and timeframes
  • And much more — ask us anything!

Let our experts tackle your toughest questions for you.

Let's Talk