Power Apps With Large Data Sets

[Music] hey everyone this is Danielle and in today's video I want to talk about power apps with large data sets so what I mean by large is just very large quantity of data and the reason I want to talk about that today is because that's something that I don't see very often recorded and that's something that we deal in the real life because it's one thing that you build you know a great demo or a proof of concept or something to present with or even to use and it works great but after it has been out there for say weeks two weeks months maybe a year if the backend data really good grows the what is it that happens to the power apps Cena is the power apps even usable and then the key thing is how do I manage those data sets so in this demonstration over here I'm actually gonna show you three major and popular connectors but the key thing is I'm not gonna show you you know a data source which has 100 200 or a thousand I'm going over 40,000 rows or items over there so this is really a good proof of concept which I first did for myself just to see is that how much can power ups handle and then also is a good demonstration for you all to see that this is really the capacity the power of power apps because it can handle such high data sources so let me actually show you really what what it is that I'm talking about in the power apps data source over here I'm actually looking at three key ones the kid three key ones that I'm looking at our azure sequel c.d.s and sharepoint and the reason i do that is because these are the three most popular data sources that i've been used when it comes to power apps so I thought I'll hit all three of them kinda show you the subtle differences in all three of them and how they handle large data sets what I'm gonna do though is actually I'm gonna break this down as three different videos and that's the three different you know you know sections I'm gonna cover on in this video over here I'm gonna focus purely on large data sets it's the data will be simple you know it'll just be single line of text or multiple line of text floating numbers things like that and I'm gonna go to complicated on the data columns but what I'm gonna handle in this one it's just a really large number of data and then we'll see what happens in the other two over there because I will go into some complex data I'll also with some attachments and I'll go and deep there those what I'm gonna save that for another video now so in this one over here I'm gonna talk about large quantity of data and this is basically it what I've actually done is I've gotten a data from the North Carolina Department of Transportation and they actually showed how that the traffic was in certain sectors over a period of the entire year so I was able to get that it's a sample free data and it has you know forty four thousand seven hundred thirty nine rows I said this is a great place to start and that's what I've done over here and well that's what I'm using in addition I'm gonna show how the inner does the app actually show all the data so I'm gonna talk about that and then what are the common functions to use and how so I'll actually show you those functions that I've used and their formulas over there so now that you have a good idea of what I'm gonna talk about let's actually spend some time on the backend data itself I'm gonna show you those three data sources over there prove to you that I've actually got you know that I'm actually putting money where my mouth is as far as that's the really data I'll prove that to you that that's the data source and then I also show you some tips and tricks of how I actually uploaded that data and then we'll end with actually looking at the app alright so let's jump into it the first one I'm gonna look at a SharePoint because I was pretty simple and straightforward one of the things I did was in the SharePoint I actually uploaded from that data that I talked about and I got the data as an Excel spreadsheet so I'll actually show you that here's the one this is actually what the data that I got it came as this Excel spreadsheet and as you can see that Excel spreadsheet is 8 Meg's so it's a pretty good sized spreadsheet over here and he even takes a few seconds to load so that's what it is it's the North Carolina Department of Transportation and its annual average daily traffic that's all the data why I made a dish there's a lot of data so that's the spreadsheet what I did was I was able to upload that into all the three places so the first place I want to look at is SharePoint so in SharePoint I went ahead and got uploaded that list and that list which I just passed by was right here the NC a the OT would check a look at this one it's 44,000 and 35 rows or items that is a lot of data and you know what that is potentially something that's going on in your own company that you're seeing this happen and you're a little concerned about your power apps are actually running into an issue with power apps and this this is a great place for you to see what all is happening so that's the SharePoint piece all right that's one thing second and if it by the way if anybody's interested how I was able to do that what you want to do is hit on new click on app and when you're clicking on an app you actually do I just type in Excel and what Excel does there's a natural section for import spreadsheet now the second tip over here is when that's the you know you want to put that spreadsheet that's obviously the one that I talked about do a little bit of cleaning up so in my cleaning of what I did was I just took the data from there and I cleaned it up to a nice spreadsheet with just the nice hitters and then I just took the data so that's basically what the cleaned up spreadsheet looks like that's the only cleanup I did and then finally I was able to export it using that external import over there and that really worked out well what it took was a lot of time to upload and again you are your bottleneck is your internet service over there so that's that's kind of a aside tidbit that I'm throwing over there so here is the SharePoint one once again I was able to go and upload this big chunk of SharePoint spreadsheet I'm in SharePoint data and now I've got you know that whole forty 4735 rows so that's that's proof over there let's take a look at the one I have in sequel so I have the same thing exact same spreadsheet I took and I was able to go ahead and upload it now while the sweet things I liked about and azure sequel is it literally took all that data and I was able to come over here I was able to do a task in the task there is an option to go ahead and import data you can do import data you can do it as a flat file I was able to take that as a flat file in other tricks that I know is that they don't do it as an excel big spreadsheet as an excel SX just drop it down to an XLS and it works really well and that's what I did this was the fastest I saw I was able to upload all those 44,000 rows literally in under 10 seconds that's how fast it went but that's for a separate discussion altogether well again I need to prove to you because when I opened this up all it shows is a thousand rows and there's a simple way to fix that I'm gonna come over here I'm gonna change that to say 50,000 I'm gonna execute that successfully and that's it forty-four thousand seven hundred and thirty-five same number which we saw in Excel spread in the share point list finally let's also go take a look at our CDs so when I go into my CD s over here common data service NCDs I was able to go ahead and upload the same data and in this data over here one thing in CD s is I don't find an easy way to find out where that spreadsheet is I mean how much is the data so a little tidbit that I was given was that you can actually use excellent tool box and I'm a big fan of excellent to buzz this is a free tool and I believe every power absent dynamics 365 people should have a copy of this free tool again that's my personal opinion but in that along with you know you're doing a powerup checker and security tool which might actually seen in my other videos there's this really cool tool called record counter so I'm gonna click on that and I'm gonna go ahead and refresh the load entities and when you first time used that app over there you go ahead and put in the URL of your dynamics 365 put in the authentication and it'll go ahead and pull all this information together because it ties it down to that environment so over here now I'm just gonna type in say in the search let's just see and see did not yep right there and see do T and then once I do that I'm gonna say get count and I shiny to even dude kept counts and it's ticking in squirrels it's actually doing the counting over there and write that forty four thousand seven hundred thirty five so this is a proof that I was able to get that data the raw data which I showed you from the North Island Department transportation and I pour it into three separate places SharePoint list CD s as your sequel table and insidious over that all of them have large chunk of cable so now that I've shown you I'm kind of you know giving you a solid confirmation that here's the data are there but now let's go ahead and take a look at the actual power app another tidbit I have is when you're uploading it to CDs you have the option to use an Excel flat file but it tends to function a little bit nicer when you actually have that flat file or the Excel spreadsheet in onedrive because what I did was when I had that one Drive spreadsheet on my local machine I was uploading it you know doing the upload from from a local machine with Excel spreadsheet or dos EDS and for some reason it failed twice it could be that I have a bad internet speed or whatever was happening on the traffic at that time so what I did was I actually used what the wizard gave me it's wizard said that do you want it on your local machine or do you want from onedrive so I said hey onedrive is an option I just uploaded my father were here to onedrive and then when the time came to do that bulk insert into CD s entity I put it this link over here and it still took a while I mean I just set that up and I walked away for an hour and I came back and after it was done it said completed and all the data came up so it's a little bit tidbit is that when you're doing the bulk import of such quantity go ahead and just put it in onedrive and it works really well ok so let's now jump into the spreadsheet away and the spreadsheet I'm inside this app and this app over here what I want to say this was something as a proof-of-concept I did for myself because I was getting questions and I was also having some my own concerns so here's just a quick overview of the app there's nothing fancy about the app all I've done is you know put in this intro screen over here and then I've broken it down into three connectors first let's see this is that first the CD s1 the CDs when I just showed you this is the one that's coming from the Excel spreadsheet which I just showed you and then this is these the C address equal table which I also showed you three ones that I just showed you a few minutes ago those are the connectors that I put over here and so that's one thing and then I went ahead and build a separate screen so that I can kind of focus just on each of them so now let's actually go into the app so the first thing I want to do is I want to test a few things in the app is first with all this data coming in can I even do a search for it you know can I search for something and find all the data that's coming in because we know and all of us who we know build power-ups and are kind of comfortable power apps that all of three data sources are delegated but can they really handle this much data well we're gonna prove it because in paper and documentation which is right here it says that these are the three most popular sources that are delegated oh and I'll put this link in the description below so you can actually take a look at it that these these are definitely the popular ones there are the data sources are the most popular and the support delegation and once again I said this this was my proof of concept because there was 44,000 rows over there okay let's go back so another key things which I'm showing you in before I even go testing on the file I'm going into settings I'm going to Advanced Settings and I've kept the data row limit for non gated non-delegable twenties it's 500 I did not touch that I want to leave it at that because as you and I know that this thing can go up to 2,000 but when you put it up to 2,000 it does hit performance a little bit because when the app opens it is pulling 2,000 items over there but that's again for the non-delegable ones right these three data sources are delegate above so technically I should not be having to mess with that so I left it at the default 500 and it actually works really well but I'll let you be the judge for that element you know you can control those conclusions so let's do the first one okay as your sequel you come over here - as your sequel and then just so that you guys you know for sure I'm gonna get out of this one click over here and you can see dbo North Carolina Department of Transportation so that this is coming from there let's pause over here and let me show you a few things no yellow color exclamation marks which means I am not seeing any delegation warnings that's a big thumbs-up all right that's pretty cool second thing over here is take a look at this formula what I'm doing is I'm used my favorite search function over here search function works really well for both CTS and sequel and you know and I just say sequel because it works well for on premon and azure as well so I just used you know the term right near sequel but it search really works well over here and so this is equal so i use sequel one of the things i'm doing the search is i'm saying that hey i want you to search in this sequel table whatever entry are put in this excel sprinters text control over here and you go ahead and search anything which is in the county location and and and the route table which is over there it the formula doesn't get any simpler than that it's basically saying use the search function search in that table over there search for whatever entry you're putting it in this field oh and then search that against all these three different tables I mean it literally doesn't get me simple than that but take a look at how powerful it is all right now right now power apps up is it's built this way that it in for the sake of its performance it will only let you pull one hundred at a time so right now you're seeing one hundred and if I go down over here you'll actually see that it's gonna stop at somewhere around the 99 98 that's right I was correct right at hundred and then after that it'll go ahead and pull another 100 so then that's what I did you saw the cursor just went up it just went ahead and pulled another 100 this is not a mistake this is actually by designed purely to improve performance and I personally like that I mean but you you know you just get hundred at a time and it works really well but let's not stop over there because pulling it in 100 scrolling on everyone this is great but you've got 44 thousand and more rows over here so this better work really well so what I'm gonna do is I'm actually gonna start searching for anything and I'll say let's actually take a quick look at let's see there was a North Carolina so let me do a SC a BA R Us now all we were see were a is over here but it went ahead and started pulling all the way to Cabarrus oh yeah but let's let's not stop over there because I'm gonna actually jump to my sequel because I've got all 44,000 and I'm gonna kind of scroll all of it because in sequel server management studio I'm just able to scroll and see all the way down and I'm saying things such a County such as yeah it can I see Wilson I say Vance so let's do search for those and let's see what we find alright I'm gonna get out of this one and I'm gonna do the Yadkin because I was way at the bottom over there and it goes ahead and it's pulling in all that information so if I go ahead and scroll that end say actually it did it went ahead and got me all the Yak kins over there now one thing I should have done I'll actually clean that up right now is this ID you filled it up over here so you can actually see that that ID is pretty much the same as the row over there and when I typed in for the yeah and it's filtering the Yadkin I am looking at row number forty four thousand three hundred and a bow remember the this is a delegate able one and I've got a lot of data with it and response to the search was in seconds I mean they didn't even go over fights against it was that fast so this is my confirmation that it works really well but let's just not stop over there because not only am i able to see the information can I even edit it so what I'm gonna do is I'm actually gonna just select randomly one over here Yadkin yeah this is good I'll go ahead and keep the route the same I'll go ahead and just change this to a location wherever I want to be okay just randomly something and I'm gonna go ahead and save it two things will happen first of all when you go ahead and submit that it immediately updates this gallery over here not only did it update the back end table which is on sequel on row number forty four thousand nine hundred ninety one but it went ahead and updated this over here but let's see does it need some time in the backend to crunch or something does it need that first time or can I just go ahead and type in whatever right now and I get to it let's see this is what ever what did I say better but I think I said very diverse let me just type in there that's yeah can maybe I just don't know what I typed they're ever alright so let me just type in wh EEE wherever I want to be it was there was my fault I just didn't know what I was typing but let's pause and see what happened I was able to do a search for that delegate of a lighten that row number was forty two thousand nine hundred ninety one row I was able to edit literally clicked on that and I was able to edit it I was able to go ahead and update the location information went ahead and saved it and then it was immediately searchable it didn't need any extra crawl time or anything like that it immediately saw what I searched went and did direct your search on the sequel site over the actual sequencer and it filter didn't but that's row number forty four thousand nine and ninety one I mean to me this was a such a good feeling that I was saying that power-ups is so powerful especially when you have you know you're following the rules right there are three delegate able data sources as your C code being one of them I was able to go ahead and do a search even though I said the limit at five hundred the rows of in the dongle non-delegable once said it at five hundred didn't change that the rows that are getting pulled in the gallery over there they were pulling 100 at a time didn't do any changes over there but I was able to do a search found that that search matched forty two thousand nine hundred and ninety-one in row number edited it and then did a research for that new edited text and it still came up over there this is very impressive I mean I know you've seen a lot of demos of how filtering and search and everything works but I'm willing to bet that you haven't seen something that shows the power of power apps had this much data source level alright so this is a sure sequel now let's take a step I mean now this switch over our switch our gears to know CD s and C the CDs have the same capacity over there so I'm gonna go back over here and go CD s entity and the CDs entity look and feel as the exact same but I'll just stop over here we'll take a look at that right click on it and as you can see that this is that CD s 1 this is the seediest entity that I had over there also if you remember I showed you that in the CD s entity when I came in that was the same name I was even able to see all the data but I showed you that needle trick I was able to pull all the information and this is the same amount right on same amount of rows over here forty-four thousand seven hundred and thirty-five entries were made for that entity the exact same one over here alright its formula again it's the same this is again the beauty of search the only two differences over here was obviously I'm changing the control name because the way I've named that as search but then I've also have to go ahead and get the correct table names for the ceadeus entity as you can know that all the CDSs ones start with their own prefix in this case my ceadeus entity have the prefix CR 341 and so that's the only change but all in all this is again that same search function and it doesn't get any more simpler than this but let's do the same type of test okay I'm gonna come over here and I'm gonna do that seeing Yadkin test all right as you can say search is going on and it's taking a while but if actually go down over here start saying yep I find it it's right over there what I could even do is I could do a search form from exit 65 let's see what happens from exit 65 and there's a bunch of there but check this out it is able to go ahead and get me all these entries which are a different rows I mean that one of them is we know that this one is already in the 44,000 row level over there this could be in the 10,000 that the other one could be in the 30,000 but the search is that powerful and it is that powerful because it is is CCDs entity and it's delegate able which means you're literally doing a server-side search over there but again let's let's not stop over here right I'm gonna go to this the arcane and I'm gonna say add from exit 64 this a from exit 65 - I didn't see 85 let me put in I 85 all right and so now it went ahead and updated that directly you know X out of here so it will just refresh I give it a second little go ahead and refresh and now I'm gonna type in drum and it'll go ahead and start searching start searching from exit i-77 let's see it is doing a search anyway you get the idea of it I think like if I search for it from 77 to 85 basically yeah it is doing the same thing if I can find it faster this is by no means me telling you that it's not working this is just me not knowing what I typed in a few seconds ago so let's do a so it's prime exit 79 and that that wasn't me that was been me good close enough but you you basically get the point of it that I was able to go ahead and did the edit and I was able to do the search over there whatever I did on Azure sequel side the same thing I'm able to do one Cydia side so that's two for two right to delegate able ones that's a sure sequel and that's the CD s over there I'm able to go ahead and do a search directly on a server side for over 44,000 items and I'm able to get that thing back in and I'm gonna keep that's let's take a lot to look at the last one which is Schieffelin because things a little bit more different on SharePoint but yet it is that powerful right so hopefully I've proved my point already twice it works with an azure sequel it works well on CD s but now let's go to SharePoint Online alright I'm going to click on SharePoint Online and in SharePoint Online things are a little bit more different it is still delegate able which means that I've got 44,000 over items over there on a SharePoint list but there's a way to go ahead and actually tweak your search because what happens is if you see right now if I click on this over here in the search section over here I've got a warning I've got a delegate able warning over there and what that means is that whatever worked and the search for your sequel or the search it works on CD s because remember we didn't see the delegation of that it worked for those two in SharePoint search does not work it'll actually give you a delegation warning over there so as a good example the formula that you see over here is the exact same thing right it is I've put in the data source with the SharePoint list and I'm saying that hey what I was in the sage touch whatever is in this texts over here just search for these three databases so we know that there was yeah it can was one of the counties and I'm doing a search and ranking Yadkin was at the row number forty four thousand and something but because we got that exclamation mark over there we know that the search function is non-delegable which means now we are bound by that five hundred limit over there and even if I bumped up that five hundred to two thousand there is just no way I'm gonna be able to search all the way to the bottom so kind of keep that in mind when I say keep that in mind pay attention to this type of documentation over here this documentation very clearly states for SharePoint is it will work and it you know filters such as fill functions such as filter search can be non delegated by over here but inside search you know you can if you go ahead and modify your search it is not gonna work then it suddenly becomes non-delegable I would highly recommend that you bookmark something like this they can gonna do that but with a little bit of tweaking we can actually improve it we can make it how it's supposed to be and so what I did was I already had a nice working formula so I'm gonna go up over here I'm gonna comment that out and this is what the recommended one is the recommended one is you use filter because filter works really well and then inside filter you go ahead and leverage you know start I mean you go ahead and leverage the the starts width and the starts with is able to go ahead and a combination of it will give you that functionality of same powerful functionality which we saw an agile sequel and we saw on CBS over there so let me actually show you what I mean what happened over here is we just didn't have a combination working well so what I want to do now is let's set up to try to replicate what we did before I'm gonna now get the filter and I'm going to go back to Yadkin because we tend to choose Yadkin because it's always at the bottom and check that out before very had a problem remember and I started to search Yadkin over here it didn't work but now that I'm doing the way the recommended way of using the filters the recommended function which is filters over there the search is delegate able I mean it is it is working the search I mean the the data source is delegate above because the filter function makes it delegate able and so we here what I can do is now I can do a combination of the two I'm getting filter so filter works but I can also do is inside filter go ahead and search it based on this you know so I'm gonna now do something to make sure I get you know say from exit and it is searching inside and here you go inside the from exit over here I mean inside the y at cannes i was able to get that filter and then i was also able to apply the search in there so this combination of the formulas and let's take a look at that formula again this this combination which is the inside the filter I'm doing a starts with and starts with I'm saying that I want you to start you know look against whatever is the title which in this case the title is the county do a filter the county based on whatever I've selected over here and go ahead and do a search in the location with the text that I put in over here see this combination works really well so I want to actually do this proof of concept it was literally done as a proof of concept for myself because I built haven't built hundreds and hundreds of apps but I was a little concerned is that with all these apps growing and they know some of your apps may not even hit at this level they men are not gone over ten thousand or five thousand it might still be in the hundreds but right here I have proved to you the power of power apps as long as you do two things think number one is you gotta use the correct delegate collect entities which in this case I proved three to you the three famous ones as your sequel CDs and SharePoint and think number two is that you gotta use the correct combination of formulas over there and to find those correct combinations you got to bookmark this page which by the way I've put the link down here in the description because this is how it works as long as you've got these delegate above functions in the correct formula in the area and the correct formula for these delicate ones it works really really well so hopefully this in an example that I showed you with all of my three different connectors over here with a sure sequel with CDs and SharePoint was a good kind of in an example for you just to see how powerful perhaps is but also give you that reassurance that you need is that is something gonna break when I start hitting high data and this was a clear example each of them had forty four thousand seven hundred and thirty five items or rows put them in three different places I put the manager sequel I put them in CVS I put them in SharePoint and all of them were delegated both because the search worked really well remember in as your sequel and insidious search worked well and the formulas pretty simple and straightforward in SharePoint search didn't work right the delegate able-nan delegate herbal formula I can kima but I was able to change that I was able to use a combination of filter and I put in that little end over there so I was making able to use a combination with the drop down it filtered based over there and then I was able to use the search text over there and it worked really well so there was just a little change search works for these two other in connectors but for SharePoint you got to use the combination of filter and add so once again hopefully this was helpful to you I will put some important information and links in the description below and as always keep power lapping bye

Channel: Daniel Christian

Views: 18,713

Rating: 4.9227052 out of 5

Keywords: Power Apps With Large Data Sets, Power Apps with Large SharePoint Lists, Power Apps with Large CDS Entities, Power Apps with large SQL Tables, powerapps tutorial, Power Apps performance testing, Power Apps with Search function, Power Apps with Filter function, Power Apps with Search and Filter function, Power Apps delegation, Power Apps delegation with large data, How to make large data sets work in Powerapps, Power Apps with Azure SQL CDS and SharePoint, Testing Power Apps

Id: IvTiAanB3Cs

Length: 30min 21sec (1821 seconds)

Published: Mon Feb 03 2020