Saturday, April 25, 2009

OBIEE 10.1.3.4.1 is out

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

Thursday, April 23, 2009

Swiss readers?

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

Tuesday, April 21, 2009

The ominous patch 8293083

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

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.

Monday, April 20, 2009

Special characters in EVALUATE-wrapped MDX

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

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" ;-))

Friday, April 17, 2009

CAF update

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

Thursday, April 16, 2009

OTN: Content Accelerator Framework

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

Wednesday, April 15, 2009

Screentoaster

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

Wednesday, April 8, 2009

Dynamic multi-level filtering with MDX

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.

Monday, April 6, 2009

10.1.3.4.1 on the horizon

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

Gotta remember this link for once:

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

Friday, April 3, 2009

w00t!

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

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

Thursday, April 2, 2009

Does this layout make me look fat?

/me points to the subject

Cleansing cube imports

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.

Wednesday, April 1, 2009

Showing numbers as string while retaining the thousand separator

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.