If you use 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.
The values in PRODUCT_NAME are constrained on the value chosen in the EMPLOYEE_LOGIN prompt. OBIEE does this by issuing a query that will join the Primary keys in the two dimension tables EMPLOYEE_D and PRODUCT_D to the corresponding foreign keys in the FACT 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.
Import this table into the physical layer of the RPD and make a dummy join for this table to an alias of the same table. Drag the the table and its alias to the BM layer. Now create a new Presentation Catalog and new presentation tables with the same name as the ones being used in the request.
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.
Posts
