PowerBI – Table Heat Map
Ever wondered how to create a heat map in PowerBI without having to use a custom visual? There is now a simple way to do this by using Conditional Formatting on a Matrix table! In my example I took some Google Analytics data showing the times of the day and amount of visitors in that time. Read More…

PowerBI – Turn on Preview Features
Every month PowerBI releases new features. Some of the features are in preview mode and unless you turn it on you don’t get to use the preview features. This post explains how to turn them on. Firstly you need to have the latest version of PowerBI to get the latest features. Read More…

Creating PowerBI Layouts
PowerBI has this great functionality where you can go and download preset layouts which make your PowerBI reports stand out more. There is a nice trick to doing this yourself. You can create layouts in PowerPoint and then save them as images. Then insert them into your PowerBI report as an image and send the image right to the back. Read More…

Pivot and Unpivot
One thing that I still get confused about writing is pivot queries. I find myself needing to lookup the syntax every time. Basically you use Pivot and Unpivot to change the output of a table. If you would like rows turned into columns you can use pivot and for the opposite you can use unpivot. Read More…

Merge Statements
Over the years I have come across a lot of merge statements and I find most of the time there is one fundamental flaw in how it is written by most people. In the update section in most cases I find that there is no section there to check if anything has changed against the target from the source, therefore everything gets updated each and every single time. Read More…

Querying Data in Temporal Tables
When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal table. If the PERIOD columns are not hidden, their values will appear in a SELECT * query. If you specified PERIOD columns as hidden, their values won’t appear in a SELECT * query. When the PERIOD columns are hidden, reference the PERIOD columns specifically in the SELECT clause to return the values for these columns. Read More…

Modifying Temporal Tables
Data in a temporal table is modified using regular DML statements with one important difference: the period column data cannot be directly modified. When data is updated, it is versioned, with the previous version of each updated row and is inserted into the history table. When data is deleted, the delete is logical, with the row moved into the history table from the current table – it is not permanently deleted. Read More…

Temporal Tables
Let’s start off with what is a temporal table? SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Read More…

Inline Table-Valued Functions (TVFs)
Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized. Read More…

SQL Transactions
Transactions in SQL Server are very useful, however I have come across that not many people use them. Not sure why this could be, so thought I would share how transactions work and the different modes. A transaction is a single unit of work. Read More…

Execute SFTP Task in SSIS
Sometimes you need to automate the upload of a file to an FTP or SFTP. Thought I would share the way that I use the SFTP task in SSIS to upload files. Firstly create the variables to use in the SFTP task. Variables are an easier way to manage things without going into the task, so you can change the password in the variable and not have to open up the SFTP task. Read More…

MDS 2016 Permissions Setup
In previous releases, you had to choose a Windows account during the installation and configuration of the MDS database. This account would become a system-wide administrator and have access to all models. The problem is sometimes system administrators chose their own account instead of a service account. Read More…

Master Data Services – Change System Administrator
You will notice when setting up MDS for the first time that it will use your AD account by default. This can become a problem if you leave a company and you are the System Administrator for MDS. When your AD account gets disabled by your workplace MDS will stop working, so best to set a service account as System Administrator on MDS. Read More…

Using a ZIP Task in SSIS
The ZIP file task is a great feature that SSIS has for when you need to move/email a large amount of data. Also if you have multiple files you want to move to the same location, this makes it easier for you, so you can move just one file instead of many. Read More…

Setting an Alert on a Stored Procedure
Recently I came across an issue where I needed to set an alert that when a report is populated it needs to send out an alert. Unfortunately in SSRS you can only do subscriptions and I have not found a way to do those kinds of alerts on an SSRS report. Read More…

Run a PowerShell Script from SSIS
It can be confusing to know which tool in the SSIS toolbox to use when trying to execute a PowerShell script from within SSIS. The best task to use is an Execute Process Task. Read More…

