Many years ago I developed a solution in Excel to assist administrators with the management of metadata changes and the subsequent creation of APP and ADS files for HFM, Essbase and Planning. This later became known as the ‘PwC Accelerator’ (it’s still being used by clients – someone contacted me about it just a few months ago). The premise for writing this interface was around the business need to support the development and continual evolution of metadata. This approach was much quicker and more transparent to present in Excel. The explanation and constant iterative review of the metadata to the client was immeasurably easier and more understandable.
Due to a number of clients adopting this approach, it has evolved over time to meet the changing requirements of the administrative community or the specific requirements of a client, adding custom functionality based on their requirements. Consequently, I consider myself something of a connoisseur of Excel front ends to metadata files and to say I was disappointed with the OneStream Metadata Builder on the OneStream Marketplace is putting a positive spin on it. So, as I have had a bit of spare time recently, I decided to write my own OneStream metadata manager and I leave it to you to decide which of the two you prefer.
I have written two accelerators:
- OneStream Metadata Manager
- OneStream Maps Manager (for the Transformation Rules)
This blog is about the first: OneStream Metadata Manager. I will do a separate blog on the second: OneStream Maps Manager.
Why do I need an Accelerator?
This is usually the first question from most OneStream consultants. The short answer is you don’t. It’s only a front-end to an XML file after all and you can do everything in the OneStream software anyway. This is not about highlighting a perceived deficiency in OneStream (apart from Metadata Builder of course); it’s about another way of doing it. It also has the benefit that consultants and/or administrators can easily demonstrate changes and distribute shared revisions.
At some point during the project the application and metadata revisions will be handed over to the client. For most finance users Excel is flexible, familiar and fast. Would your clients think the ability to manage, review and annotate their metadata in Excel was good or bad?
How does it ‘Accelerate’?
OneStream Metadata Manager imports and exports files for one or more dimensions. It is a bunch of Excel macros launched from a custom ribbon (OSMacros):
You now have the flexibility and connectivity of Excel for managing your OneStream metadata.
The Export process generates the OneStream XML for the member and relationship sections without any Excel formulas. There is error checking (invalid characters, missing parents, consistent descriptions etc) and the export XML can be set up to always give you a unique filename.
Brief Overview
This is an example of how it works (the screenshot shows an import of the Golfstream application). The hierarchy is managed on the left and the member properties on the right. The Parent / Child relationship is specified by indenting the child member under the parent:
Then you just make changes to the hierarchy and/or properties. When you’re ready, click Export and select the dimensions to include in the XML file. Members can be flagged as Extended, Remove, Delete or Ignore. The Ignore flag means that a member will not be included in the export file. This is useful for example when metadata has been imported from the existing application and dimension members are being moved (e.g. annotate an account with ‘Moved to UD4’ and add the Ignore flag).
Files in the following formats can be imported:
- OneStream XML
- HFM APP
- Generic hierarchy
One or more dimensions can be imported by dimension or by dimension type. If the client has their metadata in a text file or database, Excel can easily import the member information. The ‘Import | Generic hierarchy’ option imports files where the parent and child members are somewhere on the same line and then builds a hierarchy.
Sheets can be organised and colour-coded. Automatically sort and change the tab colour by dimension type (double-click the tab colour cell for a dialogue to select the colour):
There are functions for easily navigating through the sheets. Hyperlink Index of sheets:
Custom bookmarks to allow quick switching between cells/sheets:
Conclusion
I wouldn’t say it’s for everyone. But I’m sure some of you will find it useful. This is just a brief description of some of the functionality; there is a lot more.
If you want to have a look at the manual it is here
If you want to download the Excel workbook, that is here (link to OneDrive)
For the moment this is free; use it and distribute it as you wish. There is no warranty and no guarantees.
Hemy's Extras for EPM 