Sending Power BI Alerts To Users Via Teams Using Power Automate And Adaptive Cards

A few months ago my esteemed colleague Patrick LeBlanc published a great video showing how you could use Power Automate (formerly known as Microsoft Flow) to send an email when a Power BI alert is triggered. This post is going to cover almost the same ground, except that I’m going to show you how to send an alert to a user via Teams and instead of dynamically generating text for the message I’m going to use an Adaptive Card to format the alert data.

Although Adaptive Cards have been around for a while now, I’m slightly embarrassed to admit that I only learned about them a few weeks ago when I read this blog post. Full details about Adaptive Cards can be found at https://adaptivecards.io/; for those of you too lazy to click the link, here’s the official description and a short explainer video:

Adaptive Cards are platform-agnostic snippets of UI, authored in JSON, that apps and services can openly exchange. When delivered to a specific app, the JSON is transformed into native UI that automatically adapts to its surroundings. It helps design and integrate light-weight UI for all major platforms and frameworks.

 

There’s documentation on how Adaptive Cards can be used in Power Automate here and a comprehensive video here:

 

When I first saw them I knew that they would be perfect way to send Power BI alerts to people. Here’s a simple example showing how to use them for this purpose.

First of all, I set up a simple Power BI report and dashboard containing a card visual with a value in and created an alert on it, just as Patrick shows in his video. Then I went to the Adaptive Cards designer at https://adaptivecards.io/designer/ and created an Adaptive Card to display alert data:

AdaptiveCardsDesigner

Then I created a simple Flow in Power Automate with the “When a data driven alert is triggered” trigger (again, just as Patrick shows) but then used the “Post your own adaptive card as the Flow bot to a user” action:

Flow1

I pasted the JSON definition of the Adaptive Card I created in the designer earlier into the message of this action, and made it dynamic:

Flow2

…and having done that, once the alert was triggered in Power BI I got a message in Teams from the Flow bot with the alert data shown in my Adaptive Card:

AdaptiveCardTeams

My design skills are very limited and this is only my first attempt at creating an Adaptive Card, but hopefully you can see what is possible. You could also use a different data source, such as the “Transform data using Power Query” action (similar to what I showed here), instead of using a Power BI alert to supply the data.

Even better, as the blog post I referred to earlier explains, Power Automate now has a preview feature that allows you to wait for a response from an Adaptive Card that collects user input as well as displays data. Imagine a scenario where:

  • A Power BI alert is triggered because the number of widgets in your warehouse is getting dangerously low
  • This alert triggers a Flow that sends a message as an Adaptive Card to a user via Teams which not only displays the current stock level but also asks the user how many widgets to order
  • The Flow waits for a response and, when it gets one, goes away and orders that number of widgets

Hmm, this sounds like a good subject for a future blog post…

 

The “Visual Has Exceeded The Available Resources” Error In Power BI

Very occasionally you may find that a report that is slow to render – but still renders successfully – in Power BI Desktop shows the error “Visual has exceeded the available resources” for some visuals when published to the Power BI Service:

Main Error

Clicking on “See details” will show a dialog that looks something like this:

SecondError

This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.

Please try again later or contact support. If you contact support, please provide these details.More details Resource Governing: The query exceeded the maximum memory allowed for queries executed in the current workload group (Requested 1048580KB, Limit 1048576KB).

 

The official Power BI documentation has similar advice to what’s shown in this dialog about what to do here, but what’s really going on?

The information in the “More details” section of the section dialog gives you a clue: in this case it’s resource governance. When you run a DAX query in Power BI it will always use a certain amount of memory; inefficient DAX calculations can cause a query to try to grab a lot of memory. In Power BI Desktop these queries may run successfully but be slow, but the Power BI Service can’t just let a query use as many resources as it wants (if it did, it may affect the performance of other queries being run by other users) so there is a resource governor that will kill queries that are too resource hungry. In the case of the visual above the query behind it tried to use more than 1GB of memory and was killed by the resource governor.

The limits enforced by the resource governor cannot be changed in Power BI shared capacity. The limits are higher in a Premium capacity and vary depending on the capacity size, and if you are a Premium Capacity Admin there are a number of different settings on a capacity you can change that will affect this, described here. For example the Query Memory Limit (%) setting controls the amount of memory that any given query can use before the resource governor kicks in.

To demonstrate this I moved the workspace containing the report in the screenshot above to a Power BI Embedded A4 capacity and then changed the Query Memory Limit % setting to 1.

QueryMemoryLimit

This resulted in the error dialog showing that the resource governor killed the query when it tried to use 1% of the 25GB of RAM available in an A4, which is 262144KB:

LowerLimit

While it may be useful to change this setting if you’re the admin of an overloaded Premium capacity, the real solution is to tune the DAX calculations used in this visual (I suggest you read the section on materialisation starting on p568 of “The Definitive Guide to DAX” or this older article which covers the same ground) so they use less memory and return faster or change what the visual displays.

You may see the “Visual has exceeded the available resources” error in other scenarios where the resource governor is not involved. For example, setting the Query Timeout setting on the capacity to 5 seconds like so:

TimeoutSetting

…means that the visual now times out after five seconds instead, and the “See details” dialog shows the following error instead:

Timeout

More details The XML for Analysis request timed out before it was completed. Timeout value: 5 sec.

 

Power BI Dataset Refresh Scheduling Using Outlook And Power Automate

