OBIEE 12c: Substring Dashboard prompt

Carsten Weber

Hi guys,

fist of all I want to state my versions I am using for the upcoming scenario / question:

OBIEE: Version 12.2.1.1.0

OBI Administration Tool: Version 12.2.1.1.0

Oracle Essbase: Version 11.1.2.4.008.28

We are trying to implement a dashboard prompt which is directed to an analysis within Oracle BI Anwsers.

The data retrieved is stored within a dimension in our Oracle Essbase cube.

Dimension name: Station

Data value pattern: AP_ABC, AP_DEF, AP_GHI, etc. (where AP_ is a fix component / prefix)

Now we are trying to substring all values within the dimension without showing the prefix using the following formula within the dashboard prompt: SUBSTRING(Station FROM 4 FOR 3)

Within the dashboard it then shows me the correct values for selection: ABC, DEF, GHI, etc.

The problem occurs when commiting the prompt as the analysis does not show any results.

I have already searched within the OTN but did not find any solution so far. Maybe someone can help me out on this matter?

Best regards and thanks in advance

Carsten

Robert Angel

Hi,

use your dashboard prompt to populate a presentation variable.

Put the filter on your corresponding column as

YourColumn like '%'||'@{YourPresentationVariableHere}{AnyDefaultHere}'

Note; this works, but may run slower than using the explicit values as any indexes will be ignored.

Gianni Ceresa

Hi,

If you have the same exact formula both in the prompt and in the analysis the "is prompted" is still supposed to match.

The best way to find out why you don't get any result from the analysis is in the logs: what query does the analysis execute? What value does it use as filter? On which column? Just check that and you will probably find out your issue ... (like applying the 3 letters codes to the original AP_xxx values etc.).

Thomas Dodds

in the logical layer break it out 

Station = PA_ABC

and Station Prefix = PA

and Station Suffix = ABC

and be done with it

Robert Angel

Better yet in the ETL!

Gianni Ceresa

In theory yes, ETL wins.

In this case it's Essbase, so unique member name / alias rule apply and no ETL will help.

Robert Angel

My mistake - I missed that part - though I have used ETL in the past to move Essbase cubes into database tables in the past and found it superior in so many ways, largely thanks to OBIEE's lamentable short comings when it comes to MOLAP.

Gianni Ceresa

That's the feeling with 80% of Essbase cubes

But sometimes we are lucky enough to find cubes designed for analysis ... (ok, maybe it isn't really 20% but more like 2-5% )

Carsten Weber

YourColumn like '%'||'@{YourPresentationVariableHere}{AnyDefaultHere}'

Unfortunately Essbase seems not to support "LIKE"-clause.

Therefore I tried using a simple "IN"-clause in combibation with concating "AP_" in my SQL statement which results in the fact that I am only able to choose one single option in my dashboard prompt.

Anyways I will try achieving my target by making some adjustments within the Essbase cube.

Christian Berg-0racle

Carsten Weber wrote:Anyways I will try achieving my target by making some adjustments within the Essbase cube.

OBI and Essbase work together but you still need to get Essbase to play nice by...simply changing it :-)

Edit: That said - there are loads of things that you can achieve with MDX and EVALUATE as well.