Clarity

Expand all | Collapse all

Error:  NPT-217: This query produced duplicate dimensional data.

Jump to Best Answer
  • 1.  Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-16-2018 11:43 AM

    Hello everyone

    I am relatively new to SQL and NSQL. I’ve created a query in Jaspersoft Studio which provides detailed information out of the assignment and the task. Inside Studio the result is shown as wanted and during the setup of the Query inside CA PPM didn’t occur any error message. I’ve tried to change the query, so the error doesn’t happen again. Unfortunately, I do not know any further. Can someone tell me what I could do to correct the error?

    Following the query:

    SELECT
    inv.CODE AS projekt_id
    ,inv.IS_ACTIVE
    ,ip.IS_TEMPLATE
    ,oi.Z_DIVISION AS divison
    ,op.Z_PPMID AS ppm_id
    ,CASE pt.PRSTATUS
        WHEN 0 THEN 'geplant'
        WHEN 1 THEN 'freigegeben'
        WHEN 2 THEN 'abgeschlossen' END AS phasenstatus
    ,dtv.Z_PROCESS_CAPTION AS Projektprozess
    ,pt.PRNAME AS phasenname
    ,pt.PRMETHODEXTID AS phasen_id
    ,NVL(ot.Z_PSP_PHASE, 0) AS psp
    ,to_char(pt.PRSTART, 'dd.mm.YYYY') AS ende_phase
    ,to_char(pt.PRFINISH, 'dd.mm.YYYY') AS start_phase
    ,CASE WHEN pka_annual.RESSOURCE ='IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) THEN NVL(PKA_ANNUAL.PKA_PAST_YEAR_IT,0) END PKA_PAST_YEAR_IT
    ,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) THEN NVL(PKA_ANNUAL.PKA_PAST_YEAR_BUS,0) END PKA_PAST_YEAR_BUS
    ,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM SYSDATE) THEN NVL(PKA_ANNUAL.PKA_CURRENT_YEAR_IT,0) END PKA_CURRENT_YEAR_IT
    ,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM SYSDATE) THEN NVL(PKA_ANNUAL.PKA_CURRENT_YEAR_BUS,0) END PKA_CURRENT_YEAR_BUS
    ,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_1_IT,0) END PKA_YEAR_1_IT
    ,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_1_BUS,0) END PKA_YEAR_1_BUS
    ,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_2_IT,0) END PKA_YEAR_2_IT
    ,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_2_BUS,0) END PKA_YEAR_2_BUS
    FROM INV_INVESTMENTS inv
    JOIN INV_PROJECTS ip ON inv.ID = ip.PRID
    JOIN ODF_CA_PROJECT op ON inv.ID = op.ID
    JOIN ODF_CA_INV oi ON inv.ID = oi.ID
    JOIN ODF_TASK_V dtv ON inv.ID = dtv.PRPROJECTID
    JOIN PRTASK pt ON inv.ID = pt.PRPROJECTID
    JOIN ODF_CA_TASK ot ON pt.PRID = ot.ID





    left Join(    select distinct
        INV.ID AS INV_ID
        ,PT.PRID
        ,srm.FULL_NAME ressource
        ,extract(year from sl.slice_date) p_year
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_PAST_YEAR_IT
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_PAST_YEAR_BUS
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_CURRENT_YEAR_IT
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_CURRENT_YEAR_BUS
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'IT' then pka_wert.PKA END) PKA_YEAR_1_IT
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_YEAR_1_BUS
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_YEAR_2_IT
        ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_YEAR_2_BUS
        
        from inv_investments inv
        join PRTASK pt on inv.id = pt.PRPROJECTID
        JOIN PRASSIGNMENT pa ON pt.PRID = pa.PRTASKID
        JOIN SRM_RESOURCES srm ON pa.PRRESOURCEID = srm.ID
        JOIN PRJ_BLB_SLICES SL ON pa.PRID = SL.PRJ_OBJECT_ID AND sl.slice_date BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 7*12)-1
        JOIN PRJ_BLB_SLICEREQUESTS SR ON SL.SLICE_REQUEST_ID = SR.ID




        Join(    SELECT
            pt.prid
            ,round(sum(sl.slice),0) pka
            ,extract(YEAR FROM sl.slice_date) slice_date
            ,srm.FULL_NAME
            from PRTASK pt
            join PRASSIGNMENT pa on pt.PRID = pa.PRTASKID
            join SRM_RESOURCES srm on pa.PRRESOURCEID = srm.ID
            JOIN PRJ_BLB_SLICES SL ON pa.PRID = SL.PRJ_OBJECT_ID
            JOIN PRJ_BLB_SLICEREQUESTS sr ON SL.SLICE_REQUEST_ID = SR.ID
            WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'
            GROUP BY
            sl.slice_date
            ,pt.prid
            ,srm.FULL_NAME
            ,sl.slice
        ) pka_wert on pka_wert.PRID = pt.PRID




        WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'
        AND sl.slice_date BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 7*12)-1




        GROUP BY
        inv.id
        ,srm.FULL_NAME
        ,sl.slice_date
        ,pt.PRID
        ) pka_annual ON pt.PRID = pka_annual.PRID




    WHERE inv.IS_ACTIVE = 1
    AND ip.IS_TEMPLATE = 0
    AND oi.Z_DIVISION = 'P'
    AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben'




    GROUP BY inv.CODE
    ,inv.IS_ACTIVE
    ,ip.IS_TEMPLATE
    ,oi.Z_DIVISION
    ,op.Z_PPMID
    ,pt.PRSTATUS
    ,dtv.Z_PROCESS_CAPTION
    ,pt.PRNAME
    ,pt.PRMETHODEXTID
    ,ot.Z_PSP_PHASE
    ,pt.PRSTART
    ,pt.PRFINISH
    ,pka_annual.RESSOURCE
    ,pka_annual.P_YEAR
    ,pka_annual.PKA_PAST_YEAR_IT
    ,pka_annual.PKA_PAST_YEAR_BUS
    ,pka_annual.PKA_CURRENT_YEAR_IT
    ,pka_annual.PKA_CURRENT_YEAR_BUS
    ,pka_annual.PKA_YEAR_1_IT
    ,pka_annual.PKA_YEAR_1_BUS
    ,pka_annual.PKA_YEAR_2_IT
    ,pka_annual.PKA_YEAR_2_BUS
    ORDER BY
    inv.CODE


  • 2.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-16-2018 12:12 PM

    Steve, please check this link:  https://support.ca.com/us/knowledge-base-articles.TEC466930.html

     

    When converted to NSQL, there’s a dimension key produced – this dimension key likely has the duplicate that is generating your error.  Say, you are using ProjectID as the dimension key and the query returns more than one record from the same project – you’ll get this error in NSQL where you wouldn’t have gotten it from SQL run directly on the DB.

     

    In such cases, I’ve modified the query so that it creates a unique dimension key – sometimes by concatenating project id with task id, issue id…anything that will guarantee that my dimension key has a unique value.

     

    Hopefully, there’s enough info here and in link provided that you can solve this on your own – it’s more fun.  If struggling, there are experts on this community that could probably provide a more direct answer after seeing your NSQL code.

     

    Dale



  • 3.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 01:27 AM

    Hi Dale,

    Thank your for mentioning the link. I wasn't aware of the fact that the dimension key could be the problem.

     

    Steve



  • 4.  Re: Error:  NPT-217: This query produced duplicate dimensional data.
    Best Answer

    Posted 02-16-2018 12:19 PM

    The short answer is that the ID of the query results is not unique when used in NSQL. In SQL that is no problem.

    If you have task level data in a record the ID cannot be investment ID as an investment may have several tasks.

    Add something like

    (inv.id+pt.prid) u_id

    That is task ID concatenated after investment ID.

    That may not be enough as you have slice data. If they are summarize at yearly level you need to make the unique ID (u_id) to differentiate between the annual data.



  • 5.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 01:28 AM

    Hi Urmas,

    I've tried your suggestions and it seems to work just as I needed it to.

    Thank you a lot!

     

    Steve



  • 6.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 02:13 AM

    Just because you are no longer experiencing the 'duplicate errors message' does not mean your query is correct.  Potentially the only reason you are seeing correct results is because you only have one task on a project with 'AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben''.  Please see response earlier today, your query is wrong and you need to add additional join conditions.

     

    In addition, your query for PKA_ANNUAL is also potentially incorrect.  I can't work out why you have a join PKA_WERT which uses the time slice for assignment which you are also joining in the main section of PKA_ANNUAL.  Are you sure you are getting the correct data returned, as I believe you need to review this section of your code.



  • 7.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 02:34 AM

    Partly agree with Roland.

    Not getting the error means that the query is technically correct.

    However, that does not mean that it is necessarily querying and returning  exactly what you want.



  • 8.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 08:00 AM

    Hi urmas, I believe the query is wrong.  It should not be described as "technically correct". 

     

    Sorry for calling this out but I would hate to see someone to use it, or part of it, when searching this forum in the future, which happened to me earlier today and I found this topic.

     

    I can't explain why Steve is getting the "results as wanted" but  I believe the additional joins are required as described earlier.



  • 9.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 08:18 AM

    urmas

    Hi everyone,

    Thank you for pointing the mistake out.

    Before, when I mentioned, that "the result is shown as wanted" I did not have the time to check it in detail. You both are correct, some information is in fact missing.

    As I did mention in the beginning question, I am relatively new to SQL and NSQL and I am basically learning this by doing. So I am thankful to have again learnt something .

     

    At given time I am going to test the inputs of Roland.

     

    Again, thank both of you!



  • 10.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 10:41 AM

    No problem. What could be more correct is that there is no NSQL syntax error.



  • 11.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-18-2018 07:14 PM

    It is your query, in particular:

    JOIN ODF_TASK_V dtv ON inv.ID = dtv.PRPROJECTID
    JOIN PRTASK pt ON inv.ID = pt.PRPROJECTID
    JOIN ODF_CA_TASK ot ON pt.PRID = ot.ID

     

    You are not joining odf_task_v to prtask, only back to the project.  Trying adding in pt.prid = dtv.odf_pk to the second join.



  • 12.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 02-19-2018 11:15 PM

    Hi Steve, please consider below query - it has not been run in SQL so may not be syntonically correct, but hopefully points you closer to what you are trying to achieve.  There are lots of comments which you need to check, as for example, I believe you need to use two TSV's where as you are only pulling back one custom TSV for Assignments.

     

    SELECT
    inv.code || pt.prid unique_dim -- note this will return a string so you may need to recreate the portlet if you previously had an integer for the unique dim in NSQL - I personally never use inv.id + pt.prid - this can result in duplicates
    ,inv.CODE AS projekt_id
    ,inv.id prj_id -- can be used in the porlet to provide hyperlink back to project
    ,inv.IS_ACTIVE
    ,ip.IS_TEMPLATE
    ,oi.Z_DIVISION AS divison
    ,op.Z_PPMID AS ppm_id
    ,pt.prid -- can be used in the portlet to provide hyperlink back to the task
    ,CASE pt.PRSTATUS
        WHEN 0 THEN 'geplant'
        WHEN 1 THEN 'freigegeben'
        WHEN 2 THEN 'abgeschlossen' END AS phasenstatus -- you should be able to get this from odf_task_v.prstatus_caption
    ,dtv.Z_PROCESS_CAPTION AS Projektprozess
    ,pt.PRNAME AS phasenname
    ,pt.PRMETHODEXTID AS phasen_id
    ,NVL(ot.Z_PSP_PHASE, 0) AS psp
    ,to_char(pt.PRSTART, 'dd.mm.YYYY') AS ende_phase
    ,to_char(pt.PRFINISH, 'dd.mm.YYYY') AS start_phase
    , pka_annual.pka_past_year_it
    , pka_annual.pka_past_year_bus
    , pka_annual.PKA_CURRENT_YEAR_IT
    , pka_annual.PKA_CURRENT_YEAR_BUS
    , pka_annual.PKA_YEAR_1_IT
    , pka_annual.PKA_YEAR_1_BUS
    , pka_annual.PKA_YEAR_2_IT
    , pka_annual.PKA_YEAR_2_BUS
    FROM INV_INVESTMENTS inv
    JOIN INV_PROJECTS ip ON inv.ID = ip.PRID
    JOIN ODF_CA_PROJECT op ON inv.ID = op.ID
    JOIN ODF_CA_INV oi ON inv.ID = oi.ID
    JOIN ODF_TASK_V dtv ON inv.ID = dtv.PRPROJECTID and dtv.odf_user_id = 1 -- when running exernal from Clarity you need to add ' and odf_user_id = 1' or similar.  Set up the admin (id = 1) to be the required language
    JOIN PRTASK pt ON inv.ID = pt.PRPROJECTID and pt.prid = dtv.odf_pk -- if you are using the above view, you don't need this in your query, leaving to simply changes to query
    JOIN ODF_CA_TASK ot ON pt.PRID = ot.ID  -- if you are using the above view, you don't need this in your query, , leaving to simply changes to query
    left Join(    select
        pt.PRPROJECTID
        ,PT.PRID
        ,SUM(CASE WHEN trunc(sl.slice_date,'YYYY') = trunc(ADD_MONTHS(SYSDATE,-12)'YYYY') AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_PAST_YEAR_IT  -- you need to review how you are caulculating annual years example for previous year provided here
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_PAST_YEAR_BUS  -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_CURRENT_YEAR_IT  -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_CURRENT_YEAR_BUS -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'IT' then pka_wert.PKA else 0 END) PKA_YEAR_1_IT -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_YEAR_1_BUS -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_YEAR_2_IT -- review, don't believe case statement correct
        ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_YEAR_2_BUS -- review, don't believe case statement correct
        from PRTASK pt
        JOIN PRASSIGNMENT pa ON pt.PRID = pa.PRTASKID
        join srm_resources srm on srm.id = pa.prresourceid
        JOIN PRJ_BLB_SLICES SL ON pa.PRID = SL.PRJ_OBJECT_ID AND sl.slice_date BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 7*12)-1  -- you are pulling back 7 years work of data, and none for the previous year - you need to review what months are required
        JOIN PRJ_BLB_SLICEREQUESTS SR ON SL.SLICE_REQUEST_ID = SR.ID
        WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'  -- NOTE, you need to review as Assignment TSV is broken down by either Actual or ETC - given that you are searching for Past (which will be actuals) and Future (which will be ETC) you may need to pull back data from two TSV
        GROUP BY
        pt.PRPROJECTID
        ,pt.PRID
        ) pka_annual ON pt.prprojectid = pka_annual.ptprojectid and pt.prid = pka_annual.prid
    WHERE inv.IS_ACTIVE = 1 -- make this a filter in the portlet rather than harcoding here
    AND ip.IS_TEMPLATE = 0
    AND oi.Z_DIVISION = 'P'
    AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben' -- make this a filter in the portlet rather than hardcoding here 



  • 13.  Re: Error:  NPT-217: This query produced duplicate dimensional data.

    Posted 03-05-2018 03:45 AM

    urmas

    Hi everyone,

    Sorry for my late response, I was out of office the last two weeks.

     

    I do agree with the fact that the query is wrong. The query delivers multidimensional data.

    Thank you Roland for giving me the code with your comments in it. I will test your suggestions and correct the query.

     

    As far as the beginning question goes, I would consider it as solved.

    The answer - the query is wrong.

     

     

     

    Kind Regards,

    Steve