Data Warehouse Trending Materialized View Incorrect

Document ID : KB000072802
Last Modified Date : 05/07/2018
Show Technical Document Details
Issue:
The Data Warehouse trend jobs fail with a materialized view error if the period name of an Annual Fiscal Period contains a non-numeric character. 

Steps to Reproduce:
1. On the entity used by the Data Warehouse, create an Annual Fiscal Period containing a non-numeric character in the period name (Example: 'FY18' or 'Jan 1, 2018-Dec 31, 2018')
2. Run the Load Data Warehouse Job - Full Load, and wait for it to complete
3. Run any of the 3 Data Warehouse Trend jobs

Expected Results: The trend job completes successfully. 

Actual Results: The trend job fails with the below error in the bg-dwh logs:

2018/03/08 11:08:44 - MSSQL? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2018/03/08 11:08:44 - MSSQL? - Couldn't execute SQL: BEGIN
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_SUM_TREND_BY_PER_MV','SAVE_DROP');
2018/03/08 11:08:44 - MSSQL? - DBMS_SNAPSHOT.REFRESH('DWH_TRD_SUM_TREND_BY_PER_MV');
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_SUM_TREND_BY_PER_MV','CREATE');
2018/03/08 11:08:44 - MSSQL? - CMN_GATHER_TABLE_STATS_SP('DWH_TRD_SUM_TREND_BY_PER_MV',100);
2018/03/08 11:08:44 - MSSQL? - 
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_M_MV','SAVE_DROP');
2018/03/08 11:08:44 - MSSQL? - DBMS_SNAPSHOT.REFRESH('DWH_TRD_PER_TREND_BY_M_MV');
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_M_MV','CREATE');
2018/03/08 11:08:44 - MSSQL? - CMN_GATHER_TABLE_STATS_SP('DWH_TRD_PER_TREND_BY_M_MV',100);
2018/03/08 11:08:44 - MSSQL? - 
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_F_MV','SAVE_DROP');
2018/03/08 11:08:44 - MSSQL? - DBMS_SNAPSHOT.REFRESH('DWH_TRD_PER_TREND_BY_F_MV');
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_F_MV','CREATE');
2018/03/08 11:08:44 - MSSQL? - CMN_GATHER_TABLE_STATS_SP('DWH_TRD_PER_TREND_BY_F_MV',100);
2018/03/08 11:08:44 - MSSQL? - 
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_W_MV','SAVE_DROP');
2018/03/08 11:08:44 - MSSQL? - DBMS_SNAPSHOT.REFRESH('DWH_TRD_PER_TREND_BY_W_MV');
2018/03/08 11:08:44 - MSSQL? - CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_TRD_PER_TREND_BY_W_MV','CREATE');
2018/03/08 11:08:44 - MSSQL? - CMN_GATHER_TABLE_STATS_SP('DWH_TRD_PER_TREND_BY_W_MV',100);
2018/03/08 11:08:44 - MSSQL? - END;
2018/03/08 11:08:44 - MSSQL? - 
2018/03/08 11:08:44 - MSSQL? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12008: error in materialized view or zonemap refresh path

Note: The materialized view that the error is on is the DWH_TRD_SUM_TREND_BY_PER_MV view. If the query in the view is run on any query tool, the query itself will throw the following error (only when a the annual fiscal period name contains a non-numeric character): 

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

This is due to specifically to the following join: 

 t1.trend_year = pc_ln.year_name 
Cause:
Caused by DE39656 which is currently under investigation by our development team.
Resolution:
DE39656 has been targeted to be fixed in on our upcoming PPM 15.5 release. For any earlier release, please use the below workaround. 

Workaround:
Edit the period name on the Annual Fiscal Time Periods to contain only numbers. This means no spaces, dashes, letters, or otherwise non-numeric characters. For example, naming the fiscal period for 2018 as just '2018' .

After renaming the problem time periods, run the Load Data Warehouse - Full job and then the trending jobs as usual.

Note: In 15.3, the rename has to be done by clicking on the Translate icon to avoid receiving the message "New periods must not create a gap with the existing periods"  that is thrown when attempting to change the period name from the list page.

The issue with renaming the period name is caused by DE36786 and is fixed on PPM 15.4 but does not impact defect DE39656 which causes the trending job to fail.
 
Additional Information:
KB000008898: "New periods must not create a gap with the existing periods" Error Occurs when Trying to Rename a Fiscal Time Period