Uncategorized

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.

Uncategorized

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.

Uncategorized

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

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