Blog

  • How do I make a multi-line string in Python (so I can ctrl+C my SQL code)

    This should be a quick one, but it is something that took me a little while to work out, but now I know I use it pretty much every day in my projects to save me a lot of time. This is the ability to copy and paste my SQL code into my python code as a multi-line string without having to reduce it to be all on one line or using escape characters. Both of which can take a bit of time depending on how big your SQL is. You usually need to have SQL…

    » 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
  • AI – Will robot overlords take over the world

    Everyone assumes that AI is the Hollywood version with robot overlords taking over the world. In reality, AI is already here and we are using it in society. Phone apps such as Uber using an AI to figure out the closest car to your destination, Netflix suggesting films for you to watch based on your history, Amazon prompting ‘customers also bought’ options on checkout, digital assistants like Alexa and Siri all of which are helping to…

    » Read more
  • Synapse Copy Activity Fails Over Certain File Size – ADF

    Copy Activity Issue in ADF / Synapse Analytics Recently, when trying to copy a .csv file from an FTP source to a Azure Data Lake using a Copy Activity in Azure Synapse, I had an issue where files > 16MB in size would fail. To overcome this, I took the first 13k rows and created another file from this, which resulted in a 4MB file. I tested this extra small file and it worked in the copy activity no issues. I multiplied these same 13k rows out…

    » Read more
  • What are Meta Threads? Should I be using them?

    In the ever-evolving landscape of social media, there have been some notable shifts and surprises (specifically in the last 48 hours). We’re about to dive into the world of Threads, where threads aren’t just for sewing anymore…   One of the most intriguing developments has been the transformation of Twitter since Elon Musk took the helm. With his ‘unconventional’ approach, the platform has experienced quite a rollercoaster…

    » 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
  • Power BI – Recalculate / Refresh a Calculation Group

    In Power BI, when working with calculation groups, they will often need to be recalculated when certain changes are made. You will end up with this error message if you use a calculation group that’s been changed in your Power Bi Report: The query referenced Calculation Group Table ‘Calculation Group’ which does not hold any data because it needs to be recalculated or refreshed.   Thankfully, the solution to this error is quite simple…

    » Read more
  • Exporting to a CSV file from SSMS – Simple BCP Method

    Export table contents to a CSV file from SQL Server  It may be useful to export a tables contents to a CSV file for some exploratory analysis, to feed an ETL and much more. This can be done with some simple SQL commands that exist as a stored procedure, SQL agent job or just a one off piece of code. The export can be the whole table or filtered results but one thing to keep in mind is the number of rows and result size as it may become…

    » Read more
  • @@ROWCOUNT in Synapse Dedicated SQL Pool

    If you’ve been working with dedicated SQL pools in Synapse, you may have noticed that the SQL Server system variable @@RowCount is not supported. I will show you a workaround in synapse to get the same information. First, use the query label option to give your query an identifier that you can use to track it: SELECT * FROM Dim.Date OPTION (LABEL = ‘MyQuery’) You can then use DMVs to find your query using the specified label…

    » Read more
  • What is Microsoft Fabric? (Power BI + Synapse + DW + DataLake + ML)

    What Is Microsoft Fabric

    At today’s Build conference, Microsoft announced Fabric. What is this? In simple terms, think of taking Synapse Analytics, Data Warehousing, Data Lakes, Data Factory, Spark Notebooks and Machine Learning, and bring them all together into Power BI. This is underpinned by Microsoft OneLake, a high performance scalable data lake storage layer, supporting all of the above. OneLake is, as the name implies, one data lake that can be used across…

    » Read more