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
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
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 :
- 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
As we use a dual or a skinny table as source table, the performance are really good.
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.