The 100 facets of Oracle Business Intelligence

Too true to miss

2011-09-16T16:05:00.001+02:00

Hello everybody,this blog has been dormant for quite a while now. Today, my buddy Robin tweeted on a blog post which was imply too true and too good not to share. This prompted me to rethink my decision to abandon blogging and get this blog going again.So without much further ado, here's the promised link:http://jamesmorle.wordpress.com/2011/09/16/right-practice/James is spot-on with this one. Adding to his line "Best Practices are being used as an alternative to rational thought." I would like to point out one more thing which I see happening more and more often: Best Practices are being used as an excuse for not doing proper analyses or worse, justifications for sub-par implementations.Situational context can have a profound impact on the basic reasoning underyling a practice. Make sure to take it into account when choosing your Right practices.See ya!Thought of the day: Fas est et ab hoste doceri

BI Forum - consolidated geekness awards

2010-05-24T22:35:00.004+02:00

According to Daan Bakboord over here, Robin and me reached ultimate twitter geekness when I watched Daan tweeting, took a pic of him and tweeted it and Robin took a pic of me taking a pic of Daan tweeting...

Well agreed it was kinda geeky in a web 2.0orhea kind of way. Still, I vote for this pic as the winner of the generic geek award, category t-shirt:

So after this, here's all the pics I took:

BI Brighton Forum

BI Forum Brighton 2010

2010-05-23T14:00:00.004+02:00

Hi all,

well what can I say...the BI forum at the Brighton Marina was a blast. Almost the whole european blogger community was there and we had a real good time and extremely good discussions. Plus as of Friday we even had goo weather!

Last things first, at the end we voted for the best speaker of the event and Robin Moffatt won this year. Especially cool for him since we popped his speaker-cherry at this event ;-) So congrats Robin.

The first day of the event was a masterclass on OBIEE with Kurt Wolff. I'm sure you all know him, but to re-iterate: he was part of the original nQuire team and is - as he says - responsible for some of the things which confuse people today.
It's amazing to see how much of concepts and design decisions which were made by people like Kurt or Ed Suen back in 1998 are still the basis of what we do today...in almost unaltered form. @lex has a nice write-up over here and all I want to add to this is: thanks for your excellent presentation, Kurt. Brilliant explanantions and insights!

At night before the dinner we had Phil Bates rocking up together with Mike Durran and Adam Bloom to give a keynote speech for this event on the topic of "a future version of one of their products that they're working on in the labs". So you might guess that he could have meant 11g but we can't be sure since the Oracle guys continued to use that phrase throughout the next 2 days ;-)
Phil had some nice slides and we finally saw most of them demoed on a lab-version...so obviously no screenshots from there.

