Data Warehouse VW_REQUEST_STATE_HISTORY only shows changes from full ETL date, DCC 5
Hello,
We dropped and recreated our data warehouse to repair a number of issues with corrupt data. All DCC 5.0.2 ETLs works fine now, except our RIDW.VW_REQUEST_STATE_HISTORY only goes back to the date we ran the full ETL last week. There are 346k rows in the RICALM.REQUEST_HISTORY with dates going back to the beginning of CLM, so surely the needed data for state history changes is in there. I've done this before for another client and they maintained work item state history, it just took forever to run the jobs (as expected). That may have been Insight's DM ETL though.
Is the view table improperly constructed or is DCC missing some key information? We'd like our historical state information, I'll open a PMR if there isn't something obvious we're missing.
One answer
What was the solution? DCC only updates the STATE_HISTORY table after I change the Delta Load Configuration date on the data warehouse to a couple of days ago.
RTC (CLM) 5.0.2, RHEL 6.7
James Freeman
Comments
Did that update all your state history for all of time, or just for those dates? Ours updates the state history every night, but only for the day's worth (being a delta load and all).
If a "delta load since 3 days ago" fixes some aspect of the view to unlock all the information, that would be amazing. Perhaps if we set the delta load to a specific date a year ago, it would pick up state history from that date? We're only on v5 and I've seen some issues with v5 DCC ETLs so my concern there is data duplication, which is the problem that caused us to reload the whole DW in the first place.
DCC is supposed to pick-up all of the changes from the last time it ran, but does not do that for the State History table. I have a RRDI report that gets the work item's state date from the state history table and the changes during the day are not recognized until I change the delta load configuration date to the day before and run the "Change and Configuration Management - Work Item Metrics" job manually. Afterwards, all of the changes from the previous day show up on the report.