3 Important Lessons about Test Automation in Business Intelligence

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

Continue reading...

VSTS Continuous MSBuild for DWH / BI

Wait, didn't I post this already several weeks ago? Well, almost. A few weeks ago, I showed how to set up a build agent using devenv.com. Unfortunately, I ran into some problems like failing builds not reporting failure and SSAS Tabular projects not building correctly. However, it turns out to be pretty easy to build almost everything using MSBuild. Here's how.

Continue reading...

VSTS Continuous Build for DWH / BI

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.

Continue reading...

Automated Deployments using Visual Studio

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.

Continue reading...

Automated Testing for the Data Warehouse

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?

Continue reading...

Calculating commute distance using SSIS and the GoogleMaps API

Last week, I had an interesting question from one of my clients about estimates of planned commute distances. For example: What is the distance I travel when I, living at Kalverstraat 7 (Amsterdam) commute five days a week to my customer in Paris (1, rue Victor Cousin)? By the way: it should be calculated for all employees, for all customer locations, so manually entering distances is not considered an option.

Continue reading...

Moving BI to the cloud: Consider this...

While looking into the possibilities of moving my customer's BI infrastructure into the cloud, my primary question was how to handle the lack of cloud SSAS. But there's a lot more to keep in mind. Here's a few things that I think should be thought of from an architectural point of view: not only in designing your EDW architecture, but also in designing a way of work. I've summarized them below:

Continue reading...