Day 2 started off with Mike Durran again who seems to be getting the early-bird sessions every time. At least this time he got Wednesday so no real danger of too much impact from the "entertainment committee" aka "Ragnar and Borkur" (more on that later).
Mike showed us the future architecture and systems management features of 11g. So obviously he had hard time finishing a single topic without questions and everyone was volunteering his speaker time to stay on subject (equally obviously that didn't happen). That first in-depth presentation clearly showed us all several things: first of all the learning curve won't be linear...not even exponential...it will be logarithmic. Can't really say much more but be prepared to be astonished. Unfortunately I was so concentrated on the topic that I didn't take a picture :-S (neither Kurt the day before actually...).

Next up was speaker-award winner Robin Moffatt on "Performance Testing OBIEE". I think his example was the most thorough, well-structured and thought-through performance testing strategy I have seen at any clients side in the last 5 years. There's huge misconceptions on this topic out there and Robins presentation is a good guideline which covers the most important questions you have to ask yourself before going off to test your solution for performance. And here he is in action:

Next up was Elio Idema on "Complex Modelling with OBIEE". It was funny to see him more or less hitting similar topics as Kurt had the day before since it really enforced the main theme of this whole forum and the single most important recurring point in OBIEE projects: data modelling. He had some excellent points on how to procede in transforming entity-relationship data models into analysis-oriented star schemas in the physical layer and I would really suggest to anyone attempting such a feat to check out his slides and follow his main points. (I'll try and get him to blog on this as well).
I only caught him on camera afterwards so here he is (on the right):

After lunch there was Emial van Bockel who hit the same spot with "The art of modelling with OBIEE". Like last year he had some interesting slides on the way his company models information, what concepts lie beneath these models and how they evolve. Quite obviously he spawned a lively debate; here we have John Minkjan (left hand, turned), Robin Moffatt, Sukesh and Michal Zima: then we have Justin Townsend, Peter Scott and Michael Krantz.

Next up we pestered Adam Bloom...i.e. he had his presentation and we constantly asked for more since his topic was "BI in and on the Business Process – New Innovations between BI 11g and BPM 11g". Adam is the Oracle Product Manager for the whole action framework so you can imagine that we took the liberty of going down to the nitty-gritty. The scope of the whole topic is so vast though that time ran out way too early.
Here he is as he was most of the time: hacking away at a "future product that's in the lab":

After that we had a debate on "What should drive your modelling strategy from ELT to front end?" presented by Emial and myself. Obviously no pic I've taken, but good ol' @lex took one and I'm sure you agree we had THE opening slide ;-)))

With this we ended day 2 and after a short round of drinks in the bar, headed out for dinner. But this is a geek blog, so fast-forward to day 3.

In order to slow him down and not have him win speaker-of-the-event by default, Antony Heljula got the morning session this time and in the audience you could see some victims of Ragnar-and-Borkurs "entertainment blitzkrieg". Well some you couldn't see because of follow-up negotionions with their pillows, but I disgress...

Tony talked about "BI EE Architectures and Sizing" with several key hints and helps for sizing your architecture. Like Robins the day before, this presentation covers a topic which should be one of the core topics for each project but often "falls through the cracks" and results in either badly set up environments, hugely overpowered ones or awfully undersized ones.

As a little intermezzo from my side: those three days in Brighton would have been a boon to any project out there and if you have 5 minutes, then get over to rittmanmead.com, get the contact details, get the presentations wherever possible, read them and learn from them.
I don't think we re-invented the wheel or anything, but most of the topis covered simply can't be ignored in any project and were presented in a exquisitly researched form. Again, kudos to all of you guys. That's why I more or less booked my place for 2011 already.

Next up we had a real celebrity. Mr. "Oracles Person of the year"...Mark Rittman! Here he is scratching his nose ;-)
Ok jokes aside not all of you may know the person-of-the-year story, so here's what Mark has to say but of course we knew before that it's oraclenerds fault.
Mark covered "Inside the Oracle BI Server" which was clearly one of the most nerdy tech topics. (Loved it and obviously voted for it). If you were ever wondering what a level 5 log file actually contains, what it means and how you influence which part of it...that's your presentation to look out for. I had done a fair share of log file archeology but Mark had some really cool new stuff in his presentation and thanks to the presence of "the godfather" Kurt Wolff we even got some additional informations which took this one presentation really to the next level.

"ODI 11g: The New Generation of Data Integration" was then presented by Craig Stewart...smile Craig!
He reminded us first that 11g ODI was still not out there but we got the feeling that it's closer to us than another future-product-that's-still-in-the-lab. All in all I have to say that ODI grew quite a bit and contains neat new knowledge modules. Coming more from an OBIA background that plain ETL/ELT I obviously tried to get more information out of him regarding the next OBIA releases and ODIs importance within them but to no avail :-( Still it was very nice to see in which direction ODI is evolving.

The arrival of John Minkjan on the stage as the next presenter heralded a hugely funny intro to his presentation "OBIEE Customizations: When you are in the kitchen, learn to cook." Plus he looked quite neat as the only guy presenting in a suit

John gave us a good beating on the different aspects of GUI design for dashboards. He has some major points I have to say. We all have seen the horror of gradients, 3D charts, animations all over the place and eye-cancer-inducing colour palettes. I agree with John; you really should read up on analysis- and interface-design if you want to present your information in a digestible format.

Last but not least there was Venkat with his "Fusion Middleware 11g - ADF Business Intelligence Components" presentation. As one of the few things that's widely known and also officially confirmed by Oracle, ADF will become the new graphical layer of OBIEE in its next incarnation just as it has in other parts of the Oracle product stack already. This will definitely be an area where all of us will need to get more experience and dive into its depths since it really seems that for so many of the things we faked or tricked today in order to accommodate user requests for GUI behaviour or specific analysis types, ADF will be the answer.
And here's Venkat gesturing while he explains a dogfight during the Battle of Britain:

(obviously not but that's the first things that came to my mind when reviewing my pictures).

His session wrapped up the forum for 2010. If at all possible it was even better than 2009. We had Kurt Wolff over who gave us a history brush-up on OBIEE nee Siebel Analytics nee nQuire and showed us through his in-depth explanations just how well thought-out the concepts were which they defined 12 years ago and which still form the basis of our daily work.
The speakers were high-class, the presentations to the point and on intriguing topics. No sales bladibla or "we are the best" company presentations.
The discussions were very lively and people participated from A to Z with much vigor. That's just the thing that distinguishes this BI forum from others...we're not there to sell; we're not there to "steal" either by just sitting there, not opening our mouth since sharing our project experience is losing competitive advantage or something (you might get who I'm bashing here...). It's just industry-wide recognized cracks sharing, exchanging, learining and discovering together with a healthy portion of networking and most of all...fun!

Thanks again to Mark, Jon and the whole RittmanMead crew for making this forum a possibility and to al of you other guys for participating.

Hope to see you all again next year. It was awesome.

OBIA 7.9.6.1 Performance Recommendations

2010-03-02T17:27:00.002+01:00

Thanks to rnm1978 for alerting me to the availability of this rather juicy document on MOS:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=870314.1

And the really cool part?

Oracle positions Exadata solution as fast and efficient hardware for addressing I/O bottlenecks in large volume environments. The internal benchmarks for running Oracle BI Applications on Exadata will be published soon.

Sweeeet!

OBIEE - Maps integration

2010-02-22T16:49:00.003+01:00

I recently was asked about the different options of visualizing data in OBIEE on maps. Since many of the usual suspects (you'll see form the link-list below) have already blogged about this, it is easiest to provide a list with all good and informative articles rather than writing a completely new post. Here goes:

Google Maps:
www.artofbi.com:
http://www.artofbi.com/index.php/2009/07/obiee-and-google-maps-integration/

John:
http://obiee101.blogspot.com/2008/10/obiee-using-google-maps-q-style.html

Oracle Mapviewer / Oracle Spatial:
Venkat:
http://oraclebizint.wordpress.com/2007/09/25/oracle-bi-ee-10133-and-mapviewer-step-by-step-integration-phase1/

Gerard:
http://gerardnico.com/wiki/dat/obiee/presentation_service/obiee_dynamic_integration_oracle_map_nsdp

Lapp, Conrad & Sharma (OOW 2008):
http://www.oracle.com/technology/products/mapviewer/pdf/mapviewer_obiee.pdf
Abhinav (OOW 2008):

Maarten (RittmanMead Forum 2009):
http://www.rittmanmead.com/files/maarten_jan_kampen_obiee_mapviewer.pdf

If you have any other good links - preferably with examples and code snippets - post a comment and I will update the list. Maybe we will get this into a sticky thread on OTN.

See ya!

OBIEE 10g presentation server cache directory parameter

2010-02-01T23:02:00.005+01:00

A while back our oraclenerd had a question about presentation server cache settings which he sent out through twitter.

rnm1978 and me answered to his distress call (emails not included. twitter's quite useless for sending instanceconfig.xml subsets ;-)).

However, I wanted to make it clear through this post, what part of the presentation server cache is actually managed by which parameter since there's a little gotcha in there.
So basically we there are four parameter which exist for the presentation servers instanceconfig.xml (see also Johns pdf extract). By default they're not explicitly noted in the file so you need to specify them in order to change them. Here's a little sample with already modified paths:

Now normally the cache files for the presentation server reside in the \tmp directory within the respective subdirectories sawcharts, sawrptcache and sawvc; with the xml cache files lying in the \tmp folder itself. Note that I cleaned out all files in the directory for in the screenshot below to have a clean testing baseline:

Restarting the presentation server with the additional XML above gives me the following results:

Chart cache:

Report cache:

State pool cache:

XML cache:

XML cache empty? Aha, seems there's an issue. Let's check the original path:

The XML cache files are still here. Not really what we wanted. So the XMLCacheDefault CacheDirectory seems to be useless. At this point I actually created an SR on MyOracleSupport to confirm a hunch I had. Result:
"With regards the parameter , I have researched this and have not found any documentation on it, internally or published. Infact through my research it appears that it has never been the case that this parameter is used to determine the path of the nqsxxxx temp files.
Previous versions of OBI i.e. Siebel Analytics document that the temp files generated by the Analytics Web server could be redirected by using the parameter 'TempDir' in Instance config or as a registry variable, in conjunction with the work directory path."

So off to the NQSConfig.ini and change the WORK_DIRECTORY_PATHS:

#WORK_DIRECTORY_PATHS = "C:\Oracle\OracleBIData\tmp";
WORK_DIRECTORY_PATHS = "C:\DataSources\Cache\tmp";
SORT_MEMORY_SIZE = 4 MB ;
SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
VIRTUAL_TABLE_PAGE_SIZE = 128 KB ;

After that, restart all the services and check again where the cache files went.

Ok now we know how to make the rest of the cache files elsewhere.

Cheers!

OBIEE/Essbase: how to handle missing facts

2010-02-01T11:46:00.003+01:00

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.

Issues when using Firefox 3.6 on MyOracleSupport (flash version)

2010-01-22T16:34:00.005+01:00

During the day I realized that MOS was acting strangely since I upgraded to FireFox 3.6 and @rnm1978 confirmed my findings just now:
You can't directly read MOS knowledge items with Firefox 3.6

Opening a knowledge items as usual (left-click - which should display the content on the right hand side of the screen) just pulls up a blank display:

You have to right-click the item and choose "Open Item in New Window" to see its content.

Not very comfortable but there's always supporthtml.oracle.com as a fallback scenario.

Cheers,
Christian

OBIEE 10.1.3.4.1 / Essbase 9.3.1: UDML for cube outline changes - Part 2

2010-01-19T22:57:00.003+01:00

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 \server\bin 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:\Oracle\OracleBI\server\Bin>nqudmlgen.exe -u Administrator -p Administrator -r "C:\Oracle\OracleBI\server\Repository\Showcase_002.rpd" -o "C:\Oracle\OracleBI\server\Repository\UDML\Showcase_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:\Oracle\OracleBI\server\Bin>nqudmlexec.exe -u Administrator -p Administrator -I "C:\Oracle\OracleBI\server\Repository\UDML\Showcase_002.txt" -O "C:\Oracle\OracleBI\server\Repository\Showcase_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

OBIEE 10gR3 generic: Multiple custom sort on table view

2009-12-11T14:34:00.003+01:00

Ok, be honest here for a moment guys: who of you was aware that you can sort on multiple columns in a table view rendered in a dashboard?

Kudos to Erick!

Cheers,
C.

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

2009-10-28T22:24:00.001+01:00

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.

OBIEE 10.1.3.4.1 - Dynamic data source switching

2009-10-21T19:42:00.002+02:00

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.

OBIEE 10.1.3.4.1 / Essbase 9.3.1: UDML for cube outline changes

2009-10-15T21:50:00.002+02:00

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!

1 kilopost on OTN

2009-10-11T13:30:00.003+02:00

Geeky, I know...

Thomas Siebel's Hydaspes moment

2009-09-03T11:50:00.004+02:00

Tom Siebel was attacked by an elephant while on a photo safari back in August, but I actually didn't hear about it until now:

http://www.theregister.co.uk/2009/09/03/siebel_elephant_attack/

What can I say? Damn, what a holiday...wish you a complete recovery!

Jeff McQuigg's new blog location

2009-09-03T10:36:00.003+02:00

Jeff McQuigg moved his blog away from IT Toolbox so I thought I'd quickly share this:

http://greatobi.wordpress.com/

Cheers,
Christian

Essbase cube imports do not correctly generate the business model (dimensions, logical tables,…)

2009-08-31T11:46:00.002+02:00

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:

\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

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

Cheers,
Christian

RittmanMead Oracle BI Training Days 2009

2009-07-28T23:30:00.002+02:00

I'm pleased to announce that the RittmanMead training days for Oracle BI are now officially open for registration.

Speakers at this event will be Mark Rittman, Venkat Janakiraman and myself.

Agenda:

Day 1
o Oracle BI and EPM architecture overview – Mark Rittman
o Oracle BI EE Data Modeling against DW and 3NF sources – Mark Rittman
o Oracle BI Delivers + Integration with Java and BI Publisher – Venkat Janakiraman
o What’s new in Oracle BI, DW and EPM from Oracle Open World – Mark Rittman

Day 2
o Oracle BI EE Data Modeling against Essbase – Venkat Janakiraman
o Leveraging MDX functions and calculations in OBIEE – Christian Berg
o Integrating Security across OBIEE and EPM – Venkat Janakiraman
o I can do this in Hyperion – how do I do it in OBIEE? – Christian Berg and Venkat Janakiraman

Day 3
o OBIEE Systems Management with OEM BI Mgmt Pack – Mark Rittman
o OBIEE Configuration Management Best Practices – Christian Berg
o ODI functionality in Oracle BI Applications – Mark Rittman
o ODI Integration with Essbase, Planning and Oracle EPM Suite – Venkat Janakiraman

Once more, here's the link to the full event details:
http://www.rittmanmead.com/trainingdays2009/

You can find the registration page at http://www.regonline.co.uk/rmtrainingdays2009

Cheers,
Christi@n

OBIEE 10.1.3.4.1 - Catalog path containing "."

2009-07-23T19:56:00.001+02:00

I was just testing multiple web catalogs when I realized that it's a bad idea to put version numbers into the web catalog name.

As an example, I have my web catalog "samplesales_paint_v1.3" sitting ready in OracleBIData\web\catalog and my instanceconfig.xml looking like this:

< ?xml version="1.0" encoding="utf-8"?>
< WebConfig>
< ServerInstance>
< DSN>AnalyticsWeb
< CatalogPath>F:\OracleBIData\web\catalog\samplesales_paint_v1.3

Starting up the server will not load the refrenced catalog, but rather create a new one from scratch: "samplesales_paint_v1"

And the Oracle BI Presentation Services Administration duly notes:

Physical Presentation Catalog Path \\?\F:\OracleBIData\web\catalog\samplesales_paint_v1\root

The sawlog0.log reads as follows:

Type: Error
Severity: 40
Time: Wed Jul 21 23:46:36 2009
File: project/webcatalog/localwebcatalog.cpp Line: 1507
Properties: ThreadID-4328
Location:
saw.catalog.local.loadCatalog
saw.webextensionbase.init
saw.sawserver
saw.sawserver.initializesawserver
saw.threads

Could not load catalog F:\OracleBIData\web\catalog\samplesales_paint_v1.3. Either it does not exist or insufficient permissions.
---------------------------------------
Type: Warning
Severity: 40
Time: Wed Jul 21 23:46:36 2009
File: project/websubsystems/httpserverinit.cpp Line: 49
Properties: ThreadID-4328
Location:
saw.catalog.local.loadCatalog
saw.webextensionbase.init
saw.sawserver
saw.sawserver.initializesawserver
saw.threads

Creating Catalog F:\OracleBIData\web\catalog\samplesales_paint_v1.3.
---------------------------------------

The log is incorrect on both accounts. "F:\OracleBIData\web\catalog\samplesales_paint_v1.3" does exist and the folder creation in the warning message may use the correct naming but actually creates folder "F:\OracleBIData\web\catalog\samplesales_paint_v1".

XML normally accepts "." inside the element content so I guess this is a legacy fragment from Siebel Analytics versions where the web catalog was a .webcat file. Why? Well, using this element:

< CatalogPath>F:\OracleBIData\web\catalog\paint.webcat

starts my "paint" folder ;-)

Any comments on this are welcome.

Cheers,
Christi@n

"in between" filters for MDX sources

2009-07-20T20:29:00.002+02:00

Fiddling around with some more functional options we've all come to know, like and use frequently, I found that "in between" filters in answers requests going against MDX sources (Essbase, MS Analysis server etc) don't work as expected.

To showcase this, I have created a simple request. Planning and Current numbers by Fiscal Month within Europe. And I'm interested in all months between Q1 2009 and Q4 2009:

Checking the results tab we see that it's not necessarily what we'd expect:

Doing a full data scroll reveals that basically all Fiscal Month members are being pulled up.

Here's the MDX from the log:

With
set [Geo3] as 'Filter([Geo].Generations(3).members, (([Geo].CurrentMember.MEMBER_ALIAS = "Europe" OR [Geo].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'
set [Time Period4] as 'Generate({[Time Period3]}, Descendants([Time Period].currentmember, [Time Period].Generations(4),SELF), ALL)'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Current])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Planned])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2],
[Scenario].[MS3]
} on columns,
NON EMPTY {{[Time Period4]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sample.Sample]

And here's the important bit:

set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'

I've opened an SR for this since there's no mentioning on metalink, the release notes or the new features guides.

Cheers,
Christi@n

Updated my blog list

2009-07-20T20:26:00.003+02:00

Added two new Oracle BI blogs to my blog list:

http://tipsonobiee.blogspot.com
http://www.artofbi.com

Changing print controls through catalog manager

2009-07-20T20:14:00.004+02:00

Recently I was off to change the print options on all of my dashboard pages. Naturally, as with all setting, this is encoded in the XML and can be mass-manipulated by the catalog manager.

Here's the section in the XML, changed for landscape format, A4 paper size (yes, no weird US sizes ;-)) and explicit exclusion of headers and footers:

< ?xml version="1.0" encoding="UTF-8"?>
< sawd:dashboardpage sawd="com.siebel.analytics.web/dashboard/v1" saw="com.siebel.analytics.web/report/v1" xmlversion="200705140" isempty="false" duid="hsdfj3478387bs82" personalselections="true">
.
.
.
< span style="font-weight:bold;">
< saw:pageheader show="false">
< saw:pagefooter show="false">
< /saw:pagefooter>
< /saw:pageheader>

Cheers,
Christi@n

Sun shareholders "ok" Oracle takeover

2009-07-17T14:38:00.001+02:00

http://news.cnet.com/8301-1001_3-10288611-92.html

Yes, I know they're always talking about "mergers"...just like they "merged" with Siebel or they "merged" with Peoplesoft ;-)

"There can be only ONE!"

Cheers,
Christi@n

Venkats magic box on multiple hierarchy reporting

2009-07-14T09:49:00.002+02:00

Leave it to Doctor Venkat to sum this one up so nicely:

http://www.rittmanmead.com/2009/07/13/oracle-bi-ee-10-1-3-4-1-multi-hierarchy-reporting-shared-parents-and-shared-childs-fragmentation/

OOW 2k8 and 2k7 presentations

2009-07-13T15:55:00.003+02:00

Thanks to Steven Chan for reposting the links to the OOW presentations. Very useful since I had lost the URL myself a while ago.

OpenWorld 2008 Presentations
Userid: cboracle
Passworld: oraclec6
OpenWorld 2007 Presentations
Userid: cboracle
Password: oraclec6

Cheers!

You now you're surrounded by geeks when...

2009-07-09T09:08:00.002+02:00

...on orana.info, there's a posting on the return of Monkey Island!

http://www.bluestudios.co.uk/blog/?p=778

God I love this industry. And yes, I have the game on order already and fdefinitely will get me each and any incarnation of the old classics from Steam and then the ones they're preparing for the Wii ;-)

OBIEE / Essbase MDX generation issues finally resolved

2009-07-07T22:08:00.001+02:00

In one of my recent posts I was talking about the different patch levels for 10.1.3.4.0 and 10.1.3.4.1 and their impact on functionality and the MDX produced. Interestingly enough not all of the problems appear with every cube outline imported into OBIEE. Oracle initially had troubles reproducing some of the errors (using the normal OOB Essbase sample cubes).

To recap: the changes to the connectivity DLLs first introduced in patch 7349048 (for 10.1.3.4.0) enabled OBIEE / Essbase security integration, but had some side effects. Performance dropped up to 3000% in some cases, standard report level totals or pivot totals stopped working etc. This is mostly due wrongly produced MDX. Especially for the grand totals the situation is quite clear:

With
set [Currency2] as 'Filter([Currency].Generations(2).members, (([Currency].CurrentMember.MEMBER_ALIAS = "US Dollar" OR [Currency].CurrentMember.MEMBER_Name = "US Dollar")))'
set [Time Period2] as 'Filter([Time Period].Generations(2).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "2009" OR [Time Period].CurrentMember.MEMBER_Name = "2009")))'
set [Time Period3] as 'Filter(Generate({[Time Period2]}, Descendants([Time Period].currentmember, [Time Period].Generations(3),SELF), ALL), (([Time Period].CurrentMember.MEMBER_ALIAS = "Quarter 1" OR [Time Period].CurrentMember.MEMBER_Name = "Quarter 1")))'
member [Currency].[CurrencyCustomGroup]as 'Sum([Currency2])', SOLVE_ORDER= AGGREGATION_SOLVEORDER
member [Time Period].[Time PeriodCustomGroup]as 'Sum([Time Period3])', SOLVE_ORDER= AGGREGATION_SOLVEORDER
select
{ [Account].[Sales]
} on columns
from [Sample.Sample]
where ([Currency].[CurrencyCustomGroup],[Time Period].[Time PeriodCustomGroup])

The MDX simply contains no "on rows" specification. These changes remained in all subsequent patches and are also part of 10.1.3.4.1. Hence, this release suffers from the same problems.

On 10.1.3.4.0, there exists patch 8444119 which basically was constructed after 10.1.3.4.1. 8444119 itself does not solve any of the issues mentioned above. It does however include a small, non-documented switch which resolves all these issues. NOTE: this is NOT documented and will only work on 10.1.3.4.0! Oracle told me they're working on a similar fix for 10.1.3.4.1 and on updating the patch information, but no sign of either yet.

In a 10.1.3.4.0 environment, install patch 8444119, then create a new environment variable: "OBIS_Essbase_CustomGroup_Generation". Set the value to "0" (without the quotes). This will correct the issues and correct the MDX fired against Essbase. Also, performance will be stabilized and go back to the level of an unpatched 10.1.3.4.0.

Oracle unfortunately didn't specify in detail what this OBIS_Essbase_CustomGroup_Generation "performance knob" (as they call it) does behind the scene and what the other two available settings "1" and "2" entail. Hopefully they update patch 8444119 soon since its documentation still says nothing about the existance of this environment variable.

Document 845594.1 has been created an published on Metalink. This is the only document existing which points to or mentions OBIS_Essbase_CustomGroup_Generation. So if you're out there, going against Essbase cubes and running 10.1.3.4.0 with 8444119 (or any MDX-influencing other patch below) or a full blown 10.3.4.1 then check it out.

WARNING: While document 845594.1 outlines the issue and the solution, the version it refers to is WRONG!

"Applies to:
Business Intelligence Suite Enterprise Edition - Version: 10.1.3.4.1 [1900] - Release: 10g"

Patch 8444119 and the use of environment variable "OBIS_Essbase_CustomGroup_Generation" do only apply for patch 10.1.3.4.0. You can not use this solution with 10.1.3.4.1.

Cheers,
Christi@n

OBI Apps 7.9.5.2 ODI vs. 7.9.6 Informatica

2009-07-06T22:54:00.001+02:00

I just was asked what's actually in the BI Applications release which comes with ODI rather than Informatica. In general, the ODI release is 7.9.5.2 while the newest Informatica one is 7.9.6

Here's the link to the ODI Apps (Controlled Availability Release) documentation: http://download.oracle.com/docs/cd/E14224_01/welcome.html

So BI Apps with ODI is an Oracle eBusiness-exclusive release with eBusiness being required to be at release 11.5.10. These are the Applications covered in the release:

Oracle Financial Analytics
Oracle Human Resources Analytics
Oracle Supply Chain and Order Management Analytics
Oracle Procurement and Spend Analytics

The newest DAC on the other hand (it's no longer included in the files you download for 7.9.5.2 or 7.9.6) is 10.1.3.4.1 and can be found here: http://download.oracle.com/otn/nt/ias/101341/dac_windows_x86_101341.zip

Cheers,
Christi@n

Time Series and Fragmentation

2009-06-27T19:40:00.002+02:00

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!

Silence of the posts

2009-06-11T20:35:00.002+02:00

My blog is somewhat playing the sounds of silence recently. I don't really have the chance to write posts a lot these days. At least not if I want to blog with sufficient details. And yes, I know I still owe you several posts / topics.

Sorry guys. Content coming up soon ;-)

The vendor client relatioship

2009-05-28T11:34:00.001+02:00

Thanks for tipping me off on this one Mark...it really made me laugh :-D Plus, it is SO true!

Update on the OBIEE/Essbase security integration

2009-05-21T12:34:00.002+02:00

Hi guys,

Just quickly to let you all know. I'm not cutting you out of the loop by not writing on the details, but if I'd have to document all tests I run and all issues I find, I will still be here next month.

Bottom line:
- watch out for 10.1.3.4.0 with 734908
- watch out for 10.1.3.4.0 with 844119
- watch out for 10.1.3.4.1

With those three versions be VERY careful that you test each and every report you have running against Essbase.

I will update you as soon as I can with a finalized overview. Probably only once Oracle has come back...

What..the...frak?!

2009-05-20T19:54:00.002+02:00

Ummm this is possible?

http://girlgeek-oracle.blogspot.com/2009/05/making-problems-for-myself.html

Geezus...

Reminiscing Brighton

2009-05-19T23:53:00.000+02:00

Last week I had the chance an (honestly) privilege to take part in the BI Forum in Brighton. First and foremost, I'd like to say a huge thanks to the team of RittmanMead for organizing this and giving us geeks a chance to unite. We've got to do this more often!

Next up, something personal. I was extremely happy to finally meet Venkat face to face. We seem to have developed one of those friendships that start online and when you meet, it's like you know each other since years. Also, I was extremely content to meet Ed again. Damn already a year since we finished our last project together.

Let's get back to business. The lineup of speakers was quite something to say the least. I'm not going to name anyone in special since I then need to name each and every one of them.......ok here we go: Craig Stewart, Edward Roske (We're not wooorthy!), Mark Rittman, John Minkjan, Venkat (I think, you're actually entitled to carry "Venkat" as your sole identifier. Everyone else must change his name ;-)), Adam Bloom, Andreas Nobbmann, Mike Durran, Emiel von Bockel, Maarten-Jan Kampen, Adrian Ward, Antony Heljula and Peter Brink. Total quality in all presentations and here's my 10 cents on them:

