us-analytics.png
Ask an Expert

HFM Metadata Utility 2.0: New Query Function

The HFM Metadata Utility, a free utility that allows you to import HFM metadata files (.app format) into Excel and export edited content back into Excel, has been updated to allow users to run basic hierarchy queries. Download the latest version of the utility here

With the updated tool, you can now run five queries: Compare Base Members, Find Duplicates Under Parent, Find Shared Parents, List Shared Parent Members, and List Shared Base Members.

Running a query is simple. Enter your queries on the Query tab and click on the Run Queries button:

Intro_Queries2.jpg


Review the output on the Query Result tab.

Intro_Result.jpg

 

Below is an example of each query.


 

1. Compare Base Members

This query compares base members under two parents in the same hierarchy and returns a list of base members that are children of one parent, but not the other. The comparison is performed on both sides, so the result includes (1) all base members that are children of Parent1 but not Parent2 and also (2) base members that are children of Parent2 but not Parent1.

Sample query:

CompareBase_Query.jpg

 

Hierarchy:

CompareBase_Tree.jpg

 

Result:

CompareBase_Result.jpg

 


 

2. Find Duplicates Under Parent

This query finds members that appear twice under a parent. It finds both base and parent member duplicates. Enter '#root' in the Parent1 field to find all duplicates in the hierarchy.

Sample query:

FindDuplicate_Query.jpg

 

Hierarchy:

FindDuplicate_Tree.jpg

 

Result:

FindDuplicate_Result.jpg

 


 

3. Find Shared Parents

This query finds parent members in a hierarchy that are children of both parent members entered.

Sample query:

SharedParent_Query.jpg

 

Hierarchy:

 SharedParent_Tree.jpg

 

Result:

SharedParent_Result.jpg

 


 

4. List Shared Parent Members

This query lists all shared parent members (members that have more than one parent) in a hierarchy. Each member is followed by a sorted list of the parents under which it appears.

Sample query:

ListSharedParent_Query.jpg

 

Hierarchy:

ListSharedParent_Tree.jpg

 

Result:

ListSharedParent_Result.jpg

 


 

5. List Shared Base Members

This query lists all shared base members in a hierarchy. Each member is followed by a sorted list of the parents under which it appears. Note in this example that the children of Revenue appear twice in the hierarchy but are under the same parent in both cases. They are duplicate in the hierarchy but not shared.

Sample query:

ListSharedBase_Query.jpg

 

Hierarchy:

ListSharedBase_Tree.jpg

 

Result:

ListSharedBase_Result.jpg

 


 

Other Good Things To Know...

  • The Query Results tab is cleared when new metadata is loaded. Queries on the Query tab remain. Simply rerun the queries to see results for the new metadata.
  • The Query and Query Result tabs can be moved or deleted without affecting the rest of the utility.

 

Disclaimer

As before, 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.

 

Download the Updated Utility

Ready to run hierarchy queries?

Download the Tool: HFM Metadata Utility 2.0

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