Press "Enter" to skip to content

Month: February 2023

Blank Rows and Limited Relationships in DAX

Published 2023-02-15 by Kevin Feasel

Marco Russo and Alberto Ferrari cover the blank row:

We dedicated a previous article to the blank row in DAX. In that article, the goal was to explain the differences between VALUES and DISTINCT. This article here focuses on how important it is to generate the blank row to guarantee that totals are always correct.

The blank row is created for regular relationships that are invalid – that is, when there is at least one row on the many-side that does not have a matching row on the one-side of the regular relationship. The same does not happen for limited relationships, which do not generate a blank row in similar conditions. Therefore, if a model contains a limited invalid relationship, developers must pay extra attention to how they create reports to avoid obtaining inaccurate results.

Read on for an example of what they mean.

Comments closed

Working with Power BI Paginated Report Subscriptions

Published 2023-02-15 by Kevin Feasel

Olivier Van Steenland performs a conversion:

As a first step, I tried to “migrate” an example report from SQL Server Reporting Services to Power BI. In a previous blog post, I described the steps required to migrate successfully. You can find that blog post using the following link: Converting SSRS Reports to Power BI Paginated Reports.

In this blog post, I will look at the subscription functionality for Paginated Reports.

Read on for the process, as well as one limitation (and workaround).

Comments closed

Data Updates in Azure Data Explorer

Published 2023-02-15 by Kevin Feasel

Hiram Fleitas updates the data:

I recently ran into a Kustomer that migrated from TSI to ADX (Azure Data Explorer). They were really excited about using Kusto Trender but one item they couldn’t wrap their head around was how to update their hierarchy table(s) in ADX. i.e.  

- Contoso WindFarm Hierarchy (Levels: Plant > Unit > System > Name)
-- Plant
--- Unit
---- System
----- Name

As a big data platform ADX is an append-only data store, so we don’t have the options to do updates, right? Well, that’s not completely true. We absolutely don’t support updates, but we do have a couple options to simulate updates.

Read on to see what options are available to you.

Comments closed

Degree of Parallelism Feedback

Published 2023-02-15 by Kevin Feasel

Deborah Melkin looks at a nice feature in SQL Server 2022:

A couple of months ago, I wrote about my initial thoughts to SQL Server 2022. I think a lot of what I wrote still holds true. It feels like it’s building on the functionality from previous versions, especially when it comes to Intelligent Query Processing and its feedback capabilities.

The feature I’m most curious to find out more about is the Degree of Parallelism Feedback. (You can read more about it here.) One of the main reasons for this is I know very little about the settings.

Read on for Deb’s thoughts and a situation where it would have helped.

Comments closed

Praise for Purvi’s List

Published 2023-02-15 by Kevin Feasel

Andy Yun is a fan:

By sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.

Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.”

Read on to learn what had Andy so excited.

Comments closed

Take Any from KQL

Published 2023-02-14 by Kevin Feasel

Robert Cain isn’t picky:

The take_any function is a random row generator. Based on the parameters passed it, it will select a random row from the dataset being piped into it. It also has a variant, take_anyif, we’ll see both in this post.

Note that take_any was originally called any and was renamed. While any still works, it has been deprecated and you should now use take_any.

As always, Robert shares plenty of examples of how the operator works, so check it out.

Comments closed

Scheduling a Power BI Premium Backup

Published 2023-02-14 by Kevin Feasel

Gilbert Quevauvilliers has us synchronize our watches:

In this blog post I am going to detail how to create a schedule, test and confirm that the backup for the Power BI Premium dataset is successfully backed up.

This follows on from my first blog post: Automating the backup of Power BI Premium datasets/databases – Part 1

Read on for a walkthrough of what you need to do in order to create a schedule runbook.

Comments closed

The Power of Date Truncation

Published 2023-02-14 by Kevin Feasel

Magda Bronowska rounds to the nearest minute:

From MS Learn:

DATETRUNC() function returns an input date truncated to a specified datepart.

On the surface the work similarly to DATEPART(), however that function returns integer values, opposed to the dates returned by DATETRUNC() (we will see that better in the example below).

Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.

Comments closed

Distributing Azure Costs by Cost Centers, Divisions, and Projects

Published 2023-02-14 by Kevin Feasel

Pranab Paul divvies up the costs:

While working on various customer and partner facing roles, I felt the necessity of a simple and flexible solution to align Azure Cost to the customer’s organizational structure. “Project Bose” is a fully operational prototype derived from the same thought process. This is a side project I am working on during my leisure time. I found various customers derived similar solutions in-house, and there are ISV solutions as well. But there are a few fundamental differences between “Project Bose” and all the other solutions I found. “Project Bose” has a flexible backend and hence any changes in organizational structure can easily be implemented on it without disruption. It is also independent of using Resource Tags, which gives it the opportunity to remain non-vulnerable to erroneous values injected intentionally or non-intentionally by IT-Ops.

Pranab didn’t include a link to the GitHub repo, so here it is.

Comments closed