a) "BI Apps with ODI" from Craig Stewart: Now here we have an interesting new piece in Oracles product portfolio. Good for new customers. Questionable (at best) for existing ones.

b) Edward Roskes "Internals of Essbase". What can I say? Edward Roske. On Essbase. Ocean. Lots of Water. Understatement. And I just loooved his texan humour.

c) Mark Rittman spoke about "BI Apps Optimization". Do have to say "see b) ?". Didn't think so.

d) John Minkjan had a very nice presentation on caching. I would have like to taped it and hit a lot of people over the head with the tape (and the camera). Listen to him, people. Caching is a tool, a technique, something that can help you. It's NOT the solution for your problems! Thanks John.

e) Venkat had a cool gimmick prepared. He had actually written a program to keep OBIEE and Essbase security in line. Extremely nice stuff! It was his first presentation in a forum like this and in Europe. Well done! I'm going to steal that ;-)

f) Then we "blew up a BI server" with Adam Bloom. Talk about small cuase with huge effect. Ouch.

g) Andreas Nobbmanns "UDML and XML for grown-ups" session. I vote for his presentation to be judged "Too dangerous to be known widely!". Please don't let kids touch that one. They destroy enough as it is. Personally...I loved it ;-)

h) Friday started with Mike Durran on 11g. I won't say more. Or I will have to kill you. Seriously. Ed Roske has the gun.

