6 Replies Latest reply on Jun 9, 2016 8:00 AM by Christian Berg

    OBIEE 11g. Double row in table view.

    3245399

      Hi Experts,

       

      I'm creating an analysis in OBIEE 11g, but some line are doubled in measure column as below (for line S006 and S009).

      I don't know exactly where the problem is so can not solve the issue, please help me out.

      (All the columns belong to only one table, with the measure column is COUNT DISTINCT on ID column).

       

      Thanks in advance!

      split.jpg

       

      BRs,

      JJAP

        • 1. Re: OBIEE 11g. Double row in table view.
          Christian Berg

          Run the physical SQL in SQL Developer and you'll see where the duplication comes from. Most likely you have doubles returned by the query on the grain on the query.

           

          Never forget that a "row" in an analysis is an artificial construct and does not (necessarily) represent an actual row in the database but an aggregation or combination or heterogeneous sources.

          1 person found this helpful
          • 2. Re: OBIEE 11g. Double row in table view.
            Jay Gandhi

            any hidden attribute(column) in Criteria ?

            • 3. Re: OBIEE 11g. Double row in table view.
              3245399

              Thanks Christian Berg, sorry for late reply!


              Yes, the base data is in only one physical table in database, i use a count distinct on measure column and a grand total after. I looked into the log file but could not found any issue on the aggregation or combination. Below is the log file, pls help to check if i missed something,

              Thank you!


              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-0] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] ############################################## [[

              -------------------- SQL Request, logical request hash:

              fb0a5f32

              SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/01. CUSTOMER/CUS-01 KH có thẻ Active';SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7 FROM (

              SELECT

                 0 s_0,

                 "CUSTOMER"."RPT_CUS_01A"."BR_ID_LIST" s_1,

                 "CUSTOMER"."RPT_CUS_01A"."BR_LIST" s_2,

                 "CUSTOMER"."RPT_CUS_01A"."CITY" s_3,

                 "CUSTOMER"."RPT_CUS_01A"."COR_ID_LIST" s_4,

                 "CUSTOMER"."RPT_CUS_01A"."COR_LIST" s_5,

                 "CUSTOMER"."RPT_CUS_01A"."Số lượng KH" s_6,

                 REPORT_AGGREGATE("CUSTOMER"."RPT_CUS_01A"."Số lượng KH" BY ) s_7

              FROM "CUSTOMER"

              ) djm ORDER BY 1, 6 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST

              FETCH FIRST 65001 ROWS ONLY

               

               

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-23] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- General Query Info: [[

              Repository: Star, Subject Area: CUSTOMER, Presentation: CUSTOMER

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-2] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Logical Request (before navigation): [[

               

               

              RqList

                  0 as c1 GB,

                  RPT_CUS_01A.BR_ID_LIST as c2 GB,

                  RPT_CUS_01A.BR_LIST as c3 GB,

                  RPT_CUS_01A.CITY as c4 GB,

                  RPT_CUS_01A.COR_ID_LIST as c5 GB,

                  RPT_CUS_01A.COR_LIST as c6 GB,

                  count(distinct RPT_CUS_01A.CSN by [ RPT_CUS_01A.BR_ID_LIST, RPT_CUS_01A.BR_LIST, RPT_CUS_01A.CITY, RPT_CUS_01A.COR_ID_LIST, RPT_CUS_01A.COR_LIST] ) as c7 GB,

                  Report_Count(distinct RPT_CUS_01A.CSN by [ ] ) as c8 GB

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-47] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- The logical query block is rewritten to 2 sub requests.  [[

              Subrequest logical plans

              RqList

                  RPT_CUS_01A.CSN as c1,

                  RPT_CUS_01A.BR_ID_LIST as c2,

                  RPT_CUS_01A.BR_LIST as c3,

                  RPT_CUS_01A.CITY as c4,

                  RPT_CUS_01A.COR_ID_LIST as c5,

                  RPT_CUS_01A.COR_LIST as c6

               

               

              RqList

                  count(distinct RPT_CUS_01A.CSN by [ RPT_CUS_01A.BR_ID_LIST, RPT_CUS_01A.BR_LIST, RPT_CUS_01A.CITY, RPT_CUS_01A.COR_ID_LIST, RPT_CUS_01A.COR_LIST] ) as c1,

                  RPT_CUS_01A.COR_LIST as c2,

                  RPT_CUS_01A.COR_ID_LIST as c3,

                  RPT_CUS_01A.CITY as c4,

                  RPT_CUS_01A.BR_LIST as c5,

                  RPT_CUS_01A.BR_ID_LIST as c6

               

               

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-50] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- The logical query seed the plan cache [[

              plan

              RqList <<433260>> [for database 0:0,0] distinct  /* FETCH FIRST 65001 ROWS ONLY */

                  D1.c1 as c1 [for database 3023:8,27],

                  D1.c2 as c2 [for database 3023:8,27],

                  D1.c3 as c3 [for database 3023:8,27],

                  D1.c4 as c4 [for database 3023:8,27],

                  D1.c5 as c5 [for database 3023:8,27],

                  D1.c6 as c6 [for database 3023:8,27],

                  D1.c7 as c7 [for database 3023:8,27],

                  count(distinct D1.c9 by [ ]  at_distinct [ D1.c9] ) as c8 [for database 0:0,0]

              Child Nodes (RqJoinSpec): <<433282>> [for database 0:0,0]

                  RqJoinNode <<433283>> []

                      (

                          RqList <<433286>> [for database 0:0,0]

                              D1.c1 as c1 [for database 3023:8,27],

                              D1.c2 as c2 [for database 3023:8,27],

                              D1.c3 as c3 [for database 3023:8,27],

                              D1.c4 as c4 [for database 3023:8,27],

                              D1.c5 as c5 [for database 3023:8,27],

                              D1.c6 as c6 [for database 3023:8,27],

                              D1.c7 as c7 [for database 3023:8,27],

                              D1.c9 as c9 [for database 3023:8,27]

                          Child Nodes (RqJoinSpec): <<433304>> [for database 0:0,0]

                              RqJoinNode <<433305>> []

                                  (

                                      Child Nodes (RqCache): 1465379976:214:1885489349:0x7f01d9deab70

                                      RqBreakFilter <<433309>>[2,3,4,5,6,9] [for database 0:0,0]

                                          RqList <<433310>> [for database 0:0,0]

                                              0 as c1 GB [for database 3023:8,27],

                                              case  when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end  as c2 GB [for database 3023:8,27],

                                              case  when D903.c3 is not null then D903.c3 when D903.c4 is not null then D903.c4 end  as c3 GB [for database 3023:8,27],

                                              case  when D903.c5 is not null then D903.c5 when D903.c6 is not null then D903.c6 end  as c4 GB [for database 3023:8,27],

                                              case  when D903.c7 is not null then D903.c7 when D903.c8 is not null then D903.c8 end  as c5 GB [for database 3023:8,27],

                                              case  when D903.c9 is not null then D903.c9 when D903.c10 is not null then D903.c10 end  as c6 GB [for database 3023:8,27],

                                              D903.c11 as c7 GB [for database 3023:8,27],

                                              0 as c8 GB [for database 3023:8,27],

                                              D903.c12 as c9 [for database 3023:8,27]

                                          Child Nodes (RqJoinSpec): <<433360>> [for database 0:0,0]

                                              RqJoinNode <<433361>> []

                                                  (

                                                      RqList <<433364>> [for database 0:0,0]

                                                          D901.c1 as c1 [for database 3023:8,27],

                                                          D902.c1 as c2 [for database 3023:8,27],

                                                          D901.c2 as c3 [for database 3023:8,27],

                                                          D902.c2 as c4 [for database 3023:8,27],

                                                          D901.c3 as c5 [for database 3023:8,27],

                                                          D902.c3 as c6 [for database 3023:8,27],

                                                          D901.c4 as c7 [for database 3023:8,27],

                                                          D902.c4 as c8 [for database 3023:8,27],

                                                          D901.c5 as c9 [for database 3023:8,27],

                                                          D902.c5 as c10 [for database 3023:8,27],

                                                          D902.c6 as c11 GB [for database 3023:8,27],

                                                          D901.c6 as c12 [for database 3023:8,27]

                                                      Child Nodes (RqJoinSpec): <<433390>> [for database 0:0,0]

                                                          RqJoinNode <<433391>> []

                                                             

                                                                  (

                                                                      RqList <<433395>> [for database 0:0,0]

                                                                          D1.c2 as c1 [for database 3023:8,27],

                                                                          D1.c3 as c2 [for database 3023:8,27],

                                                                          D1.c4 as c3 [for database 3023:8,27],

                                                                          D1.c5 as c4 [for database 3023:8,27],

                                                                          D1.c6 as c5 [for database 3023:8,27],

                                                                          D1.c1 as c6 [for database 3023:8,27]

                                                                      Child Nodes (RqJoinSpec): <<433409>> [for database 0:0,0]

                                                                          RqJoinNode <<433410>> []

                                                                              (

                                                                                  Child Nodes (RqCache): 1465379976:217:2119630650:0x7f01d9de9670

                                                                                  RqList <<433414>> [for database 3023:8:VINID_PROD,27] distinct

                                                                                      RPT_CUS_01A.CSN as c1 [for database 3023:8,27],

                                                                                      RPT_CUS_01A.BR_ID_LIST as c2 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.BR_LIST as c3 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.CITY as c4 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.COR_ID_LIST as c5 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.COR_LIST as c6 GB [for database 3023:8,27]

                                                                                  Child Nodes (RqJoinSpec): <<433428>> [for database 3023:8:VINID_PROD,27]

                                                                                      RqJoinNode <<433429>> []

                                                                                          VGR_CUS_01A AS RPT_CUS_01A

                                                                              ) as D1

                                                                      OrderBy: c1 asc, c2 asc, c3 asc, c4 asc, c5 asc [for database 0:0,0]

                                                                  ) as D901 FullOuterStitchJoin <<433392>> On D901.c1 =NullsEqual D902.c1 and D901.c2 =NullsEqual D902.c2 and D901.c3 =NullsEqual D902.c3 and D901.c4 =NullsEqual D902.c4 and D901.c5 =NullsEqual D902.c5; actual join vectors:  [ 0 1 2 3 4 ] =  [ 0 1 2 3 4 ]

                                                             

                                                                  (

                                                                      RqList <<433459>> [for database 0:0,0]

                                                                          D2.c6 as c1 [for database 3023:8,27],

                                                                          D2.c5 as c2 [for database 3023:8,27],

                                                                          D2.c4 as c3 [for database 3023:8,27],

                                                                          D2.c3 as c4 [for database 3023:8,27],

                                                                          D2.c2 as c5 [for database 3023:8,27],

                                                                          D2.c1 as c6 GB [for database 3023:8,27]

                                                                      Child Nodes (RqJoinSpec): <<433473>> [for database 0:0,0]

                                                                          RqJoinNode <<433474>> []

                                                                              (

                                                                                  Child Nodes (RqCache): 1465379976:217:925634613:0x7f01d9de9670

                                                                                  RqList <<433478>> [for database 3023:8:VINID_PROD,27]

                                                                                      count(distinct RPT_CUS_01A.CSN by [ RPT_CUS_01A.BR_ID_LIST, RPT_CUS_01A.BR_LIST, RPT_CUS_01A.CITY, RPT_CUS_01A.COR_ID_LIST, RPT_CUS_01A.COR_LIST] ) as c1 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.COR_LIST as c2 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.COR_ID_LIST as c3 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.CITY as c4 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.BR_LIST as c5 GB [for database 3023:8,27],

                                                                                      RPT_CUS_01A.BR_ID_LIST as c6 GB [for database 3023:8,27]

                                                                                  Child Nodes (RqJoinSpec): <<433499>> [for database 3023:8:VINID_PROD,27]

                                                                                      RqJoinNode <<433500>> []

                                                                                          VGR_CUS_01A AS RPT_CUS_01A

                                                                                  GroupBy: [ RPT_CUS_01A.BR_ID_LIST, RPT_CUS_01A.BR_LIST, RPT_CUS_01A.CITY, RPT_CUS_01A.COR_ID_LIST, RPT_CUS_01A.COR_LIST]  [for database 3023:8,27]

                                                                              ) as D2

                                                                      OrderBy: c1 asc, c2 asc, c3 asc, c4 asc, c5 asc [for database 0:0,0]

                                                                  ) as D902

                                                  ) as D903

                                          OrderBy: c2, c3, c4, c5, c6, c9 [for database 0:0,0]

                                  ) as D1

                          OrderBy: c9 [for database 0:0,0]

                      ) as D1

              OrderBy: c1 asc, c6 asc NULLS LAST, c5 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST, c4 asc NULLS LAST [for database 0:0,0]

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Sending query to database named VINID_PROD (id: <<432744>>), connection pool named Connection Pool, logical request hash fb0a5f32, physical request hash d994fc45: [[

              select distinct T11560.CSN as c1,

                   T11560.BR_ID_LIST as c2,

                   T11560.BR_LIST as c3,

                   T11560.CITY as c4,

                   T11560.COR_ID_LIST as c5,

                   T11560.COR_LIST as c6

              from

                   VGR_CUS_01A T11560

               

               

              ]]

              [2016-06-08T16:59:36.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Sending query to database named VINID_PROD (id: <<432756>>), connection pool named Connection Pool, logical request hash fb0a5f32, physical request hash d98fc0e0: [[

              select count(distinct T11560.CSN) as c1,

                   T11560.COR_LIST as c2,

                   T11560.COR_ID_LIST as c3,

                   T11560.CITY as c4,

                   T11560.BR_LIST as c5,

                   T11560.BR_ID_LIST as c6

              from

                   VGR_CUS_01A T11560

              group by T11560.BR_ID_LIST, T11560.BR_LIST, T11560.CITY, T11560.COR_ID_LIST, T11560.COR_LIST

               

               

              ]]

              [2016-06-08T16:59:42.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-42] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: 5df61700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Query Result Cache: [59124] The query for user 'giapnv' was inserted into the query result cache. The filename is '/data/Midleware/obiee_home/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache/NQS__736125_61177_229.TBL'. [[

               

               

              ]]

              [2016-06-08T16:59:45.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-42] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: 68b4b700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Query Result Cache: [59119] The query for user 'giapnv' exceeded the cache limit of 100000 rows. The query was not inserted into the query result cache. [[

               

               

              ]]

              [2016-06-08T17:00:20.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-42] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Query Result Cache: [59119] The query for user 'giapnv' exceeded the cache limit of 100000 rows. The query was not inserted into the query result cache. [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-34] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Query Status: Successful Completion [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-26] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Rows 624043, bytes 1903972496 retrieved from database query id: <<432744>> [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-28] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Physical query response time 19 (seconds), id <<432744>> [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-26] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Rows 1066, bytes 17840576 retrieved from database query id: <<432756>> [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-28] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Physical query response time 5 (seconds), id <<432756>> [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-29] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Physical Query Summary Stats: Number of physical queries 2, Cumulative time 24, DB-connect time 0 (seconds) [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-24] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Rows returned to Client 1070 [[

               

               

              ]]

              [2016-06-08T17:03:37.000+07:00] [OracleBIServerComponent] [TRACE:3] [USER-33] [] [ecid: be0c9c870af4134f:-3b9d6568:154e62d1074:-8000-00000000000df931] [tid: d9def700] [requestid: 3f710009] [sessionid: 3f710000] [username: giapnv] -------------------- Logical Query Summary Stats: Elapsed time 241, Response time 240, Compilation time 0 (seconds) [[

               

               

              ]]

              • 4. Re: OBIEE 11g. Double row in table view.
                3245399

                Hi Jay Gandhi, thanks for your reply!

                 

                There's no any hidden column in Criteria! If converted to query, this is the simple SQL with group by on columns so i couldn't understand why the measure is splitting into two rows, as you can see other columns are exactly the same. The query doesn't give double row when running in SQL Developer.

                • 5. Re: OBIEE 11g. Double row in table view.
                  3245399

                  Wow, i finally solve the issue by using COUNT instead of COUNT DISTINCT; that sounds mysterious. Does anyone know what happen to the COUNT DISTINCT? is this a bug of OBIEE 11 ?

                  • 6. Re: OBIEE 11g. Double row in table view.
                    Christian Berg

                    As already said: something in the query is forcing those lines as being distinct. Like a forced key column.

                     

                    So basically you're hiding the issue and not solving it...