us-analytics.png
Ask an Expert

HFM Metadata Utility: How to Edit .App Files in Excel

The Hyperion Financial Management (HFM) Metadata Utility is a free utility that allows you to import HFM metadata files (.app format) into Excel, where the content can be reviewed, edited, and exported back to a file for loading to HFM. The utility is lightweight and leverages the power of Excel for fast, easy reviews and edits. Download the HFM Metadata Utility here.

Operation
Once a metadata file is chosen for import, the utility creates a tab in Microsoft Excel for each section of the import file. It then writes the contents of that section to the tab. Sections for custom dimensions are dynamically added as well:

A39.jpg

In .app metadata files, hierarchies are divided into two sections:

(1)
a Hierarchy section, which defines the parent-child structure of the dimension
and
(2) a Member section, which defines member properties.

Below is an example of a Hierarchy section as imported by the utility (tab Entity H). Dimension members are displayed in a tree structure with each level in a separate column.

The visual structure of the hierarchy can be edited in Excel and saved back to an .app file for loading to HFM.

A01.jpg

Below is an example of a Member section (tab Entity M). Each member is listed once along with its properties. The members and properties can also be edited and saved:

A02.jpg

Other sections of the .app file — such as Application Settings, Currencies, Consolidation Methods, and Celltext Labels are also imported:

A04.jpg

Importing Files
Step 1: Extract an .app file from HFM.
Step 2: Open the utility in Excel. (Be sure to enable macros.)
Step 3: On the Main tab, select the Import/Export button.

A06.jpg

 A file selection dialog will appear.

A31.jpg

Step 4: On the first row, click on the File Selection button (square box with three ellipses) to select the import file. The full file path will be placed in the text box to the left. (You can also enter the import file path directly into the text box.)

A32.jpg

Step 5: Select the Import Type (see the Hierachy Import Options section below for more information) and click on the Import button. You will receive a confirmation message when the process is complete.

A33.jpg

Exporting Files
Click on the File Selection button (three ellipses) on the second row to select an export destination, then click on the Export button to create an .app file.

A41.jpg

 A35.jpg

Hierarchy Import Options
There are three Import Types for hierarchies:

  • Hierarchy
  • Hierarchy with Properties
  • Parent/Child

Hierarchy
Dimension members are displayed in a tree structure with each level in a separate column. Member names are listed for reference in red in the first column to the right of the hierarchy:

A01.jpg


Hierarchy w/ Properties
This option is the same as Hierarchy, except properties for each member are also displayed to the right of the hierarchy for reference. This format is great for review but takes much longer to import. The member properties (in red) are not editable on this tab. Changes are not saved. Member properties must still be edited on the Members tab.

A03.jpg


Parent/Child
This option imports the hierarchy in raw Parent/Child two-column format as it appears in the .app file. It's useful for certain types of mass edits.

A36.jpg

Tips

  • You must manually maintain the correspondence between the Hierarchy and Members tabs for each dimension. If you add or delete members, you must make the change on both tabs. Also, if you change a member's location in the hierarchy, remember to update its DefaultParent field.

  • On the Hierarchy tab, each new row should be indented by at most one column to the right of the row above. More than one indent may indicate an error or inconsistency. The utility checks for this during export and will generate an error message if this condition is found. You must correct the error before the file can be exported.

A37.jpg

 A38.jpg

  • As mentioned above, text in red on Hierarchy tabs is for reference. Changes are not saved. There is also no need to update this information when editing the hierarchy.
  • All sheets are deleted and recreated during each import. Highlighting and other information you place on a sheet is lost.

Other Good Things to Know

  • Metadata files from any HFM version that has configurable custom dimensions (11.1.2.2 and later) can be imported. (All are Metadata File Format 11.12). A separate utility for earlier HFM versions (11.1.2.1 and earlier)  with the standard four custom dimensions is also available here

  • The utility is self-contained in the Excel workbook. It is not an extension or add-in. It relies on an external metadata file and does not connect to HFM. You must enable Excel macros in the workbook to import/export files.

  • Both 32-bit and 64-bit Excel are supported.

  • Metadata file sections that are not part of a standard export are ignored during import. Examples include System Accounts, Value dimension members, and ICP members.

A40c.jpg

  • Files exported from the utility are much smaller than those exported directly from HFM. HFM files contain significant whitespace between fields. Files exported from this utility include no extra padding. The two files are logically equivalent and will produce the same result when loaded to HFM.

  • The code is Excel VBA. I've left the code modules open for inspection and revision. Feel free to review, extend, and improve the utility. If you make your own version, please credit the original source of the code. I'm also happy to incorporate improvements and suggestions from others.

Disclaimer
I've made every effort to ensure this tool is bug-free and works correctly. You use this utility at your own risk, however. Also, I will make good faith efforts to respond to questions, but the utility comes without support.

Free Utility: Start editing HFM metadata files in Excel!

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