Updating Data Marts

In some data warehouse implementations, data marts receive their data from the master data warehouse. In other implementations the data marts are updated locally and contribute data to the data warehouse database. Moving updated data to or from data marts can be straightforward if the data marts are designed to be consistent with the master data warehouse. The use of standardized schemas, shared dimensions, and common fact table formats greatly contribute to the ease of data mart maintenance.

If the data marts receive data updates from a master data warehouse, you should be able to design automated tasks that filter the data warehouse update data and post the appropriate data subset to each data mart during the update of the data warehouse database. Presentation applications used by the data mart may need to be adjusted to accommodate the new data.

If a data mart collects data locally and contributes data to the master data warehouse, you may need to treat the data mart as if it were an operational data source and bring the data into a preparation area for cleansing and verification against the data warehouse before posting it to the data warehouse. If the data mart is designed and managed as an integral part of the data warehouse, the verification process may not be necessary and the data can be loaded directly from the data mart into the data warehouse database. In either case, you may need to adjust presentation applications to accommodate the new data.

You can use Transact-SQL, Data Transformation Services (DTS), the bcp utility, or custom applications to update the data mart or data warehouse tables. You may also be able to use replication to perform data mart updates. For more information, see Replication Overview.