Composite Keys - DIM
2 posters
Page 1 of 1
Composite Keys - DIM
I have two questions
1. Can we have composite keys in DIM table?
2. Should we not have Primary Key and Foreign Key for the STAGING area of the DWH? Many places i am seeing just Primary indexes exist. Any reasons?
1. Can we have composite keys in DIM table?
2. Should we not have Primary Key and Foreign Key for the STAGING area of the DWH? Many places i am seeing just Primary indexes exist. Any reasons?
rkraj- Posts : 12
Join date : 2012-06-29
Re: Composite Keys - DIM
1. No. A dimension table has a single surrogate primary key. The natural key could be a compound key.
2. Do whatever you need to do in staging, however I've never found the need to have many indexes at all. Things usually run a lot faster if you don't.
2. Do whatever you need to do in staging, however I've never found the need to have many indexes at all. Things usually run a lot faster if you don't.
Re: Composite Keys - DIM
I am not getting clarity on your 2nd point.. I may or may not add PK/FK...that i understand. But how you are saying that without indexes your process runs faster? as DWH holds millions of recs.... While querying it will be useful if you have Primary indexes set.. Correct me if i am wrong.
rkraj- Posts : 12
Join date : 2012-06-29
Re: Composite Keys - DIM
You asked about indexes in staging. Staging is a transient area where you prepare data for loading. It usually contains the days data, you usually need to process every row, and most lookups are against DW tables, not those in staging. Under those circumstances an index on a staging table doesn't help.
But, with that said, there are no hard and fast rules about staging. You do what ever you need to do.
Also, you mention 'primary indexes'. Are you talking about Teradata? A 'primary index' is not an index in the traditional sense, rather it is a hash.
But, with that said, there are no hard and fast rules about staging. You do what ever you need to do.
Also, you mention 'primary indexes'. Are you talking about Teradata? A 'primary index' is not an index in the traditional sense, rather it is a hash.
Re: Composite Keys - DIM
Understood your point in Staging.
I have confused with respect to Primary Index. I am using DB2 where it is the traditional INDEX only.
I have confused with respect to Primary Index. I am using DB2 where it is the traditional INDEX only.
rkraj- Posts : 12
Join date : 2012-06-29
Re: Composite Keys - DIM
Teradata has a thing called a 'primary index' which defines a hash to distribute data across AMPs (it is an MPP system). What you are probably referring to is a primary key index.

» 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?
» No Surrogate keys
» Business keys or Natural keys in the Fact table
» Composite Natural ID
» Using Hash keys instead of Numeric sequence keys in Dimensional Model?
» No Surrogate keys
» Business keys or Natural keys in the Fact table
» Composite Natural ID
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|