• How to call Microsoft Fabric Data Pipelines dynamically using APIs

    The Microsoft Fabric REST APIs allow you to automate Fabric procedures and processes. In this blog post, we’ll focus on a specific API: “Run On Demand Item Job” To dynamically call another data pipeline, we’ll utilise this API within a data pipeline web activity. By passing in the itemId of the data pipeline we want to invoke, we can trigger its execution. You can learn more here:…

    » Read more
  • Automating DAX Measure Formatting in Power BI with Tabular Editor

    Until coming across this piece of code, I often used the DAX formatter (https://www.daxformatter.com/) to format all of my Tabular Model measures. However, if you end up writing a lot of measures in your Tabular Model this can become quite tedious. There is a faster method however. Using Tabular Editor 2 and some basic C# code you can format all of your Tabular model measures at once. Assuming you have Tabular Editor 2 installed on your machine,…

    » Read more
  • Automate Power BI single table refresh using Azure Synapse Analytics and Power BI APIs

    In a blog I posted back in June 2022, I explained how you could trigger a Power BI dataset refresh via Azure Synapse Analytics. https://www.purplefrogsystems.com/2022/06/how-to-trigger-a-power-bi-dataset-refresh-via-azure-synapse-analytics-in-3-simple-steps/ However, what if you just wanted to refresh a select number of tables or objects rather than a full dataset? This might be the case if you are performing intraday loads in your ETL. There…

    » Read more
  • Microsoft Fabric – So how do I build a Data Warehouse without Identity Columns and Merge?

    At the time of writing (29th June 2023) the following T-SQL commands are not supported in a Fabric Data Warehouse. Identity Columns Merge To see the full list of T-SQL limitations please refer to the documentation link below: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area Here at Purple Frog, all of our Data Warehouses are knitted together using surrogate keys to link Dimension tables to Fact tables following the…

    » Read more
  • Excel, CSV and Leading Zeros

    Yes, even at Purple Frog Systems we use Excel! Excel is great for that quick and dirty analysis, but there is nothing worse than opening a CSV file directly with Excel and finding out you’ve lost all of your leading zeros. Take a look at my Sales.csv file Viewed as a CSV in Notepad++ it looks as it should… However viewed directly using Excel… It now looks like I’ve got a sales quantity of 62 for product id 5! To solve…

    » Read more
  • Azure Data Factory Pricing – How much is my pipeline actually costing me?

    Has a client ever asked you how much it actually costs to run a single pipeline in Azure Data Factory? Have you ever thought ADF pricing is just a black box? Well, hopefully my latest blog post will give you an indication on how you can start calculating the cost of a pipeline run! I will base my analysis on a sample pipeline containing the following activities as shown below: 1 x Lookup Activity (Pipeline Activity) 1 x Copy Data Activity (Data…

    » Read more
  • Previous Row Value DAX

    In this quick blog post I’m going to show you how to use a hidden DAX function to get a previous row value with just a few lines of code. It’s that hidden that intellisense doesn’t even recognise it! I’ll use an example of some fictional race results from some well-known cartoon characters! Lets assume that in this scenario we wanted to analyse the time difference between 1st and 2nd place, 2nd and 3rd place etc in the race scenario…

    » 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
  • 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
  • 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