OBIEE Not Properly Constraining the Prompt Values Due to Fact Join

Problem Statement:

When created prompt with constraint enabled feature which has multiple columns from multiple dimension tables, OBIEE tries to restrict the prompt values through fact join at run time, i.e. because the only path two dimension tables could be joined is via fact. So, some of the rows get skipped and will not be shown in the prompt column as all of the dimension records might not be present in the fact table.

Solution Thoughts:

1. We can use the implicit fact column feature available in the Presentation layer. That way we can restrict the join between two dimension tables to single fact table rather than allowing OBIEE to have multiple fact table choices. But the problem with this is, still the fact table that we choose should have all the dimensional combination in it otherwise rows will be skipped.

2. We could built multiple prompts one for each dimension, but this introduces a usability issue of having multiple Go buttons and performance issues too as each Go button click will refresh the whole page.

3. Create one table with all the dimensional combination in it which can be used as a single hierarchy table. This might need an ETL to be developed. Create a new BMM with this table along with any dummy table just to make sure the RPD is consistent. Then create a subject area with this new BMM catalog as base and drag the columns required into the new subject area.

4. Add the surrogate key of the second dimension table into the first dimension table. Make sure that you don’t have duplicates before doing this. Establish a join between these two tables in the physical layer using the column we added. Create a new BMM and bring these two tables into that then establish a complex join. Create a new subject area with this new BMM as base and move the columns required into the new subject area.

After this has been done, the prompts can be created from the new subject area and the join will not go through fact table now and the values will be properly restricted.

Please make sure that

The names of the presentation tables and columns in the new prompts subject area (Which will be used to create prompts) should be same as that of the names of the tables and columns in the original subject area from which the reports are created.

If there are more than two tables on which the prompt has to be created, the approach should be changed accordingly, i.e. we may need to have all the dimensional combinations from these three tables in a single table.