Press "Enter" to skip to content

Category: Power BI

Data Scaling Thoughts for Power BI

Published 2022-11-21 by Kevin Feasel

Paul Turley starts a series:

The Power BI service can handle a lot of data, but just because your data sources are big doesn’t mean that your Power BI datasets will also take up a lot of space. If the data model is designed efficiently, even terabytes of source data will usually translate into megabytes, or a few gigabytes of dataset storage at most. As the industry has largely made the transition from on-prem SQL Server Analysis Services and AAS tabular models to Power BI datasets in Premium capacity, the size limits in the cloud service are notable. The following reference chart from the Microsoft Learning docs shows that a P1 Premium dedicated capacity is limited to 25 GB per dataset. That’s a lot but there are Premium capacity SKUs that can handle up to 400 GB of compressed data in an in-memory data model.

Click through for Paul’s introductory thoughts and stay tuned for part 2.

Comments closed

Defragmenting Power BI Fact Tables

Published 2022-11-16 by Kevin Feasel

Chris Webb reminds us that Power BI incremental refresh can lead to some fragmentation:

A lot of people are building large datasets in Power BI Premium nowadays, and if you’re building a large dataset you’re very likely to be using Power BI’s incremental refresh functionality with your fact tables or manually creating and refreshing partitions in them. If so, you should occasionally do a special kind of refresh to shrink the dictionaries on your fact tables.

Read on for a test of the most extreme scenario, though even less extreme versions can be bad.

Comments closed

Schedule a Power BI Dataset Refresh

Published 2022-11-14 by Kevin Feasel

Gilbert Quevauvilliers keeps to the schedule:

Below are the steps on how to use Power Automate to schedule a refresh of a Power BI dataset at the time you want it to.

I have recently seen some questions in the Power BI Community with regards to refreshing Power BI datasets. I thought it would be a good idea to blog on how to use Power Automate (Flow) to schedule a refresh of a Power BI Dataset.

Read on to see what the limitation is in Power BI and how you can use Power Automate to get around it.

Comments closed

Performance Tuning Tables with Filters in Power BI

Published 2022-11-11 by Kevin Feasel

Chris Webb doesn’t want to wait:

There are four columns: Date, Town and two measures. One measure called [Fast Measure] is, as the name suggests, very quick to execute; the other measure, called [Slow Measure], is very complex and slow. The definitions are irrelevant here. Notice that there is a filter on this table visual so only the rows where [Fast Measure] is greater than 1 are shown.

If I measure the amount of time to render this table in Performance Analyzer, it takes around 17.5 seconds to run. However, if I remove the filter on [Fast Measure], the table only takes 8 seconds to run. Why? The filter is on the fast measure and surely more rows are returned without the filter, so wouldn’t the slow measure be evaluated more?

Click through for the answer.

Comments closed

Conditional Formatting from Text in Power BI

Published 2022-11-09 by Kevin Feasel

Mara Pereira shows us a trick:

Have you ever wondered if you can apply conditional formatting based on a text field/measure instead of a numeric field/measure?

If your answer is yes, then this trick is for you!

The other day I was working with a customer who asked something that I had no idea how to build.

They wanted to apply conditional formatting over some of their visuals, but they wanted the conditional formatting applied over a text field and not over a numeric field or a measure.

Read on to see how.

Comments closed

DirectQuery Partitions on Hybrid Tables

Published 2022-11-08 by Kevin Feasel

Shabnam Watson noticed something interesting:

While I was tracing some Power BI datasets with Hybrid tables, I noticed that that the DirectQuery partition of a Hybrid table is used in all queries submitted to the Hybrid table, whether or not they are asking for data from that Direct Query partition. On first look, this does not look good because Power BI should be able to use partition pruning for a partitioned table, meaning it should only scan certain partitions. For example, if you ask for historical data from 5 years ago, the current day’s data is not needed to answer that query and hence, the Direct Query partition for today should not be used in the query.

But do read on for more information about what’s going on, as it seems there are layers of nuance here.

Comments closed

Power BI Model Documenter Version 2.1.0

Published 2022-11-08 by Kevin Feasel

Marc Lelijveld has a new version of the Power BI Model Documenter:

It is long overdue, but time for a new updated version for the Power BI Model Documenter external tool! The latest release lasts from the first week of 2022 and since then close to 6000 people have downloaded the Model Documenter installer directly from the website, excluding the downloads and clones from the GitHub repository. I can’t share enough how happy I am with all the interactions I’ve had with people from all over the world providing feedback, asking questions and willing to contribute to the Model Documenter.

Read on to see what’s changed since the last release.

Comments closed

Profiler Errors against Power BI Dataset

Published 2022-11-07 by Kevin Feasel

Shabnam Watson troubleshoots an issue:

I was trying to trace a dataset I had published to Power BI service using SQL Server Profiler and I was getting this error:

Either the trace with the ID of ‘MicrosoftProfilerTrace1667261566’ does not exist in the server with the ID of ‘autopremiumhostnorthcentralus001-081’, or the user does not have permissions to access the object.

Read on for the solution.

Comments closed