How to automate custom partitioning dataset refresh

[lavankumar1989a]

Hi Everyone,

Need you kind support and experties on enabling dataset refresh in an automated way. In my project we are pulling the data from databricks/unity catalog by using connector as Azure databricks into PowerBI as an import mode. 

Currently we have 5 years data(CY, CY-1, CY-2, CY-3) which has nearly 1B rows and dataset refresh is taking nearly 4hrs to 5hrs and mostly it's failing due to timeout issues.

We received a request from my business that we need daily refresh only 5 quaeters data including current quarter rest would be static as historical. So we approched as partitioning the PBI dataset. So we tried with Tabular editor & enabled the partitions in a dataset fact table and then with help of SSMS we triggered the refresh. it's working fine but it's a manul process.

As we are looking is we need to make this refresh process as automated way instead refresh the specific partition via SSMS. So need your various solutions along with end to end steps to enable it for my projects.

Is there any other way or other approch without tabular & SSMS also please share.

Please share the solution along with the steps or .pbix and please do let me know if any other details or clarifications needed.

Thanks you so much for your kind support.

Thanks,

Lavan

[v-nuoc-msft · Accepted solution]

Hi @lavankumar1989a 

@lbendlin Thank you very much for your prompt reply and here allow me to share some of it.

 Utilize the XMLA endpoint for advanced refresh and partition management. 

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

This method requires your Power BI dataset to be hosted in a workspace assigned to a Premium capacity, as XMLA endpoints are a feature of Power BI Premium.

If your Power BI service is hosted on Azure, you can use Azure Automation to create runbooks that refresh partitions on a schedule.

Regards,

Nono Chen

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

[lbendlin · Accepted solution]

Query folding is NOT a requirement for incremental refresh. It is very desirable, but incremental refresh will also work without it.

Anyway - you can run your XMLA script from anywhere, including Powershell or Power Automate.

[v-nuoc-msft · Accepted solution]

Hi @lavankumar1989a 

@lbendlin Thank you very much for your prompt reply and here allow me to share some of it.

 Utilize the XMLA endpoint for advanced refresh and partition management. 

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

This method requires your Power BI dataset to be hosted in a workspace assigned to a Premium capacity, as XMLA endpoints are a feature of Power BI Premium.

If your Power BI service is hosted on Azure, you can use Azure Automation to create runbooks that refresh partitions on a schedule.

Regards,

Nono Chen

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

[lbendlin]

You are describing the functionality of Power BI Incremental Refresh. Any particular reason for not using it?  It does the partition management for you automatically.  All you need to provide is a datetime value for the partition boundary filter.

[lavankumar1989a]

Thanks @lbendlin for your quick response. 

Why we are not using incremental refresh is, we have lot of power query applied steps for that fact table and due to that it doesn't support for query folding. To leverage incremental refresh along with premium, table should support query folding other wise parameters won't be enable while applying the incremental refresh in PowerBI desktop level.

Hence we partitioned big fact using tabulaer editor and tried refreshing each partition using SSMS and it tooks very very lest time when compared to my original dataset.

So now i am looking to refresh the partitions automatically instead of manully in SSMS. So please suggest the relavent solutions if any. Thanks in adavance 

[SaiTejaTalasila · Accepted solution]

Hi @lavankumar1989a ,

You can refer this -

https://www.fourmoo.com/2022/01/12/using-the-power-bi-async-rest-api-to-refresh-a-partition-with-pow...

I hope it will be helpful.

Thanks,

Sai Teja 

[lbendlin · Accepted solution]

Query folding is NOT a requirement for incremental refresh. It is very desirable, but incremental refresh will also work without it.

Anyway - you can run your XMLA script from anywhere, including Powershell or Power Automate.