With Continuous Integration working for my Data Warehousing solution (SQL Server, SSAS Tabular, SSIS), it's time to step forward. Before moving to Continuous Deployment, I want to have a rigorous and automated testing on my EDW. But how to get there?
As you might know, I'm keeping an eye on how I can automate my Data Warehousing testing. In "Automated Testing for the Data Warehouse" my observation was
In essence, testing a Data Warehouse isn't that complex
It turns out I'm not the only one thinking about this (really? 😉 ). Some time ago I listened to episode 72 from SQL Data Partners podcast, starring Lynn Winterboer. Really deep insight in the requirements for test-driven Data Warehouse work there, I definitely recommend listening the full podcast
In my earlier post "Automated Deployments using Visual Studio" I metioned that the method described was a workaround because I hadn't figured out how to do a continuous build in VSTS (Visual Studio Team Services) yet. With hindsight, that workaround was not really needed: VSTS build turns out to be só easy, that it's ridiculous to try anything else. If you, like me until yesterday, don't know where to start to set up continuous build within VSTS, you're lucky. Below is a step-by-step explanation to setup your automated build for VSTS.
As you might've noticed, I'm keeping an eye on Continuous Integration for Business Intelligence. Currently, I'm looking for a grad student to help me implement CI inside BI in his/her internship. As we're located in Holland, speaking Dutch is a prerequisite - but as a bonus, you'll be able to share your findings in English on msbiblog.com. The rest of this post will be in Dutch.
When deploying changes through DTAP, you might need to sync your environments. For example, I sync my Acceptance environment before deploying a new increment, so I can easily see the effects of my new software on production data. To be honest, I'm not very good in these database management tasks so I have to automate these sync tasks. Here's how:
In my earlier post "Automated Testing for the Data Warehouse", I sketched the outlines of what would be needed in order to achieve automated testing for your Data Warehouse solutions. Today, I want to look at the first step: build & deploy. Between the previous post and the current one, some useful content about this has been written already by Jens Vestergaard - he even uses VSTS to do his builds, something I still have to look into. Meanwhile, here's my method of acquiring the latest sources & building them using Visual Studio.
Case: we've integrated two sources of customers. We want to add a third source.
Q: How do we at the same time know that our current integration and solutions will continue to work while at the same time integrating the new sources?
A: Test it.
Q: How do we get faster deployments and more stability?
A: Automate the tests, so they can run continuously.
When integrating data, especially in agile environments, our already-integrated data is very likely to get some more integration. So WHY does automated testing happen so rarely within Data Warehouse projects?