Common Key

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?

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

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.

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.

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