Press "Enter" to skip to content

Curated SQL Posts

Rolling Average and Working Days in DAX

Published 2022-07-20 by Kevin Feasel

Marco Russo and Alberto Ferrari combine two common business requests:

In a previous article, Rolling 12 Months Average in DAX we showed you how to compute a rolling average over a time period. In this new article, we want to take you one step further and show how to compute a moving average over a certain timeframe, that takes into account only the working days. We present two variations of the same solution: one that is optimized, relying on a calculated column, and one that – despite being somewhat slower – works without requiring a calculated column. The latter can be useful in case you need to define the formula in a live-connected report, where calculated columns are not an option.

Because the formula needs to account for working versus non-working days, it cannot rely on standard time intelligence functions. Indeed, DAX time intelligence functions have no knowledge about what it means for a day to be either a working day or a rest day. The NETWORKDAYS DAX function would not be very useful in this case, because it would introduce a slow filter to compute the range of dates that includes the number of working days desired.

Read on to see how they solve this one.

Comments closed

Unresolved Reference to Object using Linked Server or External Table

Published 2022-07-20 by Kevin Feasel

Jose Manuel Jurado Diaz works around an issue with sqlpackage:

We used to have cases when our customers are trying to export their database using SQLPackage or Export service in the Portal having, for example, the following error message: Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename].External references are not supported when creating a package from this platform. In this post I would like to suggest an alternative to export this data.

Read on to see what sqlpackage does not support, how you might solve it, and Eitan Blumin’s very good comment.

Comments closed

Power BI Field Parameters and Measures

Published 2022-07-20 by Kevin Feasel

Roland Szirmai has fun with field parameters in Power BI:

Meaning that report users can switch between “dimensions” of the data. This is great and already provides a much better UI and UX, but there was no information about the limitations of what “fields” can you add to the parameter table.

To be more specific, I couldn’t find any limitation about adding measures (Explicit Measures) to the Field Parameter.

I think you can see where my mind wandered after that…

Read on for the result of Roland’s wanderings.

Comments closed

Mounting Data Lake Storage from a Spark Pool

Published 2022-07-19 by Kevin Feasel

Kamil Nowinski runs into some trouble:

Last weekend, I played a bit with Azure Synapse from a way of mounting Azure Data Lake Storage (ADLS) Gen2 in Synapse notebook within API in the Microsoft Spark Utilities (MSSparkUtils) package. I wanted to just do a simple test, hence I followed the documentation from Microsoft: How to use file mount/unmount API in Synapse.
Having an ADLS Account already created in a subscription – should be easy peasy, right?

Read on to understand when things might be a little more complicated than they seem. And more frustrating, once you see the cause of the problem.

Comments closed

The Importance of a Proper Datamart / Data Warehouse

Published 2022-07-19 by Kevin Feasel

Teo Lachev explains why you want a datamart (or a data warehouse) for BI solutions:

I sent a proposal for implementing a classic BI solution: Azure SQL-based datamart (not Power BI datamart please), ETL, semantic model, and reports. The client had a sticker shock. Return to sender … as other BI companies that quoted can do it for half! Upon digging, it turned out the other companies would build the semantic model (aka Power BI dataset) directly on top of the data source. On a T&M basis, of course, what else? By contrast, I give fixed-price milestone-driven proposals and I don’t get paid unless I deliver and meet written and agreed upon success criteria, but that’s a different story.

So, let me count the ways as the poet would say. It’s certainly technically possible to slap a dataset on top of the data source(s). That’s what self-service BI is all about right … until it doesn’t serve anymore

Read on for more detail.

Comments closed

Write Powershell Output to a File

Published 2022-07-19 by Kevin Feasel

Kenneth Fisher needs someplace to put all of his great ideas:

Last week I posted about a request to get a list of services. The first problem I ran into was expanding the column width, now I need to get the output off my screen and into a file to send the requestor. The cmdlet out-file is perfect for this and it’s pretty simple.

Click through to see how the Out-File cmdlet works as well as another cmdlet you may wish to use instead.

Comments closed

Creating Human-Readable Intervals from Lists in T-SQL

Published 2022-07-19 by Kevin Feasel

Daniel Hutmacher is making a list and checking it twice:

If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.

Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.

Read on for a really good demonstration of how powerful window functions and STRING_AGG() can be.

Comments closed

Using the WINDOW Clause in SQL Server 2022

Published 2022-07-19 by Kevin Feasel

Hasan Savran peeks through the windows:

WINDOW Operations in SQL Server can be hard to understand.  I believe one of the main reasons for that is the long and repeated code it needs. SELECT…WINDOW Clause will help us to remove repeated code and hopefully it will make the WINDOW Operations more user-friendly.

I am really happy about this syntax change, as often times, we have to re-use the same window frame (PARTITION BY and ORDER BY) or a base frame with a minor change (add one more column to the end). This removes the kind of repetition which makes queries harder to read and introduces subtle bugs.

Comments closed

Don’t Store Files in the Database

Published 2022-07-19 by Kevin Feasel

Josh Darnell provides timeless advice:

As Deborah’s invite post suggests, this is a “that one time at that client” story. I was working at a consulting firm, and we had written an app for a particular client. Part of this application’s workflow involved users uploading images alongside some other information. These were not particularly large images in the grand scheme of things – they were taken by a microscope, and were a few kilobytes each, maybe.

However, this app had been in use for a long time. And as you might have guessed from the title of this post, each of these images was stored in a single table in the database that backed this application

Yeah, that’ll be a problem… Read on for some recommendations on how to avoid the issue. One thing I would add is FileTable, which came out in SQL Server 2012. In that case, the files are actually stored on disk but are queryable via T-SQL. It introduces its own set of problems but I do have some fond feelings about having used FileTable in the past.

Comments closed

SQL Server Container Images and RHEL/Ubuntu Versions

Published 2022-07-19 by Kevin Feasel

Amit Khandelwal announces a change:

To make this approach scalable & manageable, we will publish SQL Server container images to MCR based on the distribution’s most recent version, rather than publishing it for all versions of both RHEL and Ubuntu. Here’s an example to help you understand: 

SQL Server 2017 supports Ubuntu 18.04 as the most recent distribution; thus, going forward SQL Server 2017 container images based on the Ubuntu 18.04 image will only be published to MCR and we will not publish the SQL Server 2017 container images for Ubuntu 16.04.

This seems fine to me. They focus on image support for the most recent fully-supported version and hopefully make it a bit better. I do wonder if that will change their overall Linux policy around version support, as that could be trickier to sell.

Comments closed