Blog

  • How to resolve SSIS “Row yielded no match during lookup” error.

    Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you! A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results. So why was SSIS…

    » Read more
  • Azure Backup for Virtual Machines

    Configuring Backups Backups are configured for each VM individually, selecting their own retention policies and routines. They can however utilise the same storage and vaults. 1. Select the Backup option under “Operations” in the sidebar of the VM management page, on https://portal.azure.com/ 2. Give your backup Vault a name, (where the backups will be stored), and select the resource group you’d like it to be in. 3.…

    » Read more
  • Pandas; Why Use It And How To Do So!

    Introduction Hi and welcome to what will be my first frog blog! I’m Lewis Prince, a new addition to the Purple Frog team who has come on board as a Machine Learning Developer. My skill set resides mainly in Data Science and Statistics, and using Python and R to apply these. Therefore my blogs will be primarily on hints and tips on performing Data Science and Statistics through the medium of Python (and possibly R). I thought I would start…

    » Read more
  • Making the DAX Engine Work for You

    In my last blog post which can be found here, I demonstrated that it’s important to limit your DAX filters to specific columns rather than entire tables, now we know that we can and should do it, let’s explore why and how it works. There are numerous ways to write an expression that will give the same result set, but why are some ways more efficient than others? To answer that we need to know a little about what happens under the hood,…

    » Read more
  • Azure Storage Backup Retention

    This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you…

    » Read more
  • How to create a toggle button in Power BI

    In this blog post I will be showing you how to use a toggle button in Power BI. A toggle button in Power BI allows users to quickly switch between two options. Our first step is to create a toggle button as Power BI doesn’t have one by default. You can import one but by creating it yourself, it gives you more control. Our toggle button is created by having two images of identical size over the top of each other and using bookmarks to flip…

    » Read more
  • Change the Compatibility Level of a Power BI report

    A number of new features in Power BI are limited to newer Power BI datasets, which Microsoft track through the use of Compatibility Levels. 1500+ for SQL Server 2019 compatibility 1400+ for SQL Server 2017 compatibility 1200+ for SQL Server 2016 compatibility If you want to, for example, connect to the xmla endpoint of the dataset from SQL Server Management Studio, then you need level 1500 or greater. If it’s lower you’ll get the…

    » Read more
  • Automate changing SSIS connection at runtime

    Recently a customer came to us with an issue: “We have a primary/secondary AlwaysOn failover cluster, we want the data warehouse ETL to always pull from the secondary read-only replica. When the primary fails over, how can we detect this and repoint the ETL to the new secondary?” This led us to the following qualification steps:1. Is the secondary server accessible? a. Yes – Use it. b. No – Try the primary server.2. Is…

    » Read more
  • Replicating SQL windowing functions in Power Query.

    I’m sure you’ve all heard of windowing functions in SQL Server? But what if you need to replicate these windowing functions in Power Query? Here I have a sample CarSales.csv dataset of car sales per brand, per year, per month and the number of units sold. I will use this dataset to demonstrate how we can replicate the windowing LAG function in Power Query. In my example I’d like to calculate the month on month sales growth per car…

    » Read more
  • Increasing DAX Filter Speed and Efficiency

    To understand how a filter can affect performance, first we need to understand what a filter is. A filter is a temporary table that is created on demand, held in memory, and then used as a reference for the function being filtered. The rows of the temporary filter table are all DISTINCT. This is because for the purposes of filtering, the engine doesn’t care whether there are 1, 2, 3 or 250 occurrences of a value in table, only whether it…

    » Read more