Fact Table design question

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

service order (DD) service line(DD) part number (FK) employee id
(FK) site_id(FK) date_key(FK) QTY_USED
_______________ _____________ _______________ _______________
_________ ___________ _________
1 2
123 20345 234
1 20
1

[Jimbo]

example came out messed up here:

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.

[Marco Russo]

Especially using SQL Server, I prefer not using the composite FK as a
primary key.
Remember that by default the PK is created with a clustered key, which
usually is not a good idea if your fact table is very large.
If having the FK as a unique key is a requirement, a non-clustered
index (eventually used as PK) is a better solution.
To keep the fact table updatable, I put an INT IDENTITY column used as
a PK (this doesn't affect performance during data load and allows a
better usage of disk space if you define non-clustered indexes on the
fact table.

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo