OBIEE 10G - A special subject area to handle the dashboard parameters prompts

About

All the prompts in OBIEE are column based prompt. You can't create a list 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 :

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 dummy table 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

Articles Related

Creation of the Parameter Table

In this example, we show the creation of a parameter table with a dummy table 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

Second, you need to create the column report_level from the sql statement.

click on the column tab and create it

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

and create a new complex join between them.

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

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

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

and you get :

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.