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!