For multiple columns in a dashboard prompt, you can “constrain” the values in each prompt based on the value selected in the preceding prompt.
If the columns used in the prompts come from different dimension tables, then OBIEE does the constraining by using the joins between the FACT and DIMENSION tables.
For eg:
Consider a dashboard prompt that uses two columns
1. EMPLOYEE_LOGIN from the EMPLOYEE_D table and
2. PRODUCT_NAME. from PRODUCT_D table.
This join between the dimension
and FACT table to populate the prompt values may prove to be very taxing if
your FACT table is very large, and it might take several minutes to
populate the constrained prompt values.
Let us see how we can
implement a workaround to this by populating the values of the constrain
prompt from a different table in a different subject Area thereby avoid
querying the FACT table.
Firstly, we will create a table in
the database that will have the columns used in the prompts by joining the
respective dimension tables to the FACT table using the Primary-Foreign key
relationship.
In my case I have two columns, the
EMPLOYEE_LOGIN and PRODUCT_NAME columns. It is important to keep the column
names the same as those used in the request in which you use these prompt
values.
Drag the columns from the BM layer to the respective Presentation tables you have created.
Now log into Answers and create the prompts from the new subject Area that you have just created.
Now when you choose the prompt values you will find that they are populated by querying the new table that you created and not the the FACT table, thereby improving the performance of constrain prompts significantly.
No comments:
Post a Comment