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

GroupByNoSort in OBIEE leading to wrong data

Received Response
3
Views
6
Comments
3061367
3061367 Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

We are trying to create a very simple report with our datamodel with 1 fact and 3 dimension with simplest joins with 1 column keys.

When running a report from OBIEE Answer on top of this data model , we are getting wrong results.

And i could see the logs it is some kind of function applied : GroupByNoSort

Please help.

Dillip Sahoo

Answers

  • 3061367
    3061367 Rank 1 - Community Starter

    HI,
    When i add a DISTINCT clause to logical sql by either of below mentioned methods, this issue doesnot appear:
    1) "Select Distinct" option in Logical Table Source
    2) "Issue Explicit Distinct" in Advance tab of Analysis.

    And also, when i add a Time Dimension hierarchy where DISTINCT is not allowed, the issue repeats itself.

    Thanks.
    Dillip Sahoo

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello Oracle BI by default do a group by of the metric by the dimensions, if you want to do some typical, group by, you can use logical sql, or use the advance tab in the answers to customize the information.

    I understand that you dont want to show "GroupByNoSort", please, could you post your log to check in more deep.

    Kind Regards,


  • 3061367
    3061367 Rank 1 - Community Starter

    Hi Cesar,

    Thanks for helping out.

    Relevant line of Logs you would be interested in:

    Execution Node for logical request hash f04b274b : <<50252>> Projection, Close Row Count = 603, Row Width = 2160 bytes

    Execution Node for logical request hash f04b274b : <<50351>> GroupByNoSort, Close Row Count = 26, Row Width = 2160 bytes

    Execution Node for logical request hash f04b274b : <<50352>> Projection, Close Row Count = 26, Row Width = 2160 bytes

    Execution Node for logical request hash f04b274b : <<50352>> Sort, Close Row Count = 26, Row Width = 2160 bytes

    I completely understand under OBIEE does a Group By when there is aggregation involved.

    But as you can see, the excepted result is 603 records(Line 1 of log), but I am getting only 1 random record for each combination of non-aggregated column which is 26 in this case(Line 2 of log).

    What could have gone wrong ??

    Sample Expected Output:

    Month          Year     Brand      Item_Count

    January        2015     Nike       1000

    February       2015     Nike       2000

    January        2015     Adidas    2500

    February       2015    Adidas     3000


    Sample "What I Get" Output (1 Random Record for each combo with just 1 Brand being displayed for each combo of Month and Year):

    Month          Year     Brand      Item_Count

    January        2015     Nike       1000

    February       2015    Adidas     3000


    But when I do a Explicit DISTINCT, this issue does not happen and result set returns 603 records expected.

    Thanks.

    Dillip

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello Dillip,

    If you have this kind of problems, is for two thing:

    1. Maybe there is a duplicate key in the data of any dimension or fact that you use for your report, for that reason when you use the DISTINCT, it could give this the result that you want.

    2. If it could be the model of your star schema, check your Business Model and Mapping Star Schema, and check if you the inner join. left outer, right, full outer join.

    Please, do you mind to do a "select * from" for your dimensions tables and fact table, and put it, to get a data sample.

    Kind Regards,

  • 3061367
    3061367 Rank 1 - Community Starter

    Hi Cesar,

    Sorry for delay in response, still chasing Oracle to help me on this and but NO answers from there side yet. And worst case is they are not ready to deep dive on this and mark this as a bug.

    Answers to your questions:

    1) The dimensions are perfectly having unique key and it is declared as Primary Key column.

    2) I have all joins as Inner Joins, It is a simplest star schema you can imagine of like 1 fact and 2 dimensions. And 2 measures on Facts defined in.

    Is there a way to send you over sample ddl, inserts and RPD to simulate this , if you wish to

    Thanks

    Dillip

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Please could you confirm this,

    Each "logical dimension  table" and "logical fact table" has a different Logical Table Source?

    -In other words:

    - Dimension 1 -- LTS 1 -- PHYSICAL TABLE 1: Table1

    - Dimension 2 -- LTS 2 -- PHYSICAL TABLE 2: Table2

    - Fact             -- LTS 3 -- PHYSICAL TABLE 3: Table3


    Oracle BI EE, by Default do a "Group by" by the dimension(s) that you use, and this will be enough, when you set up your model.

    That's make think.


    OK please, maybe you could check this

    - In the "Physical" Layer: Check the correct join between your tables.

    - In the "Logical"   Layer:  Check your "Logical Key" Well Defined.


    Do you mind, to put a screemshot of your physical and logical star schema, with your "logical" and "physical" keys.


    Kind Regards,