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

View previous topic View next topic Go down

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

Post  mellowplace on Thu Mar 27, 2014 12:32 pm

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.

mellowplace

Posts : 2
Join date : 2014-03-27

View user profile

Back to top Go down

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

Post  ngalemmo on Thu Mar 27, 2014 2:25 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  mellowplace on Fri Mar 28, 2014 6:46 am

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

mellowplace

Posts : 2
Join date : 2014-03-27

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Mar 28, 2014 12:18 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum