Is correct store the transactions keys in Dimensions?

View previous topic View next topic Go down

Is correct store the transactions keys in Dimensions?

Post  BrazilUser on Tue Aug 11, 2015 2:33 pm

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.


BrazilUser

Posts : 2
Join date : 2015-08-11

View user profile

Back to top Go down

Re: Is correct store the transactions keys in Dimensions?

Post  ngalemmo on Tue Aug 11, 2015 3:39 pm

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

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

View user profile http://aginity.com

Back to top Go down

Is correct store the transactions keys in Dimensions?

Post  BrazilUser on Wed Aug 12, 2015 6:06 am

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).


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

View user profile

Back to top Go down

Re: Is correct store the transactions keys in Dimensions?

Post  turbotortuga on Fri Aug 14, 2015 8:03 am

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

avatar
turbotortuga

Posts : 13
Join date : 2013-11-04

View user profile

Back to top Go down

Re: Is correct store the transactions keys in Dimensions?

Post  ngalemmo on Fri Aug 14, 2015 2:59 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Is correct store the transactions keys in Dimensions?

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