Press "Enter" to skip to content

Category: Power BI

Managed Identities and Invoking REST Endpoints from Azure SQL DB

Published 2023-03-28 by Kevin Feasel

Imke Feldmann executes a Power BI REST endpoint call from Azure SQL Database:

For Azure SQL Databases there is a very cool new preview feature: “sp_invoke_external_rest_endpoint “. This function allows you to call certain Microsoft API endpoints directly from within your Azure database and write that data back into a table for example.

With that, you can for example create a stored procedure that can be triggered from Power Automate. This is ideal for larger datasets that would require long and slow “apply-to-each” rounds or cumbersome bulk-upload-workarounds.

I was struggling with the authentication when using a system assigned managed identity (“service principal”). Thanks to Davide Mauri for telling me how to fill in the parameters for the DATABASE SCOPED CREDENTIALS to make this work for Power BI:

Click through to see that answer, as well as a demonstration of the entire process.

Comments closed

When Power Query Might Fold

Published 2023-03-27 by Kevin Feasel

Chris Webb explains an ambiguity:

This query connects to the TripPin OData feed (which is public, so you’ll be able to try this yourself) and filters the People table so it only gets the rows where the FirstName column equals “Angel”. If you paste this query into the Advanced Editor in Power Query Online to create a dataflow, you’ll see that the filter shows the “Might Fold” step indicator:

Read on to see what that indicator looks like, as well as an explanation of how Power Query doesn’t always know whether or not something is foldable.

Comments closed

Collaborating with External Individuals in Power BI

Published 2023-03-24 by Kevin Feasel

Marc Lelijveld talks to the outside world:

Let’s imagine you’re running a (fictive) company, and you’re short on data & analytics experts. Therefore, you decide to in hire expertise, and they will help you build your Power BI reports. As an employee of this organization, you rather have them starting sooner than later. But… if you need to request accounts for them at your IT organization, this might take weeks, if not a month to properly setup and run through this process. But what alternatives do you have?

In this blog I will further elaborate on the important things you should think about when working with Externals in Power BI. This blog is based on the session I’ve presented at the Dutch Power BI community day and at SQL Bits 2023 on the same topic together with my colleague Odeta Jankaitienė.

Click through for some of the important decisions you’ll need to make along the way.

Comments closed

Refreshing a Power BI Dataset via HTTPS URL

Published 2023-03-23 by Kevin Feasel

Gilbert Quevauvilliers presses the big red button:

I have found that sometimes there are other systems that are loading data, and once they are complete they then want to refresh the Power BI Dataset.

Another way to do this is to use Power Automate, in which a system or user can request a HTTPS URL and once called that will then refresh the Power BI dataset.

I explain how to do this in the steps below.

Click through to see how to set up that job.

Comments closed

Using Security Groups with Power BI Row-Level Security

Published 2023-03-20 by Kevin Feasel

Soheil Bakhshi has a recommendation for us:

However, managing RLS roles can be challenging if you have a large number of users or if your user base changes frequently. You need to manually assign each user account to one or more roles, which can be time-consuming and error-prone. Moreover, if a user changes their position or leaves the organisation, you must update their role membership accordingly.

This is where Security Groups become handy. 

Soheil explains why and then gives us a step-by-step guide on what we can do to use security groups instead.

Comments closed

Parallelization in DirectQuery

Published 2023-03-20 by Kevin Feasel

Chris Webb shares some insight:

Recently we announced an important new optimisation for DirectQuery datasets: the ability to run (some) of the queries generated by a single DAX query in parallel. You can read the blog post here:

https://powerbi.microsoft.com/en-za/blog/query-parallelization-helps-to-boost-power-bi-dataset-performance-in-directquery-mode/

A few of us on the Power BI CAT team have tested this out with customers and seen some great results, so I thought I’d write a post illustrating the effect this optimisation can have and explaining when it can and can’t help.

Chris has examples of great success, as well as not-so-great success and utter failure, and explains the why behind each outcome.

Comments closed

Tips for Power BI Modeling with ADX

Published 2023-03-16 by Kevin Feasel

Dany Hoter shares some tips on creating star schema models with Azure Data Explorer:

Relationships between DQ tables are created as M:M by default. This is not a problem and even recommended with single direction.

Read on for several tips. What’s interesting as I read this is just how radically different the advice is for ADX utilization versus Power BI utilization, such as using strings to join dimensions to facts. That would be heresy in a Kimball-style model and is a common cause for slow-down in Power BI. Yet that’s the recommendation here for working with ADX, unless I’m misunderstanding Dany’s post.

Comments closed

Power BI Group By Columns

Published 2023-03-15 by Kevin Feasel

Marco Russo and Alberto Ferrari bundle things together:

In Power BI you can specify the unique identifier of a column value by using another column or another set of columns. This feature is currently used by the Fields Parameter feature in Power BI, but it may also be used for other purposes in a model. However, there are several limitations – such as the incompatibility with MDX queries – that reduce one’s ability to use Group By Columns property in many scenarios, so it cannot be used with Excel as a client.

Read on to learn more about how grouping works in Power BI and some of the limitations.

Comments closed

Building a Dimension and Measure Matrix for Power BI

Published 2023-03-14 by Kevin Feasel

Olivier Van Steenlandt does some documentation:

In this blog post, I will guide you through all the required steps to get a Data Model Relationship Matrix in Power BI.

If you don’t know what I mean, I would like to have a straightforward overview where I can see which attribute groups and measure groups I can combine from my Tabular Model in (SQL Server) Analysis Server.

The first thing I thought of was “this is very much like a bus matrix in the Kimball model.” It’s a little different, though, as the rows in the axis pertain to measure groups rather than business units.

Comments closed

Saving Incremental PBIX File Backups

Published 2023-03-14 by Kevin Feasel

Matt Allington saves PBIX Final Report V2 No Wait V3 Draft Demo 2 Copy Copy Copy 3.pbix:

I do a lot of Power BI model and report development; maybe you do too. There’s nothing worse than spending an hour or so developing your model only to have something go wrong and you lose your work. Things that can go wrong include:

  • Your PC/App crashes. Power BI does have auto save, but I prefer that to be the last thing I rely on to save the day rather than the only thing.
  • You make a significant mistake in the approach and need to undo your work (autosave won’t save you with this problem).
  • You make a big mistake in Power Query. There is no undo in Power Query, so if you spend an hour inside Power Query, don’t save, and then make a mistake, there is no way to recover your work.

At the time of writing, there are no version control tools built into Power BI, so as a result it is up to you to manage backups yourself.

Read on for a few tips around backups and file management.

Comments closed