This blog post explains how to perform ad-hoc datasets refresh automatically in self-service Power BI model-based organization. Most of the time, Administrators may look for an option to automate this requirement, so this post helps admins who wants to automate the ad-hoc datasets refresh request.
Most of you know about how the self-service BI model works but still I need to explain to make the context clear. In self-Service BI models, administrators will setup the Power BI environment and on-board the users to the power bi platform. In detail, administrators will create workspaces for users and apply security and governance to manage and maintain the environment.
Users will have contributor access to publish the report to their Dev or UAT workspaces. Administrators will help the users to migrate the reports to PROD workspace. Users will not have access to PROD workspace most of the time. User / Business Users will have access to PROD workspace app to see their reports.
In this scenario, Users can ask admin to setup the schedule refresh and datasets will refresh on the set schedule time. In case, if user want to refresh their PROD datasets on-demand or ad-hoc basis then user should request admin to refresh the dataset may be via email.
If admin is available, then manual refresh can happen quickly but if admin is not available or support team is not available for sometime then user should wait until admin refresh the datasets or next schedule refresh time. The turn around time of the request will be longer than expected most of the time. This also another workload to admins to refresh the dataset manually for every user who are requesting to refresh their datasets.
How to automate this ad-hoc refresh? This is will be question for most of the administrators.
This post gives solution with the help of SharePoint and Power Automate.
SharePoint – You can use SharePoint online or on-prem with Power Automate. Power Automate has a default Power BI connector to refresh the datasets by passing the workspace name or id and datasets name or id.
SharePoint Side Steps
You need to create a SharePoint list with below columns.
- Power BI Workspace ID
- Power BI Dataset ID
It will be difficult to users to enter their PROD workspace ID and dataset ID. They will not have access to their PROD workspace, so admin has to share their workspace and dataset id. As I said, this is automated solution, so this step will not effective way to manage.
But the above are mandatory to pass into Power Automate. To make the process easy to users, admin can create three SharePoint lists.
- Power BI Workspace
- Power BI Datasets
- Power BI Ad-hoc Dataset Refresh – Final
The first 2 lists dependent lists for the 3rd Final List.
Power BI Workspace SharePoint List
This list should have below columns.
- Workspace Name
- Workspace ID
Enter all the PROD workspace name and ID. Again, this can be automated with the help of Power BI REST API and Power Automate.
Power BI Datasets
This list should have below columns.
- Dataset Name
- Workspace Name – Lookup column from Power BI Workspace SP list
- Dataset ID
Choose the workspace from the drop down and enter the dataset name and ID.
Power BI Ad-hoc Dataset Refresh – Final
This is SharePoint list where you need to provide access to users. This list will have below columns
- Workspace Name – Lookup
- Dataset Name – Lookup
- Workspace ID – Hidden
- Dataset ID – Hidden
- Status – Default value as “Not Triggered”
I have applied the cascading list option and built the SharePoint list to get the above look and feel (drop-downs). This is possible with the help of script editor webpart.
User can choose their workspace and dataset from the drop-down. Also, admin can enable the security in SharePoint to see only own user entries.
Power Automate
We need to setup the trigger and actions to run the workflow in Power Automate.
Trigger will be from SharePoint – When an item is created in SharePoint list.
Action – it will be Power BI Dataset Refresh.
By default it will show the list of workspace from your Power BI tenant but here it should be dynamic so you need to choose “Enter custom value” and pass the SharePoint list – Workspace ID value and same on Dataset – Dataset ID Value.
Finally, update the SharePoint list column – Status into “Successfully Triggered”.
That is, it. When user add their entry in SharePoint List then Power Automate will trigger the dataset refresh and update the SharePoint list as “Successfully Triggered”.
The full model screen
Next Step
We still can automate further like send email to users when the status changed on the SharePoint list and we may not sure whether the datasets is successfully triggered or not so we can use Power BI REST API to get the last refresh status and update the same to partner about the status of the dataset refresh.
Share your comments below.
Happy Learning!!!
Leave a Reply