Press "Enter" to skip to content

Category: Power BI

Power BI Row-Level Security for Language Selection

Published 2022-11-30 by Kevin Feasel

Kristyna Hughes has an interesting use case for row-level security:

Making multilingual reports in Power BI requires a lot of different elements. Translations can be added to PBIX files to translate column names, visual titles, etc. but these translations will not impact the data itself. One way to translate the dimensions themselves (star schema introduction) is to have a translation table that contains a display language and value column for each ID of the dimension. In the example below, the translation table would replace the original table and we could apply RLS to the Language column.

I think this is a clever use case for row-level security.

Comments closed

The Importance of Proper Data Modeling in Power BI

Published 2022-11-29 by Kevin Feasel

Paul Turley avoids “big, wide tables”:

Power BI is architected to consume data in a dimensional model, with narrow fact tables and related dimensions. Introducing a big, wide table in a tabular model is extremely inefficient. It takes up space and memory resources, impacts performance, and complicates measure coding. Flattening records into a flat table is one of the worst things you can do in Power BI and a common mistake made by novice Power BI users.

This is a conversation I’ve had with many customers. We want our cake, and we want to eat it too. We want to have all the analytic capabilities, interactivity and high performance but we also want the ability to drill-down to a lot of details. What if we have a legitimate need to report on transaction details and/or a large table with many columns? It is well-known that the ideal shape is a star schema but what if we need to shape data for detail reporting? The answer is that you can have it both ways, but just not in one table.

Read on for a better model design (hint: the Kimball style) as well as several tips and tricks.

Comments closed

Automatic Partition Maintenance in Power BI

Published 2022-11-29 by Kevin Feasel

Shabnam Watson answers an attendee question:

During one of my presentations on Incremental Refresh (IR) in Power BI, someone asked what happens during a Power BI automatic partition maintenance window when Power BI has an opportunity to merge smaller partitions into larger ones. Does Power BI use the data that is already imported into Power BI for the smaller partitions and combine it into a bigger one or does it re-read the data for those smaller partitions again. For example, if a dataset has an IR policy to refresh the last 1 day, and it has read data for all the days in a previous month, one day for each, when the new month arrives, it has an opportunity to merge the smaller day partitions into a month partition for the previous month. Does it re-read the previous month’s data from the source again or does it use what it already has in its memory?

Read on for the answer.

Comments closed

Content Endorsement in Power BI

Published 2022-11-29 by Kevin Feasel

Soheil Bakhshi helps us find the best data:

One of the key aspects of users’ experience in Power BI is their ability to collaborate in creating and sharing content, making it an easy-to-use and convenient platform. But the convenience comes with a cost of having a lot of shared content in large organisations raising concerns about the content’s quality and trustworthiness. It would be hard, if not impossible, to identify the quality of the contents without a mechanism to identify the quality of the contents. Content endorsement is the answer to this.

“I’m Commander Shepherd and this is my favorite dataset on the Power BI Citadel.”

Comments closed

Partitioning Data in Power BI

Published 2022-11-28 by Kevin Feasel

Paul Turley continues a series on working with large amounts of data in Power BI:

You don’t have to have massive tables to benefit from partitioning. Even tables with a few hundred thousand records can benefit from partitioning, to improve data refresh performance and to detect source data changes. There is little maintenance overhead, so the benefits usually outweigh the cost, in terms of effort and management.

Click through for Paul’s thoughts on the topic.

Comments closed

Reviewing an Existing Data Model with Power BI Model Documenter

Published 2022-11-23 by Kevin Feasel

Marc Lelijveld wants to see what’s out there in the wild:

In some scenarios, it can happen that you do not even have a Power BI desktop data model. For example, when you migrated from Analysis Services to Power BI Premium, or in case you have to deal with large datasets and it is directly developed using Visual Studio, Tabular Editor or any other tool of your preference and deployed over the XMLA endpoint. Similar setup could be that you once enriched your data model using Tabular Editor or ALM Toolkit, which resulted in the fact that your Power BI Desktop file, is no longer your golden version of your data model.

Another scenario could be gaining an overview of partitioning when using incremental refresh. The partitions of Incremental Refresh are only generated in the Power BI Service. So, including this information in your generated documentation is only possible when you connect directly to the Power BI Service.

But what if you still want to show a complete view of your Power BI data model, and extract insights using the Power BI Model Documenter? I can tell you; it is possible!

Read on to see what you can do in that case.

Comments closed

Testing Power BI REST APIs

Published 2022-11-23 by Kevin Feasel

Gilbert Quevauvilliers tries it:

Did you know that there is an easy way to run and extract Power BI REST API data?

The good news is that you can do this directly in your web browser. You don’t have to install or configure anything!

The method below works well if you want to either test the API to see what it returns.

Or if you want to run it to extract some data.

Read on for the process.

Comments closed

Essential Power Tools for Power BI Desktop

Published 2022-11-22 by Kevin Feasel

Jason Cockington makes several recommendations of external tools for Power BI Desktop:

The External Tools ribbon is a feature that has been available in Power BI Desktop since the July 2020 release.  This feature should be considered essential to anyone who is regularly working in the Power BI space.    In this article I recommend my top five External Tools, that I consider essential to any Power BI developer.

Read on for the recommendations. I regularly use two of the five but these do look good.

Comments closed

Fitting 10 Pounds of Data into a 5-Pound Power BI

Published 2022-11-22 by Kevin Feasel

Chris Webb does some compacting:

Power BI can handle large data volumes, but just how much data can you load into Power BI? Can Power BI handle big data? How big is “big” anyway? These are questions you may have when you’re starting out on a new Power BI project and the answers can be hard to find. Indeed the answer in most cases is “it depends”, which isn’t very helpful. In this post I will try to explain the various limits on Power BI dataset size and how you can know if you’re likely to hit them.

Click through to learn more about these limitations.

Comments closed

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