Is correct store the transactions keys in Dimensions?
3 posters
Page 1 of 1
Is correct store the transactions keys in Dimensions?
I start my studies about Data WareHouse's World...
I'm writting pl/sql codes for etl process. My dimensions have surrogate keys (ok) , date/time keys and business attributes. My first question is if is correct store the the transactions keys in Dimensions. I thought interesting because i could use this transactions keys in the search ( joins ) when i will populate the fact tables.
I'm writting pl/sql codes for etl process. My dimensions have surrogate keys (ok) , date/time keys and business attributes. My first question is if is correct store the the transactions keys in Dimensions. I thought interesting because i could use this transactions keys in the search ( joins ) when i will populate the fact tables.
BrazilUser- Posts : 2
Join date : 2015-08-11
Re: Is correct store the transactions keys in Dimensions?
I assume you are talking about things like order number. The question is: are there attributes associated with the transaction id that do not appear in any other dimension. Usually the answer is no. If no, you can store the value in the fact table itself as a degenerate dimension. There is no need to create a dimension table if there are no attributes other than the value itself.
Is correct store the transactions keys in Dimensions?
Thanks for your answer, ngalemmo.
Actually, when i said "transactions keys" i should have liked to say "source application keys", like PersonId or others original keys from the relation model (OLTP source).
Is correct store these keys (OLTP) in a Dimension (Physical Model) from Data Warehouse (OLAP)?
Edited:
I read a topic where you answered about " when source contains surrogate key instead of natural key". In my case, when i said "surrogate key/transactions keys" i was talked about internal keys, no business key. E.g PersonID ( this key is created by my OLTP, my surrogate key, but the natural key is a number that is assign by Internal Revenue Service ( only a example).
Actually, when i said "transactions keys" i should have liked to say "source application keys", like PersonId or others original keys from the relation model (OLTP source).
Is correct store these keys (OLTP) in a Dimension (Physical Model) from Data Warehouse (OLAP)?
Edited:
I read a topic where you answered about " when source contains surrogate key instead of natural key". In my case, when i said "surrogate key/transactions keys" i was talked about internal keys, no business key. E.g PersonID ( this key is created by my OLTP, my surrogate key, but the natural key is a number that is assign by Internal Revenue Service ( only a example).
Last edited by BrazilUser on Wed Aug 12, 2015 6:25 am; edited 1 time in total (Reason for editing : After read other topic :"when source contains surrogate key instead of natural key")
BrazilUser- Posts : 2
Join date : 2015-08-11
Re: Is correct store the transactions keys in Dimensions?
If when you say "Transactional Keys" you are talking about the Primary Keys from the source tables then yes, they can become the Natural Keys/Business Keys in your dimensions assuming there are enough attributes to have one, otherwise like ngalemmo said you can simply have in the fact as a degenerated dimension.
Surrogate Keys are use in a Data Warehouse to maintain the relationship between Facts and Dimensions. They have nothing to do with the transactional system, however, at the time of load or update to a dimension, the Natural Key is compared against the transaction system in order to identify new records to be inserted or update to existing records.
Natural Keys can be repeated if you have a Slowly Changing Dimension, meaning that expired and new records will have the same Natural Key but different Surrogate Keys, also one would have a date along with a flag indicating it is an expired record and the other would be the most current/active record.
When you populate your Fact tables with Dimension Surrogate Keys, you would use the Natural Key and your dimension's flag to identify the must current record and place the Surrogate Key in your fact along with your other fact data.
RGC
Surrogate Keys are use in a Data Warehouse to maintain the relationship between Facts and Dimensions. They have nothing to do with the transactional system, however, at the time of load or update to a dimension, the Natural Key is compared against the transaction system in order to identify new records to be inserted or update to existing records.
Natural Keys can be repeated if you have a Slowly Changing Dimension, meaning that expired and new records will have the same Natural Key but different Surrogate Keys, also one would have a date along with a flag indicating it is an expired record and the other would be the most current/active record.
When you populate your Fact tables with Dimension Surrogate Keys, you would use the Natural Key and your dimension's flag to identify the must current record and place the Surrogate Key in your fact along with your other fact data.
RGC
turbotortuga- Posts : 13
Join date : 2013-11-04
Re: Is correct store the transactions keys in Dimensions?
To expand a bit... The natural key is what the business (people, not a computer) uses to identify something. It is the identifier that appears in invoices, receipts, statements, whatever... Internally, a system may use surrogate keys, to identify rows. The same thing is done in the data warehouse, but these are artificial identifiers used to deal with the mechanics of joining rows.
When identifying entities in a data warehouse, it is better to use the natural key than an artificial identifier. This allows you to integrate new sources that may not be coming from the same system.
As far as storing the source's surrogate key in the dimension, fine. It is an attribute.
When identifying entities in a data warehouse, it is better to use the natural key than an artificial identifier. This allows you to integrate new sources that may not be coming from the same system.
As far as storing the source's surrogate key in the dimension, fine. It is an attribute.
Similar topics
» KPI Goals and Percentages as Dimensions or Store as Facts?
» Should all dimensions keys in the fact be PKs?
» Surrogate keys for degenerate dimensions?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Using Hash keys instead of Numeric sequence keys in Dimensional Model?
» Should all dimensions keys in the fact be PKs?
» Surrogate keys for degenerate dimensions?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Using Hash keys instead of Numeric sequence keys in Dimensional Model?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum