Forum Discussion
Incremental Refresh
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.
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 ?
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.
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.
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.