i) Emiel von Bockels presentation. Now THAT spawned discussions. Great stuff! I'd love to see how their solution evolves over the nexst 3 to 5 years. I hope he keeps us up to date.

j) Oh yeah...I'm still not over Maarten-Jan Kampens statement on using "a bit of code" for his "OBIEE and Mapviewer" integration. Dude...copyright that stuff.

k) Adrian gave me permission to skip his session since I had stuff to do, so not much to say here.

l) Antony Heljulas "SOA" bit was cool. Reminded me of my first trials after Marks mastclass last year. Gotta say though. Very nicely presented. Better than most of Oracles sales force.

m) Peter Brinks "Cube Organized Materialized views". Essbase cubes? Oracle OLAP? Cube Organized Materialized Views? It seems we're actually getting too many options from Oracle right now. Would be interesting to see how Oracle positions those options with the clients.

Wrapping the whole event up in one word sounds like this: awesome. In two words: fraking awesome! Loved it, look forward to the next, want to do one myself in Switzerland now. (Check my blog or Andreas for news on that idea).

That's it from my side for today. Off to catch some sleep.

Cheers,
Christi@n

Grand Totals in Essbase sourced requests after 10.1.3.4.1

2009-05-19T22:13:00.003+02:00

I noticed something very disturbing after applying 10.1.3.4.1 to our development environment: report grand totals for tables and row totals for pivots don't yield any results anymore unless you manually set all fact columns to "Sum" or "Avg" in the report. The "Aggr_External" which has normally had its effect (by importing it from the cube through the physical to the BMM layer to presentation layer) no longer takes care of this.

