Master Data and Dimension location

We just finished creating Master data sets and assigned responsibilities and accountabilities to data stewards. We have plans to create business process specific datamart residing in their own databases. Also, we will derive dimensionas from the Master Data sets to be used by various marts.

Do you suggest storing those conformed dimensions in one database and have all marts (containing fact tables) use those from that central location or should we use ETL to populate versions of dimensions for each mart - next to the fact tables?

Thanks in advance for your response.

Victor

Maintain dimensions in one place. The critical data element is the dimension's surrogate primary key. If you maintain a dimension table is multiple data marts there is no way to maintain consistant conforming keys across marts. You would have no easy way to integrate data from different marts.

If the facts must reside in separate databases you push the dimensions out to the marts after they have been updated. For ETL, it would be necessary to use the central dimension database as the lookup source to assign keys if there are timing issues or if it is necesary to infer dimension rows from the facts. You can always push the dimension update to the marts later.