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

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'

5. Queries tab > Attributes > Enable the Key flag for investment_key, resource_key, time_period_key, task_key
6. Data Provider Objects tab > Add the following objects: Query, Task, Investment, Resource, Assignment, Time Entry, Timesheet and Time Periods 
7. 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

8. 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

Cause

This is caused by DE196241

Resolution

This is under analysis