Incremental Refresh

[schoden]

Hi Community, 

Would appreciate if anyone can share their knowledge on incremental refresh. My main purpose/goal was to obtain a live streaming data , as mentioned in forums : which can be achieved by API via powershell or either microsoft flow trigggered SQL. 

Both I couldnt obtain desired results so I resorted to incremental refresh.  I have Pro Power BI PRO  license.

The report was set with parameter from the desktop , published it on service.

Doubt: 

1. Report dataset is now scheduled with 5 times refresh- done manually from the service, but dataflow related to the dataset is a day old data as refresh schedule is set once a day PURPOSELY not to  stress the datasource as its live datasource. 

As Pro license I cant incremental refresh dataflow , it requires Premium, What is the best work around suggested ? 

Would help me al

[swise001]

@schoden 

Would it be possible to create a second dataflow that 'acts' as an incremental refresh - without using the incremental refresh setting (which is reserved for Premium)?  In this second dataflow - you could toggle the queries to only bring in data from the current day (or whatever your window is).  Then set the dataflow to refresh shortly before your incremental refresh kicks off on your dataset. 

You'll still need to append the data from both dataflows - but perhaps the smaller dataflow won't strain the source system as much.  If you are concerned about this - it's worth doing some testing to try and see how much 'stress' these refreshes will put on it.  

[schoden]

Hi @swise001 , 

I am thinking about your solution but I am not able to make sense because even if second dataflow with just current day data, the refresh can be at max of  8 times a day only.  The report with incremental fresh picks automatically when data changes - so there is not particular timing for  datafresh , which is what need as data changes from the source every minute. But ultimately as it depends on the dataflow , only it will show data changed 8 times only. 

*When you say ***Then set the dataflow to refresh shortly before your incremental refresh kicks off on your dataset*** , does it mean that dataflow refreshing once, at a particular time will do the work ?

[swise001]

@schoden 

Based on your addition description - there will be some limitations (as you noted). 

1.  Pro datasets are limited to 8 refreshes for imported models.  This limitation will apply to your dataflows and datasets, including your incremental refresh dataset.   The idea that your incremental refresh is running more than 8 times a day - runs counter to those known limits with Pro datasets.    Detect data changes - means that that upon a refresh - incremental refresh will only import changed data - but will not be querying the datasource every minute to check.   That level of "near real-time" reporting is typically achieved with direct query datasets or composite models.  

2.  When a dataflow is used by a dataset - the refresh timings of the two objects are set independently.  So if you want your dataflow to contain updated data - it should be refreshed prior to your dataset - so that new data is staged in the dataflow, prior to it being imported into the dataset.  

[schoden]

@swise001  Hi 

Help me understand or correct me if I am wrong .

 Incremental refresh main purpose is to reduce the data freshing  time by reducing the amount of data to be refreshed (It will not cater my purpose of real time streaming data) 

For my purpose of real time streaming data , I need to direct query ( my only concern is performance and datasource stressing )

Have you tried pushing Dataset by Powershell ?   

$SqlServer = 'name';
$SqlDatabase = 'datebase';
$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;
$SqlQuery = "SELECT * FROM Table;";

$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = $SqlQuery;
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;
$SqlCommand.Connection = $SqlConnection;

$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader();

$endpoint = " My end point from the API  "

while ($SqlDataReader.Read()) {
$payload = @{  "Data That I get from API info"

}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}
$SqlConnection.Close();
$SqlConnection.Dispose();

The error I get is :

Please comment .

Cheers, 

Sangay.

[swise001]

@schoden 

You are correct with your assessment of incremental refresh.  It's mainly a tool to reduce the 'load' on dataset refreshes by only importing a part of the overall dataset.   

Unfortunately, once you've created an imported model - there is no way to 'convert' it to a direct query model.  You'd have to essentially start over again (and select "direct query") when chosing a data storage mode.  

Yes - direct query moves the processing back to the source database - so there are real concerns about performance and datasource stressing - depending on how many users and queries are being applied simultaneously.  However, this method will allow you to get 'near-real-time' info - each time a query is run - or a page is refreshed.  (In the service this can be 'forced' with a browser refresh).  

I do not have much experience with pushing datasets with Powershell.  I imagine - if you could programmatically 'force' a new dataset into your model (every few minutes?) - you could also achieve 'near-real-time' results.  This would be similar to simply refreshing and re-publishing your PBIX file at a regular cadence.  There are no limits that I know of - to how often you can manually re-publish a dataset.  

[schoden]

@swise001 

Regarding the direct query,  even though I have set the dataset refresh for 15 mins, the Dashbaord in service doesnt refresh automatically ? the report in the service refreshes every 30 minutes, So like you pointed I put a forced browser refresh( every 1 Minute ) the trick worked but I am seeing limitations as my end user will not have browser refresh installed.

But I am stumbling to understand , even When I set 15 minutes refresh in dataset , no change occurs in dashboard, is it to  do with pro license , when its available only in premium?

Thanks for sharing your knowlege with us 😄 

[V-pazhen-msft]

@schoden 
Well, incremental refresh is also available for Pro licence after the Feb 2020 update. 

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-is-generally-available/

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

[schoden]

@V-pazhen-msft   

Hi , 

For Pro licence, the incremental refresh is available only in desktop and dataset in service.

But dataflow related to those dataset can't be incremented for refresh, it needs premium license.

 

[Sujit_Thakur]

Dear @schoden  ,
I think @amitchandak  sir can spread some light on this , i also want to more about it

regards ,

Thakur Sujit