Uncategorized

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

Uncategorized

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

Uncategorized

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.

Uncategorized

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

Uncategorized

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 😀

Cheers,
Christi@n

Uncategorized

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

Uncategorized

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.