One or more but not limited to the following issues/symptoms is attributed to the Load DWH job to fail. If the following conditions are present, a data warehouse database rebuild is necessary.
1. A mismatch between versions of PPM and DWH
Run the following queries to find out if the PPM and DWH database versions match
PPM: select * from cmn_install_history order by installed_date desc
DWH: select * from cmn_dwh_install_history order by installed_date desc
2. If the Load DWH job fails due to missing views/tables, it causes a mismatch between the PPM and DWH meta data.
select a.dwh_table_name, a.dwh_column_name,
a.attr_type, a.attr_data_type, a.attr_data_size, a.is_deleted, a.js_processed
from DWH_META_COLUMNS a
order by 1,2
b.dwh_table, b.dwh_column, b.attribute_type, b.attribute_data_type
from DWH_META_COLUMNS b
order by 1,2
3. If re-compiling the invalid objects on the DWH database does not work.
There is a mismatch in the meta data during the ETL Load Data Warehouse job which causes a mismatch between the PPM and DWH schemas.
To rebuild the DWH schema, schedule for the following steps:
a.Locate the out-of-the-box database file for the corresponding db vendor:
dwh_oracle_base.db and import via the Oracle datapump utility.
dwh_mssql_base.db and restore via MS SQL Server Management Studio.
b.Run the command on the PPM application server to have it match the version PPM:
admin db dwh-upgrade -Dupgrade.phase=maintenance