Hyperion

Batch updates to Oracle EPMA metadata

Nicholas Blazosky, TopDown Consulting

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

    Requires Free Membership to View

(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.

This was first published in November 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Expert Discussion

Do you do batch updates with Oracle EPMA?

Nicholas Blazosky, TopDown Consulting
What's your opinion?
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.