Schedule a PowerBI dataset refresh on the "quarter" hour ???

Question

Schedule a PowerBI dataset refresh on the "quarter" hour ???

sgmunson

SSC Guru

Points: 110640
More actions
February 21, 2023 at 4:31 pm

#4152391

Hi all,
I know very little about Power BI, but I am hopeful that someone here has enough knowledge to know whether or not it is even possible to schedule a dataset refresh on the quarter-hour instead of merely on the half-hour or top of the hour. I know that with SSRS, I can use any given minute of the day to schedule a report run or subscription. What is the scoop with Power BI in that regard? Are we really limited to scheduling only on the hour or half-hour ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 23, 2023 at 8:08 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16147 More actions · Answer 1

you talking about power bi report server on prem or Power Bi service on cloud? if on prem scheduling is the same as with SSRS (PBIRS is a superset of SSRS)

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

Refresh schedule availability is based on the license level of PowerBI, but it can be refreshed manually more frequently.

I would question what utility refreshing that frequently actually has for your solution. BI reports become better over time as the data it consumes matures. Very fresh data can actually be harmful to a BI report if it consumes data that was incorrect or incomplete.

i.e, you do three sales per day and have something involving sales trends in the report. You refresh the model when the third sale is entered, but not complete, containing no data for the sale amount. your sales are then skewed by 33% until the next refresh a few hours later.

Or, someone types in the two zeroes for the two decimal places that were not necessary to be typed in. Now you have a sale that is recorded as 10 million dollars instead of 100 thousand dollars, your sales report is now off by 3300% and the person who can fix it won't be in until tomorrow but the weekly sales meeting is in 30 minutes.

In both these cases, neither of the bad data records provided any value to the report, the conclusions were much the same without them, but their inclusion has seriously harmed the report at the moment the report had to perform.

If you do need very fresh data, then you can use the direct query mode that pulls the data each time the data is run. Many of the options available in PowerBI aren't available in Direct query mode, but most of the options excluded are useful only working with mature data, not fresh data.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

frederico_fonseca wrote:

you talking about power bi report server on prem or Power Bi service on cloud? if on prem scheduling is the same as with SSRS (PBIRS is a superset of SSRS)

I'm not 100% familiar with our configuration, but the dataset, on refresh, runs a stored procedure whose execution duration within a good time window can be just a couple of minutes, but in a problematic window, goes horribly south and can run for more than 15 minutes. I can assure you that the query is well-tuned. The primary issue is the remainder of the environment, and until we can get more capacity in place, just need to understand what is possible versus what is not. I know that the stored procedure database is on premise, but I have no idea about the PowerBI server or ANY of it's pieces...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

CreateIndexNonclustered wrote:

Refresh schedule availability is based on the license level of PowerBI, but it can be refreshed manually more frequently.
I would question what utility refreshing that frequently actually has for your solution. BI reports become better over time as the data it consumes matures. Very fresh data can actually be harmful to a BI report if it consumes data that was incorrect or incomplete.
i.e, you do three sales per day and have something involving sales trends in the report. You refresh the model when the third sale is entered, but not complete, containing no data for the sale amount. your sales are then skewed by 33% until the next refresh a few hours later.
Or, someone types in the two zeroes for the two decimal places that were not necessary to be typed in. Now you have a sale that is recorded as 10 million dollars instead of 100 thousand dollars, your sales report is now off by 3300% and the person who can fix it won't be in until tomorrow but the weekly sales meeting is in 30 minutes.
In both these cases, neither of the bad data records provided any value to the report, the conclusions were much the same without them, but their inclusion has seriously harmed the report at the moment the report had to perform.
If you do need very fresh data, then you can use the direct query mode that pulls the data each time the data is run. Many of the options available in PowerBI aren't available in Direct query mode, but most of the options excluded are useful only working with mature data, not fresh data.

Yep, that is certainly plausible, but I get the feeling that this report should probably be in SSRS. We also are straining our capacity limits, and the stored procedure for this particular dataset is already tuned about as good as it can get. The primary issue is the overall environment in certain time-windows... So just looking to understand why the technician that can adjust the schedule for refreshing the dataset only has the choices of on the hour or on the half-hour...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 5

Ok, so you are asking for scheduling start time rather than refreshing every quarter hour.

Its been a while since I worked with that and I don't remember well enough to say what could be possible there. If the problem is the stored procedure extracting the data however, what you could do is schedule the procedure to run separately, store the results into a table(s) and then reference that flattened out table in the powerBI report instead of the stored procedure

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Indeed, the problem is primarily one of scheduling. But now that you mention it, simply moving the stored procedure into a SQL Agent Job and having it TRUNCATE and then populate a table would solve the issue and allow the scheduling of an updated dataset that is just a view on the populated table, refreshed as often as it needs to be from a PowerBI perspective. Thanks!

This reply was modified 1 year, 8 months ago by sgmunson. Reason: grammar correction

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Michael L John One Orange Chip Points: 27053 More actions · Answer 7

sgmunson wrote:

Hi all,
I know very little about Power BI, but I am hopeful that someone here has enough knowledge to know whether or not it is even possible to schedule a dataset refresh on the quarter-hour instead of merely on the half-hour or top of the hour. I know that with SSRS, I can use any given minute of the day to schedule a report run or subscription. What is the scoop with Power BI in that regard? Are we really limited to scheduling only on the hour or half-hour ?

In the Power BI portal, you can only schedule a refresh at the top or bottom of the hour.

However, you can use PowerShell and call the Rest API to trigger a refresh. You can schedule it using whatever your favorite scheduling tool may be.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

sgmunson SSC Guru Points: 110640 More actions · Answer 8

That's handy, Michael, but we're not ready as a company to embrace the use of PowerShell. I'm going to create a new table that we can refresh via a SQL Agent job, and then a view on top of it for Power BI to access.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Michael L John One Orange Chip Points: 27053 More actions · Answer 9

sgmunson wrote:

That's handy, Michael, but we're not ready as a company to embrace the use of PowerShell. I'm going to create a new table that we can refresh via a SQL Agent job, and then a view on top of it for Power BI to access.

I had set up a process that looked for failed refreshes, and would automatically refresh them. When I left that position, I forgot to bring all of the Powershell scripts that I wrote(stole!) with me.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 10

sgmunson wrote:

That's handy, Michael, but we're not ready as a company to embrace the use of PowerShell. I'm going to create a new table that we can refresh via a SQL Agent job, and then a view on top of it for Power BI to access.

Not sure but you might be able to pull it off as a CmdTask in an SQL Server job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)