Best Practice: no more queries in frontend tools

You may have experienced it: your report was working just fine, untill you published a database change and oops! a report doesn't work anymore. Especially when you have about 70+ reports running their own queries, this can be a pain.

Why would a report contain a query? Well, because it is easy, quick to develop, and why not?

The reason is, you don't get to manage all those queries at all. They are the hidden queries, long forgotten, never tuned and unfindable in your database.

Please use views or a stored procedure to feed your datasets in reporting services. If the query lives in the database and you use a daily build to check if the code still works, then you will be notified if the query is broken, before you publish it to production. Same goes for all front end tools, including Power BI. At least use a view. The other tools going to the database should not know the datamodel at all.

The idea is that a view can remain consistent while the underlying datamodel can change as needed.

Azure Data Factory, first thoughts

I have been trying out Azure Data Factory.

You can wonder if ADF is more like SSIS in the cloud or SQL Server Agent in the cloud. It does not quite feel like a pure ETL tool.¬†Coming from SSIS on premise this is a completely different experience. My first impression is that the UI is not very intuitive. It takes a while before you realize where you need to be when solving a problem. Another thing that I found was that there is not very much about it online. Stuck with a problem? you're likely to be on your own. Googling my error message got me nothing, that's quite refreshing for me (unthinkable for a SQL Server error) ūüôā

It is quite easy to copy data from A to B. In other words: you can do E L: Extract and Load. The T is not very easily done. If you want to get some data from Azure Blob storage and load it into your Azure SQL, this is a good tool. But if you want to do complex transformations? Not so sure yet...




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 Unfortunately, I ran into some problems like failing builds not reporting failure and SSAS Tabular projects not building correctly1. However, it turns out to be pretty easy to build almost everything2 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...

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 Director at The Washington Post) and Steve Duenes and Xaquín G.V. (The New York Times) some sentences got me thinking about the role of BI.

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 on The rest of this post will be in Dutch.

Continue reading...

Sharing your Power BI reports

If you are using Power BI, you may want to consider how you are going to share your reports and dashboards. Should you share your dashboards? Should you use groups or content packs?

There are several ways to do this.


  1. Share your Dashboards: open your dashboard and click on the "Share" Icon. Your recipients can open the underlying reports but they cannot edit your report or create new reports. This is great for situations where you just want your users to see data, but not make reports on their own.
  2. Create a content pack. This way, you can share a bundle of reports, dashboards and data sets. The recipients cannot alter the reports they recieve. But they can make a copy of it for themselves and edit that one. Also they can create new reports on the same dataset. This is a great way to bring out a bundle of formal reports which will always stay they same, yet still allow personal versions of the report.
  3. Create a group: if you create a group, all participants of that group can alter the reports and co-create contentpacks. This is great if the people all work together on content and have the same rights.
  4. Publish to web. Ok this is not really sharing, this is straight out publishing and should be done ever so carefully!

You can mix it together too: at this moment I have a Power BI group where several users can edit the reports. Together they publish the content pack to the rest of the organization that consumes the reports.

How do you use Power BI?


Power BI desktop or online?

When you are creating Power BI reports. Do you develop them online or in desktop mode? If you are doing pure self service BI for quick prototyping, it doesn't matter all that much. However, if you use live connections, you may want to use Desktop since you can easily switch between test and production servers.

Continue reading...

Your data is going places

There are still a lot of people who do not believe BI and cloud go together.

"My data is too valuable to store anywhere else but on premise!". Most of the times we are talking about gut feeling here. It is not a strange thought either. But the funny thing is: maybe your data is already in the cloud...

Using office 365? your data is in the cloud.

That report someone put in their dropbox? it's in the cloud.

Someone in your organisation using Power BI and publishes it to you are in the cloud.

This may seem obvious, (and it is). But unless your data governance is tight in control, your data may already be going places.

How good is your data governance?