Oracle Business Intelligence

Products Banner

OBIEE 12c: Substring Dashboard prompt

Received Response
79
Views
9
Comments

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

Answers

  • Robert Angel
    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.

  • 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
    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
    Robert Angel ✭✭✭✭✭

    Better yet in the ETL!

  • 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
    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.

  • 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% )

  • 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.

  • 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.