Uncategorized

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.

Done.

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