Blog

  • Creating a Python function to calculate Pi

    Pi is 3.14159 to 5 decimal places.To work out Pi, we will be using Leibniz’s formula:X = 4 – 4/3 + 4/5 – 4/7 + 4/9 – …This series converges to Pi, the more terms that are added to the series, the closer the value is to Pi.For the proof on why this series converges to Pi – https://proofwiki.org/wiki/Leibniz%27s_Formula_for_PiThere are several points to note about the series: It’s infinite, we need to find a way to continue…

    » Read more
  • SQL LAST_VALUE() wrong results

    You may have come across the following scenario when using the LAST_VALUE() function. You want to find the first or last value loaded in a result set, therefore you use the FIRST_VALUE() and LAST_VALUE() functions like below: You expect to get “A” for every record in the FirstValue column and “E” for every record in the LastValue column.However you see the following output: LAST_VALUE() on its own is implemented from the…

    » Read more
  • System OutOfMemoryException when deploying SSIS .ispac

    If you’ve tried deploying a .ispac using the Integration Services Deployment Wizard, you may have come across the following error: By default SQL Server uses the 32-bit version of the Deployment Wizard, which is why you’ll be getting the Out of Memory error. If you instead launch the Wizard from the 64-bit location, (default is C:\Program Files\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe), then you should be able to…

    » Read more
  • Creating Power BI Layouts using PowerPoint

    First question, why bother with layouts?Using layouts in Power BI allows a user to make their visuals stand out better, the page looks professional and more appealing to its audience. Second question, why PowerPoint?The default page size in Power BI desktop is 16:9, (this trick doesn’t work for other Power BI page sizes), which is identical to a PowerPoint slide.Therefore whatever is designed in PowerPoint will fit onto a Power BI page…

    » Read more
  • Capturing Insert and Update Counts from Merge

    This post shows hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement. This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse. You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns. The basic syntax is: INSERT INTO XXXX SELECT [Action] FROM ( MERGE XXXX AS Target USING XXXX…

    » Read more
  • Power BI Sentinel

    We all know and love Power BI, because of it’s power and ease of use, there’s a good reason why it’s number 1 in the Gartner Quadrant. But how to we avoid ‘Power BI Hell’, the new version of our old nemesis ‘Excel Hell’? How do we keep track of which data sources are being used by which datasets and reports? How to we perform an impact assessment of updating/changing a dataset? What happens if someone…

    » Read more
  • SSAS Tabular Deployment Wizard fails Newtonsoft.Json error

    The Scenario Deploying an Analysis Services Tabular model to SSAS Azure using the Analysis Services Deployment Wizard. Both Visual Studio 2017 & SQL Server 2017 installed on the client. Try and click on the ellipses to change the data source connection string or impersonation information results in a Newtonsoft.json error: “Could not load file or assembly ‘Newtonsoft.Json, Version 6.0.0.0, Culture=neutral, ……”…

    » Read more
  • Lookups and If Conditions in Azure Data Factory v2 (ADFv2)

    Azure Data Factory v2 (ADFv2) has some significant improvements over v1, and we now consider ADF as a viable platform for most of our cloud based projects. But things aren’t always as straightforward as they could be. I’m sure this will improve over time, but don’t let that stop you from getting started now. This post provides a walk through of using the ‘Lookup’ and ‘If Condition’ activities to do some…

    » Read more
  • Azure SSIS – How to Setup, Deploy, Execute & Schedule Packages

    Welcome back to work in 2018! 🙂 Let’s get stuck in with a hot topic. How do we actually use our beloved SQL Server Integration Services (SSIS) packages in Azure with all this new platform as a service (PaaS) stuff? Well, in this post I’m going to go through it end to end. Post Contents Azure Services Setup Azure Data Factory v2 Azure SQL Instance Azure SSIS IR Creating & Deploying the SSIS Package SSIS Azure Feature Pack…

    » Read more
  • Business Intelligence in Azure – SQLBits 2018 Precon

    What can you expect from my SQLBits pre conference training day in February 2018 at the London Olympia? Well my friends, in short, we are going to take whirlwind tour of the entire business intelligence stack of services in Azure. No stone will be left unturned. No service will be left without scalability. We’ll cover them all and we certainly aren’t going to check with the Azure bill payer before turning up the compute on our data…

    » Read more