AnsweredAssumed Answered

NPT-217: This query produced duplicate dimensional data.

Question asked by ChrisHardie on May 12, 2011
Latest reply on May 16, 2011 by Owen_R
Hi,

I'm new to Clarity, but have attempted to include a new field into a pre-existing query to be displayed in the portlet. However, when I display the portlet, I get the error: NPT-217: This query produced duplicate dimensional data.

From what I've read, this error is produced when a dimension key has non-unique values. However, the field I added is not a dimension key. I've included the query below, and have marked my additions in [color=#FF0303]red[color]. Thanks!
SELECT 
@SELECT:DIM:USER_DEF:IMPLIED:INC:(unique_id):unique_id@, 
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:period_name:period_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:cc_name:cc_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:cc_id:cc_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:full_name:full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:resource_id:resource_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:manager_id:manager_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:type_name:type_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:type_id:type_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:training:training@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:internal_np:internal_np@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:statutory:statutory@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:sick:sick@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:vacation:vacation@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:other:other@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:total_nb:total_nb@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:total_bill:total_bill@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:total_hours:total_hours@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_fm:bill_fm@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_fees:bill_fees@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_int_prj:bill_int_prj@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_business:bill_business@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:paid_hours:paid_hours@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_percent:bill_percent@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_business_dollars:bill_business_dollars@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:paid_overtime:paid_overtime@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_contract:bill_contract@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_z:bill_z@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_d:bill_d@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_c:bill_c@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_b:bill_b@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_e:bill_e@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_a:bill_a@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_except:bill_except@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_itd_prj_diff:bill_itd_prj_diff@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:bill_itd_prj_same:bill_itd_prj_same@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:fin_task_other:fin_task_other@,
[color=#FF0303]@SELECT:DIM_PROP:USER_DEF:IMPLIED:INC:jdi_trans_type:jdi_trans_type@[/color]
from
(SELECT
(unique_id) unique_id,
period_name period_name,
cc_name cc_name,
cc_id cc_id,
full_name full_name,
resource_id resource_id,
type_name type_name,
type_id type_id,
training training,
internal_np internal_np,
statutory statutory,
sick sick,
manager_id,
vacation vacation,
other other,
total_nb total_nb,
total_bill total_bill,
total_hours total_hours,
(bill_fm) bill_fm,
bill_fees bill_fees,
bill_int_prj bill_int_prj,
bill_business bill_business,
bill_business_dollars bill_business_dollars,
paid_overtime paid_overtime,
bill_contract bill_contract,
bill_z bill_z,
bill_d bill_d,
bill_c bill_c,
bill_e bill_e,
bill_b bill_b,
bill_a bill_a,
(bill_zero+bill_except) bill_except,
bill_itd_prj_diff bill_itd_prj_diff,
bill_itd_prj_same bill_itd_prj_same,
fin_task_other fin_task_other,
[color=#FF0303]jdi_trans_type jdi_trans_type,[/color]
CASE when (type_id = 300 and partial_month = 0 and period_name <> '2009-13') then (150+nvl(overtime.act_overtime,0))
     when (type_id = 300 and partial_month = 0 and period_name = '2009-13') then (187.5+nvl(overtime.act_overtime,0)) else total_hours end paid_hours,
(total_bill/(CASE when (type_id = 300 and partial_month = 0) then (150+nvl(overtime.act_overtime,0)) ELSE (CASE WHEN total_hours = 0 THEN 1 ELSE total_hours END) end)*100) bill_percent


from
(SELECT
unique_id unique_id,
period_name period_name,
cc_name cc_name,
cc_id cc_id,
manager_id,
full_name full_name,
resource_id resource_id,
jdi_pd_ot paid_overtime,
type_name type_name,
type_id type_id,
partial_month,
[color=#FF0303]jdi_trans_type,[/color]
Sum(training) training,
Sum(internal_np_diff) internal_np,
Sum(statutory) statutory,
Sum(sick) sick,
Sum(vacation) vacation,
Sum(other+over_used+internal_np_same) other,
Sum(training+internal_np_same+internal_np_diff+statutory+sick+vacation+other+over_used) total_nb,
Sum(bill_itd_prj+bill_bus_hours+bill_fees+bill_fm) total_bill,
Sum(bill_fm) bill_fm,
Sum(bill_fees) bill_fees,
Sum(bill_itd_prj) bill_int_prj,
Sum(bill_bus_hours+bill_fees) bill_business,
Sum(bill_bus_dollars+bill_fees_dollars) bill_business_dollars,
Sum(training+internal_np_same+internal_np_diff+statutory+sick+vacation+other+bill_itd_prj+bill_bus_hours+bill_fees+bill_fm+over_used) total_hours,
Sum(bill_z) bill_z,
Sum(bill_a) bill_a,
Sum(bill_b) bill_b,
Sum(bill_c) bill_c,
Sum(bill_d) bill_d,
Sum(bill_e) bill_e,
Sum(bill_except) bill_except,
Sum(bill_zero) bill_zero,
Sum(bill_contract) bill_contract,
Sum(bill_itd_prj_same) bill_itd_prj_same,
Sum(fin_task_other) fin_task_other,
Sum(bill_itd_prj_diff) bill_itd_prj_diff

FROM
(SELECT (per.period_name || res.id) unique_id, res.full_name, res.id resource_id, typelu.NAME type_name,
res.manager_id, per.period_name period_name, cc.NAME cc_name, cc.id cc_id, i.code, r.jdi_pd_ot,
Sum(actual_qty) actuals, typelu.id type_id, [color=#FF0303]odf_ytd.jdi_trans_type jdi_trans_type[/color],
/*
Comment: Fin Task Class 1=External Billable, 2= ITD Admin, 3= ITD FM, 4= ITD Main, 5=ITD project,
6 = ITD Mgt Fees, 7 = Other
*/
CASE when ((tk.prchargecodeid = 5000009 AND cc.jdi_id != bu.jdi_id AND tsk.jdi_fin_task_class IN ('4','2')))
     THEN Sum(actual_qty) ELSE 0 END internal_np_diff,
CASE when ((tk.prchargecodeid = 5000009 AND cc.jdi_id = bu.jdi_id AND tsk.jdi_fin_task_class IN ('4','2')))
     THEN Sum(actual_qty) ELSE 0 END internal_np_same,
CASE when i.code = 'TRAN' THEN Sum(actual_qty) ELSE 0 END training,
CASE when i.code = 'STAT' THEN Sum(actual_qty) ELSE 0 END statutory,
CASE when i.code = 'SICK' THEN Sum(actual_qty) ELSE 0 END sick,
CASE when i.code = 'OTUS' THEN Sum(actual_qty) ELSE 0 END over_used,
CASE when i.code = 'VACT' THEN Sum(actual_qty) ELSE 0 END vacation,
CASE when i.code in ('BVMT','INSU','ABST') THEN Sum(actual_qty) ELSE 0 END other,
CASE when tsk.jdi_fin_task_class IN ('3') THEN Sum(actual_qty) ELSE 0 END bill_fm,
CASE when tsk.jdi_fin_task_class IN ('6') THEN Sum(actual_qty) ELSE 0 END bill_fees,
CASE when tsk.jdi_fin_task_class IN ('6') THEN Sum(actual_qty*cost_rate) ELSE 0 END bill_fees_dollars,
CASE when (tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1'))
    THEN Sum(actual_qty) ELSE 0 END bill_bus_hours,
CASE when (tk.prchargecodeid = 5000009 AND tsk.jdi_fin_task_class IN ('5'))
    THEN Sum(actual_qty) ELSE 0 END bill_itd_prj,
CASE when (tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1'))
    THEN Sum(actual_qty*cost_rate) ELSE 0 END bill_bus_dollars,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class in ('A','B','C','D','E') AND cost_rate = 60 AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_a,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class in ('A','B','C','D','E') AND cost_rate = 65 AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_b,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class in ('A','B','C','D','E') AND cost_rate = 80 AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_c,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class in ('A','B','C','D','E') AND cost_rate = 85 AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_d,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class in ('A','B','C','D','E','F') AND cost_rate = 40 AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_e,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND cost_rate in (0) AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_zero,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND cost_rate not in (40,60,65,80,85,0) AND fr.transclass = 'EMPLOYEE')
     THEN Sum(actual_qty) ELSE 0 END bill_except,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.resource_class = 'Z' AND typelu.id = 300)
     THEN Sum(actual_qty) ELSE 0 END bill_z,
CASE when (((tk.prchargecodeid = 5000010 AND tsk.jdi_fin_task_class IN ('1')))
     AND fr.transclass = 'CONTRACT')
     THEN Sum(actual_qty) ELSE 0 END bill_contract,
CASE when ((tk.prchargecodeid = 5000009 AND tsk.jdi_fin_task_class IN ('5'))
     AND cc.jdi_id != bu.jdi_id)
    THEN Sum(actual_qty) ELSE 0 END bill_itd_prj_diff,
CASE when ((tk.prchargecodeid = 5000009 AND tsk.jdi_fin_task_class IN ('5'))
     AND cc.jdi_id = bu.jdi_id) THEN Sum(actual_qty) ELSE 0 END bill_itd_prj_same,
CASE when (tk.prchargecodeid = 5000009 AND tsk.jdi_fin_task_class IN ('7')) THEN Sum(actual_qty) ELSE 0 END fin_task_other,
CASE when (res.date_of_termination < (per.end_date - 8) or res.date_of_hire > (per.start_date - 7)) then 1 else 0 end partial_month

                                             
FROM nbi_project_res_task_facts t, inv_investments i, srm_resources res,
biz_com_periods per, odf_ca_resource r, odf_ca_jdi_cost_center cc, odf_ca_jdi_company comp,
odf_ca_task tsk, prtask tk, pac_mnt_resources fr, odf_ca_jdi_cost_center bu,[color=#FF0303] odf_ca_jdi_ytd_data odf_ytd,[/color]
(SELECT p.id, p.jdi_classification
FROM odf_ca_project p)p,
(SELECT id, name FROM cmn_lookups_v
WHERE lookup_type = 'SRM_RESOURCE_TYPE'
AND language_code = 'en') typelu


WHERE actual_qty > 0
and tsk.jdi_company = comp.code (+)
AND comp.jdi_business_unit = bu.id (+)
AND i.id = t.project_id
AND t.resource_id = res.id
AND r.id = res.id
AND t.task_id = tsk.id
AND tsk.id = tk.prid
AND i.id = p.id (+)
AND fr.id = res.id
AND res.person_type = typelu.id
AND r.jdi_mgmt_group = cc.id
AND r.jdi_job_type in ('jdi_prof', 'jdi_tech')
AND per.period_type = '13_PERIODS_PER_YEAR'
AND t.fact_Date BETWEEN (per.start_date-7) AND (per.end_date - 8)
[color=#FF0303]AND odf_ytd.jdi_task_id = t.task_id[/color]

GROUP BY res.date_of_termination, per.end_date, per.start_Date, res.date_of_hire, res.manager_id, typelu.id, cost_rate, i.chargecodeid, tk.prchargecodeid, fr.resource_class, fr.transclass,
res.full_name, per.period_name, typelu.NAME, cc.NAME, cc.id, r.jdi_pd_ot, cc.jdi_id, bu.jdi_id,
res.id, i.code, i.odf_object_code, tsk.jdi_company, p.jdi_classification, tsk.jdi_fin_task_class, [color=#FF0303]odf_ytd.jdi_trans_type)[/color] data


GROUP BY manager_id, partial_month, unique_id, period_name,cc_name,cc_id,full_name, jdi_pd_ot,
resource_id,type_name, type_id, [color=#FF0303]jdi_trans_type[/color]) details,

(SELECT (per.period_name || res.id) u_id, sum(practsum/3600) act_overtime
FROM prtimeentry te, prtimesheet ts, srm_resources res,
prtimeperiod tp, biz_com_periods per
WHERE te.prtimesheetid = ts.prid
AND ts.prresourceid = res.id
AND ts.prtimeperiodid = tp.prid
and ts.prid not in (select pradjustedid from prtimesheet where pradjustedid is not null)
AND per.period_type = '13_PERIODS_PER_YEAR'
AND tp.prstart BETWEEN (per.start_date-7) AND (per.end_date - 8)
AND prtypecodeid = 5000002  /* Overtime */
GROUP BY (per.period_name || res.id)) overtime

where details.unique_id = overtime.u_id (+)
) data

WHERE @FILTER@

Outcomes