Kevin McGinley from Red Pill Analytics wrote nice 2 posts about OBIEE and Direct Database requests (Part1 and Part2 links). In case you don't have 17 minutes to read both, here is his "Readers Digest" on YouTube. Since the contexts of the posts was his OOW14 presentation on Self Service BI, he didn't cover my favorite part of OBIEE's Direct Database request - the ability to use Presentation Variables in the select statement, and as a result natural integration of the result in a dashboard. This is the topic of the following post.
While the analysis itself can be based on Direct Database Request, the prompt must be based on a Subject Area. I created one based on the default Oracle database SH schema.
And created a simple Dashboard Prompt with a Presentation Variable P_Class:
The simple analysis will include a where statement with the Presentation Variable.
select Channel_desc, Channel_class, '@{P_Class}' PV from sh.channels
where Channel_class ='@{P_Class}{Direct}'
but since I want the Presentation Variable to handle Multiple Values, I will change the code slightly (see OBIEE 11g Tips and Tricks blog for explanation)
select Channel_desc, Channel_class, '@{P_Class}' PV from sh.channels
where Channel_class IN (@{P_Class}['@']{'Direct'})
The result in the dashboard is working fine:
While the analysis itself can be based on Direct Database Request, the prompt must be based on a Subject Area. I created one based on the default Oracle database SH schema.
And created a simple Dashboard Prompt with a Presentation Variable P_Class:
select Channel_desc, Channel_class, '@{P_Class}' PV from sh.channels
where Channel_class ='@{P_Class}{Direct}'
but since I want the Presentation Variable to handle Multiple Values, I will change the code slightly (see OBIEE 11g Tips and Tricks blog for explanation)
select Channel_desc, Channel_class, '@{P_Class}' PV from sh.channels
where Channel_class IN (@{P_Class}['@']{'Direct'})
The result in the dashboard is working fine:
This post doesn't cover the "All column values" option in prompt.
Great tip, Boris. I agree - this opens up more possibilities for the usage of the Direct Database Request functionality. I will edit my post to link to this page.
ReplyDeleteSomething I always put as a caveat for DDBRs:
ReplyDeleteIf you want to bullet-proof them and encapsulate their usage for analysts, it's sometimes not a bad idea to shove them into an opaque view in the RPD.
More security control, deeper integration (think integration into models) and no need to give this ability to end users.
Still, I love the capabilities that DDBR gives devs and admins simply due to its literal interpretation of code which allows us to write SQL, MDX or pure server function calls directly!
Thanks for Information OBIEE11g Online Training
ReplyDeletethis post very helpful to me
ReplyDeleteThanks for the post. Is there a way we can modify this syntax to use for multiple values as well as 'All Column Values'?
ReplyDeleteThis is just what I was looking for! I have one further request. I don't want to default to a specific value. In my code I have it as {'%'} however it doesn't return any data unless I enter information in the prompt. Suggestions?
ReplyDeleteYou can always wrap it in some case statement:
Delete"Column_x"=(case prompt_value='' then "column_x' else prompt_value end)
Using a DDBR I am trying to embed a link to another dashboard page which has a prompt based on a presentation variable i.e. I have a field with an ID which I need to wrap in HTML to open up a second page that filters to that ID. Your help would be appreciated.
ReplyDeletePlz tell for All columns values
ReplyDeletethanks very much .. my requirement is bit different , I need to select one month from the prompt and I want that the previous 2 months also get automatically selected and the value of all 3 months are passed in presentation variable
ReplyDeleteTop! Thank you very much
ReplyDelete