sql - OBIEE generated query using irrelevant fact tables in its select

Question

In an analysis, we are counting number of all loaned items by their interest rate. It uses one fact table F1 and three dimension tables D1, D2 and D3. F1 is joined to all these three dimension tables. Therefore, as I see it there shouldn't be any complicated query generated to get the result.

However, when running the analysis, counts are returning 0s. In database, I looked at physical queries generated for this analysis and saw that two selects are being created and another fact table F2 is being used in 2nd select.

Strange thing is that F2 is not used in an analysis, its columns are not selected in criteria tab, no LTS exists to this table from F1, D1,D2 or D3. One thing that is common is that this F2 is also joined to D1, D2 and D3. But why use F2 in query if it is not selected in analysis, do you have any ideas?

Here is what we have in BMM:

Answer 1

If this is the case then you have a situation where as per your model both facts can fulfill the needs of the analysis. Your model is probably not explicit enough.

Have you set an implicit fact column on the subject area to force F03 over F02? Note that it is only possible to set one implicit fact column per subject area.

If you really want to solve the issue rather than work around it you must make sure your model is explicit in terms of which fact to use for which query.

Also: If this is your BMM layer then one suggestion: Use correct business names and designations! Seeing something like "CAL_DAY" will have business users wondering whether they're facing some kind of 1980's tech managed by quite outdated staff ;)