In the first post, I gave a quick discussion of BIML. This time around, I want to talk about a specific business problem that I used BIML to solve.
The company I work for uses a federated data model for transactional and warehouse data. In this model, we have a number of SQL Server installations, each of which has the same main database. Because this story deals with our warehouse, I’ll focus on that. To understand the model, it’s important to realize that all of our customers are businesses and their data are completely independent. This is what lets us use a federated model. Next up is a discussion of what this means:
Suppose we have a number of instances, and on each of those instances, we have a number of Warehouse databases. On the first instance, we might have Warehouse1 through Warehouse4, Warehouse5-6 on the second instance, etc. We have separate databases to reduce the size of each individual database and give our operations teams a chance to migrate troublesome databases to their own instance. With the supporting infrastructure in place, this works rather seamlessly for our development teams, letting them write one version of a procedure which goes out to all of the databases and a data access layer which tells our code calls which database you need to look at to get the data.
Unfortunately, there are times when we need to move client data between databases—for example, suppose two of our clients merge and the newly-formed mega-client wants to see everything across both parts of the company in one view. In order to do this, we need to do a data migration. We’re going to focus on one specific logistics problem: how do we get the data from WarehouseX into WarehouseY? The tool of choice is SQL Server Integration Services, and we have approximately 40 dimensions and facts to migrate. This is something that we could do by hand, but would get very tedious. Instead, this looks like a job for BIML.