Blog
- 17-Jul-2023 β Create Power BI deployment pipelines automaticallyIn 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!
- 21-Jun-2023 β Can you just...?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.
- 06-Jun-2023 β Working with multiple Power BI dataset environmentsIn 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.
- 23-May-2023 β Reusable deployment pipelines for Power BIAzure 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.
- 09-May-2023 β Publish automatically to Power BI environments with Azure DevOps pipelinesThis post shows you how to use Azure DevOps pipelines to automate publishing of Power BI reports to different environment workspaces.
- 26-Apr-2023 β Better version control for Power BI datasetsVersion 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.
- 12-Apr-2023 β Pro DevEx for Power BIAfter 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.
- 11-Oct-2022 β Metadata-driven SQL code generationOctober 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.
- 12-Jul-2022 β Argument {0} is null or empty. Parameter name: paraKeyI 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!
- 09-Mar-2022 β SQL database project (SSDT) merge conflictsMore merge conflict fun! This time, using pre-commit hooks to duck conflicts in SSDT .sqlproj files.
- 01-Feb-2022 β Merge conflicts in tabular modelsI 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.
- 18-Jan-2022 β Ordered STRING_SPLITI'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.
- 11-Jan-2022 β Breaking the rulesJanuary 2022's T-SQL Tuesday is hosted by Andy Yun -- thanks Andy! This month, Andy's asking about learning that changes your opinion.
- 14-Sep-2021 β Infrastructure as nearly-all-codeT-SQL Tuesday #142 (September 2021) is hosted by Frank Geisler. Frank's choice of subject is "using descriptive techniques to build database environments".
- 23-Feb-2021 β More Get Metadata in ADFLast 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!
- 10-Nov-2020 β Azure Data Factory, the ADF UX and GitIf 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.
- 28-Oct-2020 β Catch-22: Automating MSI access to an Azure SQL DatabaseA 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!
- 13-Oct-2020 β The Ice Cream Van of AbstractionOctober 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.
- 07-Oct-2020 β Google Analytics API pagination in Azure Data FactoryIn 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.
- 29-Sep-2020 β Get Metadata recursively in Azure Data FactoryAzure 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.
- 10-Sep-2020 β Extract data from Google Analytics with Azure Data FactoryIn 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.
- 08-Sep-2020 β Automate to replicateIn 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 π.
- 28-Aug-2020 β Access Google Analytics with Azure Data FactoryAt 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.
- 13-Aug-2020 β Parameterising the Execute Pipeline activityA 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.
- 11-Aug-2020 β Time capsuleT-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!
- 24-Jul-2020 β Why automate ADF pipeline testing?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!
- 14-Jul-2020 β Default faultJuly 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.
- 09-Jul-2020 β Dropping temporary tablesLocal 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!
- 30-Jun-2020 β Print bigA 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.
- 25-Jun-2020 β From Azure Pipelines to GitHub ActionsI 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.
- 17-Jun-2020 β Calculating Azure Data Factory test coverageThis 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.
- 09-Jun-2020 β The under-appreciated Start β RunThis 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...
- 03-Jun-2020 β Unit testing Azure Data Factory pipelinesIn 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.
- 21-May-2020 β Testing Azure Data Factory in your CI/CD pipelineIn 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.
- 12-May-2020 β A tale of two smartphone appsThis 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...
- 06-May-2020 β Isolated functional tests for Azure Data FactoryIn 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.
- 26-Apr-2020 β Automate integration tests in Azure Data FactoryIn 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.
- 20-Apr-2020 β Set up automated testing for Azure Data FactoryTest 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.
- 26-Jan-2020 β Errors in script tasks and componentsUnhandled 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.
- 02-Jan-2020 β Managing extended propertiesExtended 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.