Blog

  • Query Store Forced Plan Failures

    Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server. Here we can see an…

    » Read more
  • Part 1: Web Scraping and Natural Language Processing- Web Scraping

    In this multi blog series I will go through what web scraping is, what Natural Language processing is as a general term as well as diving into some constituent techniques we are interested in; key word extraction, sentiment analysis and its derivative opinion mining. The last few parts will then go through a coded example of scraping the popular review site Trust pilot for reviews of the popular supermarket chain ‘Lidl’. We will then…

    » Read more
  • ADF breaking out of a ForEach activity

    Currently, the ForEach activity in ADF (or Synapse) does not break out when an inner activity fails. There are several ways you can force the ForEach to break out, the most common is to cancel the pipeline run when an inner activity fails and there are already many blogs out there that cover this. My requirement is slightly different and that is what I will show here. Consider a scenario where you have a ForEach activity which has multiple inner…

    » Read more
  • Upload Power BI Reports using PowerShell

    In this blog post, I will be sharing a PowerShell script that allows multiple Power BI reports to be uploaded at once. In a previous blogpost, I shared a PowerShell script that allowed users to downloaded multiple Power BI reports. Combined you could move several reports from one workspace to another in a few seconds. The script is downloadable at the bottom of the page as a txt file. To use the script there are three steps to take. The first…

    » Read more
  • How to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.

    In this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps: Step 1: Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory. Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal. Step 2: Login to powerbi.com and in the top right hand corner locate “Settings” and…

    » Read more
  • Deploy Azure AutoML Model And Consume In Excel

    It has come back to my turn to write a blog post, and if you remember my previous one concerned why you should use Azure based AutoMl and subsequently how to do so. If you followed that then you will be left with a model of which you’ve scored and know the performance of, but no way of how to then deploy and use your model. I will outline the steps needed to do this (which involves a major shortcut as we are using an AutoMl model), and…

    » Read more
  • The SQL Query Alias Conundrum – Order of Execution

    So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘. The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it? If you’ve ever written a SQL query, this should look familiar: SELECT DISTINCT FROM JOIN ON…

    » Read more
  • ADF Dataflow CTE workaround

    At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to deal with this limitation: re-write the query using subqueries instead of CTEs use a stored procedure that contains the query and reference the stored proc in the source of the dataflow write the query as a view and reference the view in the source of the dataflow (this is my preferred method and the one I will…

    » Read more
  • Download Power BI Reports using PowerShell

    In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once. In the Power BI service, there is no way of downloading multiple Power BI reports at once. Therefore, users must download files one by one which is slow, time consuming and inefficient. Thankfully, there are ways around this, one of which is using PowerShell. The script is downloadable at the bottom of the page as a txt file.…

    » Read more
  • How to parameterise the Execute Pipeline activity in Azure Synapse Analytics.

    Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run. However, with the use of a Microsoft API there is method which we can use to overcome this. In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below: https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run…

    » Read more