apps-infra

    Forum Stats

  • 3,873,729 Users
  • 2,266,635 Discussions
  • 7,911,624 Comments

Discussions

OBIEE 12c: Substring Dashboard prompt

Carsten Weber
Carsten Weber Member Posts: 67 Blue Ribbon

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

Tagged:
Carsten WeberThomas Dodds

Best Answer

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Nov 9, 2018 6:50AM Answer ✓

    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.

    Carsten WeberCarsten Weber

Answers

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Nov 9, 2018 6:50AM Answer ✓

    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.

    Carsten WeberCarsten Weber
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Nov 9, 2018 6:56AM

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

    Carsten WeberCarsten Weber
  • Thomas Dodds
    Thomas Dodds Member Posts: 1,698
    edited Nov 9, 2018 7:35AM

    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 Member Posts: 4,535 Bronze Crown
    edited Nov 9, 2018 8:47AM

    Better yet in the ETL!

    Thomas Dodds
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Nov 9, 2018 9:13AM

    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 Member Posts: 4,535 Bronze Crown
    edited Nov 9, 2018 9:18AM

    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 Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Nov 9, 2018 9:19AM

    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 Member Posts: 67 Blue Ribbon
    edited Nov 12, 2018 5:39AM

    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 Everything Analytics And Data Member Posts: 9,689 Gold Crown
    edited Nov 12, 2018 2:36PM
    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.

apps-infra