Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE 12c: Substring Dashboard prompt

Received Response
101
Views
9
Comments
Carsten Weber
Carsten Weber Rank 5 - Community Champion
edited August 13 in Oracle Analytics Cloud and Server

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 Rank 8 - Analytics Strategist

    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
    Gianni Ceresa Mod

    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 Rank 8 - Analytics Strategist

    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 Rank 8 - Analytics Strategist

    Better yet in the ETL!

  • Gianni Ceresa
    Gianni Ceresa Mod

    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 Rank 8 - Analytics Strategist

    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
    Gianni Ceresa Mod

    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
    Carsten Weber Rank 5 - Community Champion

    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
    Christian Berg-0racle Rank 10 - Analytics Guru 
    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.