16 Dec 2015 Providing a SQL Server Express edition with a solid maintenance plan

One of the challenges of using the free express editions of SQL Server is the lack of the SQL Agent. This means that creating a functioning maintenance plan is a bit more of a challenge then on a regular SQL Server instance. Luckily for us Ola Hallengren created his maintenance plan in such a way that it can easily be altered to make it usable for SQL express editions.

07 Dec 2015 Emailing SSRS reports on a scheduled basis, without using subscriptions (or attachments)

In the Microsft BI world, SSRS is a very much integrated product. We can find SSRS reports in many companies. A question that we encounter often as BI consultants is to render some of these SSRS reports on a scheduled basis and save them in a shared location or email them as a pdf file. Subscriptions can do this for you. However, with subscriptions, we are missing some flexibility, like dynamic report names or sending the rendered report only when certain conditions are met.

24 Nov 2015 Visual tool for turning business requirements into reality

“Visualize to involve, write to describe" In business intelligence we extract data from anywhere. Beside the technical structures that define how the data is stored, we also need to understand the business processes behind that structure. It’s all in the name, right

17 Nov 2015 Overlooked Properties in SSIS: Lookup Component Cache Mode

SSIS has so many properties that some are overlooked or ignored. In this article we will have a closer look at one of them: the “cache mode” setting of the Lookup Component. As the name indicates, the “Cache mode” lets you choose which form of caching the Lookup component will use. The available modes are Full, Partial and None. By default “Full Cache” mode is selected. This is a good default value, but there are of course situations in which you might want to change it, otherwise there would be no need for the other modes.

09 Nov 2015 TEMPDB: The Ghost of Version Store

Close to Halloween we noticed that some SQL Servers had started acting spooky, as if they wanted to add to the sentiment of Halloween. Last week I had one SQL Server doing exactly that. The server had been running perfectly for the past months but that day its TempdDB was starting to fill up. Of course this required some investigation, and I found that VersionStore was the problem for my unexpected TempDB growth. The strange thing was that one database was taking up almost all of my tempdb space in version store, but it did not have any open transactions. Spooky stuff indeed!

27 Oct 2015 Power Query Beyond the Ribbon: Types

Some time ago I gave a session on SQL Server Days about Power Query. I really enjoyed speaking there. The title of the session was “Power Query Beyond The Ribbon”. I have to admit a major strength of Power Query is that you can do pretty much everything only by using the ribbon. However this doesn’t mean that learning something about M, the Power Query Formula Language, is a waste of time. With M you can solve some complex problems, automate parts or simplify your solution.

20 Oct 2015 SQL server 2005 will become the new SQL server 2000

Many years ago Microsoft released SQL server 2005. This was an important release after SQL server 2000. Today, a decade later, Microsoft is about to stop the extended support on SQL server 2005 on April 12th 2016. SQL server 2005 is end of life, no more updates will be released. When running SQL server 2005 you will be exposed to potential bugs and security threats.

16 Oct 2015 Stad Turnhout maakte een betaalbaar rapport over z’n personeelskost

Het stadsbestuur van Turnhout wilde weten hoeveel de werkelijke personeelskost verschilde van de gebudgetteerde kost. Zo kon het zijn toekomstige budgetten nog verfijnder opstellen en daarnaast ook sneller en correct rapporteren aan de gemeenteraad.

15 Oct 2015 Patiëntengegevens kunnen niet langer verloren gaan in het Ziekenhuis Oost-Limburg

Werken met een elektronisch patiëntendossier maakt het leven veel gemakkelijker voor iedereen in het Ziekenhuis Oost-Limburg. Het heeft wel een belangrijke implicatie: wat er ook gebeurt, de digitale gegevens mogen niet verloren gaan. De wet vereist ook dat patiëntengegevens tenminste 30 jaar worden bijgehouden. Daarom ging het ziekenhuis op zoek naar een oplossing die hen volledige garanties zou geven op lange termijn, tegen de laagst mogelijke kost.

