Fact Table Design Consideration

[manickam]

We do have requirement to design a FACT table for maintaining the risks dealt with multiple subject areas.
Subject Areas:

1. Application
2. Policy
3. Infrastructure
4. LoB - Line Of Business

Can you please suggest which of the below approach would be good to go with or is there any better approach...
Note: Received Date and Status are dimension , Plan cost and expected loss are measures

Approach 1:

Risk ID Asset ID Received Date Expected Loss Status Plan Cost

Here the Asset ID can represent multiple subject areas like application, policy, LOB and Infrastructure



Approach 2:

Risk ID Application ID Received Date Expected Loss Status Plan Cost
Risk ID Policy ID Received Date Expected Loss Status Plan Cost
Risk ID Infrastructure ID Received Date Expected Loss Status Plan Cost
Risk ID LoB ID Received Date Expected Loss Status Plan Cost


In this approach we will be having different fact tables for each area of interest. A materialized view can be used to have an overiew of all the RISK.




Approach 3:

Risk ID Application ID Policy ID Infrastructure ID LoB ID Received Date Expected Loss Status Plan Cost



Approach 4:

Risk ID Is Application ID Application ID Is Policy ID Policy ID Is Infrastructure ID Infrastructure ID Is LoB ID LoB ID Received Date Expected Loss Status Plan Cost