vstrienKoos van Strien

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

All articles by vstrien

 

Three ways to build SSIS projects in VSTS

When setting up Continuous Integration in VSTS, chances are you’ll run into the problem of building SSIS projects: where database projects are building just fine, SSIS just doesn’t build out-of-the-box (at least not at the time of writing). Here’s how you fix it:
Continue reading...  

Continuous Integration for BI in VSTS: Splitting Build Steps by Project Type

If you want to set up Continuous Integration using Visual Studio Team Services (VSTS) in order to build your BI projects, you'll run into the fact that some project types can be built and released out-of-the-box by MSBuild, while others need specific logic to create or deploy artifacts. In this post, I share my current
Continue reading...  

Data Warehouse Automation vs. Data Virtualization pt. 2

I see a new pattern emerge: every time I want to write a blog post about something I've been thinking about, I discover some content that's way better than I could ever produce. Just like last week's Wikipedia article contained everything I wanted to say, so today there's an article making a far better point
Continue reading...  

Data Virtualization vs. Data Warehouse Automation?

I've been thinking about writing three posts:
Continue reading...  

I've been away for a while, the reason why will surprise you...

Aaannd that's the first clickbaity title on MSBIBlog.com. But now that I got your attention:
Continue reading...  

Data Warehouse Automation Thoughts

I've been looking into DWA for a few months now, and might give it a try sooner or later. Here's some thoughts about it:
Continue reading...  

Setting up DWH Test Automation: Design & Arch

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?
Continue reading...  

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
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
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
Continue reading...  

A new role for the BI team?

This Christmas holiday, I read Alberto Cairo's "the functional art" - a must-read if you're interested in data visualization at any level. In the fourth part of the book he includes some interviews with leading people in the data visualization field. Especially in the interviews with Hannah Fairfield (at the time of the interview Graphics
Continue reading...  

Looking for intern / grad student: Continuous Integration for BI

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
Continue reading...  

Synchronizing SQL Server environments

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
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
Continue reading...  

What makes BI specialists move (or stay)?

Recently, I got a message from within my network. The guy reaching out was looking for a senior MS BI specialist, and asked my opinion about what made people move towards another employer. Here's my $0.02:
Continue reading...  

Building the Poor Man's IoT Presentation (+ inspiration for new posts)

As you might remember, I ran a few posts covering Azure IoT Hub early this year: Azure IoT is out! Here's how you connect with Power BI Building the Poor Man's IoT Last Saturday, I did a talk on SQL Saturday in the Netherlands covering my experiences. If you're interested in my presentation, the slidedeck is on the
Continue reading...  

Azure ML Thursday 7: xgboost in Azure ML Studio

Last week, we trained an xgboost model for our dataset inside R. In order to use your trained dataset in Azure ML, you need to export & upload it much like we did two weeks ago in Python. Today, I'll show how to import the trained R model into Azure ML studio, thus enabling you
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
Continue reading...  

Azure ML Thursday 6: xgboost in R

Last Azure ML Thursdays we explored how to do our Machine Learning in Python. Python in Azure ML doesn't include one particularly succesful algorithm though - xgboost. Python packages are available, but just not yet for Windows - which means also not inside Azure ML Studio. But they are available inside R! Today, we take the same approach
Continue reading...  

Azure ML Thursday 5: trained Python models

Last week, we stepped out of Azure ML to look at building ML models in Python using scikit-learn. Today, we focus on getting the trained model back into Azure ML - the place where my ML solutions live in a managed, enterprise environment.
Continue reading...  

Azure ML Thursday 4: ML in Python

On this fourth Azure ML Thursday series we move our ML solution out of Azure ML and set our first steps in Python with scikit-learn. Today, we look at using "just" Python for doing ML, next week we bring the trained models to Azure ML. You'll notice there's a lot more to tweak and improve once you do
Continue reading...  

Azure ML Thursday 3: Tuning hyperparameters

On this third Azure ML Thursday we'll continue our series testing different models and tuning hyperparameters. Before playing with new algorithms or tuning parameters, be sure you know how to train and test your data!
Continue reading...  

Azure ML Thursday 2: Train, Test, Submit!

On this second Azure ML Thursday, I'll discuss a first entry on a competition. Also, some background about splits and cross-validation. Microsoft has provided a walkthrough for your first entry, so I won't describe all the steps you'll need to take. Rather, I'll provide some first, easy tweaks to the first submission.
Continue reading...  

Azure ML Thursday

Past two weeks, I've explored some applications of Machine Learning by doing a Cortana Intelligence competition (https://gallery.cortanaintelligence.com/competitions). This is a Kaggle-like challenge, but restricted to the Azure ML environment, which creates some challenges of its own. Because the deadline of the challenge is on October 10th, I cannot post all my experiences (yet). So I've
Continue reading...  

Happy birthday, Power BI!

Happy first birthday, Power BI! Exactly one year ago, Power BI was released in public. But this wasn't the only release: one month after the public release of Power BI my daughter Sanne was born.
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
Continue reading...  

Notes from the Dutch Biml Usergroup kickoff

Today, I attended the kickoff of the Biml Usergroup in the Netherlands. We had several hours of training from Scott Currie at a wonderful location (Impact Hub Amsterdam). Also some hints were dropped about the future of Biml and Varigence products. Here are my notes:
Continue reading...  

Lambda Architecture for the DWH

Recently I saw this post from Davide Mauri (basically he announced his session "Reference Big Data Lambda Architecture in Azure" at SQL Nexus). Although I would've loved to attend SQL Nexus, I'm not able to do so this year 🙁 . The next best thing I could do was order and read Marz & Warren's "Big Data" (Manning) -
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
Continue reading...  

Moving BI to the Cloud: Ditch SSAS?

Some time ago I read Chris Webb blogged "Thoughts On The Power BI Announcements At The MS Data Insights Summit", where between the lines was this rather interesting point: For the last few years my customers have asked me when MS was going to release SSAS in the cloud and I’ve always replied that Power
Continue reading...  

Communicating with Azure IoT using Python

During last Christmas' project (the Poor Man's IoT - read more about it here and here) I wanted to build a small Proof of Concept communicating my home freezer sensor to Azure IoT. However, if you're not a C# dev, the steps to set up this 'small' Proof of Concept can be quite daunting - especially if you're
Continue reading...  

