Master Data and Dimension location

View previous topic View next topic Go down

Master Data and Dimension location

Post  VictorB on Mon Jul 13, 2009 4:11 pm

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

VictorB

Posts : 1
Join date : 2009-07-13

View user profile

Back to top Go down

Re: Master Data and Dimension location

Post  ngalemmo on Mon Jul 13, 2009 9:11 pm

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

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

View user profile http://aginity.com

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