Press "Enter" to skip to content

Month: September 2022

ALLSELECTED in DAX

Published 2022-09-13 by Kevin Feasel

Marco Russo and Alberto Ferrari explain how the ALLSELECTED function works:

There are two ways to describe what ALLSELECTED performs and what its use cases are: a simple way and a complex way. In this article, we focus on a simple description of the function and its main use cases. We also provide details about when and how you can use ALLSELECTED without having to worry about the intricate details. If and when you want to go to the next level, invest the proper amount of time required to study this article: The definitive guide to ALLSELECTED. Be careful, the details are really complex and if you stick to the best practices, you can safely ignore said details. A real DAX guru should know all the details, but in this article we focus more on an introduction to ALLSELECTED.

Because of its inherent complexity, we describe ALLSELECTED by first focusing on a business case where ALLSELECTED is a good fit. We then provide an imperfect – yet useful – description of what ALLSELECTED is performing. And finally we provide the few best practices to follow when using ALLSELECTED. We will not provide the complete description of ALLSELECTED in terms of shadow filter context, not even as an introduction.

Even the simple solution is fairly complex.

Comments closed

query_antipattern_type Extended Event

Published 2022-09-13 by Kevin Feasel

Aaron Bertrand is intrigued:

But one thing that caught my eye in all the ruckus is a new Extended Event called query_antipattern, which is a lot more up my personal alley. You may have come across my Bad Habits series, or might have seen me rant about some of these in person. I think this is a promising compromise between manually parsing every single query and just not realizing how many little things are collectively bringing SQL Server to its knees.

This event is not yet documented, but it seems like it could be some combination of runtime code analysis and execution plan inspection. Right now, all we have to go on is a set of cryptic values in sys.dm_xe_map_values:

Read on to see those values and Aaron’s speculation.

Comments closed

Powershell Colors and Icons

Published 2022-09-13 by Kevin Feasel

Jeff Hill has fun with terminals:

If you have followed along with me before, chances are you know I like color. Color helps me zoom in on the information I want faster. Color can help me know if I have the right number of curly braces. Modern browsers and word processors show a red squiggly line when you misspell a word. Your eye is drawn to that bit of color that is different than the rest of the page. Keep reading and I’ll show you how to add some spice to your PowerShell experience.

I heartily endorse the mention of Windows Terminal later on. If you haven’t tried it before, it’s a worthy successor to CMD and the basic Powershell terminal.

Comments closed

Building a Lakehouse with Azure Synapse Analytics

Published 2022-09-12 by Kevin Feasel

Arshad Ali does a bit of construction:

Data Lakehouse architecture has become the de facto standard for designing and building data platforms for analytics as it bridges the gap and breaks the silos created by the traditional/modern data warehouse and the data lake. This blog post introduces you to the world of data lakehouse and it goes into details of how to implement it successfully in Azure with Azure Synapse Analytics.

Read the whole thing.

Comments closed

SQL Server 2022 and Azure Data Lake Storage

Published 2022-09-12 by Kevin Feasel

I continue a series on data virtualization in SQL Server 2022:

Today’s post is a fairly short one. Let’s say you want to connect to Azure Data Lake Storage Gen2 from SQL Server 2022. We saw in a recent post how to connect to Azure Blob Storage. When it comes to Azure Data Lake Storage, the story is almost the same, though there are a couple pitfalls you will want to avoid.

Read on for that error, which stymied me for a good 10 minutes.

Comments closed

Deploying an Arc-Enabled SQL Managed Instance

Published 2022-09-12 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-enabled data services:

Now that we have our Azure Arc-enabled Data Controller configured and available, we can now deploy our first Arc-enabled SQL Managed Instance into our environment. As previously mentioned depending on the type of configuration required for your environment with your Arc-enabled Data Controller (Directly connected or Indirectly connected modes) this will dictate the approach available for you to setup / configure your Arc-enabled SQL Managed Instance.

Click through for a step-by-step guide.

Comments closed

Comparing Performance of Azure SQL Databases

Published 2022-09-12 by Kevin Feasel

Reitse Eskens begins an interesting series:

The trigger for this feast were a number of questions from my employer. What database tier and SKU (Stock Keeping Unit) should we choose for customer C. Well, the answer depends on their budget and the performance they’re expecting. And we didn’t have a conclusive answer. Microsoft will provide you with their statistics on database performance, but what does that mean in real life. Because no matter what the good people in Redmond can come up with as databases, we as data engineers can do worse. Much worse.

So, I created some horrible query’s to insert, select and delete data and fired them off to a database. My own laptop with nvme disks provided the baseline, after that I gradually worked my way up from the Basic DTU database to the Hyperscale database.

This first post is an introduction to the method and laying out expectations. I’m looking forward to the remaining posts in the series.

Comments closed

Creating a Date Column in Power Query from Year Number and Month Name

Published 2022-09-12 by Kevin Feasel

Erik Svensen needs to build a date:

Sometimes it’s the little things that can help you minimize the number of steps in your Power Queries.

Here is another example that might help you.

Let’s imagine your data contains two columns with year and the month name and you want to create a date column.

Read on for a clever function modification to generate a date from these two fields.

Comments closed

Cancelling a Power BI Dataset Refresh

Published 2022-09-12 by Kevin Feasel

Chris Webb says, never mind:

The thing that got me excited about the Power BI Enhanced Refresh API, and which inspired me to start this series of posts, was the fact that for the first time it gives you a way of cancelling Power BI dataset refreshes. In this last post in the series I’ll show you how you can add an Action to your Power Automate custom connector to cancel a refresh and how you can use it in a Power Automate Flow.

Click through to see how.

Comments closed

Airflow and Akka

Published 2022-09-09 by Kevin Feasel

Chesnay Schepler responds to an announcement:

On September 7th Lightbend announced a license change for the Akka project, the TL;DR being that you will need a commercial license to use future versions of Akka (2.7+) in production if you exceed a certain revenue threshold.

Within a few hours of the announcement several people reached out to the Flink project, worrying about the impact this has on Flink, as we use Akka internally.

The purpose of this blogpost is to clarify our position on the matter.

Read on for what this means for Apache Flink.

Comments closed