On Nov 15, 11:04 pm, Jimbo <jamesfer...@hotmail.com> wrote:
> 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.

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