Press "Enter" to skip to content

Curated SQL Posts

Relative-Time Slicers in Power BI

Published 2021-09-07 by Kevin Feasel

Gerhard Brueckl wants your Power BI reports to know what day it is:

A very common requirement for a Power BI report that I stumble across at almost all of my customers is to automatically show data for the current day/month/year when a report is opened. At first sight this seems like a very trivial problem but once you dig into the problem, you will realize that all of the common solutions out there have some disadvantages and only solve the problem partially.

Click through for a clear description of the problem and a clear description of the answer.

Comments closed

An Overview of Function-as-a-Service

Published 2021-09-07 by Kevin Feasel

Grace Ol’Halloran lays out the basics of serverless computing in cloud platforms:

The term serverless computing can be misleading; how can you compute things without a server? Well, the answer is that you don’t. The term “serverless” comes from the idea that the server is abstracted from the developer, and is totally maintained by the cloud provider. In other words, the developer doesn’t really care what environment their code is run in; they just need it hosted somewhere where it can be executed. This removes the responsibility of infrastructure configuration and maintenance from the developer, but naturally gives them less flexibility and control over the environment.

It took me watching several presentations before I really understood the value behind serverless compute.

Comments closed

Advanced Functions in Powershell

Published 2021-09-07 by Kevin Feasel

Robert Cain moves from basic to advanced:

In my previous post, I covered the use of PowerShell Basic Functions. In this post we’ll cover Advanced Functions.

Advanced Functions provide several abilities over basic ones. First, you can create a detailed parameter list, including the ability to include optional parameters.

Second, you can pipeline enable your functions. Just like a cmdlet, you’ll be able to take input from the pipeline, work with it, then send it out your function back into the pipeline. This will be the subject of our next post.

Finally, you can use features such as Verbose and Debug, which will be the subject of an upcoming blog post.

Click through for examples of what you can do with advanced functions.

Comments closed

Using Triggers for Change Tracking

Published 2021-09-07 by Kevin Feasel

Chad Callihan shows how you can roll your own change tracking with triggers:

Have you ever come across triggers being used to audit table changes? In this post, we’ll look at a few examples of DML triggers in action.

Click through for examples around tracking updates and last edit times, as well as creating audit tables for operations.

If you do go this route, Chad has an important warning. In addition, I appreciate that the statements in this example support multi-row updates. That’s something people often forget when writing triggers.

Comments closed

Subgroup Analysis via Bayesian Hierarchical Modeling

Published 2021-09-03 by Kevin Feasel

Keith Goldfield ponders subgroup analysis:

Which got me thinking, of course, about subgroup analyses. In the context of a null hypothesis significance testing framework, it is well known that conducting numerous post hoc analyses carries the risk of dramatically inflating the probability of a Type 1 error – concluding there is some sort of effect when in fact there is none. So, if there is no overall effect, and you decide to look at a subgroup of the sample (say patients over 50), you may find that the treatment has an effect in that group. But, if you failed to adjust for multiple tests, than that conclusion may not be warranted. And if that second subgroup analysis was not pre-specified or planned ahead of time, that conclusion may be even more dubious.

If we use a Bayesian approach, we might be able to avoid this problem, and there might be no need to adjust for multiple tests. I have started to explore this a bit using simulated data under different data generation processes and prior distribution assumptions. It might all be a bit too much for a single post, so I am planning on spreading it out a bit.

Read on for two separate Bayesian model approaches to the problem. H/T R-Bloggers.

Comments closed

Troubleshooting Microsoft.Purview not Registered

Published 2021-09-03 by Kevin Feasel

Wolfgang Strasser investigates an issue:

In my last Azure Purview Quickstart video (#3 – Create an Azure Purview Account – link), I’ve shown you how to create a new Azure Purview account.

And what pre-prepared demos have in common, well – it “just” works there 

BUT: there are some requirements that need to be configured beforehand, in order to create an Azure Purview Account.

Basically, problems during the creation process can be listed to:

– Security / permissions

– Missing Resource providers

Read on to learn more about permissions requirements and how to deal with these issues as they arise.

Comments closed

Implicit Conversions and SARGability

Published 2021-09-03 by Kevin Feasel

Erik Darling bears bad news:

Data types are one of those core things you need to get right. Whether it’s matching types between join columns, or between parameters and predicates, not doing so can really squash application performance in quite similar ways to writing non-SARGable predicates.

That’s because — wait for it — a lot of the stuff we’ve talked about over the last week that can happen with poorly written predicates can happen with poorly matched data types, too.

Click through for an example. If this keeps up, we may never save Sarge.

Comments closed

Where Databases Fit in the Always-Valid Domain Model

Published 2021-09-03 by Kevin Feasel

Vladimir Khorikov asks an important question:

Today, we’ll talk about an important question: how does the application database fit into the concept of Always-Valid Domain Model?

In other words, is the database part of the always-valid boundary or should you consider it an external system and validate all data coming from it?

Pre-read, my answer was no, databases are part of the external world and your domain model needs to validate every time because who knows what weirdo did something to your data while it slept.

Post-read, well, you’ll have to read to find out.

Comments closed

Azure Database for PostgreSQL Replicas

Published 2021-09-03 by Kevin Feasel

Gauri Mahajan takes us through replica creation in Azure Database for PostgreSQL:

Azure Database for PostgreSQL is an Azure offering of the open-source Postgres database. As there are many databases and data warehouses that are derived from Postgres, during migration from Postgres to a different flavor of another database or data warehouse that is compatible with Postgres, often read replicas are employed. The replicas are read-only since it’s a one-way replication from the master database to replicas. And replicas serve the purpose of decreasing the load on the primary transactional database in production environments. Replicas are typically used as migration sources, reporting and ad-hoc analytics sources and for other purposes. Let’s go ahead and learn to create and manage read replicas in Azure Database for PostgreSQL.

Click through for the process.

Comments closed