Press "Enter" to skip to content

Month: June 2023

Auto-Pausing Synapse Dedicated SQL Pools

Published 2023-06-19 by Kevin Feasel

Mark Broadbent saves some money via pool auto-pausing:

This capability is neither earth shatteringly new nor unexpected, and something that Databricks has provided for some time. Of the two Data Exploration & Data Warehousing Pool types, Synapse Serverless Pool (otherwise know as the built-in Pool) by its very definition does not incur compute charges when it is not running.

Therefore this leaves us with only dedicated SQL Pool to worry about and this is where our problems begin.

Click through for the scripts to pause and resume a dedicated SQL pool, and Mark promises a part 2 in which we see the automation.

Comments closed

Converting an Excel Crosstab to Power BI Tabular Data

Published 2023-06-19 by Kevin Feasel

Leila Etaati breaks out Power Query:

There is a common way of seeing data in Excel as a Matrix format; many data entries look like a matrix in many contexts, from accounting to health data. However, to better analyze data and add more calculations and charts and, in general, get more insight from data, we need to change the matrix data in Excel to a tabular format in Power BI.

For example, below table, we have data about the Deaths from Cancer of Lip, oral cavity, and pharynx by age, sex, and year.

Not surprisingly, Power Query does a really good job at handling this common Excel style.

Comments closed

The Importance of the New Power BI Project

Published 2023-06-19 by Kevin Feasel

Paul Turley lays out an explanation:

Back in December, I posted this short article about how Power BI can participate in Continuous Integration & Continuous Deployment (CI/CD), from simplified code sharing and version control all the way up to fully-automated Build automation & deployment scenarios. However, since the product inception, there have been significant challenges in orchestrating versioning and code sharing with a single PBIX file. Achieving DevOps and CI/CD was possible but it required creative work-arounds and manual steps. In April of this year, Nathan Hales contributed this post, showing how to use Tabular Editor to manually decompose a PBIX file into a Model.bim and then create a Git repo and Azure DevOps project.

Although the essential pattern is the same, the need for manual steps and work-arounds is eliminated with this new feature release!

This is really good news, and Paul shows you how to enable the feature (currently in preview) and use it.

Comments closed

Troubleshooting Slow Performance in SQL Server

Published 2023-06-19 by Kevin Feasel

Joseph Pilov puts together a compendium:

In the past 1.5 years we in the SQL Server CSS and content teams have been working on creating new documentation to assist with troubleshooting SQL Server slow performance issues.

Here is a list of articles that were produced over this period. Hope you find these useful. Please share with others and don’t hesitate to provide feedback at the bottom of each article page by clicking on “Was this page helpful?”.

The following individuals contributed with reviews, ideas, project management and content creation: Shaun Beasley, Pradeep Madheshiya, Stephanie Okhagbuzo, Hemin Shah, Liwei Yin, Seven Dong, Haiying Yu

Read on for 14 separate articles covering a variety of issues.

Comments closed

Creating a Sparkline in the New Power BI Card Visual

Published 2023-06-19 by Kevin Feasel

Erik Svensen tries out an updated visual:

In the June 23 release of Power BI desktop we has been blessed with a new card visual that not only will give a better performance compared to adding multiple cards in the past but it also gives us some formatting options we have been dreaming of for a long time.

Here is a link to the official blogpost – New card visual | Public preview | Microsoft Power BI Blog | Microsoft Power BI

On social media you have probably already seen a lot of examples of usages, and I wanted to share how you can add a sparkline to the card to make it look something like this.

Erik does a good job of showing off the capabilities of this updated card visual.

Comments closed

Microsoft Defender for Azure SQL Slowdown

Published 2023-06-19 by Kevin Feasel

Iain Evans finds a hidden cost:

To find the root cause, we conducted a configuration comparison of the servers against an Aireforge configuration snapshot to check for any changes. We immediately noticed that an extended event session called SQLAdvancedThreatProtectionTraffic had recently been deployed. This session is deployed by default with Azure Defender for SQL Server. More info: https://learn.microsoft.com/en-us/azure/defender-for-cloud/defender-for-sql-introduction#advanced-threat-protection

Read on to see what the symptoms were and what kind of impact this had.

Comments closed

Apache Kafka 3.5 Released

Published 2023-06-16 by Kevin Feasel

Mickael Maison has an announcement for us:

We are proud to announce the release of Apache Kafka® 3.5.0. This release contains many new features and improvements. This blog post will highlight some of the more prominent features. For a full list of changes, be sure to check the release notes.

See the Upgrading to 3.5.0 from any version 0.8.x through 3.4.x section in the documentation for the list of notable changes and detailed upgrade steps.

The ability to migrate Kafka clusters from ZK to KRaft mode with no downtime is still an early access feature. It is currently only suitable for testing in non-production environments. See KIP-866 for more details.

Click through for some of the highlights and check out the full release notes as well.

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

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

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