Initial refresh to get past

[smpa01]

I have a dataset containing massive data from 3 sources. I have set up IR on each of them,

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.

How can I get past the 1st refresh.

@GilbertQ  @bcdobbs @AlexisOlson 

[bcdobbs]

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:

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":

1) CLick IR table and in property pain you should see:

2) Change the RollingWindowPeriods to what you need:

3) Click save changes to connected database.

4) Right click the table and click "Apply Refresh Policy" which should generate partitions

5) You can then refresh each parition in turn either from TE or SSMS.

Hoping that works!

[bcdobbs]

This might also help:

Incremental Refresh | Tabular Editor Documentation

[collinq]

Hey @smpa01 ,

I think that you will have to use XMLA endpoints to do the initial load.  (https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-connect-tools)  And, use the XMLA endpoint for your initial load with your incremental refresh - https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla

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.

[smpa01]

• 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  before puttting it on scheduled refresh with IR factored in. I like your idea but I can't put the pieces together. @collinq 

[collinq]

Hey @smpa01 ,

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.  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).  So, in the end, you have what you needed - all the years, but they came in one month at a time.

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

FYI - Version 2 will work fine, you do not have to purchase V3 to do what I am proposing.