Poor Man’s DRM: EPMA Batch Automation

Nearly 2 years ago, I upgraded a client’s system from EPM 11.1.2.1 to 11.1.2.4.  This client had some very clever automation that used the EPMA Batch Client and wrapper code to read records from a relational database and pump metadata updates into EPMA.

At that time, I stumbled upon a problem with the EPMA Batch Client, and logged an Oracle SR about it.  Oracle issued a bug # for it (25113781).  On August 13, 2018, nearly 2 years later (I logged the SR in June 2016), Oracle published this knowledge base article:

Enterprise Performance Management Architect (EPMA) Batch Client Error Handling Incorrect in 11.1.2.2 and higher (Doc ID 2433106.1) 

The article confirms the behavior I noticed:

In EPMA Batch Client 11.1.2.1, if the parent/child relationship you are trying to add already exists within EPMA, the batch client just prints an error message and then moves on to the next command within your batch script.

In EPMA Batch Client 11.1.2.2 through 11.1.2.4, if the parent/child relationship you are trying to add already exists within EPMA, the batch client prints a Java stack trace message and then immediately aborts;  the subsequent commands within your batch script never get executed.

Unfortunately, the KB article states two things:

There are no plans to fix the bug in 11.1.2.4.

If the bug is a problem for you, you should use EPMA Batch Client 11.1.2.1 instead.

I disagree with Oracle on the 2nd point.  When I regression tested, I discovered inconsistencies when attempting to use EPMA Batch Client 11.2.1. against an EPMA 11.1.2.4 dimension server.  Some EPMA commands worked, and others did not.  I had to abandon the 11.1.2.1 client and stick with 11.1.2.4.

If you’re in the same boat and have hit the same bug, here is the fix!

While reading your relational database or input file for parent/child members to insert into EPMA (because you either don’t own a license for Oracle DRM or choose not to use it for this purpose), you need to run these 3 SQL queries against your EPMA database before you add the CREATE MEMBER command to your EPMA batch command script.

1. Check if the ‘thechild‘ already exists within EPMA in ‘thedimension‘.

SELECT i_member_id FROM ds_member
WHERE i_library_id = 1
AND UPPER(c_member_name) = UPPER(‘thechild‘)
AND i_dimension_id =
(SELECT i_dimension_id FROM ds_dimension WHERE i_library_id = 1 AND UPPER(c_dimension_name) = UPPER(‘thedimension‘);

2. For each i_member_id returned by the above query, check if ‘theparent‘ exists.

SELECT i_member_id FROM ds_member
WHERE i_library_id = 1
AND UPPER(c_member_name) = UPPER(‘theparent‘)
AND i_dimension_id =
(SELECT i_dimension_id FROM ds_dimension WHERE i_library_id = 1 AND UPPER(c_dimension_name) = ‘thedimension‘;

3. Finally, for each result returned by query #2, check if there is a relationship with the i_member_id from query #1.

SELECT count(1) AS FOUNDREC
FROM ds_relationship
WHERE i_library_id = 1
AND i_dimension_id =
(SELECT i_dimension_id FROM ds_dimension WHERE i_library_id = 1 AND UPPER(c_dimension_name) = ‘thedimension‘)
AND i_child_member_id = childidfromquery1
AND i_parent_member_id = parentidfromquery2;

A non-zero result means the relationship already exists and you should avoid adding the CREATE MEMBER command to your EPMA batch script for this parent/child combination.

Does that make sense? Post your comments and questions below.