Press "Enter" to skip to content

Category: Power BI

Using Redis as a Power BI Datasource

Published 2023-04-25 by Kevin Feasel

Kyle Teegarden has me wondering:

Power BI is a widely used, interactive visualization tool capable of querying a variety of SQL-powered back ends. To accelerate these queries, and provide an even more responsive Power BI user experience, we’ve developed Redis SQL ODBC.

Redis SQL ODBC is a native ODBC driver that lets you seamlessly integrate Azure Cache for Redis Enterprise and Enterprise Flash tiers with Power BI. This may dramatically improve your Power BI query response times.

Knowing that the intent of Redis is individual point lookups, this was a bit surprising. Doing the queries over secondary indexes does help square that circle, however.

Comments closed

Load Testing in Power BI

Published 2023-04-24 by Kevin Feasel

Chris Webb gives us the why and the how:

If you’re about to put a big Power BI project into production, have you planned to do any load testing? If not, stop whatever you’re doing and read this!

In my job on the Power BI CAT team at Microsoft it’s common for us to be called in to deal with poorly performing reports. Often, these performance problems only become apparent after the reports have gone live: performance was fine when it was just one developer testing it, but as soon as multiple end-users start running reports they complain about slowness and timeouts. At this point it’s much harder to change or fix anything because time is short, everyone’s panicking and blaming each other, and the users are trying to do their jobs with a solution that isn’t fit for purpose. Of course if the developers had done some load testing then these problems would have been picked up much earlier.

With that in mind, Chris explains some of the things we can do to help with load testing in Power BI.

Comments closed

Using Dynamic Format Strings for Measures in Power BI

Published 2023-04-24 by Kevin Feasel

Meagan Longoria shows off a new preview feature:

The April 2023 release of Power BI desktop introduced a new preview feature called dynamic format strings for measures. This allows us to return values with different formats from the same measure. Previously, we needed to create calculation groups (usually by using Tabular Editor) to accomplish this. But now it is built in to Power BI Desktop.

Read on to learn good use cases for this feature, as well as a few important notes on operation and limitations.

Comments closed

Running a Power Query Operation Only when Data Exists

Published 2023-04-21 by Kevin Feasel

Gilbert Quevauvilliers doesn’t waste time:

I had a requirement where I wanted to only have the underlying query run if there was data in the table.

As shown in the image below a query would run, scan 20GB and then return zero rows.

Instead of performing such an expensive scan, Gilbert provides an alternative. This is important in the case of querying the Synapse serverless SQL pool, as Gilbert does—in that case, the 20GB scan costs money.

Comments closed

Building a Report Development Workflow for Power BI

Published 2023-04-18 by Kevin Feasel

Richard Swinbank thinks about process:

In this series I’ll be talking a lot about workflow. Loosely speaking, report development workflow is the set of steps you have to take to create or update a Power BI report. This typically starts in Power BI desktop, and doesn’t end until users are able to get business value out of a report in the Power BI service.

A good workflow frees a report developer to do the things they like and are good at – understanding business problems, and crafting beautiful reports to help solve them. Anything that that doesn’t contribute to that just gets in the way – a bad workflow clutters up the development experience with peripheral tasks like version control, managing environments and performing deployments.

Richard shares a good number of thoughts on the matter, so check them out.

Comments closed

Disabling Filter Pane Aggregates in Power BI

Published 2023-04-17 by Kevin Feasel

Chris Webb disables a visual element:

These numbers are counts of the number of rows for each value in the table that the field is from. The query to get these counts is usually quite fast and inexpensive, but if you’re filtering on a field from a very large table (for example a fact table) and/or using DirectQuery mode that might not be true. For example, the screenshot above is taken from a DirectQuery dataset and here’s the SQL query that generates the counts shown:

Read on to see how to do this.

Comments closed

Performing a Pareto Calculation in DAX

Published 2023-04-17 by Kevin Feasel

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

2 Comments

Key Components to Power BI

Published 2023-04-14 by Kevin Feasel

Reza Rad provides an overview of what Power BI includes:

Power BI is a cloud-based technology from Microsoft for reporting and data analysis. This reporting technology is built in a way that is not only useful for developers to create reports but also for power users and business analysts. Power BI created a simple, easy-to-use, and user-friendly environment for creating reports. And on the other hand, it is based on several powerful components which help create reports and data analysis for complex scenarios.

Every component of Power BI is responsible for a specific part of the technology. There are components for building reports, connecting to data sources, doing analytics calculations, sharing reports, etc. The following sections explain what each component is. Some of these components are explained in detail in other articles.

Click through for a long list and a series of high-level summaries.

Comments closed

Security Replication in Power BI

Published 2023-04-13 by Kevin Feasel

Marc Lelijveld and Vytautas Kraujalis lock things down, over and over and over:

Imagine, you have everything setup and well secured in your data lakehouse, or data warehouse if you will. Then a user starts consuming the data in Power BI and imports all data according to the security applied to that users’ permissions. Once the data is imported, all data can easily be shared to others who might have other permissions on the same dataset. Potentially, this leads to a breach of data to people who should not have accessed this data at all. Ideally, you replicate the security from the source into Power BI, but without setting up everything by hand.

In this blog post, you will learn how you can automate the replication of security from source to your Power BI data model in just a few steps. A blog post based on an actual client case and written by Vytautas Kraujalis and myself.

Click through for an explanation and a link to the GitHub repo containing all of the scripts.

Comments closed

Row-Level Security in Power BI

Published 2023-04-12 by Kevin Feasel

Reza Rad continues a series on data security in Power BI with two posts. The first covers row-level security:

There are multiple levels in which you can secure the data in a Power BI Dataset. Row Level Security is about applying security on a data row level. For example, sales managers in the united states should only see data for the United States, not Europe. The sales Manager in Europe won’t be able to see sales in Australia or the United States. And someone from the board of directors can see everything. The reason was that Row Level Security wasn’t part of the Power BI model. Now in the new version of Power BI Desktop, the security configuration is part of the model and will be deployed with the model.

The second looks at dynamic row-level security:

The most important question is, why dynamic row-level security? To answer this question, you must consider the limitation of static row-level security. Static row-level security is simple to implement. However, if you have thousands of roles, then it would be a nightmare to maintain. For example, if you want to create a payroll Power BI report in a company with ten thousand users, you want every user to have his/her role. Dynamic row-level security is the answer for such scenarios.

Check out both posts for more info.

Comments closed