03 Jan 2017 Power BI – Ranking with ‘Other’ group

Not so long ago a client asked me to rebuild a report of theirs in Power BI. It contained the top 5 countries ordered by number of visitors and … an Other group at the end.

20 Dec 2016 Power BI – From data to information

We live in a world where an enormous amount of data is available. But having access to the data is not the same as having insight. If we combine this data access with the right tools and a little bit of effort, you might be able to transform your data into information in no time.

13 Dec 2016 SSIS – Boosting Range Lookups with a script component

Did you ever needed to use a lookup in SSIS that was not the standard equi-join and there was no possibility to do the lookup directly in the source?

06 Dec 2016 Power BI on-premise

Although Microsoft always had said that bringing Power BI on-premise was on the road map, no clear time frame could be given. That was the case at least up until recently, when Microsoft announced the Technical Preview of the Power BI on-premise at SQLPass and Ignite.

29 Nov 2016 Dynamic bulk copy staging package

The most commonly created packages are data copy packaging from one source DB to a staging target DB. Instead of creating multiple packages (with or without BIML), a META data free solution can be made using the .NET SqlBulkCopy class.

22 Nov 2016 Dynamic bulk copy SSIS package

Using the .NET SqlBulkCopy class, you can easily create a parameterized package to copy a bunch of tables at once. Instead of creating multiple packages, we can also create a generic package that covers the complete set of tables we want to copy.

15 Nov 2016 Meta driven SSIS and bulk copy SSIS packages

ETL processes have a lot of common parts between them. One important part of it that’s frequently repeated is a data copy sub-process to copy table(s) data from one server/database to another. Sometimes full table copies, in other cases with specific data selection. For every table or selection, you create the process in one or several packages/control flows and/or data tasks. There is not much special to say about small tables, but large tables are another matter.

08 Nov 2016 Using SQL filetables with entity framework

Recently I had a problem on a project where we needed to securely store documents. It was the first time for the customer to do such a thing. Telling them this was not so hard, I convinced them to us SQL file tables to store these documents securely in an SQL server database.

25 Oct 2016 The Chamber of SQL – The Chamber of Secrets

The Chamber of SQL - Part 2 A couple of months after the breakdown of one of their databases in production, Stephanie, Marc and Richard worked out a very good plan. Stephanie created a restore script that would automatically be fired on a weekly base.

18 Oct 2016 R[101]

What is the buzz about ‘R’? What is R, how can I use it, where do I use it, how do I start? I had all the same questions about it, the first time I heard of it. That’s why to explain what the possibilities are in R and what you can use it for.

11 Oct 2016 The Chamber of SQL – just another day in paradise

Let me begin by introducing the main characters of this blog. We have Marc, a senior system administrator with a deep technical knowledge about Windows PowerShell, networks and everything related to Active Directory and DNS. There’s also Richard, CTO of the company, a great visionary and in the old days one of the best system administrators the company had. Finally, there is Stephanie, who has been working with SQL Server on various projects for almost ten years, working as an external consultant.

27 Sep 2016 Don’t fool mama Natalie!

Let me tell you a true story about mama Natalie and her son Steve. Steve is a 14-year old, clever, young man, one of the cool guys at school. He always keeps his mobile phone at hand. As a matter of fact, he has a black belt in texting. Unfortunately, it seems that Steve’s texting behavior has been a bad influence on his grades at school. Mama Natalia suspects Steve of favoring his phone over his books. But how can she make Steve see the connection?

20 Sep 2016 Triggering SQL agent jobs from Excel

A client requested whether it was possible to launch an SSIS package flow from Excel. The reason behind it was that the load of data in question was only used once a year and just over a period of one or two weeks. The actual code used to do this was no longer than four lines of VBA code in Excel.

13 Sep 2016 SSIS Lookup component – Beware the partial cache

After publishing my previous blog about making a lookup case insensitive, I received a question from my most loyal reader, my wife. Just like me, she is a BI developer, so that makes her the perfect proofreader. After reading my post, she wondered what would happen if you used a partial cache mode, and the input data didn’t match the casing of the lookup data. So I started investigating…

06 Sep 2016 Dynamic data column position for flat files in SSIS

Scenario: your customer has determined that your SSIS source for his new project is a CSV file with a header row followed by the data rows. All is developed and tests were made, all is success. After a few days of testing the customer wants the business user to have the ability to change the position of the data columns as the customer needs.

30 Aug 2016 Why is the SSIS Lookup Component Case Sensitive (or isn’t it)?

Many websites and blog posts will tell you that the SSIS Lookup Component is Case Sensitive (CS). And while they are correct that there isn’t a direct property to change the case sensitivity of the component, it can however be used in a non-case sensitive way.

24 Aug 2016 Create a date table in Power BI

One of our most-read blogpost of all times without a doubt is the one in which Frederik Vandeputte explains how you can create a Power BI date table in just two steps. To make it even easier for you, we made a step-by-step video to build your own date table in just a few minutes. 

23 Aug 2016 Database Lifecycle Management (DLM)

Controlling database deployment, managing (measured) deployment to production of database changes and keeping an overview of these changes can be challenging. As an answer to this problem, I’ve been searching for a well-working approach to implement Database Lifecycle Management (DLM), of which the target would be to use this approach on all projects.