In part 1 of this series I showed how you could create a very simple custom connector for Power Automate that allows you to call the Power BI Enhanced Refresh API. However, I didn’t show what I think is the main reason you’d want to build a custom connector: the ability to refresh specific tables or partitions in a dataset rather than the entire dataset. The main reason you’d want to do this is to speed up refresh performance, and there are two reasons why it can do so:
- Some tables in your dataset may not need refreshing every time because the data in them hasn’t changed, so not refreshing them saves you time and CPU.
- Refreshing the partitions in a table and increasing the maxParallelism property can also significantly speed up refresh performance, as I showed here.
This is a very common requirement and there are a lot of blog posts and videos out there on the subject but I think using a Power Automate custom connector is probably the easiest way of doing this, although it only works for datasets in a Premium or PPU capacity.
To implement an Action that does this in an existing connector, click on the New Action button and follow the instructions from my last post. Fill in the fields in the General box:
Then click on the Import from sample button, select the POST verb, enter the URL
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes
…leave the Headers box blank and then, in the Body box, enter the sample Body from here:
{
"type": "Full",
"commitMode": "transactional",
"maxParallelism": 2,
"retryCount": 2,
"objects": [
{
"table": "DimCustomer",
"partition": "DimCustomer"
},
{
"table": "DimDate"
}
]
}
[Note that you can’t have two Actions with the same URL in the same custom connector]
There’s some extra work to do here though. Click on the dropdown on the body parameter in the Request box and click Edit:
Next, click Edit under “table” in the Parameter section:
…and edit the title and description to reflect the fact that this parameter will contain the list of tables and parameters you want to be refreshed:
Once you’ve saved the connector you can test it – but one thing that caught me out is that the Test step in the custom connector designer doesn’t actually handle the tables and partitions parameter properly (something mentioned in passing here) so you’ll want to test this in a real flow. Here’s an example that refreshes the DimDate table and two partitions (FactInternetSales2001 and FactInternetSales2003) of the FactInternetSales table:
Notice that you can add as many tables or partitions to be refresh as you want by clicking the Add new item button; if you want to refresh a table just enter the table name and leave the partition box empty, but if you want to refresh a partition you need to fill in both the table name and the partition name.
And that’s it, at least for now. Next time I’ll take a look at some of the options for datasets that use incremental refresh.
Gracias por compartir Chris 🙂
Thanks for these blogs. In regards to performance, I agree that partitions are critical.(Especially for “structured” data sources which need to execute a substantial amount of single-threaded PQ in order to spool the required data out to our dataset. If we partition by year/month then the total duration of these refresh operations can be decreased *dramatically*).
I appreciate the way partitions allow us to solve performance problems. But I’m in a catch-22 with one of my models. I was deciding between publishing the model from the Analysis Services extension for VS, *or* publishing via Power BI desktop. On the VS side, I cannot use the “web” connector for my “structured” data source, but I can create partitions. On the Power BI desktop I can use a “web” connector but I cannot create partitioning by fiscal year!
I know Microsoft is trying to accommodate the requirements of all types of users (I.T. users as well as “citizen developers”). And I normally don’t have trouble picking between VS-extension or PBI-desktop for a given dataset. But in the case that I need to use a “web” source, along with custom partitions, I’m totally stuck. Can you please let me know when “web” connectors (and “custom connectors”) will be available for use from the AS extension for VS? Is there any way to inject partitions into the back door of my PBIX models (similar to how we incorporate calculation groups via Tabular Editor)?
It’s very unlikely that any new connectors (or custom connectors) will be supported in Visual Studio. I use Tabular Editor to create partitions but you can only do so on datasets in the Power BI Service, so if you do that you need to use tools like ALM Toolkit for deployment to avoid overwriting these partitions.
>>I use Tabular Editor to create partitions but you can only do so on datasets in the Power BI Service…
Thanks for the tip. I was aware of this, but haven’t tried it yet. There are a couple reasons I haven’t pursued this. (1) I am leery of using third-party tooling for models. I’d like to avoid the risk of not having full support from Microsoft on the tooling side of things. (Side note: I have a hard enough time getting support straight from the Power BI PG, without having to work thru an intermediate community. The PG can be unresponsive to our technical problems for months at a time.) I do generally like to use community projects as a rule. But the Microsoft tabular technologies aren’t sufficiently open source, so it seems unadvisable for anyone to build up a community on top of it. And (2) the tabular partitions need to be a first-class member of the model, not an afterthought that is updated after deployment.
IMHO, The Visual Studio modeling experience is still superior to PBI desktop. Are you saying that they aren’t going to invest in that anymore? We have been pretty successful deploying our bim’s to PBI premium from VS, and it seemed like Microsoft had an ongoing goal for fully supporting the deployment/management of models via XMLA.
Thanks Chris.
I tried this and the testing for the custom connector went all good when I initially set it up. I was able to create a flow and run it a few times, every time the flow failed but refresh was triggered 🙂
Things changed the next day. The flow succeeded and the refresh was never triggered. I recreated the custom connector, but no luck. I can’t get it to work consistently throughout the day. It works a couple times and then no refresh is ever triggered even when the run/test succeeds.
I have Power BI Pro license and I am using a dataset in premium workspace for testing.
Wondering if there is any limit on number of refreshes that can triggered per dataset/per day? Is there any licensing constraint on Power Automate side as this custom connector is a premium connector?
Hi I had a similar issue and was receiving a 403 error. It seems like the solution is to turn off the Asynchronous Pattern setting for the custom connector action as discussed here: https://community.fabric.microsoft.com/t5/Service/Enhanced-REST-API-dataset-Refresh-Forbidden-403/m-p/2620736
His Chris,
If I were to refresh multiple partitions for one table, what would my request body look like?
I’m just about to set up a single table refresh. We are working with Premium per user, but starting to wander into the world of Power BI Embedded. So will there be any further licencing issues (ie charges for Embedded) by taking this route?
Well Power BI Embedded is bought and paid for in a completely different way, and is intended for building applications for sharing with external customers rather than for internal BI use. If your users are all internal you should be using Premium or Fabric capacities.
Sorry Chris – didn’t make myself clear. There is no need for us to be using Embedded, but Does following your cracking blog here trigger the need for Embedded licences?
No, it doesn’t