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:
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.
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:
Other sections of the .app file — such as Application Settings, Currencies, Consolidation Methods, and Celltext Labels — are also imported:
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.
A file selection dialog will appear.
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.)
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.
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.
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:
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.
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.
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.
- 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.
- 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.
