obiee

Uncategorized

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

Uncategorized

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

Uncategorized

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

Uncategorized

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!

Uncategorized

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…

Uncategorized

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

Uncategorized

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

Uncategorized

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