6 Replies Latest reply on Jan 27, 2016 10:15 PM by cesar.advincula.o

    GroupByNoSort in OBIEE leading to wrong data

    3061367

      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

        • 1. Re: GroupByNoSort in OBIEE leading to wrong data
          3061367

           

          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

           

          1 person found this helpful
          • 2. Re: GroupByNoSort in OBIEE leading to wrong data
            cesar.advincula.o

            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,


            • 3. Re: GroupByNoSort in OBIEE leading to wrong data
              3061367

              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

              • 4. Re: GroupByNoSort in OBIEE leading to wrong data
                cesar.advincula.o

                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,

                • 5. Re: GroupByNoSort in OBIEE leading to wrong data
                  3061367

                  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

                  • 6. Re: GroupByNoSort in OBIEE leading to wrong data
                    cesar.advincula.o

                    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,