Fact Design Question in SQL Server

Fact Design Question in SQL Server

am 16.11.2007 21:23:16 von jimbo

Can a degenerate dimension act a a primary key in a fact table when a
composite of the foreign keys (linking to dimensions) doesnt form a
unique key?...The fact's granularity is at the line level and
aggregating is not an option...

example.


1st record:


service order(DD) = 1
service line(DD) = 1
part number(FK) = 2345
employee_id(FK) = 1234
site_id(FK)=345
date_key(FK)=123
qty_used=5


2nd record:


service order(DD) = 1
service line(DD) = 2
part number(FK) = 2345
employee_id(FK) = 1234
site_id(FK)=345
date_key(FK)=123
qty_used=6


From this example you can see that a composite of the foreign keys
doesnt define a unique identifier for a record, if the fact is at a
granularity of the line level.