Kimball Data: Modeling Data as Both Fact and Dimension with View

Question

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

https://www.kimballgroup.com/2007/12/design-tip-97-modeling-data-as-both-a-fact-and-dimension-attribute/

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."

Answer 1

Kimball's page that you are quoting says:

numerical measurements that we put into fact tables, and textual descriptors that we put into dimension tables as “attributes”

According to Wikipedia's page Star_schema:

Fact tables record measurements or metrics for a specific event. Fact tables generally consist of numeric values, and foreign keys to dimensional data where descriptive information is kept. [...] Fact tables are defined as one of three types:

• Transaction fact tables record facts about a specific event (e.g., sales events)
• Snapshot fact tables record facts at a given point in time (e.g., account details at month end)
• Accumulating snapshot tables record aggregate facts at a given point in time (e.g., total month-to-date sales for a product)

and

Dimension tables usually have a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data.

So, there is a one-to-many relationship between the dimension table records and the fact table records.

In your case, I would expect fact table to have a date as one of the fields, and it represents time snapshot of the values for that particular date.

If you include, for example, PolicyCoverageAmount in both tables, it will represent the current value in the DimAutoInsurance table, and value at a specific time in the FactAutoInsurance table.

Yes, the same number will be in two places. But the idea is to optimize for querying instead of normalizing, so some redundancy is expected.