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.
