All you need to know about incremental refresh for PRO | Power BI desktop update february 2020

so hello everybody it is power weight which means that the power bi t must release a new power bi desktop update these time it is for february 20 and they have released would probably will be the best feature for the year and it is incremental refresh for pro licenses here's the thing in this video i'm going to show you what it is how to configure it we were gonna talk about what works what doesn't work what you did you need to be mindful of everything and anything you need to know are you ready let's go ok so I have here a power bi file that basically lists all the videos I have done and we're going to schedule refresh it actually you have this available on my website I'm going to link down below in case you want a search ok so before we start setting up incremental refresh what is it incremental refresh basically allows you to when you are refreshing a data set get only new or modified data ok what does it mean what it means that the refreshes are faster it will fade less often because it's less to refresh and the consumption is reduced and these reduce you might say ok I'm not paying for them sunshine oh but I mean if we are as mindful of the environment is a good thing so make sure you setup incremental refresh ok there is no idea to refresh die Norma's databases if there's no need for it with that said how do you configure it and I'm going to show you I'm going to show you this is what we're going to do we're going to go to transform data because to configure incremental refresh you need to create two parameters with a specific name that is now reserved for this use so to create a parameter you go here - parameters and you have to create two parameters the first one is called range they have to be call exactly like these reins start and they have to be daytime what do you put in current value I was actually a little bit confused by this you can put anything that is a date/time bar then power bi will working on fire itself for what I understand so here I'm going to put I don't know my youtube channel start in January so how about that and then when you put because they stay time you have to put it in daytime format so that would be one and then we need to create another one which is range and and spirit right though as you'll get in trouble so there you go ding time this is I'm going to put I don't know 20 20 about that okay and then they will appear here once they are configured here we have them and what we need to do now is apply those into our date column so if I call here this is the list of the videos again on my youtube channel so this is the date that I want to be able to have incremental refresher so when I publish a video okay and what we need to do is to filter these with the parameters that we just created so if you have a product sales you would be the sale date probably or the last update theta V SharePoint whatever it could be right so you go in here and you just daytime filters and then custom filter you get the street again daytime filters and then custom filter and now we are going to configure those two parameters insert I don't think it matters where on your query you put that filter but obviously the earlier you put it the better because it will falter correctly from the beginning I'm just guessing so here you cannot have two equals you can do is before or equal and then is bigger or is bigger than equal up before okay so all the way won't work and you have to be very mindful with the parameters that are daytime if you don't have the time and this should be daytime because if there's not any time you're going to get into trouble so is before well wait wait wait is before range and and then is after or equal so it has to be after our start date and before our end date and again equal should be just in one of these places click OK and then it will grab the dates here and then it will do I thought I would have to do like today is there something but it what I read it doesn't seem like it you just just need some sample and then the power bi service will work it out but there I guess they start it has to be correct use the end date that is not important you just let me know anyhow if that's not the case so once you've done that j-just close and apply and while it loads because there are so many reasons it takes a little bit of time don't worry because we need to talk about certain things before while it is loading here's the thing how does this thing work technically in the background and what it does is it takes your humongous table and it just partitions it means that it just cuts it in smaller tables okay and how it does that Microsoft knows but do you remember that I did a power bi incremental refresh faked for pro that we did exactly that we took a big table we partition it we saved it and then we just had another table that refresh I will put the link down below and guess incremental refresh doesn't work you can still use that but what it does is it takes your big table it divided sin in the smallest he both and then it goes and looks okay as exchange house has changed has estates and then said oh the only this one changed great that's the one that I update okay so this is one of the first limit his or incremental refresh this is how what it says is it's important the partition files there is a push to the source system what queries are submitted for refresh operations it means that to push filtering down means that the source the data source to support query folding so what is telling us is that in order to do incremental refresh the source should be the one managing the query so you can do the partitions from depending on which source you are using that might be possible or not so are you using SQL and do you have a query for then incremental refresh is going to work without any problems but if you're using they have here some examples flat files blog web or data fill data feed it might not work why because this source I mean Excel is not going to be able to partition yourself a text file won't be able to partition itself right it doesn't have that functionality and if you have an SQL that doesn't have a query folding and I have a video card fold and I'm going to link down below so you know how that works but if you don't have query fall then it might work but this is what is going to happen you say here he says in such cases the engine applies the filter locally which might mean that it has to retrieve the entire table in order to do the partitions in order to divide it he was in a small table and in order to make the query refresh which basically means that you get zero advantage for incremental refresh does it make sense so if your query is able to do the partitioning the dividing of the tables in the source if not it might get slower with incremental refresh so you need to just do incremental refresh and test it okay if it works Alleluia if it doesn't go to the link down below take the video where I show you had to do this manually and do it that way instead sorry but that's likely through it and it makes sense how otherwise are you doing from in to refresh right so how do you know now that we have loaded everything you go in here and you need to do incremental refers for each table which I was quite surprised up but now in the inside myself okay maybe make sense that you do this partitioning just in the tables that need to be partition and not everything it will take longer so this is the table that we want to do the incremental refresh right click click on increment to refresh and this is what you get an Isis here unable to confirm either in query can be folder it is not recommended to use incremental reference we have known for the word course quite it's just exactly what I was telling you my advice to you ignore the warning set it up test it and how do you know if it's incremented refresh or not how long it takes to refresh if it's faster these increments very fresh and otherwise it is not and it might be slower than you stop doing that and use my other method you'll be word okay now another thing another limitation that you have is that once you deploy it is to the power bi service it says you will not be able to download the file anymore because they are assuming the earth you are doing this with gigantic files and you are not supposed to download I can take files through the internet for a lot of reason so you will be locked in the service basically hopefully you already have a power bi desktop that you can reuse so you don't need to but your users won't be able to say okay I want Sun load this file okay now what is the table that we want to do incremental refresh we I have only one table so the video table and then you toggle this on if you are not able to toggle this on and off it means that the parameters that you set up are wrong so I'd there is a rawness pair there is no daytime or whatever the parameter wrong you need to go back and do that okay this is working fine and then here you have how to configure your incremental reverse the first thing this says is how many rows do you want from your source so for example depending on what you're trying to do for example each hard eight you might not have they don't want data from 1998 we just want data from the last three years for example so here you will enter I want data from the last three years for me I want all day it does I'm going to put ten years the next one is refresh the rows where columns is in the last oh that is basically let me show you when do you want to refresh you can say okay I want to refresh once a week for the last seven days I want to refresh every day for the last days whatever it is that you need you put it here and I want to have every new day refresh I put one day but basically I publicity us every three days so hopefully that would be not every day and then this is quite cool it says detect data changes it says on a reference date in the last three days if the publish add column has changed that means here there is a modification in that column or something has been added into that column which is quite cool right so I said absolutely I want that and then I just want to I don't refresh if the day's not finished in this case for YouTube videos he gives us with you know value because it might not capture the after refresh so I want to have wait for the day to complete and then refresh click apply all and then publish to the web and you are good to go how cool is that now before I close the video let me show you this if we go to transform date that you know your query has chlorophyll and again I have a video with all the details on what query for them is down below so check that out but just so you know very very quickly without having to go there let's see if this loads I'll show you how to check if your data has query fallen or not so if you right-click on there step the last step you will see why is it so slow come on baby you see here the vo native query that it is grayed out it means that there is no little queries refresh this is from an API obviously I don't think I don't know if he will do the reference we'll see but that's the way to know it so you can go up each step and see if you broke your query fold in somewhere but everything is on the video I told you below so go and check that out okay it's a booty store favorite feature for this month I'm guessing that is between the hierarchy and the incremental refresh right ok tell me down below what you think is the best feature for the 20/20 February 22 nd Allah will see you again for Friday with another tax Fridays until then thus always take care bye-bye

Channel: Curbal

Views: 23,753

Keywords: incremental refresh power bi, Power bi, powerbi, Curbal, power bi desktop, power bi tutorial, power bi training, power bi for beginners, All you need to know about incremental refresh for Power BI Pro licenses, incremental refresh pro power bi, what is incremental refresh, configure incremental refresh in power bi, troubleshoot incremental refresh in power bi, power bi desktop update feb 2020, february 2020 power bi update

Id: BQahH53ayKU

Length: 14min 30sec (870 seconds)

Published: Wed Feb 19 2020