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

OBI Multi Fact Modelling Question

Received Response
21
Views
3
Comments
Ebin Cherian
Ebin Cherian Rank 5 - Community Champion
edited Aug 13, 2024 5:46PM in Oracle Analytics Cloud and Server

Hi All,

Oracle Business Intelligence 12.2.1.1.0

I have a question regarding filtering out data on a multi fact model. Model is as shown below.

pastedImage_0.png

image

CF - Dim is the conformed dimension to Fact tables Fact - F1 & Fact - F2.

NCF - Dim is joined only to Fact - F2.

Report is created with columns from these 4 tables and a filter is applied on a column from non-conformed dimension. OBIEE then generates 2 queries

Query 1 : NCF-Dim, Fact - F2 and CF - Dim

Query 2 : CF - Dim, Fact - F1

OBIEE generates a FullOuterStitchJoin between Query 1 & Query 2 based on join mentioned in conformed dimension. My issue is Query 1 returns 0 records and Query 2 returns multiple records, these multiple records is shown in the report and fields from Query 1 is shown blank. How can I avoid the records from Query 2 because of FullOuterStitchJoin ?

imagepastedImage_0.png

Setting FOJ_GRAND_TOTAL_LBM=0 at Advanced tab works fine. But I am looking for a way to include this in model as Users will be creating there own reports.

Answers

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

    1.) "Analysis". Not "Report".

    2.) Why do you conform the two if you then want to drop the records? The tools does exactly what you ask of it. The other fact has nothing at all to do with your filtering attribute so the result is shown.

    Correct result. Wrong assumption.

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi Christian,

    Thanks for the reply. 

    Why do you conform the two if you then want to drop the records?

    Model has multiple facts and dimensions and some of the dimensions are conformed as per the Business. Model will break if didn't have conformed dimension between F1 & F2. Not sure if i got your question  correctly.

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

    My point is that the tool does precisely what it is designed to do and what you ask it to do. Cross-fact analytics over conformed dimensions works as you describe it.

    What you think it should do is more or less a subquery. Meaning one side of things has mote weight than the other and guides what should happen there.

    Conformed models have equal importance assigned to both facts.