fact table primary key/identity choice - not sure which is correct

Hi,

I have a fact table that has overlapping dimensions, it has

creative
country
device
media partner submission
day

The "media partner submission" from the source system also links to creative, country & device.

Is therefore, the correct identity of the table, a primary key on "day, creative, country, device" OR "day, media partner submission"

Are there other considerations if any of the dims are SCD's?

Thanks,

Rob.

The only purpose of a primary key on a fact table is to allow you to update the fact table. If there is no intent to update rows, the primary key is superfluous. If these keys are type 2 keys, updating becomes problematic as the key may have changed at the time of updating. As far as describing the grain of the table, do what makes sense to the business. The grain is not always all the dimensions.

Thanks for the reply.

I'm, not actually doing updates, but I would also think that the primary key is there to protect the integrity of the data as well?  That's my primary concern, making sure there are no duplicate rows.

So in defining the grain of the table (thanks, I wasn't quite sure of the proper language here) should I use the single dimension that is a de-normalised version of the others or should I use the individual dimensions?  Or are you saying to write out the grain statement and use the one that makes most sense from a business perspective?

Thanks,

Rob

Use the one that makes most sense to the business.

As far as PK enforcement goes, I rarely use it and it has nothing to do with use of the data. From a dimensional modeling standpoint it doesn't matter what it is. If you want to enforce one to prevent duplicate rows, define it so it does that. But keep in mind, if you are using type 2 keys, chances are you will not be able to prevent duplicates using a PK declaration.