OBIEE / Essbase 9.3.1: UDML for cube outline changes – Part 2

First of all a little disclaimer: No, I haven’t died in the meantime. Other topics took precedence over writing blog posts.

Back on track, I recently received a question from Jeff Tam who also works on a hybrid OBIEE-Essbase solution. Funny enough, jsut this morning there was another question on this on the OTN OBIEE forum here. Basically being a follow up on my post here botg JEff and the poster on OTN wanted to know the detailed steps which are to be taken to manually extend dimensional hierarchies of already imported Essbase outlines in the RPD. Oracle has their own MOS Note on this issue here.

“The workaround that the customer is using now is to copy this into a notepad, correct and paste it back.” is a bit too minimalistic for my taste, so I’ll take the concept in my previous post more into detail here.

We’ll start off with a 5 generation product hierarchy within an Essbase cube that’s already in the RPD.

In Essbase, the outline for the product hierarchy has grown from 5 levels to 6. In order to represent this, we repeat the steps outlined earlier and

a) create the new physical cube column “Gen6,Product”
b) add the new physical level “Gen6,Product” to the existing “Product” hierarchy object

Now that we have the structure built in the RPD, off to correcting the external level number (which isn’t possible through the Administration Tool!). For this we save the RPD and close the Admin Tool.

Open a command window and navigate to your serverbin folder. Then we run the UDML command line to create a txt representation of the RPD in question (paths and filenames need to be adjusted to your needs of course):

C:OracleOracleBIserverBin>nqudmlgen.exe -u Administrator -p Administrator -r “C:OracleOracleBIserverRepositoryShowcase_002.rpd” -o “C:OracleOracleBIserverRepositoryUDMLShowcase_002.txt” -8 -N

Open the resulting txt file in your trusty notepad and search for DECLARE PHYSICAL LEVEL “Sample Accounts”.”LOGIC_A”..”LOGIC”.”Product”.”Gen6,Product”

Modify the the “LEVEL NUMBER EXTERNAL” line to reference the correct external level. WARNING: counting starts at 0! So Gen6,Product is


Save the txt file. Switch back to you command window and run the nqudmlexec command to recreate the RPD from the modified definitions:

C:OracleOracleBIserverBin>nqudmlexec.exe -u Administrator -p Administrator -I “C:OracleOracleBIserverRepositoryUDMLShowcase_002.txt” -O “C:OracleOracleBIserverRepositoryShowcase_003.rpd” -8

After that you’re done and the dimensional level can now be accomodated normally in your BMM and presentation layers.


Leave a Comment