Unable to see records in DWH_INV_SECURITY and DWH_RES_SECURITY tables

book

Article ID: 208902

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS STARTER PACK-CLARITY PPM

Issue/Introduction

We are querying DWH_INV_SECURITY table for our reports and Power BI and noticed the information isn't updated. We have ran Load Data Warehouse Access Rights job already. What could be the problem here?

Environment

Release : 15.1+

Component : CA PPM DATA WAREHOUSE

Resolution

You're actually looking in the old tables. Those are deprecated and as such may not work at all. 

DW_INV_SECURITY table is deprecated, starting 15.1.  Please see below:  

From version 15.1:

  • DWH_INV_SECURITY and DWH_RES_SECURITY tables removed

You can refer the changes here https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/clarity-project-and-portfolio-management-ppm-on-premise/15-1/reference/oracle-and-ms-sql-database-schema-changes.html

DWH_INV_SECURITY and DWH_RES_SECURITY are now views

  • These views exist so customer reports, etc won’t break. Don’t use these for new development!

NEW VIEWS PROVIDED FOR USE:

  • DWH_INV_SECURITY_V: Main investment DB view for security
  • DWH_RES_SECURITY_V: Main resource DB view for security

SUGGESTIONS FOR REPORTS

  1. Do not use the following views to build new reports. The views were created to support the existing reports that were based on the deprecated tables. The existing reports will still work but you may experience slow performance:
    • DWH_INV_SECURITY
    • DWH_RES_SECURITY
  2. Use the following views to build new reports:
  • DWH_RES_SECURITY_V
  • DWH_INV_SECURITY_V

 --------------------------------------------------------------------------

This is the documentation for 15.1 so since then the updates may no longer work at all. They were only kept so existing reports won't break until the customers switch to the new views. 

Please stop using DWH_INV_SECURITY and DWH_RES_SECURITY ASAP and start using the views

  • DWH_RES_SECURITY_V
  • DWH_INV_SECURITY_V

We suggest you update all the reports using those views to have the correct updated data.