fact primary key

Hi

My data warehouse will have CONTRIBUTIONS fact table which is sourced from a OLTP General Ledger Table. All transactions from the GL table will be put in a ODS staging table (GL_Stage).

The GL table primary key consists of 9 fields (transaction reference number, sub account, sub_sub account etc) and It seems pointless to put these fields in the fact table.

I was thinking of creating an identity key on the GL ODS table and then put this key in the fact table (as a link back to the source system). This key will be the primary key of the fact table. Is this a good way to do it ?

Regards

Tim

The GL table you are referring to is what? The journal table?

Break the incoming data down to dimensions. The transaction number probably has no attributes, so it should be stored as a degenerate dimension. Account would be another dimension. Wither the account dimension should include the sub-account depends and also how it is stored. If there is no dependency between the account and sub-account, then they could each be their own dimension. If their is a dependency, such that the same sub-acccount ID means something different depending on the account, then they would be stored in one dimension.

Fact tables don't really need a primary key, unless the intent is to be able to update a fact table in place, If the fact table is designed to be insert only (a transactional fact, which is what the GL journal is) then you don't need it. Another consideration with fact tables is that loading a fact table that has enforced constraints (such as PK and FK integrity constraints) significantly slow down load processes.

Thanks for that

The GL is a journal table and therefore has only inserts. Also the GL table contains other transaction types (investment switches etc) which will be held in other fact tables

I was thinking of having an ID in the contribution fact table which links to the ODS GL table so one can trace back the contribution to the source system

Therefore the ODS GL table (GL_STAGE) will have the following fields

GL_STAGE_ID (system generated identity key)
.. other GL fields

and then the Contribution FACT table will contain the ID so that I know which record in the GL_STAGE table does the contribution is associated with.


Regards

Tim