Composite Keys - DIM

View previous topic View next topic Go down

Composite Keys - DIM

Post  rkraj on Fri Aug 10, 2012 2:58 pm

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?

rkraj

Posts : 12
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Composite Keys - DIM

Post  ngalemmo on Fri Aug 10, 2012 3:30 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Composite Keys - DIM

Post  rkraj on Fri Aug 10, 2012 11:30 pm

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

View user profile

Back to top Go down

Re: Composite Keys - DIM

Post  ngalemmo on Sat Aug 11, 2012 4:46 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Composite Keys - DIM

Post  rkraj on Sat Aug 11, 2012 1:19 pm

Understood your point in Staging.
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

View user profile

Back to top Go down

Re: Composite Keys - DIM

Post  ngalemmo on Sat Aug 11, 2012 7:38 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Composite Keys - DIM

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