When the new “Refresh a dataset” action for Power Automate (formerly Flow) was released last year I couldn’t help thinking there must be something really cool you could do with it – I just didn’t know what. There are lots of great resources explaining the basics (see Jon Levesque’s video here for example) and Adam Saxton did a nice demo here using the “When an item is modified” trigger to show how to refresh a dataset when a value in a SQL Server table is updated, but that’s it. So I got thinking about the types of problem it could solve and the fun I could have with it…

While Power BI’s scheduled refresh functionality is great, it doesn’t give you as much flexibility as you might need. For example:

  • You might want to schedule your dataset to refresh only on weekdays, not weekends, and you might also want to cancel refresh on certain days like public holidays. You might also only want to refresh a dataset on a monthly basis or at less than the half-hourly granularity that the UI allows. Why? Perhaps because it’s important to minimise the load you put on your source systems; it’s also the case that for many cloud data sources the more data you read, the more you pay.
  • If you have a lot of datasets to refresh you might want to control which datasets are refreshing in parallel, again to reduce the load on your data sources and if you’re using Premium, to reduce the load on your capacity. It’s hard to get an overview of when all your refreshes are scheduled in the Power BI Portal and manage what’s happening when.

The ideal way to view when multiple events are scheduled is a calendar and we’ve got great calendar functionality in Outlook. What if you could schedule refresh of your datasets from a calendar in Outlook? It turns out to be easier than you might think! Here’s how.

The first thing I did was create a new calendar in Outlook called Power BI Refreshes:

Calendar

In this calendar I created appointments (either recurring or one-off) for every dataset refresh:

Cal2

For each appointment, I entered the unique identifier of the dataset in the Title and the unique identifier of the workspace in the Location like so:

Event

You can find these unique identifiers by going to the Settings screen for your dataset in the Power BI Portal and looking at the url:

Settings

Cathrine Wilhelmsen has more details on finding these ids here.

Last of all, I created a very simple Flow in Power Automate:

Flow

The “When an upcoming event is starting soon” trigger is fired when each of the appointments on the Power BI Refreshes calendar is about to start. It then passes the Location and Subject from the event – which of course contain the ids the workspace and dataset to be refreshed – to the Refresh a dataset action, which does the refresh.

This isn’t something I recommend putting into production but I think it’s very interesting as a proof-of-concept. I guess Logic Apps would be a more robust alternative than Power Automate and I would want to be 100% sure that events fired when I was expecting them to fire, so some thorough testing would be needed. I’m not experienced enough with Power Automate/Logic Apps to know if I’m doing the right thing, to be honest. I also feel like using ids in the meeting title and location is a bit hacky and there must be a nicer way of handling this.

On the Power BI side, it’s worth remembering that when a refresh is kicked off in Power BI the actual refreshing only starts when the Power BI Service has the required resources available, and especially in Shared capacity this can involve a wait of several minutes. What’s more the “Refresh a dataset” action does not know whether the refresh it kicks off succeeds or fails; I guess if you wanted to handle retries or notifications on failure then you would need to call the Power BI API get the refresh history of a dataset – there’s no built in action to do it, but it’s possible with a Power Automate custom connector.

If you have any thoughts about this, ideas on how to make this better or if you do put something like this into production, let me know – I would love to hear from you!

Performance Overhead Of Using Power BI Custom Visuals

Following on from the series of posts I wrote last month (starting here) about testing the performance of Power BI reports in the browser, I would now like to highlight one possible cause of slow report rendering performance: the over-use of custom visuals. Before we go any further, though, let me be absolutely clear about something:

I am NOT saying that custom visuals are bad and that you should never use them. Custom visuals are an incredibly useful feature of Power BI and be used to solve a wide range of problems.

However if you are using an older browser to view reports, have a slow PC and/or have a slow network connection, then having a large number of custom visuals on the same page can be bad for performance. In fact having a large number of any kind of visual on the same report page can be bad for performance, as Mike Carlo shows here. Even so there is an extra performance overhead when you use a custom visual instead of one of the built-in visuals.

Take for example, the following report. The dataset it uses has one table with one column and one row containing the value 1, so retrieving data from it is going to be super-fast. This page has 26 Advance Card custom visuals on it displaying the value 1 from the dataset:

image

Using Chrome DevTools and the functionality on the Network tab to simulate a cold cache and a fast 3G network (as I show here), this page takes 45 seconds to render. Another page on the same report has 26 instances of the built-in card visual to show the same data:

image

Under the same simulated network conditions, Chrome DevTools shows this page takes 25 seconds to render. So the version of the page with the custom visuals is 20 seconds slower to render than the version with the built-in visuals, although this is obviously an extreme example. Without the network throttling (but with the cold cache) the version with the Advance Card visuals loads in 8.5 seconds and the version with the built-in cards takes 5.5 seconds; still a noticeable difference but maybe not one that your users would complain about.

Let me be clear about something else here:

I am NOT saying that the Advance Card visual is badly implemented – it’s a great visual and one that is used by a lot of people without any problems. What I am saying is that any custom visual comes with some kind of performance overhead and the more custom visuals you use on a page, the more the overhead will be.

How much overhead there is will depend on a lot of different factors so you should test to see if there is any noticeable impact from using custom visuals in your own reports – it’s very likely that there won’t be one. I would go as far as saying that 99% of the time you won’t see any impact. What’s more, there are improvements being made to the Power BI Service all the time and it’s highly probable that some optimisations for the loading of custom visuals will be made in the future.

One last tip for you. If you have imported a custom visual in Power BI Desktop but are not using it, you should always delete it (as shown here). There is another, smaller overhead associated with even having a custom visual imported into a report – although, again, this may well change in the future.

%d bloggers like this: