A question which I never covered in my blog yet since it was too basic and should normally work at all times when you import an Essbase cube:
How do I get a fact to display in the BMM layer after importing a cube?

The question came up here and basically has its origin in a bizarre cube outline. Correctly defined cubes will never face this (and hence no one covered it yet, though I already spoke about changing the measure hierarchy).

Let’s look at an import without a working “fact” (in OBIEE terms…).

No fact columns…but why? Let’s open the properties of the physical cube table and check the properties of the hierarchy “Account” (my measure hierarchy).

Now that’s just wrong. The “fact” must be the measure hierarchy. Let’s change that.

Better. Now we’re still missing the actual fact columns (i.e. the members of the measure hierarchy in the Essbase outline.). In my case that should be Costs, Gross Sales, Net Sales, Returns and Sales.

Repeat that for the other four.

Now drag-and-drop the whole thing to your BMM layer.



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.



I stumbled upon a post from Shiv Bharti and thought I’d expand a bit on this idea. First of all to prevent the necessity of using the “Advanced” tab; one of the “don’t do this at home, kids” parts of Answers. Secondly since just recently there was yet another question on OTN asking for advice on how to assign / force new values to a session variable during runtime.

Let’s do the example with an Essbase cube as a data source to showcase that this approach works with any data source. First, I create an initialization block which goes against an Oracle 10gR2 source to be able to write a simple dummy select statement from DUAL indicating CoFSM42 as my default Essbase server. CoFSM41 is a backup instance and hence my Essbase server which I want to switch to in order to see what the backup contains in terms of data loads (in this example, 42 will contain data until today whereas 41 will be the backup from last week, so no data this week).

select ‘CoFSM42’ from DUAL

Of course you can implement more elaborate solutions with control tables holding the servers and schemas of your different environment and instances.

In the connection pool to my Essbase source, I change the “Essbase Server” parameter to “VALUEOF(NQ_SESSION.Essbase_Server)

Starting the server, let’s to a quick check of the data through answers by using the approach Shiv proposed. First, I create the request on my standard subject area which currently points to CoFSM42 due to the session variable.

I see that the data I entered for this week is present. Using the SET VARIABLE prefix, I change the source to CoFSM41.

Ok, that’s empty. Now off to making this switch available on the dashboard through a prompt. The prompt is a fake prompt simply unioning my two server names. Here again, you may use a control table which holds your servers.

The important bit is to choose “Set Variable” as “Request Variable” and in the variable name “Essbase_Server” (my session variable used in the connection pool). Now I combine the prompt and the request in a dashboard to see the effect the “Set Variable” has on the request I’ve built. Bear in mind that there is no “is prompted” speficication or filter on that request apart from my week specification.

Here’s the results prompted with the current server:

And here the ones for the backup server:

So the prompt nicely switches between the different data sources for us without the need of Answers access or within Answers the need to play with the query prefix. Plus, it has shown the use of prompts to changes the values assigned to session variables during runtime.

Now for all those who were hoping for another OBIEE/Essbase post or are thinking about using this to switch cubes easily in their architecture:
Unfortunately this approach can NOT be used to easily switch between cubes. And who’s to blame? The substitution variables.

Essbase substitution variables, when defined on database level (“cube” in OBIEE terminology), arrive in the form of “server:application:database:varaiable”. E.g.: “CoFSM42:Sample:Basic:vCurQtr”.
The “CoFSM42” bit can’t be switched out using a variable since the variable name is always interpreted as a literal string. Taking the example from above, doing something like “VALUEOF(NQ_SESSION.Db_Server):Sample:Basic:vCurQtr” won’t work since the variable is then actually called “VALUEOF…”

Pity, since that would make your development and testing of different Essbase sources extremely flexible.



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”.”Gen6,Time” ) MEMBER TYPE ALL EXTERNAL “Time”
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”

DECLARE HIERARCHY “Demo”.”Sample”..”CustomDemo”.”Scenario” AS “Scenario” UPGRADE ID 2161960605 HAVING
“Demo”.”Sample”..”CustomDemo”.”Scenario”.”Gen2,Scenario” ) MEMBER TYPE ALL EXTERNAL “Scenario”
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”

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”

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”

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!


I realized this one while importing a rather large cube on a test machine. After the initial import of the cube definitions into the physical layer, I pulled everything over into the BMM layer and saw that dimensions I’d expect were missing completely from the business model. Checking back on the physical layer I saw the corresponding hierarchies were missing as well.

Re-importing and fumbling around didn’t resolve this while doing a cross-check import on my laptop produced a correct representation in both the physical layer and the BMM layer.

Luckily, it seems I wasn’t the only one hitting this issue since a Metalink search yielded document 872342.1. The issue is, that the Essbase API doesn’t find enough open ports to import the outline successfully.

Workaround: Open the registry and navigate to:


Modify / create the DWORD parameter “MaxUserPort” to / with value 65534. Then apply the changes, start the Admin Tool again and re-run the import.



I received a hint on OBIEE time series functions from my friend Peter S. Apparently the time series functions AGO and TO_DATE don’t work when you’re using it on tables with fragmented LTS.

