UDML

Uncategorized

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

LEVEL NUMBER 5 EXTERNAL “Gen6,Product”

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.

Cheers,
Christian

Uncategorized

UDML constantly keeps popping up in conversartions, questions I receive and – as can be seen from a quick query – on the OBIEE OTN forum. So before I go on with this post, a reminder: UDML is NOT supported as an rpd modification mechanism! Everything you do is at your own risk.

Right-o. I’d like to tie my post to the official OBIEE-Essbase modelling guide which can be found here.

Page 8, paragraph 4.1 “Subsequent Changes to the Essbase Outline” mentions the following:

“Cube structure changes (that is, adding or deleting dimensions, and levels) require either a re-import of the cube, or manual modification to the BI Server physical metadata objects to reflect changes.”

This is something that quite some people have contacted me about and honestly, I doubt that there’s really any case which justifies re-import of a cube if you know your way around the Admin Tool and UDML. Even though it’s an Essbase source! (I’ll stick to using the term “cube” to denominate Essbase “databases” for this post.)
One thing needs to be noted though. It’s a small thing, but it basically forces you to use UDML rather than manual modification through the Admin Tool.

Let’s start with a basic cube which I have already imported into my RPD while I was still developing on the Essbase side. So far – where OBIEE is concerned – it only consists several accounts which I can analyze by time.

On the Essbase side, the cube has grown somewhat and I’ve added my “Scenario” dimension.

To get this into OBIEE, I have two possibilities: re-import of the cube of manual creation of the dimension in the physical layer. Not wanting to lose my work on the BMM and Presentation layers, I choose the latter.

Right-clicking on the “Physical Cube Table” object, select “New Object” -> “Hierarchy”.

Then create two new “Physical Cube Columns” below the physical cube table:

Now I have the hierarchy and the two columns:

Next we create the actual hierarchy tree out of them. “New Physical Level”:

And add the column:

And the same thing for level 2 giving us this:

Now this structure is correct, usable and transformable into a corresponding business model:

However in the background there is one little thing going wrong which can give you headache in Answers…especially since tracking down the source of the weird errors this produces is a real pain. I have to admit it took me a while to figure it out.

Let’s take both the time and the scenario hierarchy and copy+paste them into a text editor.

Looking at the top level of the exported UDML, the two hierarchies are alike and don’t differ:

DECLARE HIERARCHY “Demo”.”Sample”..”CustomDemo”.”Time” AS “Time” UPGRADE ID 2161957949 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen1,Time”,
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen2,Time”,
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen3,Time”,
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen4,Time”,
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen5,Time”,
“Demo”.”Sample”..”CustomDemo”.”Time”.”Gen6,Time” ) MEMBER TYPE ALL EXTERNAL “Time”
FULLY BALANCED
BELONGS TO TIME DIMENSION
DIMENSION UNIQUE NAME “Time” TYPE 1
ALIASES NOT UNIQUE
PRIVILEGES ( READ);
DECLARE PHYSICAL LEVEL “Demo”.”Sample”..”CustomDemo”.”Time”.”Gen1,Time” AS “Gen1,Time” UPGRADE ID 2161959167 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Gen1,Time” )
KEY “Demo”.”Sample”..”CustomDemo”.”Gen1,Time”
LEVEL NUMBER 0 EXTERNAL “Gen1,Time”
PRIVILEGES ( READ);

DECLARE HIERARCHY “Demo”.”Sample”..”CustomDemo”.”Scenario” AS “Scenario” UPGRADE ID 2161960605 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Scenario”.”Gen1,Scenario”,
“Demo”.”Sample”..”CustomDemo”.”Scenario”.”Gen2,Scenario” ) MEMBER TYPE ALL EXTERNAL “Scenario”
FULLY BALANCED
DIMENSION UNIQUE NAME “Scenario” TYPE 3
ALIASES NOT UNIQUE
PRIVILEGES ( READ);
DECLARE PHYSICAL LEVEL “Demo”.”Sample”..”CustomDemo”.”Scenario”.”Gen1,Scenario” AS “Gen1,Scenario” UPGRADE ID 2161960612 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Gen1,Scenario” )
KEY “Demo”.”Sample”..”CustomDemo”.”Gen1,Scenario”
LEVEL NUMBER 0 EXTERNAL “Gen1,Scenario”
PRIVILEGES ( READ);

Looking at the two respective extracts for the second level, we see the difference:

DECLARE PHYSICAL LEVEL “Demo”.”Sample”..”CustomDemo”.”Time”.”Gen2,Time” AS “Gen2,Time” UPGRADE ID 2161959169 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Gen2,Time” )
KEY “Demo”.”Sample”..”CustomDemo”.”Gen2,Time”
LEVEL NUMBER 1 EXTERNAL “Gen2,Time”
PRIVILEGES ( READ);

DECLARE PHYSICAL LEVEL “Demo”.”Sample”..”CustomDemo”.”Scenario”.”Gen2,Scenario” AS “Gen2,Scenario” UPGRADE ID 2161960614 HAVING
(
“Demo”.”Sample”..”CustomDemo”.”Gen2,Scenario” )
KEY “Demo”.”Sample”..”CustomDemo”.”Gen2,Scenario”
LEVEL NUMBER 0 EXTERNAL “Gen2,Scenario”
PRIVILEGES ( READ);

For the imported hierarchy “Time”, the “LEVEL NUMBER EXTERNAL” is correctly incremented and stored as “1” (and in fact represents the level number in Essbase) while for the manually created hierarchy “Scenario” the external level number stayed at “0”.
If you have hierarchies with more than 2 levels, each level from 1 to N has an external level number of “0”.

In the rpd, there is no way for you to affect the external level number, so UDML is your only choice. In all honesty, I normally write my new dimension hierarchies – which should be reflected in the rpd due to cube changes – simply inside a text editor. Starting with an existing hierarchy which I copy+paste, I then write the UDML to fit the Essbase outline and then adapt the external level number to fit the real Essbase level number.

With that problem out of the way there’s really nothing you can’t represent in terms of cube outline changes without having to re-import the whole thing.

So much for today. Until next time!