Clarity

  • 1.  Error: NPT-217 issue

    Posted Aug 03, 2018 05:12 PM

    Recently we are having the following error in a query of "Monthly Incurred Hours" that was not modified.

    Could you guide us in solving this problem?

     

    ErrorNPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete.

     

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:HORAS:L1.name||INV.id||ASG.prtaskid||PRM_ROLE.unique_name||ASG.prresourceid||to_char(SLC.slice_date,'mm')||to_char(SLC.slice_date,'yyyy'):id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ROW_NUMBER() ASG.prtaskid:task_int_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TSK.prexternalid:task_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TSK.prname:task_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG.prresourceid:resource_int_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:dep.id:gerencia_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:dep.DEPARTCODE:gerencia_n@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:UNT_P.id:gerencia_obs_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:UNT_P.name:gerencia_obs_n@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RES.unique_name:resource_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RES.full_name:resource_full_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when RES.is_external=0 then 'No' else 'Si' end:contratado@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.id:inv_int_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.code:inv_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.name:inv_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:par_2.name:Proyecto_progr@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:par_2.padre_code:progr_cod@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when par.name is null then inv.name else par.name end:Proyecto_Padre@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when par.padre_code is null then inv.code else par.padre_code end:padre_cod@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:l5.name:Objetivo:objetivo@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L6.name:prioridad_n:prioridad_cliente@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_prioridad_p_clnt:prioridad_clnt_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when INV.odf_object_code = 'project' or INV.odf_object_code = 'idea' then L1.name else case when INV.odf_object_code = 'application' then 'Mantenimiento' else l4.name end end:inv_tipo_proyecto@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.odf_object_code:inv_tipo_inversion@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:inv3.sb_tipo_proyecto:tipo_proyecto_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_area_req:area_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_area_req2:subarea_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_hs_indiirectas:hs_indirectas@,

             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_prioridad_p_clnt:prioridad_neg_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L6.name:prioridad_neg_name@,

             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:inv4.sb_req_procesos:req_ing_procesos@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when inv4.sb_req_procesos = 1 then 'S' else 'N' end:req_ing_procesos_x@,

             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L2.name:inv_area@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L3.name:inv_subarea@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L4.name:inv_tipo_otro_trabajo@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV5.sb_tipo_otro_trabajo:inv_tipo_otro_trabajo_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.unique_name:Grupo_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.full_name:Grupo_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.id:Grupo_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV_ROLE.unique_name:invrole_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV_ROLE.full_name:grupo_proyecto_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG_ROLE.unique_name:assignmentrole_code@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG_ROLE.full_name:grupo_tarea_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:COMP.company_id:company_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:COMP.company_name:company_name@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:to_number(to_char(SLC.slice_date,'mm')):mes@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:to_number(to_char(SLC.slice_date,'yyyy')):anio@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TIM.full_name:manager_n@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TIM.user_id:manager_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RM.full_name:rm_n@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:rm.id:rm_id@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L10.name:clasif_recur@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:recu_odf.sb_clasif_rec:clasif_recur_id@,

             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:recu_odf.sb_tipo_recurso:tipo_recurso_contratado@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when recu_odf.sb_tipo_recurso IS NULL THEN 'Efectivo' else LR4.name end:tipo_recurso_contratado_n@,

             @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:sum(SLC.slice):slice@


    FROM     PRJ_BLB_SLICES SLC

             left outer join PRJ_BLB_SLICEREQUESTS REQ on SLC.slice_request_id = REQ.id
             left outer join PRASSIGNMENT ASG          on SLC.prj_object_id = ASG.prid
             left outer join PRTASK TSK                on ASG.prtaskid = TSK.prid
             left outer join INV_INVESTMENTS INV       on TSK.prprojectid = INV.id
             left outer join ODF_CA_INV INV3           on INV.id = INV3.id
             left outer join ODF_CA_OTHER INV5         on INV.id = INV5.id
             left outer join PRTEAM T                  on ASG.team_id = T.prid
             left outer join SRM_RESOURCES RES         on ASG.prresourceid = RES.id
             left outer join PRJ_RESOURCES RES_G       on RES.id = RES_G.prid
             left outer join ODF_CA_RESOURCE recu_odf  on RES.id = recu_odf.id
             left outer join SRM_RESOURCES PRM_ROLE    on RES_G.prprimaryroleid = PRM_ROLE.id

             /* proveedor */
             LEFT OUTER JOIN ODF_CA_RESOURCE COMP_RES  ON RES.ID = COMP_RES.ID
             left outer join SRM_COMPANIES COMP        on COMP.company_id = COMP_RES.SB_COMPANIA

             left outer join ODF_CA_RESOURCE res_odf   on prm_role.id = res_odf.id
             left outer join DEPARTMENTS dep           on res_odf.sb_gerencia_agrup = dep.id
             left outer join SRM_RESOURCES INV_ROLE    on T.prroleid = INV_ROLE.id
             left outer join SRM_RESOURCES ASG_ROLE    on ASG.role_id = ASG_ROLE.id

          /* Tablas Gerencia y Gerente NUEVA */
           left outer join
             (select ASSOC.record_id,
                     ASSOC.unit_id
             from PRJ_OBS_ASSOCIATIONS ASSOC,
                  PRJ_OBS_UNITS UNT,
                  PRJ_OBS_TYPES TYP
             where UNT.type_id = TYP.id
               and ASSOC.unit_id = UNT.id
               and ASSOC.table_name = 'SRM_RESOURCES'
               and TYP.unique_name ='sb_dept') RES_ASSOC
           on RES.id = RES_ASSOC.record_id
           left outer join PRJ_OBS_UNITS UNIT         on RES_ASSOC.unit_id = UNIT.id
           left outer join
              (select FLAT.unit_id,
                    FLAT.branch_unit_id,
                    DEP.id
            FROM
                 PRJ_OBS_UNITS_FLAT FLAT,
                 PRJ_OBS_UNITS OBS,
                 DEPARTMENTS DEP,
                 ODF_CA_DEPARTMENT DEP2
              where FLAT.branch_unit_id = OBS.id
                and OBS.id = DEP.obs_unit_id
                and DEP.id = DEP2.id
                and DEP2.sb_es_gerencia = 1 ) FLT
              on RES_ASSOC.unit_id = FLT.unit_id
              left outer join PRJ_OBS_UNITS UNT_P    on FLT.branch_unit_id = UNT_P.id
            
           /* Tabla con etiquetas de Tipo Proyecto */
              left outer join CMN_LOOKUPS_V L1
              on (INV3.sb_tipo_proyecto = L1.lookup_code
                  and L1.lookup_type = 'SB_TIPO_PROYECTO'
                  and L1.language_code = 'en')

           /* Tabla con etiquetas de Area de Requerimiento */
              left outer join CMN_LOOKUPS_V L2
              on (INV3.sb_area_req = L2.lookup_code
                  and L2.lookup_type = 'SB_AREAS_REQ'
                  and L2.language_code = 'en')


           /* Tabla con etiquetas de Subarea de Requerimiento */
              left outer join CMN_LOOKUPS_V L3
              on (INV3.sb_area_req2 = L3.lookup_code
                  and L3.lookup_type = 'SB_AREAS_REQ2'
                  and L3.language_code = 'en')


           /* Tabla con etiquetas de Tipo Otro Trabajo*/
              left outer join CMN_LOOKUPS_V L4
              on (INV5.sb_tipo_otro_trabajo = L4.lookup_code
                  and L4.lookup_type = 'SB_TIPO_OTRO_TRABAJO'
                  and L4.language_code = 'en')

           /* objetivo*/
             LEFT OUTER JOIN cmn_lookups_v L5
             on (L5.language_code = 'en' and  INV.GOAL_CODE=
                 L5.lookup_code and l5.lookup_type = 'INVESTMENT_GOAL_TYPE')


          /*Tabla con de prioridades por cliente*/
            left outer join CMN_LOOKUPS_V L6
            on (INV3.sb_prioridad_p_clnt = L6.lookup_code
            and L6.lookup_type = 'SB_PRIORIDAD_POR_CLIENTE'
            and L6.language_code = 'en')


          /* padres  */
             left outer join
                  (select id, child_id, name, padre_code from
                  (select inv.id, HIE.child_id, inv.name, inv.code padre_code from
                  INV_HIERARCHIES HIE left outer join INV_INVESTMENTS INV
                  on HIE.parent_id = INV.id left outer join INV_PROJECTS PRJ
                  on HIE.parent_id = PRJ.prid  where HIE.parent_id IS NOT NULL
                  and INV.odf_object_code = 'project' and prj.is_program = 0) parent ) par
              on inv.id = par.child_id

          /* Tabla programas */
           left outer join
                (select id, child_id, name, padre_code from
                (select inv.id, HIE.child_id, inv.name, inv.code padre_code from
                 INV_HIERARCHIES HIE left outer join INV_INVESTMENTS INV
                 on HIE.parent_id = INV.id left outer join INV_PROJECTS PRJ
                 on HIE.parent_id = PRJ.prid  where HIE.parent_id IS NOT NULL
                 and INV.odf_object_code = 'project' and prj.is_program=1) parent ) par_2
             on inv.id = par_2.child_id


            /* Tabla con Manager, Gestor, Responsable o TIM */
                   left outer join SRM_RESOURCES TIM
                    on INV.manager_id = TIM.user_id

            /* Tabla con RM Asignado */
              left outer join ODF_CA_PROJECT INV4
               on INV.id = INV4.id

              left outer join SRM_RESOURCES RM
              on INV3.sb_rm = RM.id
       
           /* Tabla con etiquetas de Prioridad de Unidad de Negocio*/
             left outer join CMN_LOOKUPS_V L9
              on (INV3.obj_align_factor2 = l9.lookup_code
              and l9.lookup_type = 'OBJ_INVESTMENT_ALIGNMENT_ENUM'
              and l9.language_code = 'en')

           /* Tabla con etiquetas de Clasificacion Recursos*/
              LEFT OUTER JOIN cmn_lookups_v L10 ON            (L10.language_code = @WHERE:PARAM:LANGUAGE@
                and recu_odf.sb_clasif_rec = L10.lookup_enum    and L10.lookup_type = 'SB_CLASIF_REC')

           left outer join CMN_LOOKUPS_V LR4              on     (LR4.lookup_code = recu_odf.sb_tipo_recurso
           and LR4.lookup_type = 'SB_TIPOS_RECURSO_LISTA'        and LR4.language_code = @WHERE:PARAM:LANGUAGE@)

    WHERE REQ.request_name = 'Datos Reales - Mensual'
      AND
      ( res_odf.sb_funcion_es_factur = @WHERE:PARAM:USER_DEF:INTEGER:ES_FACTUR@
        OR @WHERE:PARAM:USER_DEF:INTEGER:ES_FACTUR@ IS NULL )

      AND ( INV.code != @WHERE:PARAM:USER_DEF:STRING:ignorar_inv_code@ or @WHERE:PARAM:USER_DEF:STRING:ignorar_inv_code@ is null )
      AND @FILTER@


    GROUP BY L1.name||INV.id||ASG.prtaskid||PRM_ROLE.unique_name||ASG.prresourceid||to_char(SLC.slice_date,'mm')||to_char(SLC.slice_date,'yyyy'),
             ASG.prtaskid,
             TSK.prexternalid,
             TSK.prname,
             ASG.prresourceid,
             RES.unique_name,
             RES.full_name,
             case when RES.is_external=0 then 'No' else 'Si' end,
             INV.id,
             INV.code,
             INV.name,
             par_2.name,
             par_2.padre_code,
             case when par.name is null then inv.name else par.name end,
             case when par.padre_code is null then inv.code else par.padre_code end,
             l5.name,
             l6.name,
             INV3.sb_prioridad_p_clnt,
            case when INV.odf_object_code = 'project' or INV.odf_object_code = 'idea' then L1.name else case when INV.odf_object_code = 'application' then 'Mantenimiento' else l4.name end end,
             INV.odf_object_code,
             inv3.sb_tipo_proyecto,
             INV3.sb_area_req,
             INV3.sb_area_req2,
             INV3.sb_hs_indiirectas,
             INV3.sb_prioridad_p_clnt,
             L6.name,
             inv4.sb_req_procesos,
             case when inv4.sb_req_procesos = 1 then 'S' else 'N' end,
             L2.name ,
             L3.name ,
             L4.name ,
             INV5.sb_tipo_otro_trabajo,
             PRM_ROLE.unique_name,
             PRM_ROLE.full_name,
             INV_ROLE.unique_name,
             INV_ROLE.full_name,
             ASG_ROLE.unique_name,
             ASG_ROLE.full_name,
             COMP.company_id,
             COMP.company_name,
             to_number(to_char(SLC.slice_date,'mm')),
             to_number(to_char(SLC.slice_date,'yyyy')),
             TIM.full_name,
             TIM.user_id,
             RM.full_name,
             rm.id,
             L10.name,
             recu_odf.sb_clasif_rec,
             PRM_ROLE.id,
             dep.id,
             dep.DEPARTCODE,
             UNT_P.id,
             recu_odf.sb_tipo_recurso,
             case when recu_odf.sb_tipo_recurso IS NULL THEN 'Efectivo' else LR4.name end,
             UNT_P.name
    ORDER BY INV.code,
             TSK.prname,
             PRM_ROLE.full_name,
             RES.unique_name,
             to_number(to_char(SLC.slice_date,'mm')),
             to_number(to_char(SLC.slice_date,'yyyy')),
             RES.unique_name


  • 2.  Re: Error: NPT-217 issue

    Posted Aug 04, 2018 02:25 AM

    Hi RnFernandez ,

     

    Dimension key ie value passed in  

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:HORAS:L1.name||INV.id||ASG.prtaskid||PRM_ROLE.unique_name||ASG.prresourceid||to_char(SLC.slice_date,'mm')||to_char(SLC.slice_date,'yyyy'):id@,

    wont have unique value for each row,so you need to prepare a sting which is unique, Besides this you can also use Row number function as dimension key to avoid this error, but you need to make sure that your query result is correct like wont have unnecessary duplicate result. 

     

    -- SQL Server
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:HORAS:ROW_NUMBER():id@,

    -- Oracle
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:HORAS:ROWNUM:id@,

     

    Regards,

    Prashank Singh



  • 3.  Re: Error: NPT-217 issue

    Posted Aug 05, 2018 07:55 PM

    and to add to this, as the original 'id' was a string attribute, please consider

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:HORAS:to_char(ROWNUM):id@

    Otherwise, you will have to delete the portlet(s) to use this attribute as a numeric attribute.