Fact Table Design Consideration
Page 1 of 1
Fact Table Design Consideration
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
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
manickam- Posts : 27
Join date : 2013-04-26
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
» Fact Table Design
» Fact Table Design
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
» Fact Table Design
» Fact Table Design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum