How to Query PPM Database Schema from Data ware house schema?

How to Query PPM Database Schema from Data ware house schema?

In Order to Query PPM Table from DWH Schema – On First Hand we need the DBLINK used between Clarity Schema and DWH Schema.

DBLINK:

A database link is a schema object in one database that enables you to access objects on another database

DB Link is created between CA PPM schema and DWH Schema for communication.

DB Link is a one way communication which can be leveraged to query CA PPM DB from DWH Schema.

Create a new Jasper Studio report in Jaspersoft Studio to Query the DWH and compare the data with Clarity.

Sample SQL Construct:

select a.clarity_cnt, b.dwh_cnt from (select count(*) clarity_cnt

from DWH_INV_HIERARCHY_V@MUN1598PDWH_MUN1598P)a,

(select count(*) dwh_cnt

from DWH_INV_HIERARCHY)b

Note: In the above example MUN1598PDWH_MUN1598P is the DBLINK.

You can get the DB for On-demand customer from CA PPM UI. Admin-> General Settings -> System Options.

DBLINK Would be: [Data Warehouse DB Schema] _ [CA PPM DB Schema)

Sample Report JRXML:

Sample Report Screen Shot from Clarity UI: