Press "Enter" to skip to content

Author: Kevin Feasel

Data Validation in Excel from a DAX Query

Published 2023-07-13 by Kevin Feasel

Gilbert Quevauvilliers builds a data validation tool in Excel:

I had a requirement where the customer wanted to make sure that the right user could only see the colour from the stock item assigned to them. Essentially using the Excel file for Row Level Security.

For the Row level security to work correctly it required the colour name to be an exact match.

For me to ensure that this would happen, I wanted to make sure I could get the data from my Power BI dataset, and then using the Excel data validation make sure that the user could either type in the value 100% correct or pick it from the drop-down list.

There are quite a few steps, but I can see business people being happy about the final outcome.

Comments closed

IS DISTINCT FROM in Snowflake

Published 2023-07-13 by Kevin Feasel

Kevin Wilkie remains distinct:

Now, the more fun – “new-ish” – version of the DISTINCT keyword.

Let’s take two values – A and B. Let’s define A = 7 and B = 2.

Snowflake will allow you to ask if A IS DISTINCT FROM B. Thankfully, in this case, it is.

Click through to see how this works. Also note that this syntax is available in SQL Server 2022.

Comments closed

Creativity, Learning from Code, and the APPLY Operator

Published 2023-07-13 by Kevin Feasel

Rob Farley covers one of my favorite operators:

SQL Server 2005 was released, of course, in 2006, and I had been running the Adelaide SQL Server User Group since September 2005. Information about the new features had been coming through, and I’d been at TechEd Australia 2005 – my first since 1999. I was still an application developer at the time (well, a manager, but still getting my hands dirty), but saw the data as the most important part of my applications. When the T-SQL enhancements in SQL Server 2005 came through, there were two things that caught my eye (I know they were available in Oracle before SQL Server, but I was focusing more on the Microsoft platform by then). They were the OVER clause, and APPLY.

This is all part of a broader story about reading code to learn from it.

Comments closed

Users and Role Members for Azure SQL Databases

Published 2023-07-13 by Kevin Feasel

Peter Schott makes a list:

I ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.

Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.

Comments closed

The Five Stages of Code Review Grief

Published 2023-07-13 by Kevin Feasel

Shane O’Neill has an experience:

You have code that keeps coming up as a high consumer of CPU on our systems? OK?

It had gotten to the stage where it kept appearing in sp_WhoIsActive? What’s that? And should it not be “whom is active”? Well, agree to disagree.

Let’s see the code so. Wow, that’s a small scroll bar! Yeah, that’s one of ours.

I should note that “Who is active?” is correct, as the sessions we are inquiring after are the subject of the question rather than the direct or indirect object, and there is no prepositional phrase which would affect the decision.

Comments closed

Shortcuts and Table Clones in Microsoft Fabric

Published 2023-07-13 by Kevin Feasel

Reitse Eskens takes a shortcut:

A few days ago, I heard the term table clone for the first time, it’s preview release date was to be confirmed and I had no idea what it was about. Two days later, a video emerged where the table clone was explained on a high level. At that point, I started to wonder what the differences are between a table clone and a shortcut. So let’s dig a little into that question!

First I’m going to create a shortcut, then a clone and finally compare the two.

Read on for the comparison, as these are quite different things.

Comments closed

Azure Stream Analytics No-Code Editor

Published 2023-07-12 by Kevin Feasel

Xu Jiang shows off a new designer:

Azure Stream Analytics is a fully managed stream processing engine designed to analyze and process large volumes of streaming data with sub-millisecond latencies. Using a SQL-like query language, it empowers you to analyze your streaming data efficiently. It only takes a few clicks to connect to multiple sources and sinks, creating a Stream Analytics job. 

The no-code editor offers an intuitive user experience that enables you to develop Stream Analytics jobs effortlessly, using drag-and-drop functionality, without having to write any code. It further simplifies Stream Analytics job development experience. With just a few clicks, you can quickly develop jobs to handle diverse scenarios in just minutes. It is available in the Azure Event Hubs portal, and now in Azure Stream Analytics portal as well.

Read on to see what it looks like and what you can do with it.

Comments closed

Object Comparison in R

Published 2023-07-12 by Kevin Feasel

Steven Sanderson checks two objects:

In the realm of programming, R is a widely-used language for statistical computing and data analysis. Within R, there exists a powerful function called identical() that allows programmers to compare objects for exact equality. In this blog post, we will delve into the syntax and usage of the identical() function, providing clear explanations and practical examples along the way.

You can also take a look at the documentation for this function to see a few more examples.

Comments closed

Understanding String Concatenation with FOR XML PATH

Published 2023-07-12 by Kevin Feasel

Brent Ozar did some noodling:

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing.

I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().

Comments closed

Power BI Projects and Git Review

Published 2023-07-12 by Kevin Feasel

Teo Lachev wants to wait:

But to digress from Fabric, let’s take a look at two developer-oriented and frequently requested features that fortunately doesn’t require Fabric: Power BI Desktop projects and workspace integration with Git. The video in the link is a good starting point to understand how these features work.

Basically, the first feature lets you save a Power BI Desktop file as a *.pbip file which generates a set of folders with human-readable text files, such as the model.bim file (has the model definition described in JSON). Next, you can put these files under source control both in Power BI Desktop using any source-code enabled tool, such as Visual Studio Code or Visual Studio. Currently in preview, Power BI projects is an experimental feature (in fact, the Publish button is disabled so you can’t publish a project to Power BI Service).

Click through for Teo’s thoughts. This is something that I wish had been done 8 years ago, and as Teo points out, there’s still room to grow. But I do consider it a step forward from only being able to save PBIX files.

Comments closed