Monday, March 30, 2009

Creating your own variables with MDX in the rpd

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.

No comments:

Post a Comment