Best practice for versioning tables/views?

Has anyone developed a best practice for versioning tables and views within the DW? I suppose as long as the only changes are “upward compatible”—adding new dimensions, attributes or measures—there would be no need for versioning… just add the appropriate new columns to existing tables/views.

But what about when a major structural change is necessary? For example, I am making some breaking changes to my customer dimension. Should I roll out the new version as DimCustomer02 (with the ‘02’ at the end denoting the version)? Appending a version number would allow multiple versions of a table/view to exist at the same time, which could support a gradual migration to the new version. Any downsides other than needing to keep track of the multiple versions?

(I would not show the version numbers to users… I would update the models used by the BI tools to use the latest version.)

I think the best approach would be keep the schemas (table structures) in a source control system. There are many source controls that are free and you can check in every version of your source in them. The one we are using and are happy with it is tortoise. http://tortoisesvn.tigris.org/
In this way you don't have to keep the different copies before and after changes. Of course it is about keeping the history of the schema changes, to retain the data you need to take a back up as well (if it's required).

Hi there, I would look first at how MediaWiki did it in their open source project that runs Wikipedia. They're all about versioning. It will definitely helps you..


______________
nose right