Question about archving

Dec 24, 2014 at 12:18 AM
In the documentation is discussion of archiving the tables 'PolicyHistory' and 'EvaluationErrorHistory', with the comment "The data stored in the table policy.PolicyHistory is not used to report through the EPM Framework reports"

But with a CASCADE delete relationship added in 4.1 from PolicyHistory to PolicyHistoryDetail, the deletion will remove PolicyHistoryDetails older than 90 day as well. Is this intentional? Any suggestions on archiving 'just' the Raw XML and retaining the history detail for a longer trend?
Coordinator
Dec 24, 2014 at 12:55 AM
It's a framework so you can modify it however you need. If you want to keep it longer than the default retention just modify the scripts to cleanup a longer period.

As far as archiving the raw XML you can setup your own archiving strategy on those files via a PowerShell or batch script to copy\zip files elsewhere before EPMF deletes it.

Sent from my Windows Phone

Mar 13, 2015 at 1:35 AM
Edited Mar 13, 2015 at 1:36 AM
Took me a while to stumble back on this. I hadn't realized earlier that PolicyHistoryDetail.PolicyHistoryID is nullable. i.e. a PolicyHistory record does not have to have a link to a record with its pre-shredded XML.

I've altered the foreign key constraint to set PolicyHistoryID to NULL when the related PolicyHistory is deleted. This accomplishes retaining PolicyHistoryDetails even when the larger Raw XML records are removed.
USE [MDW]
GO
--drop the foreign key constraint
ALTER TABLE [policy].[PolicyHistoryDetail] DROP CONSTRAINT [FK_PolicyHistoryDetail_PolicyHistory]
GO

--add the foreign key constraint; deletions of policy history will set fk to null
ALTER TABLE [policy].[PolicyHistoryDetail] WITH CHECK 
    ADD CONSTRAINT [FK_PolicyHistoryDetail_PolicyHistory] FOREIGN KEY([PolicyHistoryID])
    REFERENCES [policy].[PolicyHistory] ([PolicyHistoryID])
    ON UPDATE CASCADE
    ON DELETE SET NULL
GO

ALTER TABLE [policy].[PolicyHistoryDetail] CHECK CONSTRAINT [FK_PolicyHistoryDetail_PolicyHistory]
GO