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.

OBIEE 10.1.3.4 Grouping the Multiple Dashboards into Dashboard Menu

We might be searching a lot on how & where to club the multiple Dashboards in OBIEE into a Single Pull down Dashboard Menu.

The Solution is as simple as adding the Parameter below.
Before Adding the Parameter

After Adding the Parameter

Solution:
Need to add the Parameter with in Instanceconfig.xml

1. Add the <DashboardMaxBeforeMenu>XX</DashboardMaxBeforeMenu> where XX is the number of dashboards after which we want to group them.
2. Restart the Presentation Services.


Impact:

Since the Parameter is common for the Instance, it will group any Dashboards under common folder as a pull down menu.

Cheers,
Sunil Kumar T.S.

OBIEE Skipping Rows when performing binary join

We had a strange issue with OBIEE in one of the reports that we built recently.

The report has two week selections and two scenario selections say forecast and call and based on the selections user makes, the report should display the measures from a fact table and the measure variance between the two selections. Also there is a column selector by which user can view the data by Region, Sub Region, Country, Key Account.

When the data is being viewed by Region, everything looks fine. The problem occurs when the data is being viewed by Country. OBIEE deliberately skips one of the rows in the tabular view result set. But the grand total value includes the measure value for the skipped row. Debugging further lead us to a strange path.

OBIEE has written one logical query and 3 physical queries to retrieve the result set from the DB and performing a binary join of that data before displaying on the tabular view. One of the physical queries was only to calculate the grand total. Before performing the binary join, OBIEE sorts the data retrieved from the two physical queries. After the sort has been done one of the rows was skipped because of the case mismatch.

Solution:

There is a parameter in the NQSConfig.ini file to set the case sensitive comparison on/off. In our file, the property was set to OFF hence while sorting and joining the results from two physical queries, one of the rows was skipped. After setting that property to ON, OBIEE started displaying the missing row in the tabular view.

Exact Parameter:

Change CASE_SENSITIVE_CHARACTER_COMPARISON=OFF

to CASE_SENSITIVE_CHARACTER_COMPARISON=ON

Impact:

We need to make sure that the back end database also has this property set to ON if we are changing it in the config file. If we have multiple data sources in the physical layer, then the decision to change this property depends on the usage of each data source. If the back end database has this property OFF and we set it to ON in the config file, this may give wrong results too.

If one of the data sources in the physical layer has this property ON and the other as OFF, then we may need to create multiple RPD files and put them on different instances and set this property to ON/OFF as per the data source requirement.

Hope this helps...!!

Siva

OBIEE 10.1.3.4 Dropdown values in the prompt is restricted

We will be wondering how to increase the number of values in the drop down type prompt. With the Default Installation of OBIEE - Drop down type prompt will only show first 250 values and shows the message (Too Many Values)

Solution:
We need to add a Parameter in the Instanceconfig.xml

1. Add the <Prompts><MaxDropDownValues>XXXXX</MaxDropDownValues></Prompts> where XXXXX is the number of values.
2. Restart the Presentation Services.


Impact:

Please note that providing huge values like 1000, 10000 will have performance impact to the Dashboard.

Cheers,
Sunil Kumar T.S.