Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
61
Views
5
Comments
Karthi_obiee
Karthi_obiee Rank 5 - Community Champion
edited Aug 13, 2024 5:16PM in Oracle Analytics Forum

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!

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics & AI Strategist

    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
    Gianni Ceresa Mod

    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
    Robert Angel Rank 8 - Analytics & AI Strategist

    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
    Karthi_obiee Rank 5 - Community Champion

    Yes, I did.

  • Karthi_obiee
    Karthi_obiee Rank 5 - Community Champion

    This is what I did.

    Physical Joins

    pastedImage_5.png

    Logical joins

    pastedImage_4.png

    Dimension Hierarchy

    pastedImage_3.png

    Realtionship to FACT1

    pastedImage_2.png

    Realtionship to FACT2

    pastedImage_1.png

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

    pastedImage_0.png

    Please suggest.

    Thanks!