Let's look at the issue in detail:

Upgrading to 10.1.3.4.1, this is what we find as a default behaviour in a simple table report:

And in a pivot:

Now think about the concept behind building an rpd on top of an Essbase source for a second. Ponder what Venkat wrote about aggregations in for 4-article series here, here, here and here. No longer utilizing Aggr_External and just no longer aggregating at all is just plain stupid. (Yes, I'm on kind of a rant here...)

Now to correct that quick & dirty in the report, we assign an aggregation rule in the column formula:

And...oh miracle...we have data again:

Now as I said, I find this nothing but retarded.
Why has this been changed? Is this actually expected behaviour? If so...what's the rationale behind that? Normally, aggregation rules set on the BMM layer always persist unless they are explicitly overwritten either in the column formula or for pivots in the measures. Why drop this all of a sudden?

My manual intervention is a workaround, but I'm facing several cubes and almost a hundred reports. Furthermore, one of the Account dimensions has more than 400 members. So manually changing 400 potentially mapped account members isn't fun...

So the solution (if I will actually keep 10.1.3.4.1 and not just drop if for a downgrade) would be to modify the BMM layer and change all normal account members to "Sum" and all percentages to "Avg" as aggregation rule.

Stay tuned for my upcoming rant on dimension members and filters. Another extremely intelligent change with 10.1.3.4.1.

Update:
Venkat reacted to my post here. I tested it and it seems that despite having label only top level members like he mentioned, some analyses seem to work with totals and grand totals. This will need more investigating.

Cheerioh,
Christi@n

Interjection

2009-05-19T16:45:00.002+02:00

I still haven't blogged about the BI Forum in Brighton, the things I saw there, the things I wanted to test afterwards OR the things I need to blog in detail to explain my ad-hoc speech...

...all because of bloody 10.1.3.4.1! God that release is a pain when you're using Essbase. I know it's bad form to blog about what you're about to blog about, but I will definitely fire off a huge rant with detailed descriptions soon.

Cheers,
Christi@n

OBIEE 10.1.3.4.1 is out

2009-04-25T13:37:00.002+02:00

John Minkjan has won the race for the first post on 10.1.3.4.1!

http://obiee101.blogspot.com/2009/04/obiee-10341-is-out.html
http://obiee101.blogspot.com/2009/04/obiee-10341-first-impressions.html

You just gotta love the little things he immediately found.

100% installation
old samplesales
BIP still not working OOB

Well let's hope that the background looks better and all the bugs have been fixed that plagued us in the base release. Off to searching for a list of fixed bugs and patches included.

Cheers,
Christi@n

Swiss readers?

2009-04-23T17:43:00.003+02:00

No use hiding, folks. I see that there's people from CH browsing my blog. Yes, and I've also seen the connection from Baden and the Oracle CH offices ;-)

So please leave a comment. I'd like to see who's actually interested in this here in Switzerland.

Cheers,
Christi@n

The ominous patch 8293083

2009-04-21T19:57:00.003+02:00

Greetings, programs!

Back on April 3rd, when I initially wanted to get patch #8293083, I hit a wall.

Description OBIEE GENERATED MDX CAUSES PERFORMANCE ISSUE
Product Oracle BI Suite EE
Release 10.1.3.4.0
Last Updated 18-MAR-2009

The patch had been made obsolete over night. So I raised an SR with Oracle. First response on the 14th:

"Bug 8293083 is a duplicate of Bug 8332167.

Bug 8332167 is fixed in 10.1.3.4.410
Also in patch for Linux ARU 11105211
No patch for Windows."

Sweet. So because it was a duplicate of a Linux patch the Windows patch was made obsolete? My mind boggles trying to understand that logic, but anyways I insisted on getting the patch and voilà! The patch exists again for Windows since the 17th.

Sounds good? No, not really.

8293083 is actually QF420 and in order to install it, you need to replace your whole OracleBI\server\bin folder. In itself, not an issue, but there's also patch 8290868 (QF410), which is a patch for 7482937 which resolves a rather serious issue for MDX generation for and/or filters.

The issue being that 8290868 - like 8293083 - replaces the whole bin folder. And Oracle still can't confirm that 8293083 is an incremental patch and includes all of 8290868.

So right now there's a choice.
a) Use 8293083, but not be sure that 8290868 and 7482937 function correctly

or

b) Use 8290868and 7482937 and wait for a final statement from Oracle.

I've gone for b) since it's extremely difficult to track down each and every weird behaviour in the MDX generation for all filters, EVALUATE statements, unions etc etc.

Of course I will keep you guys updated with my latest findings and Oracles statements.

End of line.

Troubleshooting problems with Target Level – Sampling Tables

2009-04-21T18:54:00.009+02:00

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.

Special characters in EVALUATE-wrapped MDX

2009-04-20T18:18:00.003+02:00

Greetings, programs!

Something I ran into recently is a little bug in the way OBIEE 10.1.3.4.0 produces MDX out of an EVALUATE wrapper when the MDX contains special characters. (yes, it is a bug)

Let's start with a basic and pure OBIEE report. We want to see the average coverage % by the 3rd generation of our product hierarchy:

Now let's switch out the presentation column for an EVALUATE function with MDX as what we really want is the average coverage % for the scenario "Actual":

EVALUATE('(%1.dimension.currentmember, [Scenario].[Actual],[Account].[Coverage % Avg]).value' as INTEGER, Product."Gen3,Product")

Let's run it.

"Unknown Member Coverage % Avg"? Liar. I see it directly in front of me. Off to checking the log:

With
set [Product3] as 'Generate([Product].Generations(2).members, Descendants([Product].currentmember, [Product].Generations(3), leaves))'
member [Account].[MS1] as '([Product].Generations(3).dimension.currentmember,[Scenario].[Actual],[Account].[Coverage % Avg])).value'
member [Account].[MS2] as 'RANK([Product].Generations(3).dimension.currentmember,[Product].Generations(3).members)'

select
{MS1,
MS2} on columns,
{{[Product3]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube.Ess931_ASO]

-------------------- Query Status: Query Failed: Essbase Error: Unknown Member Coverage % Avg used in query

Do you see the difference? While on the guy, it states "Coverage % Avg", the log and the MDX which is actually fired, reference "Coverage % Avg". TWO spaces!
So let's work around this by changing the EVALUATE to include the "StrToMbr" function:

EVALUATE('(%1.dimension.currentmember, [Scenario].[Actual],StrToMbr("[Account].[Coverage % Avg]")).value' as INTEGER, Product."Gen3,Product")

When running this, we get the correct results again:

So if you want to reference members containing special characters, use StrToMbr since otherwise you will get a non-functioning MDX from the BI server.

End of line.

Oracle - Sun

2009-04-20T16:07:00.002+02:00

Larry definitely has got too much money:

http://www.oracle.com/sun/index.html

I vote for naming his next yacht "Ra"...or even cooler. "Sol Invictus" ;-))

CAF update

2009-04-17T13:11:00.003+02:00

Ha...ha...ha... very funny.

Ok all. CAF is really ONLY supported with 10.1.3.4.1 right now and that's not out yet. Sorry to have gotten you all exited.

Cheers,
Christian

OTN: Content Accelerator Framework

2009-04-16T09:52:00.008+02:00

When going on OTN this morning, I saw something that I hadn't seen before in the download section of the OBIEE site. CAF?! Hmmm...

Of course I downloaded it (what a question!)...and I couldn't have had a nicer start for my day! The thing's called Content Accelerator Framework or in short: "CAF". Please update your geek brains, section acronyms, accordingly. It basically is a tool for facilitating migration of rpd and webcat objects between environments. Ace!

Direct links to the zip and the pdf documentation here:
http://download.oracle.com/technology/products/bi/files/OracleBIEE_CAFV1_Setup.zip
http://www.oracle.com/technology/products/bi/pdf/oraclebiee-cafv1-usage-instructions.pdf

Here's two quick screenshots from the pdf to give you an overview:

Off to trying it out!

Update:
Venkat beat me to it...hehe.

Update 2:
10.1.3.4.1? Really? Guys, we're not like you and have access to patches before they're released. ;-)

Cheers,
Christi@n

Screentoaster

2009-04-15T19:23:00.003+02:00

Justin just sent out this link on twitter:

http://www.screentoaster.com

Check out the demo. It's really cool for a free, no-download tool! Looking forward to using this the first time for one of my posts.

Cheers,
Christi@n

Dynamic multi-level filtering with MDX

2009-04-08T21:16:00.004+02:00

Greetings, programs!

Following up on my Essbase ranking post and a question from OTN, I’d like to elaborate on the ranking and filtering of the rank results.

Again, we start with a basic report showing the top10 products by actual sales. We’re not using OBIEE functions due to the performance constraints mentioned in the first article.

The core MDX function retrieving the dimension members being:
EVALUATE('TOPCOUNT(%1.members,10,[Actual])',Product."Gen6,Product")

Business now wants to implement filters on Gen2 through Gen5 of the product hierarchy in order to be able to more specifically choose their top10 products. This normally means four “is prompted” filters and a dashboard prompt containing Gen2 through 5 as hierarchically constrained prompt column. Let’s keep it simple and try one of the generations as a fixed filter before creating the prompts and testing in a dashboard context.

As already stated, the grain of the query changes to the one of the filter and renders the whole request useless.

MDX without filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

MDX with filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Product4] as 'Filter([Product].Generations(4).members, (([Product].CurrentMember.MEMBER_ALIAS = "SMU PF - Business Products" OR [Product].CurrentMember.MEMBER_Name = "SMU PF - Business Products")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Lets' forget about OBIEE filtering. This means that we have to do the filtering within the MDX and make that filter respond to the prompts. For this we will use presentation variables and a new MDX statement:

EVALUATE('TOPCOUNT(Intersect(%1.dimension.members,Descendants([@{vPromptedProdHier}{SMU}],[Product].[Gen6,Product])),10,[Actual])',Product."Gen6,Product")

And here's the prompt to go with it:

The multiple usage of the same presentation variable ensures that we will always have the lowest level currently chosen populated in the hierarchy, which will nicely adapt our MDX statement to constrain the results to the descendants of the chosen dimension member.

Family:

With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PF - Business Products],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Group:

With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PG - BP Video],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

End of line.

10.1.3.4.1 on the horizon

2009-04-06T16:50:00.004+02:00

Just got this update from metalink while working on an SR:

"If it is, please note that there is a patch for windows 32 bits to fix this bug in 10.1.3.4, and also this bug is fixed in the patch set 10.1.3.4.1 which should come out at the end of april.
Please note that a number of fixes will be included in the patch set 10.1.3.4.1, and it might be better to apply this patch when it comes out."

Finally. Currently it's a real "patchwork" (pun intended) with all the stuff flying around.

Cheers,
Christi@n

Notepad++ regexp / entended

2009-04-06T14:59:00.003+02:00

Gotta remember this link for once:

http://markantoniou.blogspot.com/2008/06/notepad-how-to-use-regular-expressions.html

w00t!

2009-04-03T22:31:00.001+02:00

Guys, check this out: http://oraclebizint.wordpress.com/2009/04/03/mr-winker-has-arrived/

How cool is that! All rush over and congratulate Venkat properly :-D

Cheers,
Christi@n

Patch day

2009-04-03T11:06:00.003+02:00

Still experiencing quite some connectivity issues with the Essbase integration. I've finally split the service requests, so that the Essbase team doesn't need to go through me every time.

Patches currently to be tested (alone and in conjunction):

7482937 <-- already running
7568985 <-- didn't really help so far
8290868
8293083

8293083 will be interesting since this morning when I logged in I saw that the SR has been obsoleted over night:

================
This patch is obsolete in this platform and language. Please see the reason stated below. Please contact Oracle Support for replacement patch.

Reason for Obsolescence
Additional fix required.
================

I wonder how they answer to my pending patch request now...

Cheers,
Christi@n

Does this layout make me look fat?

2009-04-02T19:09:00.001+02:00

/me points to the subject

Cleansing cube imports

2009-04-02T18:43:00.004+02:00

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.

Results

Drill

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.

Showing numbers as string while retaining the thousand separator

2009-04-01T12:46:00.003+02:00

Greetings, programs!

Something that came up on OTN:
Cast a metric (number) as char but still have the thousand separator. E.g.: if "Sales" > 1000 then "Nothing to report" else 1'000?

Here's a formula you can use:

