Additonal where clause filters in OBIEE query — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Additonal where clause filters in OBIEE query

Received Response
11
Views
6
Comments
2828715
2828715 Rank 3 - Community Apprentice
edited Aug 13, 2024 6:09PM in Oracle Analytics Cloud and Server

Hi,

I have a case where Dim A and Fact A are joined using a complex join in the physical layer

Dim A :  ColA ColB StartDateId EndDateID

Fact A: ColA ColB ActivityStartDateId ActivityEndDateID Metric1

Both are joined as

Fact A left outer join Dim A on Fact.ColA=Dim.ColA  and Fact.ColB=Dim.ColB and Fact.activitystartdateid between Dim.StartDateId and Dim.EndDateID

Everything works fine but the problem comes up when I introduce a filter say Fact.ColA='ABC' then the OBiEE is generating another additional filter DimA.ColA='ABC' which is negating the outer join. Pls suggest

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Is Fact.ColA an attribute? If yes, then that belongs into a degenerate dimension and not into the fact table.

  • 2828715
    2828715 Rank 3 - Community Apprentice

    Fact.ColA is an attribute so I have split up the degenerate dimension columns from physical fact table in the logical layer, leaving only the metrics in the logical fact and all attributes into logical dimension.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Physically this is represented how?

    Alias or are you using the same object?

    Plus if you already split that...why do you say you filter on the fact? You'll be filtering on the dimension anyways if you have done what you said above.

  • 2828715
    2828715 Rank 3 - Community Apprentice

    In the physical layer its just one single alias table, the split to dimension and fact is done only in the BMM layer. The reason I am doing this is I do not have a primary key on the fact table and all I have is a couple of business keys, so if I have to do this in Physical layer, it creates the burden of a fact to fact join over these columns,

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Ok but that still doent explain your contradicting statements:

    1) "when I introduce a filter say Fact.ColA='ABC'"

    2) so I have split up the degenerate dimension columns from physical fact table in the logical layer, leaving only the metrics in the logical fact and all attributes into logical dimension.

    So how can you do this filter if you have properly put attributes into a degen dimension?

  • 2828715
    2828715 Rank 3 - Community Apprentice

    Alright sorry for the confusion.

    1) "when I introduce a filter say Fact.ColA='ABC'"

       When I say Fact.ColA='ABC'" its actually the physical column thats coming from the fact table, but in Presentation layer and BMM layer its DegenreateDim.ColA

    To summarize I have taken an alias of the fact table say Fact_A, created all the joins in Physical layer.

    IN BMM the Fact_A has been split up into Dim A and Fact A with attributes and metrics.

    Dim B :  ColA ColB StartDateId EndDateID

    Fact A: ColA ColB ActivityStartDateId ActivityEndDateID Metric1

    Both are joined as in physical layer

    Fact A left outer join Dim B on Fact.ColA=Dim.ColA  and Fact.ColB=Dim.ColB and Fact.activitystartdateid between Dim.StartDateId and Dim.EndDateID

    IN presentaion I am filtering out on DimA.ColA='ABC'

    the problem is with the additional filter on DimB.ColA interoduced by OBIEE which I am not issuing