Thursday, April 8, 2010

Copying static dimension data across environments

In a recent deployment of a data warehouse to the TEST environment I wanted to copy all records from the static dimensions table that existed in DEV. Static dimensions store data that changes rarely. For example, the data for entity such as gender rarely changes. Also, static dimensions can be used if there is no easy programmatic way to determine the list of values for an entity.

For this project, during deployment, I first scripted the database schema(tables) and objects such as users, views, stored procedures and triggers in DEV and ran those scripts in TEST. I later deployed the ETL in TEST. In order to run the initial load the data in reference static dimensions had to be populated. Now there are several ways to migrate your data from DEV to TEST but if you have SQL Server Management Studio the one listed below was a quick and easy solution.

1. Simply copy the entire result set grid of the query in DEV: select * from
2. Right click on the corresponding static dimension in the target environment and click on open table.
3. Paste the contents.

Quick and easy!

No comments:

Post a Comment