Clarity

  • Private Community

  • 1.  Duplicate dimensional data error

    Posted May 09, 2014 02:30 PM

    Hello,

    I am getting the standrad "This query produced duplicate dimensional data." error.

    I added the rownum too but still getting the same error.

    Apperciate help.

    Thanks,

    MN

    select
       @SELECT:DIM:USER_DEF:IMPLIED:resource:q.id:res_id@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:secug.id:secug_id@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:q.full_name:resname@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:q.email:resemail@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:q.is_active:resactive@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:secg.group_name:group_name@
      
    FROM SRM_RESOURCES q
    INNER JOIN CMN_SEC_USER_GROUPS secug ON secug.user_id = q.user_id 
    INNER JOIN CMN_SEC_GROUPS_V secg ON secug.group_id = secg.id
    AND secg.language_code = 'en'
    AND @FILTER@
    ORDER BY secg.group_name

     

     



  • 2.  RE: Duplicate dimensional data error

    Posted May 09, 2014 02:35 PM

    Do not see rownum in the query that you posted

    NJ



  • 3.  RE: Duplicate dimensional data error
    Best Answer



  • 4.  RE: Duplicate dimensional data error

    Posted May 09, 2014 02:38 PM

    The "error" (its a warning really) is because your query (as posted) will return multiple "res_id" values (which is what you have defined as the DIM).

    You get multiple "res_id" because you are joining each resource to the groups it belongs to, and a resource may belong to multiple groups.

    Change the query to

    select
       @SELECT:DIM:USER_DEF:IMPLIED:resource:ROWNUM:uq_id@

      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:q.id:res_id@
      ,@SELECT:DIM_PROP:USER_DEF:IMPLIED:resource:secug.id:secug_id@

    and it will work.



  • 5.  RE: Duplicate dimensional data error

    Posted May 09, 2014 02:41 PM

    That was quick wink

    I was abt the post the kb article -

    CLARITY PPM APPLICATION ADMIN, STUDIO, XOG & PROCESSES: 12.1, 12.1.2, 12.1.3, 13.0, 13.0.1, 13.1, 13.2
    Last Date:    05/27/2013
    Document ID:    TEC466930
    Tech Document
    Title:  Clarity: NSQL NPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete.

    Description:

    SQL query results not matching Portlet results
    I created a portlet that get data from an NSQL query. The results displayed in the portlet do not match what is returned when the SQL query is run directly on the database.
    In this case, the customer did not receive an error, but sometimes you may encounter the following error message displayed on the page:

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

    Solution:

    This is caused by non-unique values returned for the Dimension Key defined within the NSQL statement.
    Modify the NSQL statement so that the 'Dimension Key' returns unique values within the result set. This 'Dimension Key' can be created with any field type, but the results must be unique within the result set.

    If you ignore this error message and continue to use the portlet, you may be using incorrect or incomplete data displayed in the Portlet result set.

    The error message that you receive cannot be turned off. This error message is important to the end user to indicate that the information or data result set displayed from the Portlet may not contain all the appropriate data. Although you may need to have duplicate rows for a particular field, you can still construct your query in a manner that will retrieve a unique Dimensional Key. You are not required to display the Dimensional Key on the published Portlet. You may also consider creating a multiple dimensional portlet, that may resolve your issue. If you have a multiple dimensional portlet containing more than one Dimensional Key, the value in the Dimensional key can be non-unique if the combination of the data in all Dimensional Keys are unique.
    Reference the Clarity Studio Guide for more information on Portlet syntax and construction or consult CA Clarity Technology Services (CATS) for further assistance in creating Portlets to meet your business needs.

    Keywords: CLARITYKB, 7033, 5644

    NJ



  • 6.  RE: Duplicate dimensional data error

    Posted May 09, 2014 03:07 PM

    Thank you Dave and NJ. Its working perfect.

    I love the community always helpful. Thanks again.