SSIS Save to XML Output
There isn’t much online about saving an output of data to an XML file. When trying with a file system task it throws errors. There is a workaround for this. You can use a data flow task in SSIS to export to a flat file that is an XML file. You simply need to have a query that outputs in XML format. Read More…

SSRS Cancel Report Still Running In The Background
There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database.This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case. Read More…

SSIS Email Task Delay Validation
Recently I was trying to send a file via Email in SSIS. For the connection to the file I was using a variable as the file name changes everyday. This was causing an issue because it validates the whole package before the file gets generated and the package would fail. Read More…

MDS “Service Unavailable” Error
After restarting a server that has MDS on, I could no longer access MDS via the browser. This seemed very strange as I have restarted the server multiple times and it had never done this before. Turns out the Application Pool had stopped for MDS. So I started it up and then it would stop again, frustrating me further. Read More…

Limiting SSIS to a Single File Type
A while ago I was having some issues with SSIS and moving files. There was a hidden file called thumbs.db When SSIS was running the move file task it would fail because it can’t place duplicate files in the same location. The thumbs.db file was the problem. Even though it was hidden as a windows file, SSIS still saw it and wanted to move it. Read More…

SSIS Error – the path for isserverexec.exe cannot be found
Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance. Got the following error when trying to run my ETL jobs or when trying to deploy the jobs: “the path for isserverexec.exe cannot be found” Read More…

PowerBI Campaign/Brand Management for Twitter
PowerBI has released a fantastic template for Twitter called Campaign/Brand Management for Twitter. After following the “wizard” you will have a complete report filled with sophisticated analytics. Read More…

Windows Update KB2919355 Not Applicable
When trying to install SQL, Windows Update KB2919355 is a prerequisite, and on a new server this is not always installed and the SQL install stops until it is rectified. Read More…

Setting Up SSIS in SQL Server Management Studio
Before you can deploy a SSIS project you need to set up SSIS in SQL Server Management Studio. When installing SQL you will select to install SSIS, however, it does not setup the SSISSDB or configure any settings for SSIS. Read More…

GenesisOne™ T-SQL Source Code Unscrambler™
Have you ever worked with code that is impossible to read and decipher? You could use SQL Prompt which will format the code into a readable format, however what if there is so much going on you are still unable to decipher what is happening? Read More…

MDS Backup Strategies
Recently a colleague and I had a discussion about which method is the best to use for backing up MDS (Master Data Services) for disaster recovery. We came to the conclusion it all depended on the SQL environment version you were recovering to. Below are the 2 different methods for backing up an MDS database. Read More…

Row Versioning-based Isolation Levels
Following on from my previous post on SQL Transaction Isolation Levels, we get to the juicier part of Row Versioning-based Isolation Levels. Read Committed Snapshot and Snapshot isolation levels allow for the use of row versioning. By using the row versioning-based isolation levels, it will improve read concurrency by eliminating locks for read operations. Read More…

SQL Transaction Isolation Levels
Isolation levels can be quite useful when retrieving data and inserting data to maintain data integrity. Isolation levels control the locking and row versioning behaviour between SQL statements issued by a connection to SQL server. It defines the degree to which one transaction must be isolated from resource or other modification made by other transactions. Read More…

Contained Database
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Basically it includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Read More…

Temp Table VS Table Variable
There have been many discussions on whether a temp table is better to use or a table variable. It depends on what you would like to do. They both have their pros, cons and limitations. Read More…

SSIS Package Validation
Ever wonder why your SSIS packages are taking forever to load? This is because SSDT is trying to validate your packages. What happens is SSIS checks the data sources in the package(s) that are open and checks to ensure that the external metadata is valid. If the metadata is not valid you will get error/warning messages. Read More…

Limit Memory Used By SQL
Ever noticed SQL using most of the Memory (RAM) on the server and all your other applications start slowing down? This could be because SQL is using the maximum amount of memory it can get a hold of. Read More…

