Press "Enter" to skip to content

Category: Power BI

Creating a Power BI Report Book

Published 2020-02-21 by Kevin Feasel

Teo Lachev shows us how to build a Power BI report book:

Scenario: Management has requested an easy way to view a subset of strategic reports located in different Power BI workspaces. You can ask the users to mark reports and dashboards as favorites so they can access pertinent content in the Favorites menu, but you’re looking for an easier configuration, such as to create a book of reports with a built-in navigation that organizes reports in groups (like a table of contents), such as the screenshot below demonstrates.

Click through for the instructions as well as a discussion on why you wouldn’t necessarily want to build a Power BI app for this.

Comments closed

Options for Read-Only Licensing with Power BI

Published 2020-02-20 by Kevin Feasel

Reza Rad explains that, depending on how much you’re willing to pay, there are ways of letting users view your dashboards for free:

In most of my presentations all around the world, I still get this question often: “Is there a Read-Only license for Power BI?”, and often starts with “I have some end-users, who are not building any reports, I don’t want to pay for Developer License for them”. I have written about Licensing in Power BI previously, however, I believe that the article is not explaining it clearly enough and there are still some questions around it. So here I am going to talk about this only: The Read-Only license for Power BI.

Read on for the answers. It’s not all terrible news, but at the very low end, the answer isn’t great.

Comments closed

Power Apps and Read-Only SQL Data Sources

Published 2020-02-19 by Kevin Feasel

David Eldersveld diagnoses a weird issue:

Recently when working with a table in Azure SQL Database, I came across an issue that manifested itself in Power Apps in a few ways.

The message stated that the data source is read-only, when I knew that my SQL credentials had the correct permissions to insert and update table records. I was successfully doing this with other tables, so SQL was not the issue.

Attempting to use functions like Remove and Patch provided the message, as did Form.Edit.

The solution kind of makes sense after the fact but there’s no way I’d have guessed it.

Comments closed

Reducing Visual Count to Improve Performance

Published 2020-02-18 by Kevin Feasel

Chris Webb explains that you might get better performance in Power BI with fewer visuals:

Before we go any further, I don’t want you to go and change your reports if you’re not going to get any benefit from doing so. Use Performance Analyzer (as shown here) to determine which visuals on your report are the cause of slow performance – there’s no point redesigning visuals that are fast anyway.

As a general rule the more visuals you put on a report page the slower it’s going to get. It’s logical if you think about it: the more visuals there are, the more queries have to be run against your dataset and the more work Power BI has to do to render the report. I know there is a tendency to try to pack as much information onto a page as possible and this often happens when someone else has designed the report you’re trying to build, but you should always try to resist this. Splitting a single large page into multiple smaller pages, using slicers or filters to reduce the amount of data shown at any one time and avoiding gigantic Excel-like tables are a good idea.

It certainly doesn’t mean “get rid of all of your visuals;” after all, speed is only one part of the story. Read the whole thing.

Comments closed

Monitoring Power BI Data Gateway Performance

Published 2020-02-17 by Kevin Feasel

Olya Musokhranova shows how we can track throughput and utilization of the Power BI On-Premises Data Gateway:

In June 2019, Microsoft made Gateway Monitoring much easier via the addition of structured logs that can be enabled in the configuration file, and by providing a starter Gateway Performance Power BI report template to visualize the results.

These logs give full access to the information, minimizing the need to pull counter data from IT management systems, like the Microsoft System Center Operations Manager (SCOM); or run the Windows Performance Toolkit and dig through dust-covered gateway info and error log files; or pull refresh error messages from the Power BI Service. Enabling structured Gateway Monitoring logging doesn’t add any significant load to the server, so that’s another win.

Read on for a schema and inspiration on how to set up and configure your own dashboard.

Comments closed

Power BI Query Diagnostics

Published 2020-02-17 by Kevin Feasel

Paul Turley has a video covering Power BI query performance:

This post demonstrates how the order of steps added to a query can make a big performance difference and drastically effect the number of steps generated by the designer. I’ll demonstrate how to use the new query Diagnostics tools to compare and understand query performance.

The Power Query Editor for Power BI simplifies data transformation processing by generating query steps for each action you perform in the query designer. This whiteboard diagram shows the high-level flow of information through a Power BI solution. Every query has a source (“SRC” in the diagram) followed by a connection. The query consists of a series of transformations (“XForm”) prior to populating a table in the data model.

Read on for a high-level explanation followed by a video which covers the Query Diagnostics feature.

Comments closed

Storing Power BI Audit Logs in Blob Storage

Published 2020-02-17 by Kevin Feasel

Gilbert Quevauvilliers works around a built-in constraint with Power BI Audit Logs:

With the new Power BI Get-PowerBIActivityEvent I wanted to find a way where I could automate the entire process where it all runs in the cloud.

One of the current challenges with the Audit logs is that they only store 90 days, so if you want to do analysis for longer than 90 days the log files have to be stored somewhere. Why not use Azure Blob Storage?

Whilst these steps might appear to be rather technical if you follow them and you have access to an Azure Subscription you can do this too.

Gilbert warns us up-front that this will be a lengthy post and that is quite true. But if you need to hold those audit logs more than 90 days, this is a great way of doing so.

Comments closed

Finding the Query Used in DirectQuery Mode

Published 2020-02-14 by Kevin Feasel

Kasper de Jonge shows us how we can find which query ran in DirectQuery mode to populate a Power BI data set:

When you are optimizing your DirectQuery model and you have done all the optimizations on the model already, you might want to run the queries generated by Power BI by your DBA. He then might be able to do some index tuning or even suggest some model changes. But how do you capture them? There are a few simple ways that I will describe here.

Read on for 3 1/2 such methods.

Comments closed

Copying Measures between Power BI Datasets

Published 2020-02-13 by Kevin Feasel

Tomaz Kastrun walks us through copying measures between datasets and files in Power BI:

Measures in Power BI document are DAX based aggregations for slicing and dicing the tabular datasets using evaluate and filter context and and data relationships. Short hand, measures are DAX formulas stored in Power BI as part the dataset.

For showing how easy (once you know) is to copy measures between different dataset, I have created a sample Power BI with diamonds dataset (also available on Github).

I’d call the process convoluted. It does work, but it seems like Power BI should have an easier method.

Comments closed

Removing Duplicates with Power Query

Published 2020-02-12 by Kevin Feasel

Matt Allington wants to remove duplicate records while retaining the last version of the record using Power Query:

Today I was helping a customer with a problem that seemed quite simple on the surface.  She had a data table containing historical customer sales orders (each customer has many orders on different dates).  The objective was to filter this table in Power Query and just load one record for each customer – the one that was the last order date.  To illustrate the problem more clearly, I have adapted the scenario using the Adventure Works database.

Click through for the demo, and also read the comments as there are a lot of interesting attempts to solve the problem there as well.

Comments closed