Oracle Business Intelligence
OBI 11g->12c migration - different aggregation method used (DBMS SQL vs. BI serv aggregation) causin
hi,
we are just migrating our exisitng 11G OBI instance to 12C (important note: both are still connected to exactly the same 11G database, so literally using same data ) and, when validating report values, we are finding some issues. When fighting them, I've found some significant differences between how the data is aggregated in 12C vs. how it was done in 11G.
* in 11G usually most aggregations were usually done at DB level
*in 12C, when checking reports' logs I see that more of it is moved to BI server. But, what is more confusing (and actually causing issues) is the fact that now, depending on number of tables (or columns? not sure on this ) used in an analysis , some aggregations may be pushed to physical SQL query, or just done at BI server . To give you an example:
I had one metric, let's call it metric1 that in BMM had case statement in its formula, with data types loosely set (i.e. one of the conditions was WHEN TabName. SomeColumn > 0 THEN ..... where SomeColumn was of Varchar type).
Now, if my report was very simple, had just 2 metrics, metric1 & some other metric2, report was executing correctly (cause this case formula was pushed to physical SQL query, and database doesn't mind such loose type definition). But, whenever I've added any dimension column to the report (and, as you can imagine, the actual report containing metric1 was using some dim columns + filters), the execution of metric1 formula was getting pushed to BI server / logical query, which fails at condition TabName. SomeColumn > 0 saying something about incorrect data types used. Well, that issue wasn't a big deal, since just adding CAST in RPD formula solved it. But now I've found some more reports which are giving me headache, because values of some metric(s) differ between 11G & 12C, and if I edit the report and remove some of the metrics, limiting the report just to some dim columns and only 1 metric (the one that differes on dashboard), the values are the same between both envs.... but adding any extra metric to 12G report automatically affects (changes) value of this problematic metric (and it shouldn't, I only used dims conformed for both facts), so I guess that this time using different aggregations leads to different figures being returned. Unfortunately, because of complexity of the report (many filters used, some dimensions & some of them hidden at resulting table, so many levels of aggregations), the SQL query I get is difficult to analyze & to make any conclusions ("so where actually is the issue?").
Any idea what can be done to avoid this / force 12C to use same aggregations as in 11G to get the values match?
Also, our admin says that there's possibility of using '11G mode' in 12C, but even if we use it for some testing, we wouldn't use it long term, so it doesn't seem like a solution.
11G is:
11.1.1.7.150120 (Build 150113.1200 64-bit)
12C is:
12.2.1.4.0 (Build BIPS-20190627163935 64-bit)
Oracle DB to which both OBIs above are connected is:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Thanks in advance, cheers
p.s. my first post here, hello everybody!
p.p.s if anything in description I've provided is unclear, let me know
Answers
-
Welcome, Karol!
First things first: Have you checked whether MOS Doc ID 2542294.1 fits your issue?
-
Hi Christian,
thanks for your help. The patch was just applied, it didn't help though.
-
Ok. So how exactly did the SQL change? Any pattern in WITH clauses or the likes? Can you post a simple but representative before/after example=
-
will share SQLs in the afternoon
-
I was investigating a bit more..... seems the reason is not necessarily like it was in previous issue with aggregation being done in physical SQL vs. in BI server. Maybe sth else, no idea.
Report itself is :
the metrics that differs on 12C report vs. 11G report is the first metric '# of Sites Selected'.
Now, if any of 3:
* 2nd metric, i.e. 'AVG # of Action.....'
*1st filter, '#of Actual Sites'
*2nd filter, saved one, 'Study Monitoring Filters'
... is removed from the 12C report, then values of '# of Sites Selected' match at both reports.
See full query logs below:
-
#
#
#
#-------12C LOG:
#
#
#
#[2019-11-21T23:34:05.517+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:3] [sik: ssi] [tid: 76dfe700] [messageid: USER-0] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] ############################################## [[
-------------------- SQL Request, logical request hash:
4e895232
SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/kanickik/2019.11.19 migration issues/aux issue 24 v2 Site Action Items - Top Level';SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8 FROM (
SELECT
0 s_0,
"Study Execution"."Geography"."All Regions" s_1,
"Study Execution"."Geography"."Country" s_2,
"Study Execution"."Geography"."Region" s_3,
"Study Execution"."Study"."XXXX Study No." s_4,
"Study Execution"."- Site Life Cycle Metrics"."# of Sites Selected" s_5,
"Study Execution"."Site Action Item"."Avg # of Action Items per Site" s_6,
REPORT_AGGREGATE("Study Execution"."Site Action Item"."Avg # of Action Items per Site" BY "Study Execution"."Geography"."Region") s_7,
REPORT_SUM("Study Execution"."- Site Life Cycle Metrics"."# of Sites Selected" BY "Study Execution"."Geography"."Region") s_8
FROM "Study Execution"
WHERE
(("Site Action Item"."# of Actual Sites" > 0) AND (("Site"."Site Status" NOT IN ('Not Interested', 'Not Selected')) AND ("Study"."Project Status" <> 'Closed') AND ("Study"."Project Lifecycle Phase" <> 'Proposal')))
) djm ORDER BY 9 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 100000001 ROWS ONLY
]]
[2019-11-21T23:34:05.518+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:3] [sik: ssi] [tid: 76dfe700] [messageid: USER-23] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- General Query Info: [[
Repository: ssi, Subject Area: Clinical Operations, Presentation: Study Execution
]]
[2019-11-21T23:34:05.523+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:3] [sik: ssi] [tid: 76dfe700] [messageid: USER-72] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- [[
Logical Business Model Query (after measure predicate transformation) :
select_business_model 0 as c1,
'All Regions' as c2,
"DIM - GEOGRAPHY"."Country" as c3,
"DIM - GEOGRAPHY"."Region" as c4,
"DIM - STUDY"."XXXX Study No." as c5,
"FACT - SITE LIFECYCLE"."# of Sites Selected" as c6,
"FACT - SITE ACTION ITEM"."# Action Items" * 1.0 / nullif( "FACT - SITE ACTION ITEM"."# of Actual Sites", 0) * 1.0 as c7,
ReportSum("FACT - SITE ACTION ITEM"."# Action Items" by "DIM - GEOGRAPHY"."Region") * 1.0 / nullif( max("FACT - SITE ACTION ITEM"."# of Actual Sites" by "DIM - GEOGRAPHY"."Region"), 0) * 1.0 as c8,
ReportSum("FACT - SITE LIFECYCLE"."# of Sites Selected" by "DIM - GEOGRAPHY"."Region") as c9,
"DIM - STUDY"."PROJECT_ID" as c10,
"DIM - GEOGRAPHY"."Derived Region" as c11
from "Clinical Operations"
where "DIM - STUDY"."Project Lifecycle Phase" <> 'Proposal' and "DIM - STUDY"."Project Status" <> 'Closed' and not "DIM - SITE"."Site Status" in ('Not Interested', 'Not Selected') and ("DIM - STUDY"."PROJECT_ID", "DIM - STUDY"."XXXX Study No.", "DIM - GEOGRAPHY"."Country", "DIM - GEOGRAPHY"."Region", "DIM - GEOGRAPHY"."Derived Region") in (SELECT
"Clinical Operations"."DIM - STUDY"."PROJECT_ID" as c0, "Clinical Operations"."DIM - STUDY"."XXXX Study No." as c1, "Clinical Operations"."DIM - GEOGRAPHY"."Country" as c2, "Clinical Operations"."DIM - GEOGRAPHY"."Region" as c3, "Clinical Operations"."DIM - GEOGRAPHY"."Derived Region" as c4
WHERE
"Clinical Operations"."FACT - SITE ACTION ITEM"."# of Actual Sites" > 0
)
]]
[2019-11-21T23:34:05.524+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:3] [sik: ssi] [tid: 76dfe700] [messageid: USER-23] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- General Query Info: [[
Repository: ssi, Subject Area: Clinical Operations, Presentation: Clinical Operations
]]
[2019-11-21T23:34:05.526+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:5] [sik: ssi] [tid: 76dfe700] [messageid: USER-23] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- General Query Info: [[
Repository: ssi, Subject Area: Clinical Operations, Presentation: Clinical Operations
]]
[2019-11-21T23:34:05.590+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:5] [sik: ssi] [tid: 76dfe700] [messageid: USER-16] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- Execution plan: [[
RqList <<15106965>> [for database 0:0,0] /* FETCH FIRST 100000001 ROWS ONLY */
D1.c1 as c1 [for database 3023:26087:Clinical Operations,57],
D1.c2 as c2 [for database 3023:26087:Clinical Operations,57],
D1.c3 as c3 [for database 3023:26087:Clinical Operations,57],
D1.c4 as c4 [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 [for database 3023:26087:Clinical Operations,57],
D1.c6 as c6 [for database 3023:26087:Clinical Operations,57],
D1.c7 as c7 [for database 3023:26087:Clinical Operations,57],
sum_SQL99(D1.c15 by [ D1.c4] at_distinct [ D1.c4, D1.c12, D1.c5, D1.c3, D1.c13] ) * 1.0 / D1.c16 * 1.0 as c8 [for database 0:0,0],
sum_SQL99(D1.c6 by [ D1.c4] at_distinct [ D1.c4, D1.c12, D1.c5, D1.c3, D1.c13] ) as c9 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<15106998>> [for database 0:0,0]
RqJoinNode <<15106997>> []
(
RqList <<15104837>> [for database 0:0,0]
D1.c1 as c1 [for database 3023:26087:Clinical Operations,57],
D1.c2 as c2 [for database 3023:26087:Clinical Operations,57],
D1.c3 as c3 [for database 3023:26087:Clinical Operations,57],
D1.c4 as c4 [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 [for database 3023:26087:Clinical Operations,57],
D1.c6 as c6 [for database 3023:26087:Clinical Operations,57],
D1.c7 as c7 [for database 3023:26087:Clinical Operations,57],
D1.c10 as c12 [for database 3023:26087:Clinical Operations,57],
D1.c11 as c13 [for database 3023:26087:Clinical Operations,57],
D1.c12 as c15 [for database 3023:26087:Clinical Operations,57],
D1.c13 as c16 [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15104876>> [for database 0:0,0]
RqJoinNode <<15104875>> []
(
Child Nodes (RqCache): 1574379245:555:731378720:0x7f7976dfc150
RqBreakFilter <<15106471>>[3,4,5,10] [for database 3023:26087:Clinical Operations,57]
RqList <<15105650>> [for database 3023:26087:Clinical Operations,57]
0 as c1 GB [for database 3023:26087:Clinical Operations,57],
'All Regions' as c2 GB [for database 3023:26087:Clinical Operations,57],
coalesce( D1.c2 , D2.c3) as c3 GB [for database 3023:26087:Clinical Operations,57],
coalesce( D1.c5 , D2.c7) as c4 GB [for database 3023:26087:Clinical Operations,57],
coalesce( D1.c3 , D2.c4) as c5 GB [for database 3023:26087:Clinical Operations,57],
D1.c1 as c6 GB [for database 3023:26087:Clinical Operations,57],
D2.c1 * 1.0 / nullif( D2.c2 , 0) * 1.0 as c7 GB [for database 3023:26087:Clinical Operations,57],
0 as c8 GB [for database 3023:26087:Clinical Operations,57],
0 as c9 GB [for database 3023:26087:Clinical Operations,57],
coalesce( D1.c4 , D2.c5) as c10 GB [for database 3023:26087:Clinical Operations,57],
coalesce( D1.c5 , D2.c7) as c11 GB [for database 3023:26087:Clinical Operations,57],
D2.c1 as c12 [for database 3023:26087:Clinical Operations,57],
nullif( max_SQL99(D2.c6 by [ coalesce( D1.c5 , D2.c7)] ) , 0) as c13 [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15106430>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15106429>> []
(
RqList <<15105662>> [for database 3023:26087:Clinical Operations,57]
sum(case when A003 F_SITE_SUMMARY.DT_SITE_SELECTED_ID > 0 then 1 else 0 end by [ A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME] ) as c1 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.COUNTRY_NAME as c2 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.XXXX_PRJ_NUM as c3 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.PROJECT_ID as c4 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.DERIVED_REGION_NM as c5 GB [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15106482>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105961>> [(InNode:<<15105961>>) (OutNode:<<15105963>>) ]
D_GEOGRAPHY AS A203 D_GEOGRAPHY
RqJoinNode <<15105962>> [(InNode:<<15105962>>) (OutNode:<<15105963>>) , (InNode:<<15105962>>) (OutNode:<<15105963>>) ]
D_PROJECT_STUDY AS A201 D_PROJECT_STUDY
RqJoinNode <<15105963>> [(InNode:<<15105961>>) (OutNode:<<15105963>>) , (InNode:<<15105962>>) (OutNode:<<15105963>>) , (InNode:<<15105962>>) (OutNode:<<15105963>>) , (InNode:<<15105963>>) (OutNode:<<15105964>>) ]
F_SITE_SUMMARY AS A003 F_SITE_SUMMARY
RqJoinNode <<15105964>> [(InNode:<<15105963>>) (OutNode:<<15105964>>) ]
D_SITE AS A209 D_SITE
DetailFilter: A201 D_PROJECT_STUDY.PROJECT_ID = A003 F_SITE_SUMMARY.PROJECT_ID and A203 D_GEOGRAPHY.GEOGRAPHY_ID = A003 F_SITE_SUMMARY.GEOGRAPHY_ID and A003 F_SITE_SUMMARY.SITE_ID = A209 D_SITE.SITE_ID and not A201 D_PROJECT_STUDY.LIFECYCLE_PHASE = 'Proposal' and not A201 D_PROJECT_STUDY.PRJ_STATUS = 'Closed' and not A209 D_SITE.CURRENT_SITE_STATUS = 'Not Interested' and not A209 D_SITE.CURRENT_SITE_STATUS = 'Not Selected' and case when A201 D_PROJECT_STUDY.PROJECT_ID is null then '' else '"' || replace(cast(A201 D_PROJECT_STUDY.PROJECT_ID as VARCHAR ( 256 )) , '"' , '""') || '"' end || '.' || case when A201 D_PROJECT_STUDY.XXXX_PRJ_NUM is null then '' else '"' || replace(A201 D_PROJECT_STUDY.XXXX_PRJ_NUM , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.COUNTRY_NAME is null then '' else '"' || replace(A203 D_GEOGRAPHY.COUNTRY_NAME , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.DERIVED_REGION_NM is null then '' else '"' || replace(A203 D_GEOGRAPHY.DERIVED_REGION_NM , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.DERIVED_REGION_NM is null then '' else '"' || replace(A203 D_GEOGRAPHY.DERIVED_REGION_NM , '"' , '""') || '"' end in (Subquery(
RqList <<15105351>> [for database 3023:26087:Clinical Operations,57] distinct
case when D1.c1 is null then '' else '"' || replace(cast(D1.c1 as VARCHAR ( 256 )) , '"' , '""') || '"' end || '.' || case when D1.c2 is null then '' else '"' || replace(D1.c2 , '"' , '""') || '"' end || '.' || case when D1.c3 is null then '' else '"' || replace(D1.c3 , '"' , '""') || '"' end || '.' || case when D1.c4 is null then '' else '"' || replace(D1.c4 , '"' , '""') || '"' end || '.' || case when D1.c5 is null then '' else '"' || replace(D1.c5 , '"' , '""') || '"' end as c1 [for database 0:0,1]
Child Nodes (RqJoinSpec): <<15105372>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105371>> []
(
RqList <<15105159>> [for database 3023:26087:Clinical Operations,57]
D1.c2 as c1 GB [for database 3023:26087:Clinical Operations,57],
D1.c3 as c2 GB [for database 3023:26087:Clinical Operations,57],
D1.c4 as c3 GB [for database 3023:26087:Clinical Operations,57],
D1.c5 as c4 GB [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 GB [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15105281>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105280>> []
(
RqList <<15105166>> [for database 3023:26087:Clinical Operations,57]
count(distinct A010 F_SITE_ACTION_ITEM.SITE_ID by [ A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME] ) as c1 [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.PROJECT_ID as c2 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.XXXX_PRJ_NUM as c3 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.COUNTRY_NAME as c4 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.DERIVED_REGION_NM as c5 GB [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15105289>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105255>> [(InNode:<<15105255>>) (OutNode:<<15105257>>) ]
D_GEOGRAPHY AS A203 D_GEOGRAPHY
RqJoinNode <<15105256>> [(InNode:<<15105256>>) (OutNode:<<15105257>>) ]
D_PROJECT_STUDY AS A201 D_PROJECT_STUDY
RqJoinNode <<15105257>> [(InNode:<<15105255>>) (OutNode:<<15105257>>) , (InNode:<<15105256>>) (OutNode:<<15105257>>) ]
F_SITE_ACTION_ITEM AS A010 F_SITE_ACTION_ITEM
DetailFilter: A201 D_PROJECT_STUDY.PROJECT_ID = A010 F_SITE_ACTION_ITEM.PROJECT_ID and A203 D_GEOGRAPHY.GEOGRAPHY_ID = A010 F_SITE_ACTION_ITEM.GEOGRAPHY_ID [for database 3023:26087:Clinical Operations,57]
GroupBy: [ A201 D_PROJECT_STUDY.XXXX_PRJ_NUM, A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME, A203 D_GEOGRAPHY.DERIVED_REGION_NM] [for database 3023:26087:Clinical Operations,57]
Having: 0 < count(distinct A010 F_SITE_ACTION_ITEM.SITE_ID by [ A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME] )
) as D1
) as D1 aka SASUBWITH15104832)) [for database 3023:26087:Clinical Operations,57]
GroupBy: [ A201 D_PROJECT_STUDY.XXXX_PRJ_NUM, A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME, A203 D_GEOGRAPHY.DERIVED_REGION_NM] [for database 3023:26087:Clinical Operations,57]
) as D1 FullOuterStitchJoin <<15106428>> On D1.c5 = D2.c7 and D1.c3 = D2.c4 and D1.c2 = D2.c3 and D1.c4 = D2.c5
(
RqList <<15106876>> [for database 3023:26087:Clinical Operations,57] distinct
D1.c1 as c1 [for database 3023:26087:Clinical Operations,57],
D1.c2 as c2 [for database 3023:26087:Clinical Operations,57],
D1.c3 as c3 [for database 3023:26087:Clinical Operations,57],
D1.c4 as c4 [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 [for database 3023:26087:Clinical Operations,57],
sum_SQL99(D1.c6 by [ D1.c7] ) as c6 [for database 3023:26087:Clinical Operations,57],
D1.c7 as c7 [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15106903>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15106902>> []
(
RqList <<15107042>> [for database 3023:26087:Clinical Operations,57]
count(D1.c8 by [ D1.c4, D1.c5, D1.c3, D1.c7] ) as c1 [for database 3023:26087:Clinical Operations,57],
count(distinct D1.c9 by [ D1.c4, D1.c5, D1.c3, D1.c7] ) as c2 [for database 3023:26087:Clinical Operations,57],
D1.c3 as c3 [for database 3023:26087:Clinical Operations,57],
D1.c4 as c4 [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 [for database 3023:26087:Clinical Operations,57],
count(distinct case D1.c10 when 1 then D1.c9 else NULL end by [ D1.c4, D1.c5, D1.c3, D1.c7] ) as c6 [for database 3023:26087:Clinical Operations,57],
D1.c7 as c7 [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15107069>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15107068>> []
(
RqList <<15106006>> [for database 3023:26087:Clinical Operations,57]
A203 D_GEOGRAPHY.COUNTRY_NAME as c3 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.XXXX_PRJ_NUM as c4 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.PROJECT_ID as c5 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.DERIVED_REGION_NM as c7 GB [for database 3023:26087:Clinical Operations,57],
A010 F_SITE_ACTION_ITEM.ACTION_ITEM_SK as c8 [for database 3023:26087:Clinical Operations,57],
A010 F_SITE_ACTION_ITEM.SITE_ID as c9 [for database 3023:26087:Clinical Operations,57],
rownum(1 by [ A203 D_GEOGRAPHY.DERIVED_REGION_NM, A010 F_SITE_ACTION_ITEM.SITE_ID] at_distinct [ A203 D_GEOGRAPHY.DERIVED_REGION_NM, A010 F_SITE_ACTION_ITEM.SITE_ID] ) as c10 [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15106488>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15106314>> [(InNode:<<15106314>>) (OutNode:<<15106319>>) ]
D_SITE AS A209 D_SITE
RqJoinNode <<15106317>> [(InNode:<<15106317>>) (OutNode:<<15106319>>) ]
D_GEOGRAPHY AS A203 D_GEOGRAPHY
RqJoinNode <<15106318>> [(InNode:<<15106318>>) (OutNode:<<15106319>>) ]
D_PROJECT_STUDY AS A201 D_PROJECT_STUDY
RqJoinNode <<15106319>> [(InNode:<<15106314>>) (OutNode:<<15106319>>) , (InNode:<<15106317>>) (OutNode:<<15106319>>) , (InNode:<<15106318>>) (OutNode:<<15106319>>) ]
F_SITE_ACTION_ITEM AS A010 F_SITE_ACTION_ITEM
DetailFilter: A201 D_PROJECT_STUDY.PROJECT_ID = A010 F_SITE_ACTION_ITEM.PROJECT_ID and A203 D_GEOGRAPHY.GEOGRAPHY_ID = A010 F_SITE_ACTION_ITEM.GEOGRAPHY_ID and A209 D_SITE.SITE_ID = A010 F_SITE_ACTION_ITEM.SITE_ID and not A201 D_PROJECT_STUDY.LIFECYCLE_PHASE = 'Proposal' and not A201 D_PROJECT_STUDY.PRJ_STATUS = 'Closed' and not A209 D_SITE.CURRENT_SITE_STATUS = 'Not Interested' and not A209 D_SITE.CURRENT_SITE_STATUS = 'Not Selected' and case when A201 D_PROJECT_STUDY.PROJECT_ID is null then '' else '"' || replace(cast(A201 D_PROJECT_STUDY.PROJECT_ID as VARCHAR ( 256 )) , '"' , '""') || '"' end || '.' || case when A201 D_PROJECT_STUDY.XXXX_PRJ_NUM is null then '' else '"' || replace(A201 D_PROJECT_STUDY.XXXX_PRJ_NUM , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.COUNTRY_NAME is null then '' else '"' || replace(A203 D_GEOGRAPHY.COUNTRY_NAME , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.DERIVED_REGION_NM is null then '' else '"' || replace(A203 D_GEOGRAPHY.DERIVED_REGION_NM , '"' , '""') || '"' end || '.' || case when A203 D_GEOGRAPHY.DERIVED_REGION_NM is null then '' else '"' || replace(A203 D_GEOGRAPHY.DERIVED_REGION_NM , '"' , '""') || '"' end in (Subquery(
RqList <<15105351>> [for database 3023:26087:Clinical Operations,57] distinct
case when D1.c1 is null then '' else '"' || replace(cast(D1.c1 as VARCHAR ( 256 )) , '"' , '""') || '"' end || '.' || case when D1.c2 is null then '' else '"' || replace(D1.c2 , '"' , '""') || '"' end || '.' || case when D1.c3 is null then '' else '"' || replace(D1.c3 , '"' , '""') || '"' end || '.' || case when D1.c4 is null then '' else '"' || replace(D1.c4 , '"' , '""') || '"' end || '.' || case when D1.c5 is null then '' else '"' || replace(D1.c5 , '"' , '""') || '"' end as c1 [for database 0:0,1]
Child Nodes (RqJoinSpec): <<15105372>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105371>> []
(
RqList <<15105159>> [for database 3023:26087:Clinical Operations,57]
D1.c2 as c1 GB [for database 3023:26087:Clinical Operations,57],
D1.c3 as c2 GB [for database 3023:26087:Clinical Operations,57],
D1.c4 as c3 GB [for database 3023:26087:Clinical Operations,57],
D1.c5 as c4 GB [for database 3023:26087:Clinical Operations,57],
D1.c5 as c5 GB [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15105281>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105280>> []
(
RqList <<15105166>> [for database 3023:26087:Clinical Operations,57]
count(distinct A010 F_SITE_ACTION_ITEM.SITE_ID by [ A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME] ) as c1 [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.PROJECT_ID as c2 GB [for database 3023:26087:Clinical Operations,57],
A201 D_PROJECT_STUDY.XXXX_PRJ_NUM as c3 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.COUNTRY_NAME as c4 GB [for database 3023:26087:Clinical Operations,57],
A203 D_GEOGRAPHY.DERIVED_REGION_NM as c5 GB [for database 3023:26087:Clinical Operations,57]
Child Nodes (RqJoinSpec): <<15105289>> [for database 3023:26087:Clinical Operations,57]
RqJoinNode <<15105255>> [(InNode:<<15105255>>) (OutNode:<<15105257>>) ]
D_GEOGRAPHY AS A203 D_GEOGRAPHY
RqJoinNode <<15105256>> [(InNode:<<15105256>>) (OutNode:<<15105257>>) ]
D_PROJECT_STUDY AS A201 D_PROJECT_STUDY
RqJoinNode <<15105257>> [(InNode:<<15105255>>) (OutNode:<<15105257>>) , (InNode:<<15105256>>) (OutNode:<<15105257>>) ]
F_SITE_ACTION_ITEM AS A010 F_SITE_ACTION_ITEM
DetailFilter: A201 D_PROJECT_STUDY.PROJECT_ID = A010 F_SITE_ACTION_ITEM.PROJECT_ID and A203 D_GEOGRAPHY.GEOGRAPHY_ID = A010 F_SITE_ACTION_ITEM.GEOGRAPHY_ID [for database 3023:26087:Clinical Operations,57]
GroupBy: [ A201 D_PROJECT_STUDY.XXXX_PRJ_NUM, A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME, A203 D_GEOGRAPHY.DERIVED_REGION_NM] [for database 3023:26087:Clinical Operations,57]
Having: 0 < count(distinct A010 F_SITE_ACTION_ITEM.SITE_ID by [ A201 D_PROJECT_STUDY.PROJECT_ID, A203 D_GEOGRAPHY.COUNTRY_NAME] )
) as D1
) as D1 aka SASUBWITH15104832)) [for database 3023:26087:Clinical Operations,57]
) as D1
GroupBy: [ D1.c3, D1.c4, D1.c5, D1.c7] [for database 3023:26087:Clinical Operations,57]
) as D1
) as D2
) as D1
OrderBy: c4, c12, c5, c3, c13 [for database 0:0,0]
) as D1
OrderBy: c9 asc NULLS LAST, c4 asc NULLS LAST [for database 0:0,0]
]]
[2019-11-21T23:34:05.593+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:5] [sik: ssi] [tid: 76dfe700] [messageid: USER-18] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- Sending query to database named Clinical Operations (id: <<15106471>>), connection pool named cpStudyStartup.DMARTS@OBI, logical request hash 4e895232, physical request hash 40f68f87: [[
WITH
OBISUBWITH0 AS (select distinct D1.c2 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
D1.c5 as c5
from
(select count(distinct T86225.SITE_ID) as c1,
T26195.PROJECT_ID as c2,
T26195.XXXX_PRJ_NUM as c3,
T26210.COUNTRY_NAME as c4,
T26210.DERIVED_REGION_NM as c5
from
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_ACTION_ITEM T86225 /* A010 F_SITE_ACTION_ITEM */
where ( T26195.PROJECT_ID = T86225.PROJECT_ID and T26210.GEOGRAPHY_ID = T86225.GEOGRAPHY_ID )
group by T26195.XXXX_PRJ_NUM, T26195.PROJECT_ID, T26210.COUNTRY_NAME, T26210.DERIVED_REGION_NM
having 0 < count(distinct T86225.SITE_ID)
) D1),
SAWITH0 AS (select sum(case when T47009.DT_SITE_SELECTED_ID > 0 then 1 else 0 end ) as c1,
T26210.COUNTRY_NAME as c2,
T26195.XXXX_PRJ_NUM as c3,
T26195.PROJECT_ID as c4,
T26210.DERIVED_REGION_NM as c5
from
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_SUMMARY T47009 /* A003 F_SITE_SUMMARY */ ,
DMARTS.D_SITE T50594 /* A209 D_SITE */
where ( T26195.LIFECYCLE_PHASE not in ('Proposal') and T26195.PRJ_STATUS not in ('Closed') and T50594.CURRENT_SITE_STATUS not in ('Not Interested', 'Not Selected') and T26195.PROJECT_ID = T47009.PROJECT_ID and T26210.GEOGRAPHY_ID = T47009.GEOGRAPHY_ID and T47009.SITE_ID = T50594.SITE_ID and concat(concat(concat(concat(case when T26195.PROJECT_ID is null then '' else concat(concat('"', replace(cast(T26195.PROJECT_ID as VARCHAR ( 256 ) ) , '"' , '""')), '"') end , concat('.', case when T26195.XXXX_PRJ_NUM is null then '' else concat(concat('"', replace(T26195.XXXX_PRJ_NUM , '"' , '""')), '"') end )), concat('.', case when T26210.COUNTRY_NAME is null then '' else concat(concat('"', replace(T26210.COUNTRY_NAME , '"' , '""')), '"') end )), concat('.', case when T26210.DERIVED_REGION_NM is null then '' else concat(concat('"', replace(T26210.DERIVED_REGION_NM , '"' , '""')), '"') end )), concat('.', case when T26210.DERIVED_REGION_NM is null then '' else concat(concat('"', replace(T26210.DERIVED_REGION_NM , '"' , '""')), '"') end )) in (select distinct concat(concat(concat(concat(case when D1.c1 is null then '' else concat(concat('"', replace(cast(D1.c1 as VARCHAR ( 256 ) ) , '"' , '""')), '"') end , concat('.', case when D1.c2 is null then '' else concat(concat('"', replace(D1.c2 , '"' , '""')), '"') end )), concat('.', case when D1.c3 is null then '' else concat(concat('"', replace(D1.c3 , '"' , '""')), '"') end )), concat('.', case when D1.c4 is null then '' else concat(concat('"', replace(D1.c4 , '"' , '""')), '"') end )), concat('.', case when D1.c5 is null then '' else concat(concat('"', replace(D1.c5 , '"' , '""')), '"') end )) as c1
from
OBISUBWITH0 D1) )
group by T26195.XXXX_PRJ_NUM, T26195.PROJECT_ID, T26210.COUNTRY_NAME, T26210.DERIVED_REGION_NM),
SAWITH1 AS (select T26210.COUNTRY_NAME as c3,
T26195.XXXX_PRJ_NUM as c4,
T26195.PROJECT_ID as c5,
T26210.DERIVED_REGION_NM as c7,
T86225.ACTION_ITEM_SK as c8,
T86225.SITE_ID as c9,
ROW_NUMBER() OVER (PARTITION BY T26210.DERIVED_REGION_NM, T86225.SITE_ID ORDER BY T26210.DERIVED_REGION_NM DESC, T86225.SITE_ID DESC) as c10
from
DMARTS.D_SITE T50594 /* A209 D_SITE */ ,
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_ACTION_ITEM T86225 /* A010 F_SITE_ACTION_ITEM */
where ( T26195.LIFECYCLE_PHASE not in ('Proposal') and T26195.PRJ_STATUS not in ('Closed') and T50594.CURRENT_SITE_STATUS not in ('Not Interested', 'Not Selected') and T26195.PROJECT_ID = T86225.PROJECT_ID and T26210.GEOGRAPHY_ID = T86225.GEOGRAPHY_ID and T50594.SITE_ID = T86225.SITE_ID and concat(concat(concat(concat(case when T26195.PROJECT_ID is null then '' else concat(concat('"', replace(cast(T26195.PROJECT_ID as VARCHAR ( 256 ) ) , '"' , '""')), '"') end , concat('.', case when T26195.XXXX_PRJ_NUM is null then '' else concat(concat('"', replace(T26195.XXXX_PRJ_NUM , '"' , '""')), '"') end )), concat('.', case when T26210.COUNTRY_NAME is null then '' else concat(concat('"', replace(T26210.COUNTRY_NAME , '"' , '""')), '"') end )), concat('.', case when T26210.DERIVED_REGION_NM is null then '' else concat(concat('"', replace(T26210.DERIVED_REGION_NM , '"' , '""')), '"') end )), concat('.', case when T26210.DERIVED_REGION_NM is null then '' else concat(concat('"', replace(T26210.DERIVED_REGION_NM , '"' , '""')), '"') end )) in (select distinct concat(concat(concat(concat(case when D1.c1 is null then '' else concat(concat('"', replace(cast(D1.c1 as VARCHAR ( 256 ) ) , '"' , '""')), '"') end , concat('.', case when D1.c2 is null then '' else concat(concat('"', replace(D1.c2 , '"' , '""')), '"') end )), concat('.', case when D1.c3 is null then '' else concat(concat('"', replace(D1.c3 , '"' , '""')), '"') end )), concat('.', case when D1.c4 is null then '' else concat(concat('"', replace(D1.c4 , '"' , '""')), '"') end )), concat('.', case when D1.c5 is null then '' else concat(concat('"', replace(D1.c5 , '"' , '""')), '"') end )) as c1
from
OBISUBWITH0 D1) ) ),
SAWITH2 AS (select count(D1.c8) as c1,
count(distinct D1.c9) as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
count(distinct case D1.c10 when 1 then D1.c9 else NULL end ) as c6,
D1.c7 as c7
from
SAWITH1 D1
group by D1.c3, D1.c4, D1.c5, D1.c7),
SAWITH3 AS (select distinct D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
sum(D1.c6) over (partition by D1.c7) as c6,
D1.c7 as c7
from
SAWITH2 D1)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13
from
(select 0 as c1,
'All Regions' as c2,
coalesce( D1.c2, D2.c3) as c3,
coalesce( D1.c5, D2.c7) as c4,
coalesce( D1.c3, D2.c4) as c5,
D1.c1 as c6,
D2.c1 * 1.0 / nullif( D2.c2, 0) * 1.0 as c7,
0 as c8,
0 as c9,
coalesce( D1.c4, D2.c5) as c10,
coalesce( D1.c5, D2.c7) as c11,
D2.c1 as c12,
nullif( max(D2.c6) over (partition by coalesce( D1.c5, D2.c7)) , 0) as c13,
ROW_NUMBER() OVER (PARTITION BY coalesce( D1.c2, D2.c3), coalesce( D1.c3, D2.c4), coalesce( D1.c4, D2.c5), coalesce( D1.c5, D2.c7) ORDER BY coalesce( D1.c2, D2.c3) ASC, coalesce( D1.c3, D2.c4) ASC, coalesce( D1.c4, D2.c5) ASC, coalesce( D1.c5, D2.c7) ASC) as c14
from
SAWITH0 D1 full outer join SAWITH3 D2 On D1.c5 = D2.c7 and D1.c3 = D2.c4 and D1.c2 = D2.c3 and D1.c4 = D2.c5
) D1
where ( D1.c14 = 1 )
]]
[2019-11-21T23:34:08.754+00:00] [OBIS] [TRACE:2] [] [] [ecid: b27afc39-238d-4a45-8f5f-785e3528d30b-00017c7b,0:2:17:5] [sik: ssi] [tid: 76dfe700] [messageid: USER-34] [requestid: d7410027] [sessionid: d7410000] [username: kanickik] -------------------- Query Status: [nQSError: 46066] Operation cancelled. [[
[nQSError: 43120] Query Cancelled
]]
-
#
#
#-------11G LOG:
#
#
#
[2019-11-21T23:30:17.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:3] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] ############################################## [[
-------------------- SQL Request, logical request hash:
fa3224db
SET VARIABLE QUERY_SRC_CD='Report';SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8 FROM (
SELECT
0 s_0,
"Study Execution"."Geography"."All Regions" s_1,
"Study Execution"."Geography"."Country" s_2,
"Study Execution"."Geography"."Region" s_3,
"Study Execution"."Study"."XXXX Study No." s_4,
"Study Execution"."- Site Life Cycle Metrics"."# of Sites Selected" s_5,
"Study Execution"."Site Action Item"."Avg # of Action Items per Site" s_6,
REPORT_AGGREGATE("Study Execution"."Site Action Item"."Avg # of Action Items per Site" BY "Study Execution"."Geography"."Region") s_7,
REPORT_SUM("Study Execution"."- Site Life Cycle Metrics"."# of Sites Selected" BY "Study Execution"."Geography"."Region") s_8
FROM "Study Execution"
WHERE
(("Site Action Item"."# of Actual Sites" > 0) AND (("Site"."Site Status" NOT IN ('Not Interested', 'Not Selected')) AND ("Study"."Project Status" <> 'Closed') AND ("Study"."Project Lifecycle Phase" <> 'Proposal')))
) djm ORDER BY 1, 9 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 100000001 ROWS ONLY
]]
[2019-11-21T23:30:17.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:3] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- General Query Info: [[
Repository: Star, Subject Area: Clinical Operations, Presentation: Study Execution
]]
[2019-11-21T23:30:17.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:5] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Sending query to database named Clinical Operations (id: <<9955741>>), connection pool named cpStudyStartup.DMARTS@OBI, logical request hash fa3224db, physical request hash 1a1b55db: [[
WITH
SAWITH0 AS (select sum(case when T47009.DT_SITE_SELECTED_ID > 0 then 1 else 0 end ) as c1,
T26210.COUNTRY_NAME as c2,
T26195.XXXX_PRJ_NUM as c3,
T26195.PROJECT_ID as c4,
'All Regions' as c5,
T26210.DERIVED_REGION_NM as c6
from
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_SUMMARY T47009 /* A003 F_SITE_SUMMARY */ ,
DMARTS.D_SITE T50594 /* A209 D_SITE */
where ( T26195.PROJECT_ID = T47009.PROJECT_ID and T26210.GEOGRAPHY_ID = T47009.GEOGRAPHY_ID and T47009.SITE_ID = T50594.SITE_ID and T26195.LIFECYCLE_PHASE <> 'Proposal' and T26195.PRJ_STATUS <> 'Closed' and T50594.CURRENT_SITE_STATUS <> 'Not Interested' and T50594.CURRENT_SITE_STATUS <> 'Not Selected' )
group by T26195.XXXX_PRJ_NUM, T26195.PROJECT_ID, T26210.COUNTRY_NAME, T26210.DERIVED_REGION_NM),
SAWITH1 AS (select T26210.DERIVED_REGION_NM as c3,
T26210.COUNTRY_NAME as c6,
T26195.XXXX_PRJ_NUM as c7,
T26195.PROJECT_ID as c8,
T86225.SITE_ID as c9,
ROW_NUMBER() OVER (PARTITION BY T26210.DERIVED_REGION_NM, T86225.SITE_ID ORDER BY T26210.DERIVED_REGION_NM DESC, T86225.SITE_ID DESC) as c10,
ROW_NUMBER() OVER (PARTITION BY T26195.PROJECT_ID, T26210.COUNTRY_NAME, T86225.SITE_ID ORDER BY T26195.PROJECT_ID DESC, T26210.COUNTRY_NAME DESC, T86225.SITE_ID DESC) as c11,
T86225.ACTION_ITEM_SK as c12
from
DMARTS.D_SITE T50594 /* A209 D_SITE */ ,
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_ACTION_ITEM T86225 /* A010 F_SITE_ACTION_ITEM */
where ( T26195.PROJECT_ID = T86225.PROJECT_ID and T26210.GEOGRAPHY_ID = T86225.GEOGRAPHY_ID and T50594.SITE_ID = T86225.SITE_ID and T26195.LIFECYCLE_PHASE <> 'Proposal' and T26195.PRJ_STATUS <> 'Closed' and T50594.CURRENT_SITE_STATUS <> 'Not Interested' and T50594.CURRENT_SITE_STATUS <> 'Not Selected' ) ),
SAWITH2 AS (select count(distinct case D1.c10 when 1 then D1.c9 else NULL end ) as c1,
D1.c3 as c3,
count(distinct case D1.c11 when 1 then D1.c9 else NULL end ) as c4,
count(D1.c12) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
SAWITH1 D1
group by D1.c3, D1.c6, D1.c7, D1.c8),
SAWITH3 AS (select sum(D1.c1) over (partition by D1.c3) as c1,
'All Regions' as c2,
D1.c3 as c3,
sum(D1.c4) over (partition by D1.c8, D1.c6) as c4,
sum(D1.c5) over (partition by D1.c8, D1.c6) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
SAWITH2 D1),
SAWITH4 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13
from
(select 0 as c1,
case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end as c2,
case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end as c3,
case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end as c4,
case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end as c5,
D1.c1 as c6,
D2.c5 * 1.0 / nullif( D2.c4, 0) * 1.0 as c7,
case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end as c10,
case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end as c11,
D2.c5 as c12,
D2.c1 as c13,
ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end , case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end , case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end , case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end , case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end ASC, case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end ASC, case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end ASC, case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end ASC, case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end ASC) as c14
from
SAWITH0 D1 full outer join SAWITH3 D2 On D1.c2 = D2.c6 and D1.c4 = D2.c8
where ( 0 < D2.c4 )
) D1
where ( D1.c14 = 1 ) )
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c10 as c9,
D1.c11 as c10,
D1.c12 as c12
from
SAWITH4 D1
order by c4, c9, c5, c3, c2, c10
]]
[2019-11-21T23:30:17.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:5] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Sending query to database named Clinical Operations (id: <<9956100>>), connection pool named cpStudyStartup.DMARTS@OBI, logical request hash fa3224db, physical request hash b7b7f910: [[
WITH
SAWITH0 AS (select sum(case when T47009.DT_SITE_SELECTED_ID > 0 then 1 else 0 end ) as c1,
T26210.COUNTRY_NAME as c2,
T26195.XXXX_PRJ_NUM as c3,
T26195.PROJECT_ID as c4,
'All Regions' as c5,
T26210.DERIVED_REGION_NM as c6
from
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_SUMMARY T47009 /* A003 F_SITE_SUMMARY */ ,
DMARTS.D_SITE T50594 /* A209 D_SITE */
where ( T26195.PROJECT_ID = T47009.PROJECT_ID and T26210.GEOGRAPHY_ID = T47009.GEOGRAPHY_ID and T47009.SITE_ID = T50594.SITE_ID and T26195.LIFECYCLE_PHASE <> 'Proposal' and T26195.PRJ_STATUS <> 'Closed' and T50594.CURRENT_SITE_STATUS <> 'Not Interested' and T50594.CURRENT_SITE_STATUS <> 'Not Selected' )
group by T26195.XXXX_PRJ_NUM, T26195.PROJECT_ID, T26210.COUNTRY_NAME, T26210.DERIVED_REGION_NM),
SAWITH1 AS (select T26210.DERIVED_REGION_NM as c3,
T26210.COUNTRY_NAME as c6,
T26195.XXXX_PRJ_NUM as c7,
T26195.PROJECT_ID as c8,
T86225.SITE_ID as c9,
ROW_NUMBER() OVER (PARTITION BY T26210.DERIVED_REGION_NM, T86225.SITE_ID ORDER BY T26210.DERIVED_REGION_NM DESC, T86225.SITE_ID DESC) as c10,
ROW_NUMBER() OVER (PARTITION BY T26195.PROJECT_ID, T26210.COUNTRY_NAME, T86225.SITE_ID ORDER BY T26195.PROJECT_ID DESC, T26210.COUNTRY_NAME DESC, T86225.SITE_ID DESC) as c11,
T86225.ACTION_ITEM_SK as c12
from
DMARTS.D_SITE T50594 /* A209 D_SITE */ ,
DMARTS.D_GEOGRAPHY T26210 /* A203 D_GEOGRAPHY */ ,
DMARTS.D_PROJECT_STUDY T26195 /* A201 D_PROJECT_STUDY */ ,
DMARTS.F_SITE_ACTION_ITEM T86225 /* A010 F_SITE_ACTION_ITEM */
where ( T26195.PROJECT_ID = T86225.PROJECT_ID and T26210.GEOGRAPHY_ID = T86225.GEOGRAPHY_ID and T50594.SITE_ID = T86225.SITE_ID and T26195.LIFECYCLE_PHASE <> 'Proposal' and T26195.PRJ_STATUS <> 'Closed' and T50594.CURRENT_SITE_STATUS <> 'Not Interested' and T50594.CURRENT_SITE_STATUS <> 'Not Selected' ) ),
SAWITH2 AS (select count(distinct case D1.c10 when 1 then D1.c9 else NULL end ) as c1,
D1.c3 as c3,
count(distinct case D1.c11 when 1 then D1.c9 else NULL end ) as c4,
count(D1.c12) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
SAWITH1 D1
group by D1.c3, D1.c6, D1.c7, D1.c8),
SAWITH3 AS (select sum(D1.c1) over (partition by D1.c3) as c1,
'All Regions' as c2,
D1.c3 as c3,
sum(D1.c4) over (partition by D1.c8, D1.c6) as c4,
sum(D1.c5) over (partition by D1.c8, D1.c6) as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
SAWITH2 D1),
SAWITH4 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13
from
(select 0 as c1,
case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end as c2,
case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end as c3,
case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end as c4,
case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end as c5,
D1.c1 as c6,
D2.c5 * 1.0 / nullif( D2.c4, 0) * 1.0 as c7,
case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end as c10,
case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end as c11,
D2.c5 as c12,
D2.c1 as c13,
ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end , case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end , case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end , case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end , case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c6 is not null then D2.c6 end ASC, case when D1.c3 is not null then D1.c3 when D2.c7 is not null then D2.c7 end ASC, case when D1.c4 is not null then D1.c4 when D2.c8 is not null then D2.c8 end ASC, case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end ASC, case when D1.c6 is not null then D1.c6 when D2.c3 is not null then D2.c3 end ASC) as c14
from
SAWITH0 D1 full outer join SAWITH3 D2 On D1.c2 = D2.c6 and D1.c4 = D2.c8
where ( 0 < D2.c4 )
) D1
where ( D1.c14 = 1 ) ),
SAWITH5 AS (select nullif( max(D1.c13), 0) as c11,
D1.c4 as c12
from
SAWITH4 D1
group by D1.c4)
select D2.c11 as c1,
D2.c12 as c2
from
SAWITH5 D2
order by c2
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Query Status: Successful Completion [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Rows 1981, bytes 5150600 retrieved from database query id: <<9955741>> [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Physical query response time 5 (seconds), id <<9955741>> [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Rows 5, bytes 1080 retrieved from database query id: <<9956100>> [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Physical query response time 5 (seconds), id <<9956100>> [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Physical Query Summary Stats: Number of physical queries 2, Cumulative time 10, DB-connect time 0 (seconds) [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Rows returned to Client 1981 [[
]]
[2019-11-21T23:30:23.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 42374480502d170a:-1281b15f:16e8e202b6d:-8000-000000000001c948,0:1:1:6:7] [tid: 4b4] [requestid: 3e870016] [sessionid: 3e870000] [username: KanickiK] -------------------- Logical Query Summary Stats: Elapsed time 6, Response time 5, Compilation time 0 (seconds) [[
]]