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

Question

Scenario:

Let suppose we have two Dimension  table (A and B)  and Fact Table C, we have joined A & B and B &C on Business Model and mapping layer, but on presentation layer we expose only A and C and we want to fetch  result from A and C on presentation Layer but it is throwing error- [nQSError: 14025]  No fact table exists at the requested level of detail.

How could we resolve this ?

[Deleted User] · Answer

https://community.oracle.com/tech/apps-infra/discussion/4483540/nqserror-14025-no-fact-table-exists-at-the-requested-level-of-detail

Did you give up on this?

[Deleted User] · Answer

Your train of thought should stop abruptly and immediately the millisecond you type out " we have joined A & B ".

You joined your dimensions together? Physically? Unless those two "dimensions" are actually a single branch of a snowflake, then your model is already fundamentally wrong from the start on the physical layer.

In the RPD you have a physical and a logical model which do not necessarily look the same. Also you can't just join things physically and assume that upwards things work "as imagined" in an automagical way. You need to draw an entity relationship diagram first and understand which physical table is supposed to join to which other physical table and which logical entity (NOT table) is supposed to have a relationship (NOT "join") to which entity.

NIKHIL ARORA-Oracle · Answer

Yes, I am able to see the same issue if i expose Table B on Presentation Layer,

No, Table B is not there in generated SQL,  and if i am exposing A,B,C on presentation layer and fetch atleast one field from them then also i am getting same error.