13 Oct 2015 How to create a date table in Power BI in 2 simple steps

With the introduction of "Calculated Columns" in Power BI Desktop things got easier. In this blogpost I will show you how to create a complete date table is just 2 simple steps.

05 Oct 2015 Creating a scalable tempdb for your cloud servers

When deploying to the cloud one of the hardest challenges is to get tempdb to scale in the same way as your virtual server. Why is this, well as cloud machines are scalable and thus can change configurations during their lifecycle the number of tempdb files should be able to scale accordingly, so the amount of tempdb files becomes a dynamic value. Luckily for us, this parameter changes only after a shut down and restart of your virtual machine, so if we had a script that could adjust our tempdb needs every time the configuration changes we could deploy this and be more at ease.

29 Sep 2015 Master Data Services in real life

Master Data Services is one of the hidden gems in the Microsoft BI Tool stack. As the name suggests the idea is to use the tool to technically support Master Data Management. But what we see in real life is that Master Data Services, although being a good tool, isn’t up to standards compared to the master data management tools offered by other big vendors like IBM and SAP. This doesn’t mean that you shouldn’t use Master Data Services (MDS). MDS has a big added value in lots of Business Intelligence and Data Warehousing projects.

28 Sep 2015 SQL Server virtualiseren, waarom?

Meer en meer krijgen wij als consultant de vraag of het nuttig is om SQL Server te virtualiseren. Buiten het licentieverhaal, waar virtualisatie alleen al een enorme impact heeft, zijn er ook technische redenen om wel of niet te virtualiseren. Dit is vooral zo wanneer de bestaande omgeving veel kleine fysieke servers heeft of enkele grote machines met meerdere instances. In deze post lijsten we dan ook de voor- en nadelen op van het virtualiseren van SQL Server. Monday 28 September 2015 SQL Server

17 Sep 2015 Zien ze het BI-bos nog door de bomen bij Microsoft?

Deze vraag krijg ik de laatste tijd vaak voorgeschoteld van klanten die advies vragen welke richting ze uit moeten met hun Business Intelligence. Sinds de overname van Datazen en de grote update van Power BI afgelopen zomer beginnen alle puzzelstukjes in elkaar te vallen en kunnen we spreken van een compleet BI-platform dat zeer concurrentieel is ten opzichte van de andere spelers in de markt.

10 Sep 2015 Migrating Access 2007 to SQL Server 2014

There comes a time in every SQL Server dba’s life when they will have to consolidate an MS Access database to an SQL Server. It's inevitable and destiny decided that it was my turn today. A heavily used Access database which had almost grown beyond its capacity sealed my fate. It had about 1.2 GB of data and had become extremely slow at executing queries, which was something we could no longer ignore. This was the first Access database I was going to migrate, so I started by doing some research on the good old internet. Many people suggest that making SSIS packages is the easiest way to do this, while others recommend using openrowset or opendatasource. I chose the openrowset technique.

28 Aug 2015 What to expect from Datazen

What to expect from this tool, recently acquired by Microsoft? Here are some of the features that make Datazen a great and enterprise ready application.

18 Aug 2015 ETL with Direct Database Access versus API/JSON

There is a large variety in possible data sources from which we can extract and that number is not likely going to decrease. The ETL path that has to be taken for each source can vary heavily depending on all kinds of factors including application architecture, company security, departmental policies, etcetera. Middle-sized to large companies tend to use multiple applications from which data can be extracted to feed the greedy monster that is BI.

04 Aug 2015 Excel in SSIS: fixing the wrong data types

Imagine your client gives you an Excel file as the SSIS source for a new project. When you load the data you find that the data type is not aligning as expected and some of the rows are not filled with the data provided. You then try to change the input data type in the Excel source component, but you can’t seem to get the output you want. So what's the problem? Well, let’s take a look at the input.