Power BI Fact table refersh

[anusha_2023]

We published P&L financial report and semantic model via import mode currecntly scheduled refresh 8 times a day. Now I received a new request to refresh the semantic model 12 times a day between 8AM to 8PM. This requested setting is only for monthly closing weeks that means one week for each month other days only once a day refresh is fine. Data source is connected  through on- premises gateway using dataflow inside Fabric workspace. Is there any simple solutions or work around I could use for example having two semantic models for one Report so that I can schedule refresh each 8 times or any other ideas. I do have fabric F8 capacity not premium. So looking for easy solutions. Appreciate for your input. 

[Anonymous · Accepted solution]

Hi @anusha_2023 ,

Here are my thoughts and I hope they help: 

1. Refreshing the Semantic Model More Than 8 Times a Day:
With a Power BI Pro license, you are limited to 8 refreshes per day. To exceed this limit, you would need a Power BI Premium subscription. However, there are some workarounds, such as creating multiple copies of the dataset and scheduling them at different times.

2. Power Automate Subscription Needed:
For your team of two developers with F4 Fabric capacity and Power BI Pro licenses, you would need the Power Automate Premium subscription. This plan costs $15.00 per user/month. This should be sufficient for building the flows you need.

3. Power Automate Premium for Developers:
Yes, purchasing two Power Automate Premium subscriptions for your developers should be enough to build and manage the required flows. This plan includes access to premium connectors and other advanced features necessary for your automation tasks.

4. Using Power Automate to Resolve API Limitation:
Power Automate can help you automate the refresh process, but it won’t bypass the 8 refreshes per day limit imposed by the Power BI Pro license. If you need more frequent refreshes, you might consider the workaround mentioned earlier or upgrading to Power BI Premium.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anusha_2023]

Thank you very much for the detailed explanation. I do have couple of more concerns in order to implement this solution.

1. Using Power automate is it allowed to refresh the semantic model more than 8 times a day without Power BI Premium subscription.

2. If I choose to implement this solution which kind of power automate subscription I needed. We are two developers in the team where we have F4 Fabric capacity with Power BI pro license. And reports we are sharing with users using external tool "Reporting hub". 

3. I saw the Power automate plans, if we buy two Power automate Premium for developers is that enough for build this kind of flow.

Since I have not used power automate before I have no idea on this process. Aplologies for basic questions.

4. I am using notebooks for scheduling the semantic models until now there API has limitation to refresh a semantic model for 8 times. 

client.post(f"/v1.0/myorg/datasets/{datasetId}/refreshes")

Is this power automate solution that was suggested above will benefit to resolve my problem.

Thanking you,

Anusha

[Anonymous · Accepted solution]

Hi @anusha_2023 ,

Here are my thoughts and I hope they help: 

1. Refreshing the Semantic Model More Than 8 Times a Day:
With a Power BI Pro license, you are limited to 8 refreshes per day. To exceed this limit, you would need a Power BI Premium subscription. However, there are some workarounds, such as creating multiple copies of the dataset and scheduling them at different times.

2. Power Automate Subscription Needed:
For your team of two developers with F4 Fabric capacity and Power BI Pro licenses, you would need the Power Automate Premium subscription. This plan costs $15.00 per user/month. This should be sufficient for building the flows you need.

3. Power Automate Premium for Developers:
Yes, purchasing two Power Automate Premium subscriptions for your developers should be enough to build and manage the required flows. This plan includes access to premium connectors and other advanced features necessary for your automation tasks.

4. Using Power Automate to Resolve API Limitation:
Power Automate can help you automate the refresh process, but it won’t bypass the 8 refreshes per day limit imposed by the Power BI Pro license. If you need more frequent refreshes, you might consider the workaround mentioned earlier or upgrading to Power BI Premium.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anusha_2023 · Accepted solution]

Thank you very much for the detailed explantion. 

[Anonymous]

Hi @anusha_2023 ,

I think you can create two plan refreshes. One to represent the regular schedule refresh and the other to represent the closing weekly schedule refresh.

Next you can create processes using Power Automate.

To create a process that enables the Closing Week Refresh program.
   1. Log in to Power Automate.
   2. Create a new Process that is triggered by the Plan.
   3. Set the trigger condition to the Closing Week Start Date of each month.
   4. Add an Update Dataset Refresh Plan action and select the Closing Week Refresh Plan.
Create a process to restore a regular refresh schedule.
   1. Create another new Process that is triggered by the Plan.
   2. Set the trigger condition to the closing week end date of each month.
   3. Add an Update Dataset Refresh Plan action and select Regular Refresh Plan.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[suparnababu8]

Hi @anusha_2023 

To address your requirement of refreshing the semantic model 12 times a day during monthly closing weeks and once a day otherwise, you can consider the following approach:

Solution: Using Power Automate for Dynamic Refresh Scheduling

1. Create Two Refresh Schedules:

   • Regular Schedule: Set up a refresh schedule to run once a day for the entire month.
   • Closing Week Schedule: Set up a refresh schedule to run 12 times a day between 8 AM and 8 PM for the closing week.

2. Use Power Automate to Switch Schedules:

   • Create a Power Automate Flow: This flow will dynamically switch between the two refresh schedules based on the current date.
   • Determine Closing Week: Define the closing week for each month in your flow.
   • Switch Schedules: Use the flow to enable the 12-times-a-day schedule during the closing week and revert to the once-a-day schedule for the rest of the month.

Steps to Implement:

1. Set Up Refresh Schedules:

   • Go to your Power BI workspace and set up the two refresh schedules as described.

2. Create Power Automate Flow:

   • Trigger: Use a recurrence trigger to check the date daily.
   • Condition: Add a condition to check if the current date falls within the closing week.
   • Actions:
     • If it is the closing week, enable the 12-times-a-day refresh schedule.
     • If it is not the closing week, enable the once-a-day refresh schedule.

Example Power Automate Flow:

1. Trigger: Recurrence (daily)
2. Condition: Check if the current date is within the closing week.
3. Actions:
   • Enable 12-times-a-day Schedule: Use the Power BI REST API to update the dataset refresh schedule.
   • Enable Once-a-day Schedule: Use the Power BI REST API to update the dataset refresh schedule.

Power BI REST API:

You can use the Power BI REST API to manage the refresh schedules programmatically. Here is an example of how you can use the API to update the refresh schedule:

PATCH https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshSchedule
Content-Type: application/json
Authorization: Bearer {accessToken}

{
"value": [
{
"days": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
"times": ["08:00", "10:00", "12:00", "14:00", "16:00", "18:00", "20:00"]
}
]
}

Replace {groupId}, {datasetId}, and {accessToken} with your actual values.

Thanks!