In Data warehousing, Kimball discusses modeling an insurance policy premium as Both Dimension and Fact. I have the following table,
create table dbo.DimAutoInsurance
(
DimAutoInsuranceId int primary key identity(1,1),
CustomerName varchar(100),
CustomerAddress varchar(255),
PolicyCoverageAmount numeric (15,2),
PolicyBeginDate datetime,
PolicyExpirationDate datetime
)
For the Fact table, should I reconduct ETL another table for fact? Copying the data again, would seem redundant. Or should I create a view? What is best database design strategy?
create view dbo.FactAutoInsurance
as
select
DimAutoInsuranceId,
PolicyCoverageAmount numeric (10,2),
from dbo.DimAutoInsurance
Kimball:
"A more ambiguous example is the limit on a coverage within an automobile insurance policy. The limit is a numerical data item, say $300,000 for collision liability. Furthermore, many queries would group or constrain on this limit data item. This sounds like a slam dunk for the limit being an attribute of the coverage dimension.
One could pose some important queries summing or averaging all the limits on many policies and coverages. This sounds like a slam dunk for the limit being a numeric fact in a fact table. Rather than agonizing over the dimension versus fact choice, simply model it BOTH ways! Include the limit in the coverage dimension so that it participates in the usual way as a target for constraints and the content for row headers, but also put the limit in the fact table so it can participate in the usual way within complex computations."