OBIEE, OBI Apps, ODI, Informatica, Essbase, BI Publisher...you name it. The OBI suite entails them all and more.
I will talk mainly about its technical aspects, interesting findings and noteworthy solutions, but will definitely throw in some random weirdness.

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 - cubeThe 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:

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.

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.

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.

Friday, March 27, 2009

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)!

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:

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:

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:

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.

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.

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).

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:

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.

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.