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.

Uncategorized

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.

Uncategorized

Little reminder for myself. GoURL doesn’t like essbase generation names:

saw.dll?Go&Path=/users/administrator/SMU&Options=rd&Action=Navigate&P0=1&P1=eq&P2=Product.”Gen2,Marketing Product”&P3=SMU’

Gotta URL encode it…

saw.dll?Go&Path=/users/administrator/SMU&Options=rd&Action=Navigate&P0=1&P1=eq&P2=Product.%22Gen2,Marketing Product%22&P3=SMU’

Update:
Unfortunately I can forget that Idea since I can’t wrap the GoURL around an EVALUATE function. Keep you posted…

Uncategorized

Greeting, programs!

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
– cube
The 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:

DECLARE SESSION VARIABLE BLOCK “SUB_VAR_BLOCK_Ess931” AS “SUB_VAR_BLOCK_Ess931” CONNECTION POOL “Accounts”.”Ess931 cube connection”
ASSOCIATED VARIABLES (
“SUB_VAR_BLOCK_Ess931″.”Ess931:vCurQtr”,
“SUB_VAR_BLOCK_Ess931″.”Ess931:vCurMo”,
“SUB_VAR_BLOCK_Ess931″.”Ess931:vCurWk”,
“SUB_VAR_BLOCK_Ess931″.”Ess931:vCurYr” ) RP SCOPE REFRESH PERIODICITY 6000 DATABASE MAP (
{DefaultMulDB} AS {substitution variables} )
PRIVILEGES ( READ);

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.

SELECT {} ON COLUMNS, LastPeriods (8, [&vCurMo]) ON ROWS FROM Ess931_ASO.MyCube

Unfortunately, while the query runs fine, the result looks like this:

Ok so back to what we had before and let’s adapt that:

SELECT {} ON COLUMNS,
{[Time Period].[&vCurMo].Lag(1),
[Time Period].[&vCurMo].Lag(2)
} ON ROWS FROM Ess931_ASO.MyCube

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.

End of line.