No Fact Table Exists at the requested level of detail error

Question

Hi,

I am building a report with two fact tables(F1 & F2) and 4 Dimensions(D1,D2,D3,D4).F1 is joined with all the dimensions and F2 is joined with D2 alone.When I am running the report using D1,D2,D3,D4,F1,F2 I am getting a No Fact table exists at the requested level of detail error.Since D2 is the conformed dimension would it alone not be sufficient(as conformed dimension) to run the report.

Can anybody tell me why this is not working?

Appreciate your responses...

Thanks,

Yaseen

Robert Angel · Answer

Hi Yaseen,

imagine the following; -

Fact A -> Time -< Fact B

Now imagine Fact A has all levels of detail in the time hierarchy, Day / Month / Quarter / Year, but Fact B only has detail from the monthly level.

So, if you ran a two fact query at anything down to Month level then all would be good.

If you ran anything at day level you would get the error that you describe.

This is also typically experienced when developers try to introduce Fact aggregates but leave gaps such that all possible combinations of dimension detail are not covered by the fact tables.

You might want to have a look at this as one possible fix for your issue, I say fix because if the underlying fact detail is not sufficient then all you can really do is make the data invariant at those levels, there is no magic pro-rata style solution built in for this.

One fact table and measure based on two different time dimensions