Press "Enter" to skip to content

Curated SQL Posts

Updating Views with INSTEAD OF Triggers

Published 2022-04-26 by Kevin Feasel

Chad Callihan performs sleight of hand:

I ran into a strange issue recently and thought it would make for a great blog post. I was notified of a standard application process failing with the following SQL error:

Msg 4405, Level 16, State 1, Line 19
View or function ‘XXXXX’ is not updatable because the modification affects multiple base tables.

I recognized the error but was baffled as to why it was showing up in this particular area. I’d updated or modified this view plenty of times in the past so what’s going on?

Read on for the answer.

Comments closed

Azure Databricks Security Considerations

Published 2022-04-26 by Kevin Feasel

Craig Porteous provides some advice on configuring Azure Databricks:

Azure Databricks is an analytics platform and often serves as the central compute component of a data platform, to process ETL/ELT data pipelines and data science workloads. As Databricks is a third-party platform-as-a-service offering securing it works differently to most other first-party services in Azure; for example, we can’t use private endpoints. (More on these in the Azure Storage post)

The two main approaches to working with Databricks in our secure platform are VNet Peering or VNet Injection

Click through to learn the difference between these two, as well as a few other factors to keep in mind as you’re deploying Databricks.

Comments closed

Viridis Color Palettes in Power BI

Published 2022-04-26 by Kevin Feasel

Meagan Longoria shares a few themes:

I am a fan of the viridis color palettes available in python and R, so I decided to make Power BI theme files for each of the 4 color maps (viridis, inferno, magma, plasma). These color palettes are not only lovely to look at, they are colorblind/CVD friendly and perceptually uniform (or close to it).

The screenshots below show the colors you’ll get when you use my theme files.

Click through to get the theme files and some additional advice from Meagan in the GitHub repo itself.

Comments closed

Diagnostics ID and ActivityID in Power Query

Published 2022-04-26 by Kevin Feasel

Chris Webb looks into activity IDs:

I was looking at the output of Power Query’s Query Diagnostics feature recently (again) and trying to understand it better. One of the more confusing aspects of it is the way that the Power Query engine may evaluate a query more than once during a single refresh. This is documented in the note halfway down this page, which says:

Jorge’s comment on the post adds even more context around what the ID Chris comes up with actually means.

Comments closed

Synapse Database Templates GA

Published 2022-04-26 by Kevin Feasel

Kevin Schofield makes an announcement:

We’re pleased to announce today that Synapse Database Templates are now Generally Available and that we are also making available three additional Synapse Database Templates for Healthcare Insurance, Healthcare Providers, and R&D and Clinical Trials.

The Healthcare Insurance template is a comprehensive data model that addresses the typical data requirements of organizations providing insurance to cover healthcare needs (sometimes known as Payors).

The Healthcare Providers template is a comprehensive data model that addresses the typical data requirements of organizations providing healthcare services.

The R&D and Clinical Trials template is a comprehensive data model that addresses the typical data requirements of organizations involved in research and development and clinical trials of pharmaceutical products and devices.

Read on to learn more about how these templates work and what you can do with them.

Comments closed

Finding the Last Refresh Time on Power BI Partitions

Published 2022-04-26 by Kevin Feasel

Dennes Torres has written a tool:

On the article Automating table refresh in Power BI I explained many methods to automate refresh of individual objects, which could be tables or partitions.

This creates the need of good ways to visualize the last refresh date and time for each partition and table. The portal shows the refresh date/time for the entire dataset, we can’t identify on the Power BI portal the exactly date for each table last refresh.

Read on for a link to the refresh code and an example of it in action.

Comments closed

Debugging a Production Failure

Published 2022-04-25 by Kevin Feasel

Roel Hogervorst diagnoses trouble:

When you are in panic mode you focus on what is right in front of you and make suboptimal decisions. Here is some I have made.

Read on for a couple stories as well as a practical implementation of debugging as an OODA loop. Something that Sean McCown mentioned before has always stuck with me: it’s amazing just how few people know how to troubleshoot issues. Our inclination seems to be one of two things: adduce a conclusion from the first piece of evidence (usually just a flimsy error message) or immediately give up.

Comments closed

PyODBC vs C# ODBC Performance Differences

Published 2022-04-25 by Kevin Feasel

Jose Manuel Jurado Diaz explains a performance difference:

A customer asked today, why using ODBC Driver 17 for SQL Server in Python with PYODBC we have a slightly difference in terms of time taken if we compare with C# System.Data.Odbc. Following, I would like to share my lesson learned about it.

Read on for Jose’s explanation. My short version is, it seems particularly important when using the Python ODBC driver to write the exact query you want rather than a SELECT * or query which returns rows/columns you don’t need.

Comments closed

Microsoft.Build.Sql for Database Projects

Published 2022-04-25 by Kevin Feasel

Drew Skwiers-Koballa announces a new way of handling database projects:

Declarative development creates an environment where developers can focus on creating database objects while relying on the support of tooling locally and and in deployment pipelines to manage applying the differential changes calculated on the current state of the target database. Developers create objects such as tables or stored procedures by writing their definition with CREATE statements in scripts that live in source control just as if it is source code for any component of an application. Existing functionality for SQL projects in Visual Studio, Azure Data Studio, and VS Code provides developers with declarative development capabilities, however the existing SQL project file format has a few limitations.  With Microsoft.Build.Sql and SDK-style SQL projects, we look forward to unlocking new scenarios for your development practices.

It does sound interesting.

Comments closed