DWH Investment Security View Has Duplicate Records in MSSQL Database

Document ID : KB000115900
Last Modified Date : 26/09/2018
Show Technical Document Details
The DWH_INV_SECURITY_V view contains two records for the user/investment when rights are provided through OBS as well as having Global Rights. 

This will cause duplicate projects to show on Report Parameters in Reports and Jobs as well as in ad-hoc views.

Steps to Reproduce: 
  1. Create an OBS and associate it to the Project object
  2. Create a unit in the OBS
  3. Mark the OBS to be used for Access Rights
  4. Create a project and associate it to the unit from Step 2
  5. Create a Group from Administration - Groups
  6. On the Group's Access Rights - select the OBS Unit Access Rights
  7. Click on Add, and Add the 'Project - View Management' access right, for the OBS, select the OBS unit from Step 2
  8. Go to Administration - Resources and open the properties to an existing user
  9. Under the Resource Properties, go to the Groups tab, and add the group created in the previous steps
  10. Go to Resource's Access Rights - Global, and add the 'Project - View Management - All' global right to the user
  11. Go to Home - Reports and Jobs, run the Load Data Warehouse job
  12. Once the Load DWH job completes, run the 'Load Data Warehouse Access Rights' job
  13. Once the access rights job completes, run the following query:
select * from dwh_inv_security_v
where user_uid = '<user_uid>'
and investment_key =<internal investment id>

Expected Results: The query returns one row where the Global_View_Right = 1
Actual Results: The query returns two rows, one where the Global_View_Right =1 and another where the Global_View_Right =0
Only reproducible on MSSQL for PPM 15.3 and 15.4
Caused by DE40362
DE40362 has been fixed on PPM 15.4.1.

For assistance with a workaround on an affected version, please contact CA Technical Support.