I’ve been writing a series on Power BI Implementation Models for Enterprises for Tekki Gurus.
Part 3 that covers what I consider Cloud Friendly Clients is now published:
Note: Sorry but Tekki Gurus have closed down.
Enjoy !
2024-03-08
And another new SQL Down Under show is published!
Once again, I had the great pleasure yesterday to record a podcast with one of my UK based friends Bob Duffy.
Bob is a Principal Data Architect at Prodata’s SQL Centre of Excellence in Dublin, Ireland. He helps enterprise customers build large scale enterprise data warehouses in Azure, typically integrating ERP and financial systems.
Bob is a long-term Data Platform MVP, a Microsoft Certified Architect, Master, and Analysis Services Maestro. He holds a number of Azure role-based certifications. Bob also specialises in performance tuning, architecture, load testing, DevOps, Software engineering, MQ architecture, semantic modelling and meta data driven design on the MS platform.
2024-03-07
This is part 4 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.
When I was reading the documentation for CDC in Azure SQL Database, I kept coming across a mention that it required at least an S3 service level objective (SLO), if you were using a DTU-based database.
I really hoped that wasn’t the case.
2023-09-23
It’s scary how much of the world’s data lives in CSVs. Yet, as a standard, it’s problematic. So many implementations just don’t work as expected. Today, I had a client asking about why Azure Data Factory wouldn’t read a CSV that, while odd, was in a valid format.
The simplest equivalent of the file that wouldn’t load, would be one like this:
First Column,Second Column,Third Column,Fourth Column
12,Terry Johnson,Paul Johnson,031-23423
13,Mary Johnson,"Paul,Johnson",031-23423
14,Mia Johnson,"Paul ""the beast"", Johnson",031-23423
16,Cherry Johnson,Paul Johnson,031-23423
There are meant to be four columns. The source system wrapped strings in quotes only when the string contained a comma, as that was the delimiter for the file.
2023-09-12
I had the great pleasure to get to spend time today, talking with Mark Pryce-Maker. We were discussing the SQL warehousing experience in Microsoft Fabric.
Mark is a Senior Program Manager at Microsoft, where he’s working closely with the Microsoft Fabric team. He has a strong background in many areas of data, most recently prior to Fabric, with helping customers migrate to Azure Synapse Analytics. He has worked extensively with the dedicated SQL pools in that service.
2023-09-07
This is part 3 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.
To show how this works, I have created an Azure SQL Database called CDCTest. I created it as a DTU-based database with a service level objective (SLO) of S3. I’ll discuss more about the licensing implications of CDC in part 4.
2023-06-02
This one had me stumped lately.
I had an variable in ADF where the value contained carriage returns. I needed to remove them.
I guessed that would be easy:
@replace(variables(‘fileContent’), ‘\r’, ‘’)
But no matter what variation of this I tried, it wouldn’t remove them.
Thanks to the genius of fellow MVP Simon Sabin, the answer was:
@replace(variables(‘fileContent’),base64ToString(‘DQ==’),’’)
Note: if you need a line feed (LF) it’s base64ToString(‘Cg==’) and a carriage return / line feed pair (CRLF), is base64ToString(‘DQo=’).
2023-04-24
I was working with a client who has having trouble debugging an ADF pipeline, related to an ODBC linked service not working as expected.
The user had configured the connection string property of an ODBC connection this way:
The Test Connection for that worked fine, but when you click Apply, it fails with an Internal Server Error. Specifically, the error was:
2023-04-24
I was able to record another new Cosmos Down Under podcast today. My guest was Microsoft Senior Program Manager Rodrigo Souza.
In the show, we discussed the Change Data Capture feed for the Analytical store in Azure Cosmos DB. This is a powerful new capability and worth learning about.
I hope you enjoy it. You’ll find it, along with all Cosmos Down Under podcasts at:
2023-04-12
In the part 1 of this series, I discussed the positioning of Change Data Capture. In part 2, I want to cover how it works.
There are many ways that you can output details of changes that occur in data within SQL Server. Many of those methods require actions to occur at the time the data change is made. This can be problematic.
The first problem with this, is the performance impact on the application that’s making the change. If I update a row in a table and there is part of the process that writes details of that change to some type of audit or tracking log, I’ve now increased the work that needs to happen in the context of the application that’s making the change. Generally what this means, is that I’ve slowed the application down by at least doubling the work that needs to be performed. That might not be well-received.
2023-03-06