OBIEE - Best practice for Lookups in Business Layer

Question

Good morning,

In the current development of the Business Layer (OBIEE 12c) for some dimensions and facts tables, we have a question about the lookup columns. What is the best practice about lookups? Is better place in Dimensions? in Facts?

About this, we have a current problem. Two common dimensions and two facts. In the facts A one lookup and the fact B another lookup. In a new Analytics report, select an attribute from Dim A, Dim B and Lookup from Fact A. The results show correctly. But, adding the another lookup from the Fact B generates nqsError 14025 No fact table exists at the requested level of detail.

If we change the lookup selected in analytics, delete Lookup from Fact A and leave the Lookup from Fact B the report works.

Is there a best practice about lookups? This issue is normal and we can't select more than one lookup from Fact tables? Or the lookups must be placed in dimensions only?

Thanks,

Benjamin

John_K · Answer

Generally lookups should be in dimensions and measures should be in facts.

I wrote a couple of posts about combining facts with different levels of granularity.

http://wegobeyond.co.uk/blog/entry/combining-facts-subject-areas-in-oracle-obiee.html

[Deleted User] · Answer

Facts....well no. If you tie them to facts but the end result is an attribute then obviously dimension. Even if just degen-dim. If you do it on the fact itself and expose attributes the Analytics Inquisition will hunt you down! (Plus you'll just get unusable stuff that throws errors, so loss-loss)

Thomas Dodds · Answer

Is this what you are referring to:  https://www.rittmanmead.com/blog/2010/08/oracle-bi-ee-11g-lookup-tables-sparse-and-dense-lookups/