case when (LENGTH(cast(Fact.Sales as char)) > 12) then
(SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 15 FOR 3) || '''')
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 9) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 12 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 6) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 9 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 0) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 6 FOR 7)
else 'Nothing to report' END

End of line.

GoURL with Essbase dimension members

2009-03-30T17:00:00.004+02:00

Little reminder for myself. GoURL doesn't like essbase generation names:

saw.dll?Go&Path=/users/administrator/SMU&Options=rd&Action=Navigate&P0=1&P1=eq&P2=Product."Gen2,Marketing Product"&P3=SMU'

Gotta URL encode it...

saw.dll?Go&Path=/users/administrator/SMU&Options=rd&Action=Navigate&P0=1&P1=eq&P2=Product.%22Gen2,Marketing Product%22&P3=SMU'

Update:
Unfortunately I can forget that Idea since I can't wrap the GoURL around an EVALUATE function. Keep you posted...

Creating your own variables with MDX in the rpd

2009-03-30T15:46:00.007+02:00

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:

DECLARE SESSION VARIABLE BLOCK "SUB_VAR_BLOCK_Ess931" AS "SUB_VAR_BLOCK_Ess931" CONNECTION POOL "Accounts"."Ess931 cube connection"
ASSOCIATED VARIABLES (
"SUB_VAR_BLOCK_Ess931"."Ess931:vCurQtr",
"SUB_VAR_BLOCK_Ess931"."Ess931:vCurMo",
"SUB_VAR_BLOCK_Ess931"."Ess931:vCurWk",
"SUB_VAR_BLOCK_Ess931"."Ess931:vCurYr" ) RP SCOPE REFRESH PERIODICITY 6000 DATABASE MAP (
{DefaultMulDB} AS {substitution variables} )
PRIVILEGES ( READ);

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:

SELECT {} ON COLUMNS,
{[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.

Confusion

2009-03-30T12:27:00.002+02:00

Ok, I feel I owe an explanation as to my choice of address for this blog since questions about it kept coming in.

In ancient Greek mythology (before the age of heroes, even before the age of gods), the hekatonkheires were three giants who possessed a hundred and fifty hands. They were the children of Gaia and Uranos and basically the first beings in existance after the chaos. Cast into Tartaros,

So while I could have chosen an address directly referencing the topic, I found it more fitting to pay hommage to these mythological entities. If we regard all the aspects that Oracle BI entails nowadays, you really need a hundred hands (or a hundred heads, depending on the source). And who would be better suited for a task like this than those guys.

Hope that answers your questions.

Christi@n
Cheers,

Ranking on Essbase from OBIEE

2009-03-27T16:20:00.006+01:00

Greetings, programs!

So I was doing some ranking reports on Essbase sources. From a rather simple request, this developed into a long quest for the best solution in terms of performance, usability, flexibility and making the thing dynamic. For anyone who faces this…here’s a quick overview of the steps I went through and my findings. You might need to scroll somewhat to get to the final solution.

The report was basically about getting the top N products by “actual versus forecast” “gross sales and backlog” (i.e. two measure hierarchies are taking part in this) on the lowest level of the product dimension with N being a prompted value.
The product dimension itself included two hierarchies: business and marketing. The split beginning at Generation2, the query should only look at the marketing hierarchy and then filter more detailed on the marketing units (Gen3) in a prompt.

Fair enough. So let’s create this using the normal functionalities in Answers.

The important bit being the “Rank” column:

topn(Scenarios."Act vs Forecast",@{vPromptedRanking}{10})

Firing this off against the datasource results in…a long while of me staring at “Retrieving data”. Performance is quite bad with this simple example taking 35 seconds. In my other use cases with more members and especially some nasty calculated members in the account hierarchy we are hitting up to 5 minutes in the dashboard with all the prompts and whatnot. Unacceptable. To the log(cave)!

+++Administrator:2e0000:2e0039:----2009/03/27 03:24:27
-------------------- Sending query to database named MyCube Scenarios (id: <<154586>>):
With
set [Account4] as 'Filter([Account].Generations(4).members, ([Account].currentmember.MEMBER_ALIAS = "Gross Sales & Backlog" OR [Account].currentmember.MEMBER_Name = "Gross Sales & Backlog"))'
set [Market3] as 'Filter([Market].Generations(3).members, ([Market].currentmember.MEMBER_ALIAS = "Europe" OR [Market].currentmember.MEMBER_Name = "Europe"))'
set [Product2] as 'Filter([Product].Generations(2).members, ([Product].currentmember.MEMBER_ALIAS = "SMU" OR [Product].currentmember.MEMBER_Name = "SMU"))'
set [Product8] as 'Generate({[Product2]}, Descendants([Product].currentmember, [Product].Generations(8),SELF), ALL)'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ([Time Period].currentmember.MEMBER_ALIAS = "Q4-09" OR [Time Period].currentmember.MEMBER_Name = "Q4-09"))'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Market3]},{[Time Period3]}),Scenario.[Act vs Forecast])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Market3]},{[Time Period3]}),Scenario.[Act vs Forecast %])', SOLVE_ORDER = 100
member [Scenario].[MS3] as 'AGGREGATE(crossjoin ({[Market3]},{[Time Period3]}),Scenario.[Forecast])', SOLVE_ORDER = 100
member [Scenario].[MS4] as 'AGGREGATE(crossjoin ({[Market3]},{[Time Period3]}),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2],
[Scenario].[MS3],
[Scenario].[MS4]
} on columns,
NON EMPTY {crossjoin ({[Account4]},{[Product8]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02

First thing we see: there’s no ranking. No restriction whatsoever with regards to the “Act vs Forecast”. What does this mean for the query? It will actually retrieve me all the members I’ve chosen for all rows in the query. Unconstrained except for the filters in the “With” statement. Not very performing to start with, but that full data stream is then received by the BI server, treated again and everything after number 10 cut off:

+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02
-------------------- Rows 1363, bytes 774184 retrieved from database query id: <<154586>>
+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02
-------------------- Physical query response time 34 (seconds), id <<154586>>
+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02
-------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 34, DB-connect time 0 (seconds)
+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02
-------------------- Rows returned to Client 10
+++Administrator:2e0000:2e0039:----2009/03/27 03:25:02
-------------------- Logical Query Summary Stats: Elapsed time 35, Response time 34, Compilation time 0 (seconds)

Basically the TopN function isn’t translated into its Essbase cousin TopCount which would have neatly given us 10 records right from the start. To cut the story short for this bit: I’ve gotten a confirmation from Metalink: “1. Yes none of the Answer based functions are translated into MDX functions for 10G. This may be implemented in 11g.” Bugger.

While the SR was pending I already had started with my alternative approach. Do whatever it takes to constrain the data set with the obvious choice being the usage of TopCount. To test faster I shrink the report. The prompts are kicked out and only generic filters kept.

Major change is the usage of an EVALUATE function which retrieves me the dimension members I want:

EVALUATE('TOPCOUNT(%1.members,10,[Act vs Forecast])',Product."Gen8,Product")

Let’s look at the query:

+++Administrator:2e0000:2e0043:----2009/03/27 04:20:07
-------------------- Sending query to database named MyCube Scenarios (id: <<160359>>):
With
set [Account4] as 'Filter([Account].Generations(4).members, ([Account].currentmember.MEMBER_ALIAS = "Gross Sales & Backlog" OR [Account].currentmember.MEMBER_Name = "Gross Sales & Backlog"))'
set [Market3] as 'Filter([Market].Generations(3).members, ([Market].currentmember.MEMBER_ALIAS = "Europe" OR [Market].currentmember.MEMBER_Name = "Europe"))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ([Time Period].currentmember.MEMBER_ALIAS = "Q4-09" OR [Time Period].currentmember.MEMBER_Name = "Q4-09"))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(8).members,10,[Act vs Forecast]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account4]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual)', SOLVE_ORDER = 100
select
{ [Scenario].[MS1]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Ok. 10 rows queried from Essbase. Performance is at 10 seconds for the whole report Next step: I bring back the prompts constraining on the marketing branch of the hierarchy and the marketing units in particular:

-------------------- Sending query to database named MyCube Scenarios (id: <<84494>>):
With
set [Account4] as 'Filter([Account].Generations(4).members, ([Account].currentmember.MEMBER_ALIAS = "Net Sales & Backlog" OR [Account].currentmember.MEMBER_Name = "Net Sales & Backlog"))'
set [Market3] as 'Filter([Market].Generations(3).members, ([Market].currentmember.MEMBER_ALIAS = "Europe" OR [Market].currentmember.MEMBER_Name = "Europe"))'
set [Product2] as 'Filter([Product].Generations(2).members, ([Product].currentmember.MEMBER_ALIAS = "SMU" OR [Product].currentmember.MEMBER_Name = "SMU"))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ([Time Period].currentmember.MEMBER_ALIAS = "Q4-09" OR [Time Period].currentmember.MEMBER_Name = "Q4-09"))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,5,[Act vs Forecast]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account4]},crossjoin ({[Market3]},crossjoin ({[Product2]},{[Time Period3]}))),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Cute. If I combine TopCount with OBIEE filters and both of them are based on the same dimension, the aggregation level is changed to the one of the filter criteria. Since I use SMU, all numbers are retrieved for this level. Lesson learned.

New MDX:

EVALUATE('Intersect(TopCount(Descendants([SMU],6,SELF),10,[Act vs Forecast]),%1.dimension.members)',Product."Gen8,Product")

This one’s a bit more complex to read, but we basically force an intersection of the top 10 descendants of the marketing hierarchy starting member called SMU, 6 generations down (since SMU is generation 2) with the lowest level product.

The results are nothing but staggering in terms of performance increase.

+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Sending query to database named MyCube Scenarios (id: <<158447>>):
With
set [Account4] as 'Filter([Account].Generations(4).members, ([Account].currentmember.MEMBER_ALIAS = "Gross Sales & Backlog" OR [Account].currentmember.MEMBER_Name = "Gross Sales & Backlog"))'
set [Market3] as 'Filter([Market].Generations(3).members, ([Market].currentmember.MEMBER_ALIAS = "Europe" OR [Market].currentmember.MEMBER_Name = "Europe"))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ([Time Period].currentmember.MEMBER_ALIAS = "Q4-09" OR [Time Period].currentmember.MEMBER_Name = "Q4-09"))'
set [Evaluate0] as '{Intersect(TopCount(Descendants([SMU],4,SELF),10,[Act vs Forecast]),[Product].Generations(6).dimension.members) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account4]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Act vs Forecast])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Query Status: Successful Completion
+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Rows 10, bytes 2720 retrieved from database query id: <<158447>>
+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Physical query response time 0 (seconds), id <<158447>>
+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds)
+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Rows returned to Client 10
+++Administrator:2e0000:2e0040:----2009/03/27 04:04:14
-------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds)

All is done in less than one second. 10 rows received, 10 rows treated. Nice. Let’s make this MDX dynamically reacting to two different prompts for hierarchy and unit as well as the TopN:

For switching hierarchies:
EVALUATE('TopCount(Intersect(Descendants([@{vPromptedHier}{SMU}],6,SELF),%1.dimension.members), @{vPromptedRanking}{10},[Act vs Forecast])',Product."Gen8,Product")

And for switching the actual units:
EVALUATE('TopCount(Intersect(Descendants([@{vPromptedSMU}{Business Markets}],5,SELF),%1.dimension.members), @{vPromptedRanking}{10},[Act vs Forecast])',Product."Gen8,Product")

Seems perfect. It does everything I need, is dynamic and flexible enough and is quick. So basically you could stop reading here.
Alas, checking the actual result sets I see some differences between the OBIEE TopN based report and the MDX TopCount based one:

TopCount

TopN

The highest in TopCount is only fourth in the TopN while 915-000036, 984-000061 and 984-000061 have been dropped from the TopCount. Finding the reason for this was a bit tedious, but those three members are actually shared members.
My whole product dimension is basically an unbalanced hierarchy. The business hierarchy using the products as its lowest level in generation 7 while the marketing hierarchy uses the products as lowest level in generation 8. Logically inconsistent levels (since the groupings etc. differ) – CHECK. Inconsistent depth (7 vs. 8 generations) - CHECK. With most products being shared members on one branch or the other, this kills TopCount.

I have the feeling that transforming the hierarchy into a ragged one would solve the issue since both branches would end on generation 8 (the target of my MDX).
But let’s continue down the road where we have control over the bits and pieces: I’m waiting on input on the MDX itself from a new service request I raised. But since I still need a viable solution that supports all my requirements and is fast (everything beyond 10 seconds is slow for me).

After some trials I meet halfway between the OBIEE and MDX solution:

The “Products” column still holds the MDX:

EVALUATE('ORDER(Intersect(%1.dimension.members,Descendants([@{vPromptedSMU}{Business Markets}],5,SELF)), [Act vs Forecast],BDESC)',Product."Gen8,Product")

It now retrieves descendant products of the prompted marketing unit, ordered by actual vs. forecast. This will still retrieve quite some records, but we have the certainty that even shared members are included.

My search for the TopN now happens again over the filters.

rank(Scenarios.Act vs Forecast) is less than or equal to @{vPromptedRanking}{10}

I left all additional calculated members etc. in, so you can see that even with the added payload, this is a way to go. So let’s have a look at the log:

-------------------- Sending query to database named MyCube Scenarios (id: <<174885>>):
With
set [Account4] as 'Filter([Account].Generations(4).members, ([Account].currentmember.MEMBER_ALIAS = "Gross Sales & Backlog" OR [Account].currentmember.MEMBER_Name = "Gross Sales & Backlog"))'
set [Market3] as 'Filter([Market].Generations(3).members, ([Market].currentmember.MEMBER_ALIAS = "Europe" OR [Market].currentmember.MEMBER_Name = "Europe"))'
set [Evaluate0] as '{ORDER(Intersect([Product].Generations(8).dimension.members,Descendants([SMU - Business Markets],5,SELF)), [Act vs Forecast],BDESC) }'
member [Scenario].[MS1] as 'AGGREGATE({[Market3]},Scenario.[Forecast])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE({[Market3]},Scenario.[Act vs Forecast])', SOLVE_ORDER = 100
member [Scenario].[MS3] as 'AGGREGATE({[Market3]},Scenario.[Act vs Forecast %])', SOLVE_ORDER = 100
member [Scenario].[MS4] as 'AGGREGATE({[Market3]},Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2],
[Scenario].[MS3],
[Scenario].[MS4]
} on columns,
NON EMPTY {crossjoin ({[Account4]},{[Evaluate0]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Query Status: Successful Completion
+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Rows 603, bytes 342504 retrieved from database query id: <<174885>>
+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Physical query response time 6 (seconds), id <<174885>>
+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 6, DB-connect time 0 (seconds)
+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Rows returned to Client 10
+++Administrator:2e0000:2e006c:----2009/03/27 05:33:28
-------------------- Logical Query Summary Stats: Elapsed time 7, Response time 6, Compilation time 0 (seconds)

6 seconds is an acceptable performance for an interim solution. What’s left now is to replace the normal navigation on dimension members retrieved through the rpd with goURL commands on the column populated by the MDX. Oh yeah…and to hope the service request will give a solution for the Topcount issue with shared members.

Thanks to Pradeesh for sharing the workload and also trying out some of the weirder options I came up with.

End of line.

Oracle: Total BI

2009-03-27T15:54:00.003+01:00

Hello and welcome!

Last Saturday I had a nice chat with the guys from RittmanMead and Jon was inquiring as to why I'm not blogging my findings and thoughts, which I frequently send out. Prime reason - and he fully agreed with that - is the "assumed knowledge" factor.

When I note something for myself (i.e. offline braindumping), I can't help but wonder whether it would be "blog-worthy" or not. Obviously the answer normally was: "Naah. That's too basic" or "That's way too case-specific to be explained in a way useful to others". Some time afterwards, lo and behold! Venkat, Mark, John or one of the other usual suspects write about since they stumbled upon it themselves, someone asked the question (for the hundredth time) on OTN or because they're working through their collected project post-its.

So he got me thinking. Then this week: enter Chet Justice (aka oraclenerd). He basically asked his audience to "pressure" me into blogging.

Well Jon, Chet (and all the others who kept asking the same)...here I am.

Cheers guys!
Christi@n