I'm designing a Business Vault table filled with the stages that make up a Life Insurance Policy for easy reporting and self-service analytics. The Policy data moves through about four systems - application, underwriting, policy administration, finance. We join this together by business keys in our data vault. We have Business Vault conformed satellites for the main subject areas of Policy, Cover, Customer etc.
What I am looking at is a universal Business Vault Policy time span table. It would receive all Policy status event data into a single place to track Policy events by tracking changes to various status fields. Policy Applications can have status changes like Submitted, Approved, Rejected. Policy Underwriting can be Lodged, Accepted, Processed. Policy Administration can be Active, Inactive, Lapsed. If you filter on a single policy you can see the entire life-cycle event history of that Policy.
So what I was thinking:
- Stage Name: a business label for what this status change represents. Eg. "Policy Application Approved"
- Original System: the system the status change came from.
- Status Name: the name of the status field.
- Old Status Value: what the status changed from.
- New Status Value: what it changed to. Trying to make this user friendly for self service BI/Analytics.
- Status Change Date: the date on which the status change happened.
- Time Span: Number of hours between status change from that system