Hoping to find a solution to this problem. The value lists in my dashboard prompts are incorrect whenever I use the Limit Values By checkbox. I would like to have the values in the prompts always limit down to the available values from the actual reports.
For example....If I had a report that showed Total Sales $ as a fact and then had two dimension columns. Country from a country dimension and then Sales Person from a different dimension. I would expect that if I selected a country and filtered the dashboard, that if i then went to the Sales Person filter that I would only see sales people for the selected country. For some reason the results are very odd. They dont seem to make any sense at all. Usually the lists are far shorter than expected. Even if there are values in the reports on the Dashboard they may not show up in the prompt list.
The data warehouse design follows all best practises and it is a simple star schema. From what I can tell OBI doesnt seem to care about the report results when figuring out the prompts. Obviously the more prompts you have the more complicated this would get. Is this just something that OBI doesnt do very well?
Any help would be greatly appreciated. Im pretty disappointed in the functionality at this point and feel as though I must be doing something wrong.
1> The prompts are getting results from cache.
Clear your cache and you might see correct results.
2> Country and Sales person dimension are joined to more than one fact tables and no implicit fact column specified.
Set an Implicit fact column as Total Sales $ for the Subject Area which your prompt is based on and your issue will be resolved.
First thing, caching is not turned on so that negates that possibility.
As for the second point, and this is where I may be missing something. I understand that you can set an implicit fact table but you could never cover all of the possibilities this way. How would you handle the following scenario.
1) If you are using two dimensions that are joined to four fact tables.
2) You need to create two reports. One using facts from two of the fact tables and one using facts from the other two fact tables. In this case the star schema would be conforming and this is a common scenario.
3) If you have to set the implicit fact table to only one fact table, how can you ensure that it would make sense for all reports that can be created in that subject area. You cant guarantee that the results of the prompts will be correct.
You have 2 dimensions, Country and Sales and they are joined to 4 fact tables. Assuming they all are part of same subject area.
Now you will have to select an implicit column for this subject area, keeping in mind that you should not get any data loss.
For example you might have a Fact1 which stores only Laptop Sales, Fact2 stores Desktop sales, Fact3 stores iPhone Sales and Fact4 stores ALL Sales. ( Just for the sake of simplicity to explain)
Now if Sales Person is constrained on Country, the OBIEE may select any of the Facts. Suppose it selects Laptop fact, then you will see Laptop Sales Person only which will not be relevant to other reports.
While designing the Subject Area the developer would know that setting an implicit fact from Fact4 will not result in any data loss in reports.
In the example I gave above, the fact tables were related. You might have a scenario where all 4 fact tables are completely unrelated. In that case, I agree that you cannot guarantee relevant result for all the reports.