Obiee – A special subject area to handle the dashboard parameters prompts


All the prompts in OBIEE are column based prompt. You can’t create a prompt if you don’t select a column. And as it’s the only way to set up a variable for a formula, it’s impossible to bypass this constraint.

In our case, we don’t care about the value in the dimension column as we want parameter values as for instance the hierarchy level :

{{:dat:obiee:presentation_service:obiee_parameter_prompt_result.jpg|}}

The idea behind the scene is :
– to create a column which is on the top of a dual view (or skinny table) in the physical layer.
– to create a dummy business model because BI Server requires us to have a minimal of one logical join.
– to create a special subject area (presentation catalogue) to separate the functional of the parameters columns
– to be able to choose this column in order to create one or several others parameter prompts.

The advantages of this solutions are that :
* as you query a skinny table (or dual view), the performance are very good
* as you use a column, you can translate in other languages the label of the parameter prompt
* you can add a parameter value without changing the original dashboard prompt

===== Creation of the Parameter Table =====

In this example, we show the creation of a parameter table with a dual view but you can choose to create a table to store your parameters.

* In the physical layer, right click on a physical schema and choose New Physical Table
* select “Select” as Table Type from the drop down menu
* and enter the Sql below

Example :

SELECT 'MONTH' AS REPORT_LEVEL FROM DUAL
UNION
SELECT 'QUARTER' AS REPORT_LEVEL FROM DUAL

{{:dat:obiee:presentation_service:obiee_dummy_table.jpg|}}

Second, you need to create the column report_level from the sql statement.
* click on the column tab and create it

{{:dat:obiee:presentation_service:obiee_dummy_table_columns.jpg|}}

If you choose this column in a query, the values MONTH and QUARTER will be returned.

===== Creation of the Business Model Layer =====
* Drag and drop **TWO** times this table in order to create two logical table source :
* Parameters1
* Parameters2
* Select this two tables,
* Right click / Business Model Diagramm / Selected Tables only
{{:dat:obiee:presentation_service:obiee_dummy_business_model.jpg|}}
* and create a new complex join between them.
{{:dat:obiee:presentation_service:obiee_dummy_model_2.jpg|}}

This step is important beacause Obiee require that a fact table has minimum one complex logical join

===== Creation of the Presentation Catalog =====
* Right click in the presentation layer / New Presentation Catalog and name it Parameter
* Just drag and drop one of the two logical table and you are done.
* You can cache this new presentation catalog to the others users by giving the correct privilege. In the Oracle BI Presentation Dashboard / Administration / Manage Privileges.
* Reload the metadata in the BI Presentation Dashboard in Administration / Reload Files and metadata

{{:dat:obiee:presentation_service:obiee_parameter_subject_privilege.jpg|}}

In the picture above, you can see that only the Presentation Server Administrator have the right to the Parameter Area but everybody can use a object (report, prompt) create with it

===== Creation of the dashboard prompt =====

Now that we have our parameter column in our parameter subject area, it’s possible to choose it in order to create a parameter prompt.

* Go to answer
* Click on the icon : New Dashboard prompt on the left side
* And select the subject area Parameter
{{:dat:obiee:presentation_service:obiee_new_dashboard_prompt.jpg|}}
* in the left frame, select the column of the select table (in our case report_level)
* and define the options that you need as the setting of a presentation variable
{{:dat:obiee:presentation_service:obiee_parameter_prompt.jpg|}}
* and you get :
{{:dat:obiee:presentation_service:obiee_parameter_prompt_result.jpg|}}

===== What about now if I don’t want MONTH and QUARTER but MONTH and YEAR ? =====

You can change the values in your parameter table or you can use the SQL value capabilities of the prompt :
* Go back to your prompt
* select SQL Value in the show result
* and tape this SQL :

SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE 'MONTH' END FROM Parameter
UNION
SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE 'YEAR' END FROM Parameter

As one is always different of zero, you will obtain MONTH and YEAR as value in the drop down list.

You can not enter this SQL :

SELECT 'MONTH' FROM Parameter
UNION
SELECT 'YEAR' FROM Parameter

