Common Key

View previous topic View next topic Go down

Common Key

Post  rbs100 on Sun Sep 18, 2011 12:12 pm

Is it a better idea to have common key across the data mart to join the tables across to join with single key? If yes, what is the better approach?


rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Common Key

Post  Tootia on Sun Sep 18, 2011 7:59 pm

I'm not sure what you mean by Common Key. But obviously if you need to related (join) two tables, you need a key which exists in both.
However can't see having a key across whole datamart is practical.

Tootia

Posts : 7
Join date : 2011-08-30
Location : Australia

View user profile

Back to top Go down

Re: Common Key

Post  rbs100 on Wed Sep 21, 2011 5:56 am

Tootia wrote:I'm not sure what you mean by Common Key. But obviously if you need to related (join) two tables, you need a key which exists in both.
However can't see having a key across whole datamart is practical.
Actually what we tried to do is to integrate the different marts and to do a drill across reports. But drill across had difficulty because of the complex queries.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Common Key

Post  buckleyc on Wed Sep 21, 2011 9:03 am

Your data marts should publish, ideally,from a data warehouse. In that situation, if the CustomerKey for Fred is 1 in the warehouse, it's 1 in all the marts. It certainly makes it easier to troubleshoot and I personally go with a consistancy approach to avoid confusion.

That's my long way of saying, yes you should have a consistent key across your data marts for the same dimensional data.

buckleyc

Posts : 7
Join date : 2011-09-19

View user profile

Back to top Go down

Re: Common Key

Post  ngalemmo on Wed Sep 21, 2011 1:12 pm

Keys don't really matter. Data is aggregated and combined based on attributes, not keys. They only purpose of the key is to allow a fact row to reference the correct dimension row.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Common Key

Post  buckleyc on Wed Sep 21, 2011 3:09 pm

Fact table Ids, I agree, but if you building a summary that can be sliced by X dimensions, your keys to the dimensions had better be the same if you want a consistent path back to the data warehouse. If you change your keys, you have no solid evidence that what you are presenting in the end came from a particular dimensional record other than the business key matches.

buckleyc

Posts : 7
Join date : 2011-09-19

View user profile

Back to top Go down

Re: Common Key

Post  ngalemmo on Wed Sep 21, 2011 4:17 pm

buckleyc wrote:Fact table Ids, I agree, but if you building a summary that can be sliced by X dimensions, your keys to the dimensions had better be the same if you want a consistent path back to the data warehouse. If you change your keys, you have no solid evidence that what you are presenting in the end came from a particular dimensional record other than the business key matches.

I am assuming there are single dimension tables, not individual dimension tables for each fact. That said, dimension table primary keys should never change, otherwise joins to facts won't work without rekeying the facts (a nasty thing to try to do).

But queries should not rely on keys to aggregate and combine facts. A type 2 dimension is a good example, where a given entity would have a multitude of different keys from the same fact. Aggregating on a key, rather than attributes, would usually not give the desired results.

But even in the worst case, where each fact has their own unique dimension tables with keys different than similar dimension tables, queries should still be ok provided attribute values are consistant. I would agree that auditing would be a real pain as you would need to take into account the particular fact table a measure comes from, and creating aggregate fact tables and drill down would be impractical. Which is why such an arrangement is not good practice.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Common Key

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