Wednesday, October 28, 2009

OBIEE 10gR3 and Essbase System 9/11: Changing the measure dimension of a cube import in the RPD

Something I wanted to post since a while and was just asked again today: when you import a cube with multiple measure hierarchies into an OBIEE rpd...what are the steps to swtich from using one measure hierarchy to another?

Let's start with a very simple cube which was imported as uses "Account" as its default measure hierarchy:



In the structure you see the second measure hierarchy "Scenario" as one of the dimensions. I want to use "Scenario" as our measure dimension and hence our fact in the Business Model. The first thing to do is to clone the whole physical object "Sample Accounts":



In step 2 I double-click the "Custom" physical cube table, to the hierarchy tab and open the "Account" hierarchy.



Note the dimension type currently is "Measure Dimension". I change this to "Other" to transform "Account" into a normal dimension where OBIEE is concerned.



Now confirming with "Ok" shows us the effect of this change:



Now both our dimensions "Account" and "Scenario" are showing as hierarchy objects. The physical cube columns "Net Sales", "Margin" etc. are useless now so I delete them. Next up is the change of "Scenario" into the new measure hierarchy and hence the fact. Again I go into the properties of the hierarchy object and change its dimension type to "Measure Dimension":



That removes the "Scenario" dimension from the display in the physical layer:


What is missing now are the actual members inside the Scenario dimension. During import all the account members are created automatically as physical cube columns. A simple change of the dimension type doesn't automatically create the corresponding cube columns, so I do it manually:



It's actually very simple, you just need to take care to exactly match the MEMBER_NAME property or the ALIAS from Essbase. And make sure to follow the standard: if there's an alias, use the alias otherwise use the member name. In my case I just quickly create two physical cube columns "Actual" and "Budget":




And with that I'm done. I now have a new representation of my cube using the scenario dimension with its members as the measure dimension for analysis and the fact of the OBIEE Business Model:



Cheers,
C.

Wednesday, October 21, 2009

OBIEE 10.1.3.4.1 - Dynamic data source switching

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.

Cheers,
C.

Thursday, October 15, 2009

OBIEE 10.1.3.4.1 / Essbase 9.3.1: UDML for cube outline changes

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!