Initial refresh to get past

Question

I have a dataset containing massive data from 3 sources. I have set up IR on each of them,
&nbsp;
However, when I initiate the 1st refresh (full refresh), I am getting the follwoing error
The database was evicted and the operation cancelled to load balance the CPU load on the node.Please try agian later.
I only want the intital refresh to get past cause I know I would never have to look back as IR is already factored in.
&nbsp;
How can I get past the 1st refresh.
GilbertQ&nbsp;&nbsp;bcdobbs&nbsp;AlexisOlson&nbsp;

bcdobbs · Answer

Morning, never experienced this but I think the following might work. Are you starting off life in PBI Desktop? If so configure the IR with a very narrow range that will only load a small amount of data and publish it to the service.

It should hoepfully publish. At that point you need to force a refresh in the service so that it setups an initial policy and partitions:

&nbsp;
&nbsp;
Then you need to connect to the XLMA end point with tabular editor and "modify". I've tried with TE 2 so that it's "free":
&nbsp;
1) CLick IR table and in property pain you should see:

&nbsp;
2) Change the RollingWindowPeriods to what you need:

&nbsp;
3) Click save changes to connected database.
&nbsp;
4) Right click the table and click "Apply Refresh Policy" which should generate partitions

&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
5) You can then refresh each parition in turn either from TE or SSMS.
&nbsp;
Hoping that works!

collinq · Answer

Hey&nbsp;smpa01&nbsp;,
&nbsp;
I think that you will have to use XMLA endpoints to do the initial load.&nbsp; (https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-connect-tools)&nbsp; And, use the XMLA endpoint for your initial load with your incremental refresh -&nbsp;https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
&nbsp;
Basically, I am saying that you have to set up your incremental refresh, with the XMLA endpoint, with the partitions pre-set (maybe use SSMS or Tabular Editor??) and then the very first load IS an incremental load.
&nbsp;

smpa01 · Answer

How Can you upload a dataset with pre-set partition when the initial refresh has not happened. In my understanding, unless the first refresh happens the partions are not created. If I can upload a datset with pre-set partition ( I dont know how that can happen when the first refresh has not happened) i can simply manually refresh the partions from ssms&nbsp; before puttting it on scheduled refresh with IR factored in. I like your idea but I can't put the pieces together. collinq&nbsp;

bcdobbs · Answer

This might also help:
Incremental Refresh | Tabular Editor Documentation

collinq · Answer

Hey&nbsp;smpa01&nbsp;,
&nbsp;
The way it works is that the first load is done incrementally, so you don't have to do a full refresh and then increment afterwards.&nbsp; From a not-so-technical view, think of it as the partitions running one at a time and then appending themselves - but each partition goes into the queue by itself and so you can update millions of rows of data based on your partition (like months from years).&nbsp; So, in the end, you have what you needed - all the years, but they came in one month at a time.
&nbsp;
You can do this with a number of tools, SQL and Tabular Editor being the two most popular .This article lines it out pretty well when using Tabular Editor:
Incremental Refresh | Tabular Editor Documentation
&nbsp;
FYI - Version 2 will work fine, you do not have to purchase V3 to do what I am proposing.
&nbsp;
&nbsp;

Forum Discussion

Initial refresh to get past

Related content

SQL initialization error

Incremental Refresh - DateTime Partition Column Works in DEV but Initial Refresh Times Out in PROD

Incremental Refresh in Power BI dataflow Gen 1 refreshing from past 13 hours

Initial data load for incremental refresh on a nearly useless datetime column

Cyclic Reference Error on Initial Refresh