because Parameter describe a presentation catalog and not a table. In the Sql of OBIEE, you really need to have for each column the name of the table otherwise you will obtain this error :

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 42021] The query does not reference any tables. (HY000)
SQL Issued: SELECT ‘MONTH’ FROM Parameter

===== Performance =====
As we use a dual or a skinny table as source table, the performance are really good.

===== Conclusion =====
You can add to your parameter subject area one column by parameter or you can change the values with the SQL capabilities from the prompt. In the two case, a sql statement against a little table of Oracle is performed and doesn’t therefore require to read data by an I/O operation on a disk. You can therefore choose your preferred solution but keep in mind that one column for one dashboard prompt is the natural Obiee mechanism and permit a good translation in other languages.

Tags: ,

10 Responses to “Obiee – A special subject area to handle the dashboard parameters prompts”

  1. Srini says:

    Thanks for the article. I have done the same thing in the past but was just wondering if it can be achieved using logical sql in the presentation server rather than having to change the RPD. Did you try doing that? Thanks

  2. gerardnico says:


    As explain in the section “What about now if I don’t want MONTH and QUARTER but MONTH and YEAR ?”, you must use this kind of SQL formula.
    In the show column of the dashboard prompt, select SQL Value and tape it.

    SELECT CASE WHEN 1=0 THEN Table.Column ELSE 'MONTH' END FROM SubjectArea
    UNION
    SELECT CASE WHEN 1=0 THEN Table.Column ELSE 'YEAR' END FROM SubjectArea

  3. Krish says:

    Hi Gerard,

    Nice article. A novice question. Once the user selects the MONTH or Quarter how do you join to the Fact TAble ? I mean how the toggle is reflected in the fact joins.

  4. gerardnico says:

    You use them to set a presentation variable.
    With this variable, you can use the Guided Navigation to show only the reports for the accurate level : MONTH or QUARTER.
    The goal is not to modify the physical query.

    Good day
    Nico

  5. Danail says:

    Hi Gerard,

    This is great stuff here. Is it possible to use this apporach to assing a value to a presentation variable that is different from the one chosen by the prompt? E.g. the promt is 05-2010 and the presentation variable to be used in the dashboards to have end-of-month value 05/31/2010? What would be your advise on that?

    Thank you very much!

    Cheers,
    Danail

  6. gerardnico says:

    No. What you want to do in background is that when the user select the prompt, he/she knows that he/she select in reality a date.

    You have then a logic and where you have a logic, you have a transformation.

    This transformation can be:
    - in the time dimension table with an extra boolean column to indicate that it’s the last day of the month
    - a function to transform a month in a date and the most difficult is that you have to get the last date of the month

    To create the function, you can try:
    - the logical SQL of BI Server depending of the data type (number of string) of the month column
    - or create a database function and use the evaluate function to call it.

    Not easy but feasible.

    Good luck
    Nico

  7. DashboardPrompts says:

    We have a dashboard prompt that has the case statement in it. Then that sets the presentation variable. The presentation variable is used then in a forumla column which dynamically chooses the column the user wanted. The issue is that our other prompts which have “Constrain” checked no longer constrain. If we remove this workaround, then constrain works again. Are you aware of this limitation. Is this because the case statement was not done in the business model as you have indicated in this example. Thanks.

  8. DashboardPrompts says:

    Ah, it is working now. The CASE statement we used was selecting against an account dimension column. I changed it to select against an Invoice Dimension column and it worked. Not sure why this had any effect.

  9. John Lilley says:

    The problem with this solution is that it relies on presentation variables. These do not allow the use of ‘all values’ and every column in the prompt will need to be selected, otherwise the presentation variable is not set and the report brings back nothing.

  10. gerardnico says:

    Thanks for the feed back.

    I set in the article a presentation variable but it’s not mandatory. May be I was not enough explicit.

    This solution is not based on presentation variable but on a source table (In my example, I have created a view but you can also use an XML table). In this case, you have all possibilities (included the all values) provided by presentation service (dashboard, answer and prompt). Just check the All values check-box and don’t set a presentation variable and it must works.

    Cheers
    Nico

Leave a Reply