Clarity

  • 1.  Portlet with duplicate dimensional data

    Posted Oct 20, 2016 04:28 AM

    Portlet is showing error: NPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete

    Query is:

    SELECT DISTINCT
    @SELECT:DIM:USER_DEF:IMPLIED:mngprojects:inv.ID:inv_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.NAME:inv_NAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:INV.IS_ACTIVE:ACTIVE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:SRM.USER_ID:MGR_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:SRM.FULL_NAME:MGR_NAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:proj.obj_stakeholder2:SPON_NAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:res_spon.ID:SPON_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:res_spon.FULL_NAME:SPONSOR@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:program.program:program@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:program.parent_id:program_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.percent_complete:percent_complete@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.prGuidelines:strat_inicijativa@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:odf.hp_priority2:hp_priority2@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:odf.hp_project_status:hp_project_status@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:unit.id:project_unit_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:unit.name:project_unit_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT(inv.schedule_start):schedule_start@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT(inv.schedule_finish):schedule_finish@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.STATUS_INDICATOR:STATUS_INDICATOR@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.STAGE_CODE:Stage_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:appr.name:Stage_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:late.days_late:Days_late@,
    @SELECT:METRIC:USER_DEF:IMPLIED:late.days_late:Days_late_sl@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT( BaseL.start_date):base_start@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT( BaseL.finish_date):base_finish@,
     @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.prusertext1:project_code@

    FROM inv_investments inv
    INNER JOIN INV_PROJECTS PRJ ON PRJ.PRID = INV.ID
    AND @NVL@(PRJ.IS_TEMPLATE,0) = 0

    inner join COP_inv_DAYS_LATE_V  late on inv.id=late.investment_id


    INNER JOIN ODF_CA_PROJECT ODF ON ODF.ID = INV.ID

    LEFT OUTER JOIN PRJ_OBS_ASSOCIATIONS ASSOC ON INV.ID = ASSOC.RECORD_ID
    AND    ASSOC.TABLE_NAME = 'SRM_PROJECTS'

    LEFT OUTER JOIN PRJ_OBS_UNITS_FLAT FLAT ON ASSOC.UNIT_ID = FLAT.UNIT_ID

    LEFT OUTER JOIN PRJ_OBS_UNITS UNIT ON FLAT.UNIT_ID = UNIT.ID

    LEFT OUTER JOIN CMN_LOOKUPS_V APPR
    ON APPR.LOOKUP_CODE = inv.stage_code
    AND APPR.LOOKUP_TYPE = 'INV_STAGE_TYPE'
    AND APPR.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@

     

    left outer JOIN SRM_RESOURCES SRM
    ON inv.manager_id = SRM.USER_ID

    LEFT OUTER JOIN prj_baseline_details BaseL ON BaseL.IS_Current = 1
    AND BaseL.Object_Type = 'PROJECT'
    AND BaseL.OBJECT_ID =  inv.id

    inner join odf_ca_project proj on inv.id=proj.id

    inner join srm_resources res_spon on proj.obj_stakeholder2=res_spon.id

    inner join v_hp_program_for_portlet program on inv.id=program.project_id

     

    where @FILTER@
    AND (@WHERE:SECURITY:INV:INV.ID@)
    AND    ((FLAT.BRANCH_UNIT_ID IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:FLAT.BRANCH_UNIT_ID:PROJECT_OBS@))

     

    What should be changed?

     

    Thank you!



  • 2.  Re: Portlet with duplicate dimensional data

    Posted Oct 20, 2016 04:48 AM

    The dimension of a NSQL portlet needs to be unique.

     

    You have defined the dimension like this;

    @SELECT:DIM:USER_DEF:IMPLIED:mngprojects:inv.ID:inv_ID@,

     

    So you get the message just because your query is not bringing back one record per project (inv.ID).

     

    if your query is designed to be one record per project then you need to fix your SQL, if your query is designed to bring back multiple record per project then you need to choose (or construct) a different unique dimension.

     

    (search for that error message in these discussions ; you will get many results)