Press "Enter" to skip to content

Category: Power BI

Optimizing Power BI Data Load from a Folder of Parquet Files

Published 2021-03-31 by Kevin Feasel

Chris Webb has a tip for us:

In all the testing I’ve done recently with importing data from Parquet files into Power BI I noticed something strange: loading data from a folder containing multiple Parquet files seemed a lot slower than I would expect, based on the time taken to load data from a single file. So I wondered – is there something that can be optimised? It turns out there is and in this blog post I’ll show you what I did.

Click through to see how Chris cut load time down to approximately half what it was.

Comments closed

Building a P&L Statement in Power BI

Published 2021-03-23 by Kevin Feasel

Matt Allington has a follow-up from a prior post:

I blogged about how to Build a P&L With Power BI back in April 2020, and the response from my readers was great. Several people asked how to add percentages such as % of net sales revenue, % gross profit, etc. into the P&L. I decided therefore to do a follow up article to show how you could add percentages to the P&L and also to further demonstrate how I go about solving such problems. Keep in mind, there is rarely one way to solve a problem in DAX; I’m simply sharing the way I solved this problem.

Click through for a video as well as a demo in screenshots.

Comments closed

Parquet File Performance in Power Query

Published 2021-03-22 by Kevin Feasel

Chris Webb troubleshoots a performance issue:

There has been a lot of excitement around the newly-added support for reading from Parquet files in Power BI. However I have to admit that I was disappointed not to see any big improvements in performance when reading data from Parquet compared to reading data from CSV (for example, see here) when I first started testing it. So, is Power Query able to take advantage of Parquet’s columnar storage when reading data?

The answer is yes, but you may need to make some changes to your Power Query queries to ensure you get the best possible performance. Using the same data that I have been using in my recent series of posts on importing data from ADLSgen2, I took a single 10.1MB Parquet file and downloaded it to my PC.

It seem like an area of future growth for Power Query, but Chris does show how to eke out some gains right now.

Comments closed

Common Power BI Administration Scripts

Published 2021-03-17 by Kevin Feasel

Brett Powell continues a series:

Today’s post builds on top of Power BI Admin Scripts Part I by describing five additional PowerShell scripts that Power BI service administrators can utilize to address relatively common scenarios. Like Part I, the five new scripts have been added to my GitHub repository and I’ll only share context in the blog.

Please be sure to read through the prerequisites section of Part I and confirm you have the necessary permissions (e.g. Power BI Admin role) and software installed such as the latest Power BI Management PowerShell modules.

Check it out for five more scripts. Brett also teases a part 3, but you have to wait until after the credits sequence to see it.

Comments closed

Thinking About Power BI Premium Per User

Published 2021-03-16 by Kevin Feasel

Martin Schoombee has an analysis for us:

When Power BI Premium was released in June 2017, many of us felt left out as the jump from Pro to Premium was just too high…unless you were an Enterprise-level customer that could afford the ~$5,000/month price tag. The economics just didn’t make sense for most people, and the size limitations of Power BI Pro was still a concern to companies who wanted to jump on board.

At Microsoft Ignite this year, we finally got the answer we were waiting for. The pricing for Power BI PPU (Premium Per User) was announced and will be generally available in April 2021. With the price set at $20/user per month, which is better than I expected, I think the landscape is going to change in a significant way. Let me explain…

I’m tied in reasonably well to the Power BI blogging space and I’m a bit shocked that Power BI Premium Per User hasn’t been the recipient of a lot more excitement. I typically live on the budget side of things and disliked the $5K entry point for Premium. At $20 per user, I’m quite happy. Martin does point out a third scenario, however: using Azure Analysis Services plus Power BI Professional. But even in his chart, I think I’m still picking Premium Per User over AAS + Pro due to the additional functionality at approximately the same price.

Comments closed

Row-Level Security and UseRelationship

Published 2021-03-10 by Kevin Feasel

Teo Lachev points out an issue when combining row-level security with the USERELATIONSHIP() function in a Tabular model:

You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:

The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.

Read on to learn what the issue is and one potential workaround.

Comments closed

Power BI: Parquet Files and Streamed Binary Values Error

Published 2021-03-08 by Kevin Feasel

Chris Webb explains an error:

If you’re using the new Parquet connector in Power BI there’s a chance you will run into the following error:

Parameter.Error: Parquet.Document cannot be used with streamed binary values.
Details:
[Binary]

This isn’t a bug or anything that can be fixed, so it’s important to understand why it occurs and what you can do about it.

Click through for the explanation.

Comments closed

Power BI Object Level Security

Published 2021-03-04 by Kevin Feasel

Gilbert Quevauvilliers shows us an example of Object Level Security in Power BI:

My example which I am going to detail below will show you how I will restrict a user from viewing sales data. The same user will be able to see Quantity amounts. This becomes really powerful because not all users need to see all the data.

My goal here is to show you how to the basics on how to use Object Level Security. Yes, there are more advanced options to configure a combination of Row Level Security and Object Level Security.

By using Object Level Security, it means that I can now have a single model which can be used for Financial and Non-Financial reporting.

Read on for an example.

Comments closed

Power BI Connector for Databricks

Published 2021-03-01 by Kevin Feasel

Stefania Leone, et al, announce general availability of the Power BI connector for Databricks:

We are excited to announce General Availability (GA) of the Microsoft Power BI connector for Databricks for Power BI Service and Power BI Desktop 2.85.681.0. Following the public preview, we have already seen strong customer adoption, so we are pleased to extend these capabilities to our entire customer base. The native Power BI connector for Databricks in combination with the recently launched SQL Analytics service provides Databricks customers with a first-class experience for performing BI workloads directly on their Delta Lake. SQL Analytics allows customers to operate a multi-cloud lakehouse architecture that provides data warehousing performance at data lake economics for up to 4x better price/performance than traditional cloud data warehouses.

This is easier to work with than the Apache Spark connector and it looks like it should be faster than that connector as well.

Comments closed