TNPMW: Summaries using Pcalcs with aggregator do not produce correct result

Resolving The Problem

Root Cause

This is because the default aggregator of Pcalc is applied at time scope level. In this case, Pcalc has Avg aggregator, which results in single row for specific CBSSB_Cell_Carrier_id for that day. Hence, Sum or Max or Min of single row will result in same row.

INSERT /* ctxt=REPORT tnpmklsunz14.persistent.co.in_10.44.240.78 */ INTO ABHI_PCAL_SUMM#_DSM (summary_id, CBSSB_CELL_CARRIER_ID, instance_id, tstamp, SUMSUM_T3NR1R2BDX2AIUH1J035XRO, MINMIN_T3NR1R2BDX2AIUH1J035XRO, MAXMAX_T3NR1R2BDX2AIUH1J035XRO) (
SELECT /* TheReport */ 1083,
DECODE(CBSSB_Cell_Carrier.E0_CBSSB_Cell_Carrier_Id, ' grp ', null, null, '-null-', CBSSB_Cell_Carrier.E0_CBSSB_Cell_Carrier_Id) E0_CBSSB_Cell_Carrier_Id,
DECODE(CBSSB_Cell_Carrier.E0_instance_id, ' grp ', null, null, '-null-', CBSSB_Cell_Carrier.E0_instance_id) E0_instance_id,
DECODE(CBSSB_Cell_Carrier.t_day, ' grp ', null, null, '-null-', CBSSB_Cell_Carrier.t_day) t_day,
(CBSSB_Cell_Carrier.xp$2)/(3600) XP1,
CBSSB_Cell_Carrier.xp$4$min XP3,
CBSSB_Cell_Carrier.xp$6$max XP5
FROM
(SELECT /*+ ORDERED */
/* CBSSB_Cell_Carrier ER */
TimeScope.t_day t_day,
E0_CBSSB_Cell_Carrier_Id E0_CBSSB_Cell_Carrier_Id,
min(E0_instance_id) E0_instance_id,
sum( TimeScope.xp$2 ) xp$2,
min( (TimeScope.xp$2)/(3600) ) xp$4$min,
max( (TimeScope.xp$2)/(3600) ) xp$6$max,
sum(TimeScope.wm__avail) wm__avail
FROM
(SELECT /*+ ORDERED USE_HASH(cp) USE_HASH(ZTE_DOCELFCHERL_TAB) PARALLEL(ZTE_DOCELFCHERL_TAB 2) */
/* TimeScope */
cp.E0_CBSSB_Cell_Carrier_Id E0_CBSSB_Cell_Carrier_Id,
to_char(trunc(cp.tstamp, 'DD'), 'YYYY-MM-DD') t_day,
min(cp.instance_id) E0_instance_id,
min(cp.instance_id) instance_id,
min(E0_vendor_id) E0_vendor_id,
avg(ZTE_DOCELFCHERL_TAB.W0B0R16A4C2AIUH1J035XROK0M) xp$2,
sum(nvl2(ZTE_DOCELFCHERL_TAB.instance_id, 1, 0)) wm__avail
FROM
(SELECT /*+ ORDERED USE_HASH(bt) CARDINALITY(bt 100000) */
/* cp */
bt.value value,
bt.tstamp tstamp,
NC_CBSSB_CELL_CARRIER.CBSSB_CELL_CARRIER_ID E0_CBSSB_Cell_Carrier_Id,
NC_CBSSB_CELL_CARRIER.nc_id instance_id,
NC_CBSSB_CELL_CARRIER.nc_id traffic_instance_id,
NC_CBSSB_CELL_CARRIER.vendor_id E0_vendor_id
FROM
NC_CBSSB_CELL_CARRIER NC_CBSSB_CELL_CARRIER,
rg_nc_busyhour_tmp bt
WHERE
bt.tstamp BETWEEN NC_CBSSB_CELL_CARRIER.timestamp AND NC_CBSSB_CELL_CARRIER.endstamp
AND (NC_CBSSB_CELL_CARRIER.CBSSB_CELL_CARRIER_ID = bt.localkey)
AND (NC_CBSSB_CELL_CARRIER.vendor_id IN (21))
AND (bt.value IS NOT NULL)) cp,
ZTE_DOCELFCHERL_TAB ZTE_DOCELFCHERL_TAB
WHERE
ZTE_DOCELFCHERL_TAB.instance_id (+)= cp.traffic_instance_id
AND (ZTE_DOCELFCHERL_TAB.tstamp (+)= cp.tstamp)
AND (ZTE_DOCELFCHERL_TAB.tstamp (+) BETWEEN to_date('2012-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND to_date('2012-09-25 23:30:00', 'YYYY-MM-DD HH24:MI:SS'))
GROUP BY cp.E0_CBSSB_Cell_Carrier_Id,
to_char(trunc(cp.tstamp, 'DD'), 'YYYY-MM-DD')) TimeScope
GROUP BY TimeScope.t_day,
E0_CBSSB_Cell_Carrier_Id) CBSSB_Cell_Carrier
)


Workaround

Since the Pcalcs do not bring any value to the result, you can decide to remove them from the definition so that it would not perform any calculation during summary execution and also drop the unnecessary column for the Pcalcs from summary tables.

Procedure:
1. Export summary definition.
2. Remove aggregation type for the Pcalcs in the exported definition and save it.
3. Delete the summary from the system.
4. Reimport the exported definition.
5. Rerun summary.



Proposed Solution

There are two solutions to avoid such aggregation to be used.

1. All new tech pack solutions must not provide such aggregation for Pcalcs in summary.
2. Do not include Pcalc with aggregation for any summary definition.