A quick test yielded this:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22042] AGO function may not be used on ‘Amount’ because its source is horizontally partitioned. (HY000)

Checking metalink, I found document 739584.1 – AGO function with combined multiple sources. Enhancement request #7438154 has been raised and is targeted for 11g.

Cheers all!


Greetings, programs!

A little excursion into the little-known world of Marketing Analytics. Namely sampling tables for target levels. Here’s some tips for getting them to work correctly:

I.) Check that there is a physical table mapped that to the W_ table defined as SAMPLE_W_ has Object Type “None”…otherwise you can’t select a dynamic name for the table. “General” tab:

II.) Check that the correct Sampling Table identifier is associated with the physical table in the “Dynamic Name” tab:

III.) Creating new target levels in Siebel Analytics:

  • After creating new target levels for marketing segmentation, it is necessary to administer them correctly in the Presentation Server
  • Start the BI Server with the new rpd
  • Go to “Admin”
  • Go to “Manage Marketing Defaults”
  • Choose the “Default Campaign Load File Format”, the “Default Global Audience Segment” and the “Profile Dashboard” for each Target Level
  • If you don’t yet have these items, create them

IV.) Creating an Answer request as “Profile Dashboard” for a target level:

  • Create the request as usual with the columns you want to have displayed.
  • To make it work for the drill down on the segment count, add a filter that prompts for the ID. E.g.: “ROW_ID is prompted”

End of line.


Greetings, programs!

After Venkat just recently wrote about one task usually done after a cube import – namely the setup of the time dimensions – I’d like to continue a bit on this topic.

One thing that gets many people and can lead to raised eyebrows (in a questionable fashion) is the way, the dimension objects are built in the business model when dragging a cube over.

The blue’s a dead giveaway that something changes as of Gen3. Let’s look at the keys for the lowest level.

And in detail:

“Gen8,Department#1” only has Gen8,Department as key column.

To see how this behaves if we do nothing, we create a request. Just Gen7,Department, constrained to a specific member.



I’d call that “suboptimal”…so back to the rpd. Kill the multi-column key. Keep the other and rename it (yes, I’m pedantic with stuff like that). Do the same on all other levels.

Back to our report and let’s look at the results now:

Much better. Depending on how your cubes are built, don’t forget to ensure that the option “Use unqualified member name for performance” is unchecked.

End of line.


Greeting, programs!

A while ago I had an issue with reporting against Essbase, namely in the area of the time dimension. More precise: getting the reports to react to rolling weeks, months, quarters etc.

Now we all know and use variables all the time. CurrentQuarter, CurrentFiscalYear etc. are used all over the place and if you look into your average BI apps repository you’ll find the one or the other dozen variables.
The premise here was, that all variables had to be based on Essbase since the cubes were the “single source of truth” for all derived reports and all definitions should be consistent. Fair enough. Alas, when getting down to business and defining all the variables needed for the reports, there was some reluctance with regards to the creation of all these new variables in Essbase.

As you all know, variables in Essbase have a scope of either
– server
– application
– cube
The fear was, that with numerous cubes running on the same server, the variable management and especially their automation (refresh) would get out of hand. So I was asked to work around that…keeping the original premise in mind: the governance of the cube. All variables have to be sourced from there.

To start out with, I imported the existing variables relevant to the time dimension with a server-wide scope.

If you look at the details of the initialization block behind those variables, you’ll see that there’s no script in the “Data Source” section but rather a string saying “substitution variables”. In UDML this looks like this:

{DefaultMulDB} AS {substitution variables} )

So let’s create a new init block and a new variable in the “Variable Target” section (here called “vCurMo_01”). Click “Edit Data Source” and choose the connection pool which points to your Essbase server. Then set “Use database specific SQL” to TRUE.
Now for the syntax. What we’re looking for is “last month”, i.e. the existing “vCurMo” variable – 1 month. Since we’re in March (MAR-09), we’re expecting February to be our result (FEB-09)

SELECT {} ON COLUMNS, {[Time Period].[&vCurMo].Lag(1)} ON ROWS FROM Ess931_ASO.MyCube

Hit “Test”.

It picks up February in the format of the time dimension in Essbase. All in all, our init block looks like this.

So far so good for one month. What if we want more months / more quarters / more weeks? Can we just expand the MDX? Let’s try it and see if we can get the last 8 months rolling. For that we create 7 more variable targets in the init block called “vCurMo_02” through “vCurMo_08”. Then we change the MDX.

SELECT {} ON COLUMNS, LastPeriods (8, [&vCurMo]) ON ROWS FROM Ess931_ASO.MyCube

Unfortunately, while the query runs fine, the result looks like this:

Ok so back to what we had before and let’s adapt that:

{[Time Period].[&vCurMo].Lag(1),
[Time Period].[&vCurMo].Lag(2)
} ON ROWS FROM Ess931_ASO.MyCube

Still the same. You might have guessed it: if you want to write your own MDX to get a dimension member into a variable, then it’s 1 variable = 1 init block. Due to the way MDX queries retrievbe results and the way the variables are populated, you can’t fill multiple members variables in one block.

End of line.