Press "Enter" to skip to content

Author: Kevin Feasel

Load Testing in Power BI

Published 2023-04-24 by Kevin Feasel

Chris Webb gives us the why and the how:

If you’re about to put a big Power BI project into production, have you planned to do any load testing? If not, stop whatever you’re doing and read this!

In my job on the Power BI CAT team at Microsoft it’s common for us to be called in to deal with poorly performing reports. Often, these performance problems only become apparent after the reports have gone live: performance was fine when it was just one developer testing it, but as soon as multiple end-users start running reports they complain about slowness and timeouts. At this point it’s much harder to change or fix anything because time is short, everyone’s panicking and blaming each other, and the users are trying to do their jobs with a solution that isn’t fit for purpose. Of course if the developers had done some load testing then these problems would have been picked up much earlier.

With that in mind, Chris explains some of the things we can do to help with load testing in Power BI.

Comments closed

Snowflake Data Governance

Published 2023-04-24 by Kevin Feasel

Enrique Lopez de Lara shares a few ways that Snowflake allows us to protect data in its system:

The role hierarchy in the previous section defines what can be done on different objects and by whom. However, it doesn’t restrict which records within a table a user can see or which values should be masked within a column. That’s where the data governance policies in this section come into play.

All data governance policies and tags are stored in the PROD_DB_GOV database under three schemas: MASKING, ROWACCESS and TAGS. Putting all the policies and tags in a single database allows us to centralize them and better restrict access to them. Please note that only the GOV_ADMIN role has read/write permissions on it.

These are, for the most part, very similar to what we’re used to in relational databases: application and system roles, row-level security, and data classification.

Comments closed

Finding Special Characters in Powershell

Published 2023-04-24 by Kevin Feasel

Patrick Gruenauer looks for special characters:

Sometimes special characters are a nuisance. If you are trying to create some user accounts in on-premise or cloud environments, you should avoid special characters in usernames. In this blog post I will show how to find this special characters.

Click through for a regular expression-based approach, which also allows you to exclude special but not special enough characters.

Comments closed

Retrieving Redshift Query History

Published 2023-04-21 by Kevin Feasel

Koen Verbeeck wants to see what you did last summer:

Because my Windows machine apparently decides to install updates over night (and thus reboot my machine), it has happened that I lost the query that I was writing for Redshift in the tool DBeaver. When you work with SQL Server Management Studio (SSMS), you typically don’t have this issue as a temporary copy is always saved. Close down SSMS, restart it and the queries are still there.

Click through to see what you can do.

Comments closed

Upgrading an Expired SQL Server Evaluation Edition

Published 2023-04-21 by Kevin Feasel

Chad Callihan loses track of time:

SQL Server Evaluation editions are great to use for testing early releases. When the real deal comes out, it’s best to upgrade then and there (probably Evaluation to Developer if you’re testing and experimenting). But what happens if you let an evaluation edition expire by accident? The good news is you can still upgrade but it’s a bit tedious.

Click through to see how.

Comments closed

Running a Power Query Operation Only when Data Exists

Published 2023-04-21 by Kevin Feasel

Gilbert Quevauvilliers doesn’t waste time:

I had a requirement where I wanted to only have the underlying query run if there was data in the table.

As shown in the image below a query would run, scan 20GB and then return zero rows.

Instead of performing such an expensive scan, Gilbert provides an alternative. This is important in the case of querying the Synapse serverless SQL pool, as Gilbert does—in that case, the 20GB scan costs money.

Comments closed

A Use Case for Removing Schema Prefixes

Published 2023-04-21 by Kevin Feasel

Aaron Bertrand threatens to angry up the blood:

I’ve long been a huge advocate for always referencing objects with a schema prefix in SQL Server.

In spite of what may be a controversial title to many of my regular blog readers, I don’t really want you to stop that practice in most of your T-SQL code, because the schema prefix is important and useful most of the time. At Stack Overflow, though, there is a very specific pattern we use where not specifying the schema is beneficial.

Mind you, Aaron’s use case is a rather niche example, so I don’t plan on burning him in effigy. Just maybe singeing him a tiny bit.

Comments closed

Transport Layer Security and SQL Server

Published 2023-04-21 by Kevin Feasel

Michael Howard provides an explanation:

Invariably, when I ask people what Transport Layer Security (TLS) does, they say something like, “it protects my credit card info when I buy things online.”

The response is not incorrect, but it’s not the whole story, either, and let’s just ignore the server-side credit card protection requirements and PCI compliance for a moment.

TLS provide three security services, with an optional fourth.

Click through for those services and some of the ways we can improve our security posture when connecting to (or hosting!) a SQL Server instance.

Comments closed

Kafka Topics and Message Ordering

Published 2023-04-19 by Kevin Feasel

Francesco Tisiot calls us to order:

One of Apache Kafka’s most known mantras is “it preserves the message ordering per topic-partition,” but is it always true? In this blog post, we’ll analyze a few real scenarios where accepting the dogma without questioning it could result in unexpected and erroneous sequences of messages.

Click through for a dive into what can go wrong with ordering. The good news is, in most cases, exact ordering isn’t critical. For cases in which it is critical, you’re trading off reduced throughput for increased order integrity.

Comments closed

Building Strings in Powershell with StringBuilder

Published 2023-04-19 by Kevin Feasel

Robert Cain takes to a .NET class to build some strings:

As I was creating the next post in my ArcaneBooks series, I realized I had not written about the StringBuilder class. As the code in my ArcaneBooks module relies on it in several places, I thought it best to add a new post to my Fun With PowerShell series explaining how to use it before continuing.

It’s a common need in any language, and PowerShell is no exception, to need to add more text to an existing string.

Read on to understand why the StringBuilder class exists and how to use it. If you’re doing a lot of string manipulation, particularly of large strings in loops, this can provide a noticeable performance impact.

Comments closed