Business Intelligence – Oracle

Oracle BI EE 10.1.3.3.2 – Handling Sort Order in Hyperion Essbase 9.3.1 – EVALUATE and MDX

Posted by Venkatakrishnan J on April 28, 2008

Another common question that one would get while working with BI EE and Hyperion Essbase is “How to have the default sort order specified in an Essbase Cube to be available in BI EE reports?”. This is a very valid question since by default BI EE sorts the dimension attributes alphabetically. For example look at the screenshot below,

    

As you see above, BI EE by default has alphabetically sorted the channels report. But if we look at the outline of the channels dimension, the sorting order would be completely different. In most cases, we would like to have the same sort order as the cube (not an alphabetical sort).

    

In order to overcome this, there are 2 approaches. Both of them would involve the use of EVALUATE function and then passing an MDX function to the cube to fetch the rank of each dimensional attribute. So, lets look at the first approach. In this approach we shall be adding a new column and in the formula enter the below formula

EVALUATE(‘RANK(%1.dimension.currentmember,%2.members)’ AS INTEGER,CHANNELS.”Gen4,CHANNELS”,CHANNELS.”Gen4,CHANNELS”)

What this basically does is, it passes the member of the dimension to the MDX function RANK and retrieves the position of the member in the particular level.

    

    

Now hide this new column and apply sorting on it. This will give the default sort order as available in the Essbase Cube.

    

The problem with the above approach is that the sort order would have to be created for each and every report. In order to overcome that, create another logical column in the repository with the evaluate function and then apply sort order on the 4th level based on this column.

    

    

Also, ensure that you have assigned an hierarchy level to the new column.

    

    

Thanks to Alan Lee for sharing this. One can extend this to provide lot of different functionalites that BI EE does not offer out of the box.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3.2 – Handling Sort Order in Hyperion Essbase 9.3.1 – EVALUATE and MDX”

  1. Alex Mendelev said

    Hi Venkat,
    Thanks for your helpful blog!

    I’ve been using your tip for members sorting and it worked just fine.
    For some reason it doesn’t work for me anymore.

    The sort order column expression does not always return values. For some members I get nulls.
    I tried to modify the function:

    EVALUATE(‘Rank(%1.dimension.currentmember,%2.dimension.currentmember.siblings)’ AS INTEGER , PNL.DIM_ACCOUNT.”Gen4,DIM_ACCOUNT”, PNL.DIM_ACCOUNT.”Gen4,DIM_ACCOUNT”)

    Now it returns values for all the members, but the numbers are not unique. – I get result = 1 for several children of the same parent.

    Do you have any idea – what can be the problem?

    For us it’s a real showstopper.

    Best Regards,
    Alex

  2. Rich said

    Does anyone know if this same functionality is available with Essbase 7.1.6? I have been unable to get any MDX to run via OBIEE to Essbase 7.1.6. I pretty sure the RANK function is unsupported in this version of Essbase, but I’ve tried to do a simple MDX query to return the member name and it errors out.

    My sample below is for a time dimension that contains Year, Qtr and Month levels and I’m running this from answers as a formula. I’m not too familar with MDX, so maybe I have the syntax all screwed up.

    evaluate(‘%1.dimension.CurrentMember.Name’ as char(10), Time.”Month”)

    The error is as follows:

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. Essbase Error: Syntax error in input MDX query on line 3 at token ‘Name’ (HY000)

    SQL Issued: SELECT Time.”Month” saw_0, evaluate(‘%1.dimension.CurrentMember.Name’ as char(10), Time.”Month”) saw_1 FROM “EBIT Forecasts” ORDER BY saw_0, saw_1

    Thanks in advance for any help,
    Rich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: