Blog

In this post, I accelerate creation of new reports by building their deployment pipelines automatically. Create your report, push it to Git, see it appear in Power BI!

Communicating technical complexity to non-technical colleagues can be tough -- but it's essential if you want to explain why you're going to need 6 months to build a report 😂. This post tries to help with a one-slide explainer.

In a recent article I talked about deploying Power BI reports through any number of environments (not just three, and automatically!). In this new post I look at how to do exactly the same thing for standalone/shared PBI datasets – and how to bind thin reports to different source datasets automatically during deployment.

Azure DevOps pipeline templates allow you define common functionality once, then reuse it in many pipelines. A great use case for this is publishing #PowerBI reports automatically -- templated deployment makes it easy to create a pipeline per report, giving you low overhead deployment with fine-grained control.

This post shows you how to use Azure DevOps pipelines to automate publishing of Power BI reports to different environment workspaces.

Version controlling a Power BI PBIX file along with all its data can be problematic, for reasons of both size and privacy. In this post I look at managing Power BI datasets separately, without data, and deploying them using Azure DevOps pipelines.

After deploying an obsolete version of a buggy #PowerBI report to the wrong workspace, I realised it wasn't the report I meant anyway 😒. Time to invite #DevOps to this party.

October 2022's T-SQL Tuesday is hosted by Steve Jones – thanks Steve 😊. Steve's invitation this month is to write about using dynamic SQL – that is, T-SQL statements built up from text strings and then executed as code.

I encountered this error while using Azure Data Factory's Script activity -- with a bit of luck, writing it down will help me remember how to fix it next time!

More merge conflict fun! This time, using pre-commit hooks to duck conflicts in SSDT .sqlproj files.

I sometimes find working with Visual Studio's projects a challenge in multi-developer environments, because each project type seems to have its own vulnerability to Git merge conflicts. This post looks at how to avoid them when working with tabular models for Power BI Premium, AAS or SSAS.

I'm a bit late to this party, but some long-awaited news is finally here – STRING_SPLIT now returns the ordinal position of string elements in various Azure SQL offerings.

January 2022's T-SQL Tuesday is hosted by Andy Yun -- thanks Andy! This month, Andy's asking about learning that changes your opinion.

T-SQL Tuesday #142 (September 2021) is hosted by Frank Geisler. Frank's choice of subject is "using descriptive techniques to build database environments".

Last year I wrote a post about doing this in pure ADF, with really terrible performance. By way of apology, I've had another go in an Azure Function!

If you're using Azure Data Factory (ADF), you're probably using Git and almost certainly using the ADF User Experience (ADF UX) – ADF's online integrated development environment (IDE). These three components are so closely interlinked that sometimes it's hard to think about them separately – in this article I try to do exactly that.

A problem I ran into recently is how to automate granting access to SQL databases in different environments. I'm using Terraform to build parallel data engineering environments which – amongst other things – include one or more SQL databases and instances of Azure Data Factory (ADF). ADF pipelines need access to databases, and I want to authenticate linked service connections using the factory's managed identity (MSI). Building this automatically is harder than it sounds!

October 2020's T-SQL Tuesday is hosted by Rob Volk (b|t) with the subject Data Analogies, or: Explain Databases Like I’m Five! Thanks for hosting, Rob! It turns out that I don't do much specifically databasplaining™ by analogy, but there's one analogy I go back to again and again when talking to people unfamiliar with a useful concept, both inside and outside of SQL Server: abstraction.

In a previous post I created a pipeline to retrieve data from the Google Analytics reporting API, using an OAuth 2.0 access token for authorisation. Azure Data Factory's Copy data activity handles various styles of paged API response, but it doesn't support the approach taken by the Google Analytics reporting API. In this post I look at how to make that work.

Azure Data Factory's Get Metadata activity returns metadata properties for a specified dataset, but not recursively. In this post I try to build an alternative using just ADF.

In a previous post I prepared an ADF pipeline to make authorised Google Analytics API requests, using an Azure Function to obtain an OAuth 2.0 access token. In this article I'm going to use the returned token to authorise an API connection and extract data from Google Analytics.

In September 2020's T-SQL Tuesday, Elizabeth Noble (b|t) wants to know what members of the SQL community have automated to make their lives easier. Thanks for asking, Elizabeth 😀.

At the time of writing, Azure Data Factory has no connector to enable data extraction from Google Analytics, but it seems to be a common requirement – here's how to do it using ADF's current feature set.

A shortcoming of Azure Data Factory's Execute Pipeline activity is that the pipeline to be triggered must be hard-coded into the activity – so it's impossible to use metadata-driven approaches like iterating over a list of pipeline names. This post looks at an alternative approach.

T-SQL Tuesday for August 2020 is hosted by Tamera Clark (b|t). She's asking for help in assembling a #SQLCommunity time capsule -- thanks for hosting, Tamera!

Since writing my series on automated testing Azure Data Factory pipelines, I've had a few questions along the lines of "why bother?". One reader commented that "a typical ADF developer tests their pipeline doing debug runs". This is exactly how I develop pipelines: make a change, run the change, repeat until the pipeline does what I want. "Why bother with more testing?" is a good question!

July 2020's T-SQL Tuesday is hosted by Kerry Tyler (b|t). Kerry describes pilots' use of plane crash reports to learn aviation safety lessons, and asks for similar – albeit hopefully lass catastrophic – tales of SQL Server-related disaster.

Local temporary tables – tables with names that begin with a single # character – are dropped automatically by SQL Server when they are no longer in scope. So why drop them explicitly at all? Here are some ideas!

A feature of T-SQL is that strings longer than 8000 bytes are truncated by PRINT. If you haven't already discovered this, you might wonder why it's a problem – the answer (for me at least) is dynamic SQL.

I use Azure Pipelines a lot, both for CI/CD (including automated testing for Azure Data Factory) and for building disposable Azure environments repeatably. Most of my code, however, lives in GitHub. GitHub provides its own service for automating CI/CD software workflows – GitHub Actions – and in this post I compare it to Azure Pipelines.

This is the sixth and final article in a series on automated testing for Azure Data Factory pipelines. In software engineering, code coverage (or test coverage) measures the proportion of a program's source code executed by a given test suite. In this article I use execution history data to measure the proportion of a data factory's activities (across all pipelines) executed during a full test run – the test suite's activity coverage.

This month's T-SQL Tuesday, hosted by Kenneth Fisher (b|t), asks for specifically non-SQL related tips and tricks. I found that much harder than any number of technical problems, which I guess is the point of T-SQL Tuesday...

In part three of this series I looked at functional tests for ADF pipelines: verifying, in isolation, that pipelines are “doing things right”. In this post I'll be testing isolated pipelines to check that they're “doing the right things” – this is one description of a unit test.

In my previous post I used ADF pipeline parameters to implement dependency injection for ADF pipelines and build isolated functional tests using the NUnit testing framework. In this article, I integrate the NUnit testing solution into an Azure DevOps pipeline, so that I can run tests automatically whenever changes are made to ADF resources.

This month's T-SQL Tuesday is hosted by Glenn Berry (b|t). Glenn's invitation was “to write about what you have been doing as a response to COVID-19”, and it feels like my answer should be this: not much...

In this article, I'll look at isolating a pipeline from its external dependencies in order to test it independently and with a range of testing scenarios. I'll be trying to establish that a pipeline is “doing things right” in isolation – this is a functional test.

In my previous post, I set up and ran one basic test of a single pipeline. In this article, I refactor my VS testing solution to make it easier to add new tests and to test new pipelines.

Test automation allows you to run more tests, in less time, with guaranteed repeatability. If you change an existing ADF dataset definition for a new ADF pipeline, how do you know haven't broken something else? Automatically re-testing all your ADF pipelines before deployment gives you some protection against regression faults. Automated testing is a key component of CI/CD software development approaches: inclusion of automated tests in CI/CD deployment pipelines for Azure Data Factory can significantly improve quality.

Unhandled errors thrown out of SSIS script tasks are usually accompanied by the message “Exception has been thrown by the target of an invocation” – this isn't very informative! A more manageable approach is to wrap as much of your code as possible inside a try/catch, then raise caught errors to SSIS for cleaner failure and easier diagnosis. This article shows you how to do that.

Extended properties are a means of attaching user-defined key-value pairs to database objects. This page provides some utility scripts to facilitate interaction with extended properties.