Press "Enter" to skip to content

Curated SQL Posts

Database Projects and Version Control

Published 2022-10-21 by Kevin Feasel

Olivier van Steenlandt helps you get database code into source control:

In this blog post, we will focus on how to get started with Database Projects and how to get this into Source Control (Azure Repos). So together we will create our first Database Project, import our database into the project and push it to the Azure Repository.

Before we can start, we need to make sure that we have the required tools installed, in this blog post I will focus on Visual Studio. In order to create your first Database Project, you need to ensure that the SQL Server Data Tools extension for Visual Studio is installed.

This one I intended to post earlier in the week but it got away from me for a little bit. Do check it out.

Comments closed

A WPF-Based Countdown Timer

Published 2022-10-20 by Kevin Feasel

Jeffrey Hicks keeps track of time:

Last year I released a PowerShell module called PSClock. The module contains a command to create a transparent WPF form displaying a clock. Shortly after, someone posted a request for a countdown timer. Not an unreasonable request and one I finally got around to implementing. However, I already had a module with a number of timer and countdown-related commands called PSTimers I had been thinking of a WPF-based timer, so this solved two problems.

Read on to see how it works.

Comments closed

An Analysis of Resumable Online Index Operations

Published 2022-10-20 by Kevin Feasel

Chris Taylor sums it up:

This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.

Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information

Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.

Comments closed

Power BI Line Chart: Summarized or Split

Published 2022-10-20 by Kevin Feasel

Prathy Kamasani gives us options:

A colleague of mine came to me with an interesting use case, “ Switch between a summarized value or selected value with multiple legends”. For example, I have five countries and their GDP values. When the end user goes to the report, the user would like to see the average GDP of all countries, but when the user selects single or multiple countries on the slicers, the line chart should show only selected values.

Click through for the guide.

Comments closed

The Importance of Power BI Object Names

Published 2022-10-20 by Kevin Feasel

Paul Turley sends us an e-mail:

Using common language is critical but often trivialized, when describing requirements, deliverables and project expectations. When people are working together, depending on each other to complete important tasks, they must have a clear understanding of the common language and terminology. It is usually only after a word, phrase or abbreviation has been used with an assumed meaning that we realize the error and have gotten ourselves into trouble. Often, on a daily basis, I review project proposals and requirement documents containing inaccurate language related to Power BI project work. I also getting a lot of virtual eye-rolling when correcting seemingly inconsequential language. But I can also cite many cases when subtle misinterpretations became costly mistakes.

This is at a higher level than naming measures or dimensions.

Comments closed

Extended Event Duration Filtering and Looping

Published 2022-10-20 by Kevin Feasel

Erik Darling notes a problem with time-based filters on operations:

There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.

If I watch the event output, eventually, I’ll see this:

Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.

This happens a lot and I’m not sure there’s a good answer for it.

Comments closed

Storytelling from a Dashboard

Published 2022-10-19 by Kevin Feasel

Amy Esselman hits on a tricky problem with dashboards:

As powerful and as useful as dashboards are, they’re optimized for the exploration of data, not the communication of specific insights. Once we’ve used our dashboards to uncover something worth sharing, we’ll usually be better served by making a separate presentation, designed to bring the findings to light and get others to act upon the information.

The path from dashboard to story might not always be intuitive. This article will use a dashboard from a recent storytelling with data engagement to illustrate how to transform dashboard insights into an action-inspiring story. 

This is especially important if you’re a developer who understand the value of storytelling but doesn’t quite know how to support it while showing arbitrary data.

Comments closed

DATE_BUCKET and DATETRUNC in SQL Server 2022

Published 2022-10-19 by Kevin Feasel

Itzik Ben-Gan shows a good use of a pair of new T-SQL functions:

Time-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.

Comments closed

Finding the Real Error in a DAX Measure

Published 2022-10-19 by Kevin Feasel

Teo Lachev builds on a prior post:

This blog builds upon my previous “Resolving Tabular Conversion Errors” and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2000, 133) Failed to resolve name ‘SYNTAXERROR’. It is not a valid table, variable, or function name.” All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use Tabular), so if we can get the script, we might be able to correlate the error.

Read on to see how you can capture that script and get the error. The technique is definitely not intuitive.

Comments closed