[nQSError: 14025] No fact table exists at the requested level of detail

APPLIES TO:

Business Intelligence Suite Enterprise Edition – Version 11.1.1.9.170418 and later
Information in this document applies to any platform.

SYMPTOMS

ISSUE CLARIFICATION
============================

Issue Seen : After Upgrading from OBIEE 10g to 11g the report throws the following error :

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail: [,,[AGENCY.Agency],[POPULATION.Population],,,,,,,,,,,,,,,,,,,,,,,,,,,,]. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: AGENCY.Agency, POPULATION.Population (HY000)

The same reports are working fine in 10g.

CAUSE

The error was caused because of incorrect metadata design in rpd. The fact source “Case” was not recognized as a fact source by obi server because of incorrectly defined joins in the BMM layer. The many side of the join was pointing to dimension tables instead of the fact table and because of this Case Logical Table was not recognized as a fact source.

As per rpd design best practices the join definitions in BMM layer has to be correctly defined between the fact and dimension tables .

SOLUTION

The solution is to recreate the joins in BMM layer and make sure that the many side of the join points to the Fact source.
Once this change was done and the modified rpd was uploaded, the report did not throw error and displayed results fine.