Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 11g. Double row in table view.
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!
BRs,
JJAP
Answers
-
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.
0 -
any hidden attribute(column) in Criteria ?
0 -
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) [[
]]
0 -
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.
0 -
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 ?
0 -
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...
0