The Data provider generated query does not honor the joins specified.
search cancel

The Data provider generated query does not honor the joins specified.

book

Article ID: 440682

calendar_today

Updated On:

Products

Clarity FedRAMP Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

STEPS TO REPROCUCE:
  1. MUX > Reporting > Data Providers
  2. Create a new Data Provider
  3. Details tab > Enable Include Query
  4. Queries tab > Query > Add the following query:
SELECT fct.task_key,
  fct.resource_key,
  fct.investment_key,
  fct.ACTUAL_HOURS,
  fct.ACWP_COST,
  time_period_key,
  fiscl_p.year_name,
  fiscl_p.period_name
FROM DWH_INV_ASSIGN_PERIOD_FACTS fct
  INNER JOIN DWH_CMN_PERIOD_F_V FISCL_P ON fct.PERIOD_KEY = FISCL_P.PERIOD_KEY
  INNER JOIN dwh_tme_period TME ON TME.start_date = fiscl_p.period_start_Date
  AND TME.finish_date = fiscl_p.period_end_date
  AND FISCL_P.LANGUAGE_CODE = 'en'
  1. Queries tab > Attributes > Enable the Key flag for investment_key, resource_key, time_period_key, task_key
  2. Data Provider Objects tab > Add the following objects: Query, Task, Investment, Resource, Assignment, Time Entry, Timesheet and Time Periods 
  3. Data Provider Joins tab > Add below joins

(Structure: Type, Object, Attribute, Join, Object, Attribute)

    • Right, query, investment_key, Equals, Investment, Investment Key
    • Right, query, task_key, Equals, Task, Task Key
    • Right, query, resource_key, Equals, Resource, Resource Key
    • Right, query, time_period_key, Equals, Time Periods, Time Period Key
    • Inner, Investment, Investment Key, Equals, Task, Investment Key
    • Inner, Task, Task Key, Equals, Assignment, Task
    • Inner, Time Entry, Assignment Key, Equals, Assignment, Assignment Key
    • Inner, Timesheet, Timesheet Key, Equals, Time Entry, Timesheet Key
    • Inner, Timesheet, Resource, Equals, Resource, Resource Key
    • Inner, Time Periods, Time Period Key, Equals, Timesheet, Time Period
  1. Go to the Preview tab and look to the resulting query:
SELECT q.*
FROM (
    SELECT dwh_inv_investment.investment_key inv____investment_key,
      dwh_tme_sheet.timesheet_key timesheet____timesheet_key,
      dwh_inv_task.task_key task____task_key,
      dwh_tme_entry.timeentry_key timeentry____timeentry_key,
      dwh_res_resource.resource_key resource____resource_key,
      dwh_inv_assignment.assignment_key assignment____assignment_key,
      dwh_tme_period.time_period_key timeperiod____time_period_key,
      sql00000009.actual_hours odf_sql___sql00000009____actual_hours,
      sql00000009.investment_key odf_sql___sql00000009____investment_key,
      sql00000009.period_name odf_sql___sql00000009____period_name,
      row_number() over(
        ORDER BY 1
      ) AS row_num,
      5000000 + row_number() over(
        ORDER BY 1
      ) AS odf_pk,
      COUNT(1) OVER() AS rows_count
    FROM (
        SELECT fct.task_key,
          fct.resource_key,
          fct.investment_key,
          fct.ACTUAL_HOURS,
          fct.ACWP_COST,
          time_period_key,
          fiscl_p.year_name,
          fiscl_p.period_name
        FROM DWH_INV_ASSIGN_PERIOD_FACTS fct
          INNER JOIN DWH_CMN_PERIOD_F_V FISCL_P ON fct.PERIOD_KEY = FISCL_P.PERIOD_KEY
          INNER JOIN dwh_tme_period TME ON TME.start_date = fiscl_p.period_start_Date
          AND TME.finish_date = fiscl_p.period_end_date
          AND FISCL_P.LANGUAGE_CODE = 'en'
      ) sql00000009
      RIGHT OUTER JOIN dwh_inv_investment dwh_inv_investment ON sql00000009.investment_key = dwh_inv_investment.investment_key
      RIGHT OUTER JOIN dwh_inv_task dwh_inv_task ON sql00000009.task_key = dwh_inv_task.task_key
      RIGHT OUTER JOIN dwh_res_resource dwh_res_resource ON sql00000009.resource_key = dwh_res_resource.resource_key
      RIGHT OUTER JOIN dwh_tme_period dwh_tme_period ON sql00000009.time_period_key = dwh_tme_period.time_period_key
      JOIN dwh_inv_assignment dwh_inv_assignment ON dwh_inv_task.task_key = dwh_inv_assignment.task_key
      JOIN dwh_tme_entry dwh_tme_entry ON dwh_tme_entry.assignment_key = dwh_inv_assignment.assignment_key
      JOIN dwh_tme_sheet dwh_tme_sheet ON dwh_tme_sheet.timesheet_key = dwh_tme_entry.timesheet_key
    WHERE 1 = 1
      AND (
        (
          dwh_inv_investment.sub_type IN ('project', 'idea', 'cit')
        )
        OR (dwh_inv_investment.investment_key IS NULL)
      )
    ORDER BY row_num OFFSET 0 ROWS FETCH next 100 ROWS ONLY
  ) q
ORDER BY row_num
 
Expected Results: All specified joins are part of the resulting query
Actual results: Some of the joins are dropped and query is not correct

Environment

Clarity 16.4.1, 16.4.2

Cause

This is caused by DE196241

Resolution

The behavior reported is working as designed.

When building a Data Provider query, Clarity uses a spanning-tree algorithm that starts from the primary object and visits each table once. If a join leads to a table that has already been visited, that join is skipped to prevent invalid or ambiguous SQL.

In Clarity 16.4.3 aliases have been introduced for Joining to Same Object in Data Providers.

Alias Objects will be the same as regular Objects and can be used in Joins, Filters, Pre-Filters, and Preview.