XMLA & Incremental Refresh

[HB13]

Hi PBI gang

I have a massive and very very slow view in SQL that I pull into PBI (on a good day all 38 million rows take about 5+ hours to load)

I set up an incremental refresh policy on this table to store the last 5 years and refresh the last 6 months of data (no other settings/options applied). These 6 months takes about 30 minutes to refresh. I have now found out that there are changes/updates happening to older rows of data (rows that have already been stored) which is now affecting the outputs of all reports using this dataset. I worry that if I make the refresh partition longer its going to take too long and fail in PBI service.

I'm not too sure as to how far back in the data changes will occur so in looking for other options I came across XMLA endpoints...

I am hoping someone can explain in layman's terms the difference between incremental refresh and an XMLA endpoint refresh and if the two can be combined.

I'm a bit confused between the two...
From my understanding the refresh via XMLA endpoint can only be done through SSMS... is there a way to automate this as it won't be practical for someone to log in there to refresh this table daily?

Please advise !

Many thanks

[Hariharan_R · Accepted solution]

Hi @HB13 

Incremental Refresh - You can setup a incremental refresh for your fact tables or any tables which contains transactions with date column. Incremental refresh will help to create partitions on the huge table. When you refresh it will be quicker because instead of refreshing one big fact table it can easily refresh the partitions in parallel. 

XMLA Endpoint - In context to refresh strategy, using XMLA you can see the partitions of the table that you defined in Power BI as part of incremental refresh. If you do refresh on the dataset, it will refresh all the tables in the dataset. Using XMLA, you can refresh one particular table or partitions.

You also can use the below REST api to refresh tables, partitions etc.

https://docs.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin