Press "Enter" to skip to content

Author: Kevin Feasel

Reviewing Always Encrypted Queries with Extended Events

Published 2023-06-16 by Kevin Feasel

Matthew McGiffen takes a look:

In previous posts we’ve looked at a number of aspects of Always Encrypted and how it works. I think it’s very useful to understand how it actually works in practice, so in this post we’re going to look at what happens in the background when you execute a query.

I’m continuing on from the examples in the last few posts on this subject. So if you want to repeat the examples for yourself you’ll need to have followed steps from Setting Up Always Encrypted and Executing Queries Using Always Encrypted.

Read on to see what actually happens when you perform a simple INSERT or SELECT operation and there are Always Encrypted columns involved.

Comments closed

Implementing Multiple Retries in Powershell

Published 2023-06-16 by Kevin Feasel

Jose Manuel Jurado Diaz has a script for us:

We often encounter support cases where our customers leave the query execution timeout value at its default. In certain situations, we may find that when this value is reached, the application reports an error and does not continue. In this case, I would like to share an example implemented in PowerShell that allows incrementing the command timeout value up to 5 attempts, with a 5-second margin in each operation.

My recommendation here would be to use Polly instead, as it built in support for exponential backoff and more complex retry logic. Adam Driscoll has a Powershell wrapper called pspolly, which may also prove helpful.

Comments closed

Drillthrough Support in Power BI Paginated Reports

Published 2023-06-16 by Kevin Feasel

Dan English is excited:

Last month there was a new update that has been made that provides support for drillthrough report actions in the Power BI service (Microsoft Fabric)! Those of you who are familiar with SQL Server Reporting Services (SSRS) know what a great feature this is to include in your reports to allow users to jump to another report for additional information such as detail level report and even allow the ability to pass the context (parameters) to the report.

Drillthrough was a big item that might have been a blocker for some companies preventing them from migrating from on-prem SSRS or Power BI Report Server (PBIRS) to the Power BI, well not anymore:)

So how does this work? 

Read on to learn more, including something which is in Power BI paginated reporting but not SSRS.

Comments closed

Changing Visual Titles on Filter with DAX

Published 2023-06-16 by Kevin Feasel

Allison Kennedy has a new title:

Today’s post is a short one with a video to demonstrate what I have learned. I’m a big believer in providing your end user with as much information as possible. Someone new should be able to pick up the report and understand it without needing to get a degree in Power BI or go digging through your company’s intranet to learn what’s what. 

One of the neat features that Power BI provides, is the ability for any visual to act as a filter or ‘cross-filter’ to other visuals. This provides us with comparative values and highlighted bars, but Power BI tooltip just says ‘highlighted’, which isn’t very helpful.

Click through for a bit of DAX code which can help with that, as well as a video demonstrating how it all comes together.

Comments closed

Schema Optimization and Disk Usage in Cassandra

Published 2023-06-15 by Kevin Feasel

John Del Castillo has an after-action report for us:

Instaclustr’s automated systems are constantly monitoring the growth of Instametrics, and periodically it reaches a threshold where TechOps determines it requires more storage. 

When this happens, they contact the Engineering team to get permission to add more nodes. 

On one of these occasions, our team of experts in TechOps looked more closely at our usage of Cassandra and how it has changed over the years, to see if there were ways to further optimize it. 

And we found a way to reduce our disk usage by over 20%, with just a change to our data schema. 

Read on to see what they did and how they were able to save a good amount of disk space.

Comments closed

Joins in KQL

Published 2023-06-15 by Kevin Feasel

Robert Cain picks back up on a series:

I’m still working on my ArcaneBooks project, mostly documentation, so I thought I’d take a quick break and go back to a few posts on KQL (Kusto Query Language). In this post we’ll cover the join operator.

A join in KQL operates much as it does in SQL. It will join two datasets together into a single result.

Even so, there’s a little more to joins in KQL than in T-SQL, with innerunique being unique to KQL. The closest T-SQL analog would probably be a CROSS APPLY (SELECT TOP(1) ...) operation. KQL also uses join to combine the concepts of EXISTS and NOT EXISTS in SQL.

Comments closed

Hard-Coding Values in T-SQL Queries

Published 2023-06-15 by Kevin Feasel

Matthew McGiffen defends a practice:

If someone else comes along to look at this code they don’t know what the value of zero means. My code’s not clear. Worse I might refer to the same value multiple times in my procedure, so if I need to change it later I have to change it in multiple places.

Good practice from other coding languages would say that I replace it with a meaningfully named constant. As mentioned, in T-SQL we don’t have constants so I’ll compromise and use a variable:

And that good idea is where everything goes off the rails. There are a few places where T-SQL offers you “close enough” to other programming languages but where performance suffers as a result: ‘constant variables” like in this example, nested views and user-defined functions for encapsulation, etc.

Comments closed

Microsoft Now Offering Replacement Hardware for Surface Devices

Published 2023-06-15 by Kevin Feasel

Laurent Giret shares the good news;

Starting today, Microsoft is now offering replacement components for Surface devices in its Microsoft Store. This should be great news for consumers with Surface devices that are out of warranty, and Microsoft is also offering free repair guides for its various Surface devices.

“While we have always offered world-class warranty and repair services via Microsoft support, we have been working to increase repair options by designing products that are easier to repair and by expanding our network of Authorized Service Providers. As part of this larger initiative, we are excited to offer replacement components to technically inclined consumers for out-of-warranty, self repair,” announced Tim McGuiggan, VP Devices Services & Product Engineering at Microsoft.

I’m big enough on right-to-repair that I’ll even mention it here. That said, the prices are, uh, a bit high. Compare to Framework’s prices for their parts and I guess the best advice is still, “don’t break your stuff.” But I’m happy to see Microsoft make these parts available and put out the guides. For decades, there was a norm that you released the tech specs and made repair parts available, and any step we see toward moving back to that norm is something I’m happy with.

Comments closed

Thoughts on Community-Driven Documentation in Postgres

Published 2023-06-15 by Kevin Feasel

Robert Haas shares some thoughts:

In my opinion, the PostgreSQL documentation is simultaneously excellent and fairly poor, and both its excellence and its shortcomings are direct results of the process by which the documentation is produced. The PostgreSQL documentation is stored in the same git repository as the source code, and anyone who patches the source code so as to change documented behavior must also patch the documentation to match.

This means that nearly all documentation updates are made by the developer who is most familiar with what is changing in the code, or sometimes by another developer who has studied those changes closely. Therefore, the documentation is usually extremely accurate. Sure, there are oversights, but it would be incredible to discover that some PostgreSQL command has a documented option which doesn’t actually exist, or that a parameter which is documented to take a string argument actually takes an integer or a Boolean. Typically, the descriptions of what SQL statements do and how that behavior is changed by parameter settings or options passed to the command itself are crisp and precise.

But read the whole thing, as there are downsides to this approach.

Comments closed

Connecting to a Fabric Warehouse via SSMS

Published 2023-06-15 by Kevin Feasel

Reitse Eskens does some digging:

Whilst working on a blogpost on Fabric Data Warehouse, I started wondering if I could work around the SQL web interface and connect to my OneLake with SSMS and/or ADS. As it turns out, you can!

Specifically, you can connect to see things in a warehouse or the Tables view of a lakehouse, not the Files view. There is a built-in web viewer, but Microsoft Fabric definitely is intended to work with normal SQL tools, not just its web interface and Power BI.

Comments closed