Experiences with Power BI Enterprise Gateway

A few weeks ago, I blogged about my experiences with the Analysis Services Connector. Because the Enterprise Gateway was still in preview back then, I didn't want to use that in production yet. Meanwhile the Enterprise Gateway has been released, so it's time to update my findings.
Continue reading...  

Security and the Enterprise Gateway

While upgrading from Analysis Services Connector to the Power BI Enterprise Gateway, you might notice a change in authorization structure. Here's a brief description of the differences you need to know when upgrading. I thought there was a change in authorization structure, but I had didn't fully understand the way Analysis Services Connector authorizes its users.
Continue reading...  

Building the Poor Man's IoT

This Christmas holiday I spent some time fiddling with sensors to build a Proof of Concept on Azure IoT. In an earlier post I've shown how to connect your Azure IoT Hub data to a live Power BI dashboard, and as promised, here's part two: a description - as well as working code - how to
Continue reading...  

Hard Work Pays Off: Aligning matrices (SSRS non-ugly #6)

In the previous posts in the 'SSRS non-ugly' series, we looked at grid-based report layouts, a powerful technique to standardize your layout and align all elements within your report. All this grid stuff is cool, but how to cope with matrices? I mean, c'mon Koos - last time you just deliberately left out the matrices
Continue reading...  

Azure IoT is out! Here's how you connect with Power BI

Azure IoT Hub is public! And if you're a little bit like me, you want to play with it NOW - which is what I did. Guess what? It turns out to be not only easy, but also pretty cheap (not to mention awesome) to set up your IoT Proof of Concept!
Continue reading...  

SSRS non-ugly #5: The nitty griddy details

In this post - part of my 'creating non-ugly SSRS reports' series - I'll introduce you to a design and layout technique quite common in webdesign, but AFAIK not too often applied in reporting: using a column grid. Curious? Read on!
Continue reading...  

Unexpected SSIS Precedence Constraints Behaviour

In one of my projects, I had two simultaneous sequence containers. One is executed always, and one only rarely. So I decided to control the Task Flow with a package parameter. As soon as there was task I needed to do once all paths were completed, I ran into SSIS behaviour that was (to me) unexpected.
Continue reading...  

SSRS non-ugly #4: Think About Layout

In the fourth, fifth and sixth parts of my series about non-ugly SSRS reports, I'll provide some hands-on 'wins' to improve your report layout. Today: two quick wins.
Continue reading...  

Issues (& solutions) with Power BI Analysis Services Connector

Last couple of weeks, I tried to install the Power BI Analysis Services Connector, and ran into a few issues (and found some solutions). I thought it'd be good to share them, in case anyone runs into the same issues. So here's my story of installing Power BI Analysis Services connector - translated to a
Continue reading...  

SSRS non-ugly #3: Chart styles

This is the third post in a new series on non-ugly SSRS reports. As I stated in the opening post: SSRS doesn't have to be ugly This week, lots of folks are tweeting and blogging about the new SSRS 2016. Exciting stuff, looks really great! But what about when you're still stuck with one of the RTM versions of
Continue reading...  

Creating non-ugly SSRS reports #2: formatting tables

This is the second post in a new series on non-ugly SSRS reports. As I stated in the opening post: SSRS doesn't have to be ugly In part 1 (the use of typography & fonts), we focused on using typography (and avoiding borders) and some colors to get rid of the Office 2003-styling SSRS has by default. One
Continue reading...  

Creating non-ugly SSRS reports #1: The use of typography & fonts

This is the first post in a new series on non-ugly SSRS reports. As I stated in the opening post: SSRS doesn't have to be ugly One of the key things I pointed at to create less-ugly SSRS reports is by getting rid of the Office 2003-style. This is something that's - among other things - signified
Continue reading...  

SSRS - it doesn't have to be ugly..

There is an important fact in life that few people realise. I'll state it here, so you all know it: SSRS reports don't have to be ugly. You read that right. Read it again, if you want. It's still there.
Continue reading...  

Re-compressing Excel PowerPivot workbooks for an even smaller filesize

Today, I needed to send an Excel-file to a customer, but it was too large according to his mailserver. Of course, there are several solutions to that, but uploading the file to MegaUpload or RapidShare wasn't exactly what the customer had in mind :).
Continue reading...  

Package generation with SSIS - an overview

Although you can hardly ignore BIML (with bloggers like Cathrine Wilhelmsen and Bill Fellows sharing good stuff), there are still other options for package generation in SSIS. While enjoying a small conversation on LinkedIn about the automation options, I suddenly remembered still having a slidedeck from a presentation I gave early 2014. Please note that these are my
Continue reading...  

Visual Studio Eror Messages

Yes, that typo was intended. Remember when I said this? [Using Google to find a solution to] Error messages? Be sure to include the exact message - "in quotes" Recently I encountered a occasion where this helped especially well. A colleague emailed me with the question why his VS2010 install didn't load his SSRS Preview correctly. The 'Run' functionality worked, but the
Continue reading...  

Gathering knowledge and keeping up

When training and coaching Microsoft Business Intelligence developers and analysts, people sometimes ask how to keep up with all the new stuff developing. Or, how to start building your knowledge inside your profession. Here are some thoughts about that.
Continue reading...  

If you ever need to determine daylight saving time in Europe, here's the T-SQL script

Or, as the Dutch call it, 'zomertijd' and 'wintertijd'
Continue reading...  

SQL Server MONEY datatype limitations

Be very, VERY careful when using SQL Server's MONEY datatype. It's okay for storage of money things (offers some nice features too), and has an accuracy of 0.0001. Once you start calculating with that, beware. 1/10000th is quite a limitation.
Continue reading...