Power bi Incremental Refresh and Stored Procedures

[Dilantha]

Hi,

I have few questions on below scenarios.

Currently Im retreive data from stored procedures to the power bi desktop. (Without parameters). 

1. Can I implement incremental refresh to above queries ? If not what is the process I need to follow to get that result ?

2. I have implemented stored procedures without parameters, and I implemented global slicer.

    what is the best approach to select date range and add those dates to global filter ?

    currently I merged with date in to global calander (received from SP)

Thank You

[frano72]

Hi @Dilantha - did you ever find a solution to this ?  I have something similar where I want to keep 5 years of data in dataflow but the store proc, i only want it to return the last 5 days and use that to add to the overall dataflow.

[Dilantha]

Thank you for the response,
I earlier saw article about can not apply incremental refresh for the data retreived by stored procedure . Can you explain that please. Is that true or false ?

And if I add incremental refresh it can be only date(12/1/2020) format for RangeStart and RangeEnd ? Do I need to add date/time(12/1/2020 12:00:00 AM) format? Because I have seperated columns for date and time.

Thank You

[v-yingjl · Accepted solution]

Hi @Dilantha ,

"I earlier saw article about can not apply incremental refresh for the data retreived by stored procedure ."

Did you mean the query folding under incremental refresh? 

If so, it did not forbbiden but not recommend becasue this can cause incremental refresh to be very slow, and the process can run out of resources either in the Power BI service or in the on-premises data gateway if used.

If not, coud you please share the article that you have seen it before?

"And if I add incremental refresh it can be only date(12/1/2020) format for RangeStart and RangeEnd ? Do I need to add date/time(12/1/2020 12:00:00 AM) format? "

The data type of the parameters must be date/time, but it's possible to convert them to match the requirements of the datasource. For example, the following Power Query function converts a date/time value to resemble an integer surrogate key of the form yyyymmdd, which is common for data warehouses. The function can be called by the filter step.

(x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

[Dilantha]

"I earlier saw article about can not apply incremental refresh for the data retreived by stored procedure ."

If that line not clear, Im sorry about that. 
Previously I saw article we cannot apply incremental refresh for the data retreived from stored procedure. 

So I need to confirm , 
Is that possible to add incremental refresh to mysql SP?
If I added incremental refresh to stored procedure data , is that affect to loading time, performance and data ?

Sorry for repeating same question again and again, I need clear picture on that. 

Thanks for your patience and help

[v-yingjl]

Hi @Dilantha ,

It is possible to add incremental refresh to mysql SP but you will recevie the warnning message as I previous posted, not forbbiden but not recommend.

get the Mysql sp in power bi

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sashi1811]

did this work .. ? Incremental refresh for SP with data parameters??

[Dilantha]

Thank You so much, It really helped me

[v-yingjl]

Hi @Dilantha ,

1. If want to configure incremental refresh for the source, it should have a date column in it and configure RangeStart and RangeEnd parameters for it to enable incremental refresh, please refer: Incremental refresh in Power BI 

2. Not certain what is your expected. What does select date range and add them to gloabl filter represent? What does global filter represent? The slicer in the report page or the filter on the whole report or others? Could you please consider sharing more details about it for further discussion?

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.