Have you ever found yourself in a position where you added a completely new alias or needed to add a user-defined attribute (UDA) to almost all of your account members? What happens if you're working with an Oracle Enterprise Performance Management Architect (EPMA) managed application that does not have integrations to interface tables or a good offline file to update that metadata?
Fear not, for there are a few nifty tricks for creating your own offline metadata management file and a way to easily update a number of properties quickly and easily on Oracle EPMA with few outside tools.
Getting started with Oracle EPMA
To start, you need to extract a current copy of your outline. Some of you might remember the old online analytical processing, or OLAP, underground tool that allowed you to extract your outline. Oracle EPMA has a similar tool that allows us to generate an ADS file-- it's called the Hyperion EPMA Architect File Generator. The tool allows you to generate pristine ADS files based on your current dimensionality in Oracle EPMA, which means you don't even need to deploy the version you wish to extract.
You can do the extraction in four simple steps:
1. Log into the client server.
2. Open the EPMA File Generator tool.
3. Navigate to the tab titled From EPMA Application.
4. Save a copy of the ADS file and move it to your local machine.
Beyond the ADS file header, we have the dimensions and the associated properties of the application. By default, this tool provides you with all the dimensions present in Oracle EPMA.
The second part defines the associations. Typically, association updates can be made directly in Oracle EPMA very easily, so my recommendation is to not make large updates to this section.
The remaining sections are devoted to the properties and members of your dimensions. You have a header that defines all the properties that are applicable to your dimensions. Note that this will be different depending on the dimension that you select -- there are different properties associated with an account dimension versus a generic dimension.
OK, enough background, let's get to updating!
Batch updates to Oracle EPMA metadata
Doing the batch updates is fairly straightforward.
1. Open Excel and import the ADS file (when you import, make sure you specify pipe | delimited)
2. Isolate the dimensions you want to change, and copy that section in Excel to a new tab.
Now here comes the easy part: Make your changes. Once you've made your changes in Excel, save the tab you created to a comma separated values (CSV) format. Open the CSV in Notepad and simply run a find and replace on all comma values and replace them with pipe delimited values.
Now, take the flat file you created back into your original ADS. If you made changes to only one dimension, you can prune the ADS file so that you are importing only the dimension that you changed. Prune the header to include only the dimension that you import.
Log back into Oracle EPMA and create an import profile. Then complete your import by navigating back to the Library Job Console to see if any errors came up.
A few helpful hints
Forgot to add a UDA? Don't worry, doing this is as simple as adding that UDA to the UDA column.
What happens if you have commas in your ADS file already? I typically replace the comma values with a character that is not used, say a number sign. When you export to CSV format, do your normal replace function and then replace the number sign with a comma.
Some other common issues are adding a duplicate alias, appending a new alias or adding a large number of members.
For simple, large updates that need to be made quickly in a pinch, the EPMA Architect File Generator is a powerful tool to help you manage your native Oracle EPMA metadata.
About the author:
Nicholas Blazosky is a senior consultant at TopDown Consulting Inc., a Hyperion implementation provider and Oracle partner based in San Francisco. Nick is an Oracle Hyperion Planning 11 Certified Implementation Specialist who also specializes in Essbase.