Press "Enter" to skip to content

Month: April 2022

This Month in Synapse

Published 2022-04-28 by Kevin Feasel

Ryan Majidimehr recaps the updates for Azure Synapse Analytics in April of 2022:

Since the GA of Synapse, customers have asked for a fine-grained RBAC (role-based access control) role that allows a user persona to monitor the execution of Synapse Pipelines and Spark applications without having the ability to run or cancel the execution of these applications.  

Now, customers can assign the Synapse Monitoring Operator role to such monitoring personas. This allows organizations to stay compliant while having flexibility in the delegation of tasks to individuals or teams. 

That is indeed useful.

Comments closed

Simplifying T-SQL Code

Published 2022-04-28 by Kevin Feasel

Ed Pollack shares several good T-SQL tips to simplify code:

While trying to solve the data challenges an organization throws our way, it is often easy to get buried in large, complex queries. Despite efforts to write concise, easy to read, maintainable T-SQL, sometimes the need to just get a data set quickly results in scary code that is better off avoided rather than committed.

This article dives into a handful of query patterns that can be useful when attempting to simplify messy code and can both improve query performance while improving maintainability at the same time!

Click through to learn more. I use most of those tips to great effect (though don’t often have need to hit some of the DMVs Ed mentions).

Comments closed

A Power BI Report for Power BI Report Access

Published 2022-04-28 by Kevin Feasel

Gilbert Quevauvilliers sets up an infinite loop:

In this blog post I show the final part which is how I created the Power BI report which takes the previous 3 steps and then creates the Power BI Report.

I am going to show you how I got the data in using Power Query and then created the Power BI report.

Read on for the process. But now I want a report to see who has access to the report for who has access to reports. And I think I need a report for that layer. And that layer. And…

(Shh, yes, I know you can get that all from the same report but it’s so rare I get to make a “Turtles the whole way down” reference).

Comments closed

The KQL where Operator

Published 2022-04-27 by Kevin Feasel

Robert Cain continues a series on KQL:

In my previous post, we saw how the search operator was used to limit the results of a query. This post will focus on the where operator, which performs a similar function.

Whereas search is used to limit based on matching of a string, the where operator is used to match based on a condition. In this post we’ll see some of the conditions that can be used with a where operator to narrow down a dataset.

Read on for plenty of uses of the operator.

Comments closed

Calculations in DAX with CALCULATE()

Published 2022-04-27 by Kevin Feasel

Marco Russo and Alberto Ferrari explain one of the most important DAX functions:

CALCULATE, with its companion function CALCULATETABLE, is the only function in DAX that can change the filter context. Its use is very intuitive at first, and most DAX developers start using CALCULATE without knowing the most intricate details of its behavior. Then, sooner than later the use of CALCULATE becomes frightening because CALCULATE starts to misbehave. When this happens, it is nothing but a signal that you need to learn more theory and deepen your understanding of the behavior of CALCULATE.

In this article, we do not introduce the most complex behaviors of CALCULATE. Instead, we provide a beginner’s guide to CALCULATE, and we try to avoid making things simpler than they are. CALCULATE is definitely a complex function. Here we introduce its base behaviors, with a solid theoretical foundation.

Definitely a must-read if you work with Power BI and don’t have CALCULATE() down pat.

Comments closed

Dynamic M Parameters now GA

Published 2022-04-27 by Kevin Feasel

Dany Hoter shows off dynamic M parameters in Power BI:

The user is creating an application in which he wants to embed Power BI.

The same Power BI report will be used in different contexts and the user wanted to have a different header each time and to provide the header as part of the URL.

There may be other ways to solve this problem(?) but as the solution was already using ADX, the user came with a way that involves Direct Query and a dynamic parameter to solve the dynamic header.

Click through to see how to solve this problem.

Comments closed

Thin Reports in Power BI

Published 2022-04-27 by Kevin Feasel

Soheil Bakhshi’s reports get the Kate Moss treatment:

Shared Datasets have been around for quite a while now. In June 2019, Microsoft announced a new feature called Shared and Certified Datasets with the mindset of supporting enterprise-grade BI within the Power BI ecosystem. In essence, the shared dataset feature allows organisations to have a single source of truth across the organisation serving many reports.

A Thin Report is a report that connects to an existing dataset on Power BI Service using the Connect Live connectivity mode. So, we basically have multiple reports connected to a single dataset. Now that we know what a thin report is, let’s see why it is best practice to follow this approach.

Read on for Soheil’s thoughts on the topic and a tutorial on how to create a thin report.

Comments closed

Combining flashlight and plotly in R

Published 2022-04-26 by Kevin Feasel

Michael Mayer analyzes candidate models:

Since almost all plots in flashlight are constructed with ggplot, it is super easy to turn them into interactive plotly objects: just add a simple ggplotly() to the end of the call.

However… it is not straightforward to show interactive plots in a blog! Thus, we show only screenshots of the resulting plots here and refer to the complete HTML report here: https://mayer79.github.io/flashlight_plotly/flashlight_plotly.html

We will use a sweet dataset with more than 20’000 houses to model house prices by a set of derived features such as the logarithmic living area. The location will be represented by the postal code.

Click through for the blog post or check out the report.

Comments closed