SSRS Reporting Server Recycle Time
Many people complain about their reports taking a long time to load when they access them first thing in the day. This is due to the reporting server’s recycle time. What happens is SSRS regularly restarts the application domain after a specific time period. Upon first request to the report server after the application domain has been restarted, SSRS needs to load all the settings and this takes quite a long time. Read More…

Detecting Row Count Anomalies with Simple Linear Regression
A follow up from my previous post on Detecting ETL Duration Anomalies with Simple Linear Regression, I thought I would share how to detect Row Count Anomalies with Simple Linear Regression. This is very useful to detect when tables row counts all of a sudden grow by an abnormal amount, or are flushed unexpectedly. Read More…

Master Data Services (MDS) 2016 Prerequisites for IIS
This is just a high-level overview of the prerequisites that are needed for IIS. You need to have IIS installed on the server you are installing MDS on in order for MDS to work. The easiest way to check if you have all the prerequisites for IIS installed is to open up Master Data Services Configuration Manager. Read More…

Detecting ETL Duration Anomalies with Simple Linear Regression
Thought I would share a piece on detecting row count anomalies using Simple Linear Regression. This post will give you a brief overview of what simple linear regression is and how you can use it to your advantage with testing. In this post I will be demonstrating how to use Simple Linear Regression to detect ETL duration Anomalies. Read More…

Registered Servers in SQL Server Management Studio
Many people hear about Registered Servers in SSMS and then completely forget about it and how useful it can be if they are in a large environment where they need to connect to many servers. Registered Servers allows you to save the server details in SSMS and give it a friendly name so when you need to connect to the server you can just double click on it. Read More…

Formatting Shortcuts in SSDT for SSIS Packages
When developing SSIS packages the tasks and transformations tend to be misaligned after dragging them onto the designer from the SSIS toolbox. You can correct this by selecting Format > Auto Layout > Diagram. When you have multiple packages in your SSIS project this can become quite tedious. Read More…

Retrieving Data Types of All Columns in a Database
Thought I would share a quick post about retrieving all the data types of all columns in a database. This can be quite a handy script when you are comparing development and production databases to make sure all the data types match in each table. Read More…

Creating a “Run As” Account for Scheduled ETL Jobs
When working in large SQL production environments that have multiple jobs doing different things you might want the ETL jobs to run as another account other than SQL Server Agent. This can help with security by limiting the amount of access the account has to the databases the packages in the ETLs are connecting to. Read More…

Email Job Execution History in HTML Format
Knowing if your jobs have succeeded or failed can be quite handy. Especially when it comes to knowing whether your backups are failing or if you have ETLs that are failing. You can choose to set up email notifications on each job, although if you have multiple servers you could be receiving a lot of email throughout the day. Read More…

Scripting Multiple SQL Server Agent Jobs
Thought I would share this useful tip on how to script multiple SQL Server Agent jobs into one script in order to execute it on another server. Read More…

Management Data Warehouse (MDW)
Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s. Read More…

Management Data Warehouse (MDW) Reports
Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. Read More…

Parameter Sniffing in Microsoft SQL Server
Parameter Sniffing in SQL Server is SQL Server’s effort to reduce the CPU overhead by caching the query execution plan and using it for all similar queries instead of recompiling the query each time at execution. Read More…

Setting up FILESTREAM and FileTable in SQL Server
In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up. Read More…

FileStream and FileTable in SQL Server
FileStream and FileTable are used to store unstructured data in the database. You can store BLOBs such as Word documents, PDFs and images. Read More…

Preparing for the MCSA and MCSE Exams
I recently took the exams to get my MCSA and MCSE in BI and spent quite a few hours preparing for the exams. I thought I would share how I prepped for the exams. Read More…

Running Visual Studio as a different user
When you are working as a consultant at clients and your machine is not on their network and you need to develop in Visual Studio with the credential they provide, it can be quite handy to run your Visual Studio with the Windows credentials they provide. Read More…

SQL OUTPUT Clause
For my first post I thought I would share about the SQL OUTPUT clause which returns information from modified rows (INSERT, DELETE, UPDATE). Read More…