How to create a logical join with two facts and a confirmed dimension?

Karthi_obiee

Hi All,

I am trying to create a logical join with two facts and a confirmed dimension.

However I see this error message when I create an analysis by pulling one column form each table (Fact1, Fact2, Dim1).

State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail:

I am expecting the OBIEE server to generate the query some thing like this, please suggest how to achieve this.

Select

Fact1.column1, Fact2.column2, Dim1.column3

from

Fact1, Fact2, Dim1

where

((Dim1.code = Fact2.code) and (Dim1.code=Fact1.code));

Thanks!

Robert Angel

Can you also show us the physical joins?

Can you also show what has been modelled on the dimension hierarchies in question and the settings on the table source for the fact / dimension relationships in question?

Gianni Ceresa

When you use conformed dimensions and multiple fact tables, your business models require hierarchies and you must set content level based on what fact exists at what level of detail.

Robert Angel

See -> https://docs.oracle.com/middleware/12211/biee/BIEMG/GUID-9BA0DED0-83DD-4326-82E5-4A607B75952A.htm#hpp_l_advanced

If your relationship is as simple as you describe I would expect (for it to work); -

Fact 1 - relationship to dimension 1 set to detail

Dimension 1 - has a dimension hierarchy with a minimum of total level and detail level, relationship to fact1 and fact2 set at detail level

Fact 2- relationship to dimension 1 set to detail

Physical joins model Fact1 -> Dim1 <- Fact2

Logical Relationships modelled Fact1 -> Dim1 <- Fact2

Karthi_obiee

Yes, I did.

Karthi_obiee

This is what I did.

Physical Joins

Logical joins

Dimension Hierarchy

Realtionship to FACT1

Realtionship to FACT2

When I ran the analysis using the code which is the key from the three tables, I see this error.

Please suggest.

Thanks!