Can I have multiple primary keys for FACT tables?

[joelwenzel]

My fact tables all contain an id identy column which is an automatically generated number. I use this column as my primary key. However, each fact in my table can also be identified by a group of dimensions. Should I label the relationships between these dimensions and my fact table as identifying? This would result in my table having two primary keys...an id key and the group of columns key.

 

[johnherman]

Use a generated unique primary key (identity) as your primary key. The other keys are "natural" keys, even if they are generated numbers, too. Even if the relationships are identifying, do not flag them as such in the dimensional model. Referential integrity can and should be handled by your ETL process rather than as declared in the database.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[sweetleaf]

just out of curiousity why not use some or all the combined fk's as the pk for the fact table?

 

[johnherman]

You can, however....

you would be working with a large concatenated key rather than a single integer. It will probably hurt performance since each added record must be checked against the rest for uniqueness rather than simply assigning an incrementing integer.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[avjoshi]

I what context would one need primary key on a fact table?

Anand

 

[johnherman]

There are a lot of rare reasons.

For Instance:

Wbat if the transaction (fact) was in error, but auditable reports had already been generated on the bad data. It would be necessary from an audit (SarOx) point of view to be able to reproduce the report and to show your correction.

If I get a chance to dig through my notes and journals, I'll get some more. That one was off the top of my head, so to speak.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[avjoshi]

Thanks John.

I never thought about that. I guess I never came across that requirement in my limited experience in DW implementations. Our strategy has been, if the error is a result of technical glich in the software then simply modify the record.
If, on the other hand, the error represents the business reality at that point in time then append new record with to reflect the new reality.

you always learn somthing new every day.

Anand.

Anand

 

[sweetleaf]

What sort of change capture is best applied in fact tables?
For dimensions it's either type1, type2 and some cases type3.

 

[johnherman]

I always use type 2 in fact tables. If, for instance, the change is to a money field, and reports have already been run on that data, you need an audit trail. There may exist one or more reports which were run against that bad data, reports which may exist in hard copy, HTML, Excel, etc. and which may resurface later with questions regarding the integrity and accuracy of the warehouse.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw