For my next project I'm going to setup a home SQL server and build a power app over it. Do you have or know of any videos that can help with either? I have never used SQL but I have installed it so many times as an IT admin. TIA! Great video btw
This one talks about on prem data gateway. Old but concept is same. That will let you talk to the sql. From there remember your table needs a primary key. Powerapps on premise data gateway ruclips.net/video/uqx_h4Ym8ng/видео.html
@@ShanesCows I did both on prem and could SQL. THen I had gpt make it all into a PS script that made it in sharepoint too. Just so its free when it comes time to use it. Great lab! ty again Shane!
@@ShanesCows I haven't given up on signing up for your website and I'm working towards it, but my circumstances changed some this month when I decided to improve my job situation. I've been on about 10 interviews in 2 weeks so I'm just waiting to hear back now. Once I have this all worked out I will def be signing up. =)
100% agree! I am a SQL expert but I found out dataverse just goes better with power platform and actually I have never used any data sources but dataverse to build power platform apps.
You will see this link referred to on the forum a lot - just used it today. I also loved your comments and ranking of SharePoint and the 80% use comment - that would be my judgement from forum posts as well. I have never needed to use anything else in the five years I have been writing apps - it is a good solid reliable platform and better still out of the box at no extra cost. Warren Belz
Spot ON! I am literally having to deal with the data source problem right now. I developed an App that has Dataverse backend and did not realize the cost of the Premium. Well working with a company that has 6K+ users (estimated 1500 that would use my app) I had to nix the idea and go back to working with SharePoint instead. Here is my scenario for anyone else to learn from my mistake... 1. The app is approval flow is only a temporary solution to help our company get off of MOSS and SP 2010 servers (YUCK). 2. Dataverse is great for everything Shane says, but the premium licensing per user per month was going to add up. Until I can save the company money elsewhere, it is not easy to convince them to spend money on the Premium. 3. The app/SharePoint list will see about 5-10 entries a day with the possibility of an occasional bulk import of data. 4. This interim app is only in place until end of 2024. I am confident that speed will not be a huge issue during this time as long as I keep the control that I am doing with my Gallery loads and how I filter the data.
One important point about SQL-server is that you can run it on-prem and have all the data in your own data center and use the rapid application development capabilities of the power platform without moving everything to the cloud.
One more reason to adopt Dataverse, IMO, is the ability to work Offline. I don't know if any other data source is supported for offline use, but the scenarios where I would encourage adding PowerApps to your toolkit (I'm a retired Access developer) often involve remote locations--think warehouses, job sites, and such--physical locations where an internet connection is not guaranteed. Scanning barcodes in a warehouse for Stock Take comes to mind as a prime example.
The new way of offline is on DataVerse but we have been building them the old way offline with other datasource for years. 😎 I agree offline is a great tool.
Till Dataverse and SQL become free and open or at least reasonably priced specially for for mid/big firms, will continue to use Excel (which outperforms Dataverse in some cases - my actual testing) and SP.
After 20 years of using Excel and 5 years developing automated Power Query solutions, I am an Excel geek, I love Excel. However, I've literally just started my Power Apps journey in the past few weeks (late to the party I know) and just watching your videos (with others I mentioned in a tweet including you Shane) I have already told my bosses the way to go is (drum roll...), exactly the same priority in your list. I've said we should be looking at Dataverse moving forward and not Excel or even Lists (albeit I have to start somewhere to learn the basics). Just seems more logical? So, as a numpty beginner I agree with your results :)
Thanks Shane! Good video for sharing with people who doesn't know differences of data sources, because for many of citizen developers SharePoint is only one thing.
Hi Shane, We generally get a threshold limit error once the records in an sharepoint list exceed 25000 records which restricts us to add or delete any column fields.. therefore it was recommended to use an excel file saved under a sharepoint folder, apparently i was informed that in an excel we can store more than 100000 records without any threshold limit error. Let me know of your views in this regards.
Nice summary Shane, no surprises which is good. But I feel you could have included a mention for dataverse for teams. I use this extensively without premium license. I think dataverse fot teams is no 1 (taking cost into account).
I just finished a project that takes an Excel upload as a template, stores it in SharePoint, validates it against SAP, handles approvals and configurations in Dataverse, and then writes data to SAP. Shane is 100% right. I think it’s more of a Dataverse as much as possible first approach and then find the best solution for the other stuff. As an example the Excel connector does not work with Dataverse so my next best option was SharePoint (with the client’s use case)
Love the video!. Thank Shane! Although I have watched most of your videos and have learned a lot from them, I have to disagree a little bit with the ranking. I think the order from 5 to 1 is Excel, SharePoint, almost anything else (1000+ connectors), SQL, Dataverse. This is based on all of the reasons you provided regarding SharePoint, delegation, not a RDBMS, problems with large amount of data.... yada, yada.
Thank you for making this. It's rare that I get requests to build an app. But, when I do, my employer says we can use SharePoint for the back end. I tell them no and why. Now, I can add your voice. Hopefully, they'll believe one of us.
Thanks Shane, you are right of course, but as we are a non profit organization getting MS365 for free we just don’t have the resources to move to premium. I started building a local Access database than moved the data to a file server and after that moved the data to SharePoint and am now in the process to replace the access frontend with PowerApps we will need to stick with SharePoint.
No harm in that Peter. Not everyone can afford or is ready for premium data sources. That is why I think 80%ish of apps are built on SharePoint. No judgement here. 😎
It depends on the platform (Public vs GCC), what the client has available and the type of data that you are going to be handling. Currently, our client (GCC) does not have DataVerse or SQL server available. We have defaulted to using SharePoint for everything. We are waiting on the approval of DataVerse and will be moving our items to that in the future. None of our items include PII so having someone see the SP list is not as big of a deal. I have never created a PowerApps using excel! LOL! I try to stay out or convert excels to a SP list as much as possible.
Dataverse is a steep learning curve for me 😕😕. Planning to start your university this winter, so hope to get better at it. I still stuck to SharePoint. THX for great video about the subject.
Shane, what are your thoughts on building Canvas Apps against SQL Server, where all of the data access has to be done via stored procedures in the SQL Server database? My understanding is that you have to call each stored procedure from a Flow. Is that correct? If so, are there any disadvantages to this approach? If your users have Power Apps per User licences, they can call unlimited flows, right? But, will the Canvas App perform well? Won't Flows introduce latency? Is there another method of executing stored procedures from a Canvas App without calling Flows?
Pete check out the new low code dataverse plugins. I believe calling SQL Stored Procedures is one of their current use cases learn.microsoft.com/en-us/power-apps/maker/data-platform/low-code-plug-ins I haven't built one yet but it is on my list to try.
Thanks Shane. Good staff. One thing though Security in sharepoint is a 3 click process You can lock users from seeing the page and use Power Apps unrestricted
You can hide it but they still have access. They could write their own flow for example that the list would still have access to. There are ways to hide from SharePoint but there a lot of little things to be careful with.
@@ShanesCows Thanks Shane for the video very useful! The way I did it is by using a flow with a service account that has write permissions on the sharepoint list. The user clicks on confirm in the app, the flow starts running and the serviceaccount writes the adjustments in the list. The user has only view permissions for the list.
Thanks for this great overview @ShaneCows. We use AD-secured Azure SQL with our canvas apps, but have been pulling our hair out with ALM support. i.e. you can't promote an app from Dev to Test to Production environments using the standard Solutions mechanism because the system won't re-wire the data connections to point to the correct SQL instances. Probably a consequence of not supporting AD-secured SQL with Datasource environment variables. Even Microsoft support told us last year that we have to delete and re-add every SQL connection each time we move between environments!!! Can you confirm one way or another if this is true? Thankyou!
Yeah Rich I don't have a better answer for you. Sometime remapping SQL is easy and sometimes it is terrible. Have you tried the old import/export way instead of SOlutions. Does it work any better?
@@ShanesCows I wonder if the Environment Variables work for SQL server (the way it does for SP) during migration from DEV to UAT to PROD for Datasource connections?
This is not true. Instead of environmental variables, just use connection references of your different SQL sources. You can configure it for each environment DEV, TEST, and Prod. You can configure this with standard setuo but is even better using Azure Pipelines if you have access to that. Can easily create a deployment settings file to manage environment variables and connection references.
Really any of them. Check out this video that tells you how to build an offline power apps app with any data source ruclips.net/video/NwbqdA9j8Ek/видео.html Or this video, that is way easier, but only works with Dataverse ruclips.net/video/LH6-wUPinnw/видео.html
You missed the hybrid option where it's plausible - Dataverse for Teams. I have an app for concurrent voting by a small number of users built on SPO. For fun I started porting it over to Teams Dataverse and I was blown away by the response time during testing. The only sticking point is that it can only be run within Teams.
Yeah, someone else pointed out I missed D4T. 😟 I wish I had of thought of it. Here is the comment to their post thought might give you perspective on why I forgot it. Post: Controversial take. We almost never use D4T. We find it has so many little "gotchas" that always blow up in our face. I didn't even think about it when making the video. If I had, then it would have probably been 5th and Excel would have went to 6th. It is sad, I really wanted it to be the perfect data source and thought it would be when it was in Beta. But when it rolled out it just had so many little things that didn't land right, we have not found much use for it. If you are having good luck with it, then please keep using it. But my ranking puts it pretty low. Here is the link to LinkedIn if you want to chime in. www.linkedin.com/posts/cincyshane_powerapps-sharepoint-sqlserver-activity-7076536606081380352-i98B?
@@ShanesCows can you elabore a bit more? I have an app with 300 users running on top of DV4T. This made me worried it will crash and burn. So far I have not seen any issues.
I use D4T for some apps. I find it works great for collecting images as they just save as images. No messing with attachments. Also I set users up with it on their teams ribbon so they can access it really easily.
Hi Shane ! Thanks again for your great work. Learnt so much from you. I would like to connect my SQL Database to Powerapps. It is not a Azure SQL, it is hosted on my cloud server at WHC and i can access throung my Cpanel or WHM interface. I tried eveything but always get a BadGateway error. Is it even possible to connect non Azure database? or does it has to be on premise absolutely like on my personnal computer/server ? Thanks
Cathy if you install the On Premises Data Gateway in the same place that your SQL is hosted, so it was "local", then you could do it. If not I don't have any great suggestion off the top of my head. YOu are probalby looking at hitting it via API.
nice share, been struggling with Sharepoint list on delegation issue, considering the case for far more than thousands of record, it seems not working on filtering/sorting & searching... Any suggestion? Should the data source be changed? thank you.
Shane love the video, I’ve been developing with Power Platform and SQL server for a couple years now. The organization I work with is moving to an Azure Managed instance. We work with a lot of Medicaid Data so any suggestions on how to secure the endpoint for the MI so that our end users can access the information within the app? We’re currently using a VPN but that just seems so out dated. I think this is an instance where DataVerse might be the superior datasource or at least it’s the data source that MS wants us to use?
I am not an expert here but I will point you in a general direction for you to decide from. 😎 I think what you want to do is give the Power Platform IPs access to your managed instance. learn.microsoft.com/en-us/power-platform/admin/online-requirements Then your apps should be able to talk to your data without a VPN. Moving your data to Dataverse would also be an option that would work as Power Apps can always talk to your DV. But, I am far from an expert here so please get any of my thoughts verified by an adult. 🤣
@@ShanesCows I’d definitely rather not migrate to DataVerse, but I get the feeling MS wants me to regardless. I didn’t realize you could configure the IP of the app! I’ve been trying to figure out how to register our Apps in AZ so I can add them to our subnets but so far my research hasn’t been fruitful. I’ll take a look at your suggestion. Hopefully it turns out better for me. Thank you again for the suggestion and the content! Would be where I am today without your videos back in 2019/2020
Hey Shane. I have a budget tracker app and it runs off SharePoint (Microsoft list). Initially I didn’t expect it to delegation to be an issue. However the more I learn and see the list grow in data, I am worried it is not going to work as intended. Is it easy to convert the list to dataverse or have I got to start over?
It depends. 🙃 Sorry, that is the best answer I got. You would probably start with a data flow to move your SP data to DV. How easy that is depends on how complex your SP data is. I am going to think about this question more and see if I can make a good video on it. The challenege is there are so many "it depends" the video might be not helpful. Hmmm
My first attempt at dataverse I ran into read/write permissions issues… so reverted back to SP. how do you actually manage the read/write permissions into dataverse? I assume I missed something from the original set up
Quick question If we dont want the data to be visible to everyone then we should go for dataverse right? SharePoint and excel we need to grand permission if we do so then they can manually modify it right?
Dataverse gives you the most options for security, yes. Excel is all or nothing. With SharePoint if you want to jump through a lot of hoops check out this video, you can manually control permissions. ruclips.net/video/vGSNrj82JcI/видео.html
You can't ignore excel or csv as there are a lot of external programs and data from there are available only in excel or csv. Excel is good datasource for input. Later you need to save data to Sharepoint or Dataverse. I agree that edit data in Excel bad idea
Fair point. That said. if you have the license, I always recommend storing those files on a sharepoint and then using a dataflow to extract and load the data into dataverse.
Working for a non profit, which is one of the primary drivers of writing powerapps for stuff rather than purchasing software products in the first place, it's impossible to justify the per user licensing cost of dataverse. "What? You mean we have to buy a license for every user to use the app?" What's missing is a premium tier between per user and per app licensing. Per env I might be able to sell. we have a premium pbi workspace, so the logic is similar.
the main issue is how much does the connector cost. We would love to use dataverse or sql but the connectors cost alot when you have thousands of users.
Remember that licensing is enforced on the Users of the app, not the developers. So I can build an app without a premium license but if the app is premium and I share it with 10 people then all 10 will need a premium license to use the app.
How to work with pipelines and SQL Server? I mean, if you want to deploy you app in a PROD Enviroment, you should export and re-import it manually, right? You can not use pipelines since there is no support to enviroment variables and SQL Server.
Great video, thank you so much. I use Dataverse with power apps and MS access. I have a question when power apps sends new data to Dataverse, it does not always update in MS access. Do you know how I can get it to do so?
I am developing an app with Azure SQL DB as datasource but I am having two issues: 1- I can't connect a table that has a stored procedure. It makes things a little bit more complicated 2- I am having delegation warnings with simple functions like filter and summarizing, which in theory are delegable. Any reccommendation about that?
I found some of your RUclips videos about power apps, but was unable to find one to address my specific problem and was hopeful you might be able to help. If I already have connections to 2 data sources or excel tables in my app, and I need a drop down to reflect the values of a specific one of those 2 tables, based on the table name value that exists in a gallery, how would I do that? In the items property of the drop down, I can see where you specify the data source/table name, however I can’t seem to find the right formula/reference so that the text value from my gallery = the data source to be used. Any thoughts would be greatly appreciated, thank you!
You would have to do some type of If In a dropdown you would have something like this: If(Gallery1.Selected.Department = "Executive", ExecutiveTable, NonExecutiveTable) THat would be the idea.
What about Powerapps for Teams using Dataverse vs. regular Powerapps using Sharepoint? Not allowed anything premium at this point. My user base hasn't been sold on Teams, though I could get the ones that have to use the apps to use it, but I hate the idea of using Sharepoint as a data source.
Dataverse for Teams is an option I should have mentioned. But honestly we never use it. There are a bunch of little gotchas with it that always seem to get us so we just generally don't use it. I would rank it above Excel but below SharePoint if I was redoing my list. I think there was another comment where we discussed this also but I told them the same thing. Hope it helps, keeping in mind it is all just my opinion. 🙃
Hi Shane Young, thank you for the nice video, for Sharepoint, I do not know how to use it as a relational database, SQL and Dataverse can create a relational data structure, Sharepoint seems do not have the same function,
In SharePoint you can use LookUp columns just like in Dataverse if you want. What I prefer is to maintain my own key. This video will show you how I do it with SharePoint ruclips.net/video/xgznk4XlPCo/видео.html
@@ShanesCows Yes Shane Young, I just had a look, what I thought is that Sharepoint is not a best practice for relational data, can it be atomic for the process? Can it work perfectly like Dataverse or SQL regarding the data transactions?
I am thinking to create an app (after I learn) for 130 people, only 4 columns, but about 7k items (rows) a year, would Sharepoint be enough as a Datasource? It is simple app for workers adding leaving/returning info: adding text for a leaving reason and adding leaving date, and after they are coming back to add returning date. But I wonder what if it begins to lag or something (errors), maybe I should not event start with an app, or maybe to give edit Sharepoit directly without an app, hmm.
I think SharePoint will be okay to start and learn. If you do start to outgrow it then consider archiving the older items to get the number of items lower.
Will all users who use the powerapps application have to have the premium license or only those who have direct access to the database for its creation?
@@ShanesCows I'm struggling with this 😭. I initiated a project which I will use Wide World Importers in a Azure SQL Database, but I get stuck in populate procedures (that add sample data to the current date)
Hello Shane, I'm building a Power app from an Excel spreadsheet but I want to change the data source. The problem is I'm struggling to get it all together in one app. Can you help me out with this problem?
Everything you need to build a Model-driven Power App ruclips.net/video/KFjJ97dKzO8/видео.html 😎 start there and then let me know what questions you have next.
I use on-premises SQL Server and frequently utilize Dataverse, powered by a data stream, for historical data that needs to be displayed and searched. It's an excellent combination.
I love to switch from SP to dataverse, but thr problem is i might have around 1k user of the app, does that mean i also need to the the same number of premium licenses for each user?
Hi Shane, I'm using dataverse as a connector, but I have a lot of trouble sharing the data with internal users. I've given them access to the security group, environment, the app within the environment, and all the dataverse tables, I've delegated them security roles, dataverse search role and environment maker, still they cannot see the data in the drop downs from the dim tables except for when I give them the "system administrator" security role, and that puts my app at risk. Any advice?
But what is using Excel as the data source is "a business requirement"? Asking for a friend. 😉 On a semi-related note, it seems there's a small resurgence of interest in Access. Microsoft is cautious as ever and won't tell people "it's going away for good", so lots of people are still using it. Maybe consider making an "Access v. Power Apps/Dataverse" video?
I have a question about licensing. If you have a PowerApp licence per App, what is about 10 usd / month, can you use Dataverse as a datasource with basic or standard O365 users?
you can use it for that app only. also every user using the app needs to have at least the same licence (or pay as you go agreement at the environment level)
I think I need to do a licensing video. 🤩 Licensing is for the user of the app, not the creator. So you can build a premium app without a premium license. But everyone you share it with will need a premium License to use it.
@@ShanesCows I was having a discussion with someone last week and they asked me this exact question: do I need a licence to build a premium connector app? I knew they could, but when searching the MS documentation there is no mention of it. How can we prove (except obviously by trying) that this is the case (for example to the IT department)?
I need to find a way to start back up the conversation about moving out of sharepoint. We currently have 4 apps with several hundred users and we are building more. my predecessor built the entire ecosystem and any time i talked about dataverse or sql he said "no, $$$. SharePoint is fine"
As always, nice video Shane. Premium licensing is just not viable imo. With a company of 1000 staff, that'll be an extra $20000/month to fork out. Yikes!
@@ShanesCows True! I also realise many apps do not require the whole organisation to access it, so apps can be designed for specific departments/teams only.
@@ShanesCows I'm kind of curious about getting a discount. How does one go about asking for that? Let's say we already have 365 and we're talking about 5000+ employees, but maybe only 200 or so are actually going to be using the apps?
I have one app that I feel is forces to use Ecel as a data source. If anyone has a better suggestion, I am open to it. Ideally this should be a SQL connection, because all that is being done is a simple SQL query is being run in power query - and that seems to be the sticking point. Our organizational security structure doesn't allow the vast majority of the end users to access SQL server. So, in order to have refreshed data, every month I need to update the excel file. If the data was static SharePoint would be fantastic, but the data changes somewhat month to month. There is also a delegation issue as there are ~4300 rows and excel will let you get around that, but SharePoint seems to be capped.
So please please please can someone explain if I build an app on Dataverse and say my users will have 2 or 3 apps they routinely use, what licenses do they need? I can never get it straight!
Then you would most likely by per app license. Then each user would need a license per app. If you take retail pricing it is $5 per user per month. The per user is $20. So not till you get to 4 apps per user would you want to consider changing. Hope that helps
Absolutely spot on Shane, especially on excel as database,. It's never been a database and it will never be one, it's an incredible spreadsheet tool and it should be used only as that, a spreadsheet.
With SQL you are more independent. And only because MS using Dataverse for nearly everything in PowerApps it does not need to be the best. I prefer to stick to SQL. It has a more flexible interface to other infrastructures
I'm still in two minds whether PowerApps as a platform is a good solution for applications with big data. Seems to me the performance of PowerApps can't compete when comparing it to a classic web frontend and sql backend for instance. Thoughts?
Think of it like this, there are times when it makes sense to write an application in c or other low level languages to eke out the absolute most performance. Doing so though comes at the cost of requiring advanced degrees which is less assessable than something like python. There are many use cases though where python is perfectly reasonable to write code in and it’s much faster to develop than implementing the same thing in c (that’s besides the debate on interpreted vs compiled code which has similar considerations in flexibility of development vs performance at runtime). This analogy carries over into power apps, except in that case the accessibility is even higher, to the point that people familiar with the data can potentially build/tweak the app/interfaces they have to the underlying data themselves. Basically, ask yourself how much customization/tweaking do you want non-developers to have? If I’m an expert in a given field, being able to build/tweak my own interface relatively easily to the underlying data can be huge. The extra second it might take to load/calculate every-time they use it might suck, but the cost in both time/money to hire a dev, communicate a vision, have that vision iteratively realized can easily justify such a decision. Not to mention when a change is desired and you lose access to the original dev, the hours of code scanning required to understand the custom code original dev built a platform in for a task that could take minutes in a standardized platform.
i agreed with the point u listed, but these top 2 datasource only applicable for high currency countries western/europe company, for most of the asian SME company they prefer to hire programmer to handle the huge database instead of upgrading to premium package by double up the costing, microsoft marketing is too weak in marketing analyse, if they able to adjust the premium costing and they might able to shaking all other cloud storage services company with this powerapps
I think you should have broken out Dataverse and Dataverse for Teams. I would put Dataverse for Teams second and shift everything down. I would even go so for as saying Dataverse for Teams is the better "free" option.
Sorry I forgot D4T. If I hadn't of I would have put it at 5th and moved Excel to 6th. We have not had good luck with D4T. Glad it is working well for you. 😎 If you check the other comments you can see a few different conversations on it. 😎
this doesnt seem to be available in the stage gov environment im on. nor can i connect ms access to sharepoint to manipulate data without writing flows
@Cows This is a bit surprising and disappointing. I work for a small cash strapped NGO so I was thinking that it would be a good option. Would you recommend Sharepoint over Dataverse for Teams then? These are the two options available to us.
I'd love to flip my excel online timesheets from excel to dataverse but I'd then have to get 60 dataverse licences and probably more for those who want to see the powerbi reports. It's a hard sell
Well now I feel a little silly. I use Excel, but really only because it's just how I learned to create my Power BI reports & eventually Power Apps. Don't really have a data science background. I've heard of SQL & Dataverse, but know nothing about them or how to use them. The main reason I use Excel is that I know it (like probably everyone)...and typically manipulate my data before incorporating it into my master data source file.
Rob if it is working for you that is okay. You will just find it has challenges as you learn to build bigger and badder apps. I would recommend replacing your excel Table with a SharePoint list as your first step. 😎
@@ShanesCows you know...my bad. I had Power BI & Power Apps mixed up in my quick comment of your video. How I actually have it built is my Power App is connected to a SharePoint list...to which I export the completed entries to Excel...which I have connected to my Power BI as it's data source. Sorry for the confusion. But...Garrett & Jeff have been super helpful with my App FYI!
You might find the series of Canvas Apps vs. Model-driven apps helpful. ruclips.net/video/SKSfgyWP2jc/видео.html Shows you how to build the same Dataverse app with both tools. 😎
Im so desperate to switch over from SPL to Dataverse with my project app but sad to say im just a user level in our organization and our admin does not have any idea aside from creating a 365 profile. Grrrrr , I want freeeee Dataverse ahaha
Meh. SharePoint is the starting point for all projects. It might evolve elsewhere as apps expand, but SharePoint is the best choice for a universal answer. Your answer reeks of Microsoft MVP status.
Sorry you feel that way. I wasn't going for the most common answer, I was going for the best answer. I would say that I have built more Power Apps than just about anyone on the planet, and I can tell you that Dataverse is the best. 80% of apps are built on SharePoint, it is the most popular, but that doesn't make it the best. I used to openly hate Dataverse, but that ship has sailed. It caught up and passed all of the other ones. Dataverse still isn't perfect by any means, it has plenty of challenges and room for growth. But if you want the fastest, most secure, most functionality data source Dataverse wins by a mile.
Can someone comment on exactly why Excel is so bad as a data source? The video and the comments are filled with people bashing it, but I never actually heard why it's so terrible. What is likely going to go wrong, and if the scale is small enough is it really going to matter?
For my next project I'm going to setup a home SQL server and build a power app over it. Do you have or know of any videos that can help with either? I have never used SQL but I have installed it so many times as an IT admin. TIA! Great video btw
This one talks about on prem data gateway. Old but concept is same. That will let you talk to the sql. From there remember your table needs a primary key.
Powerapps on premise data gateway
ruclips.net/video/uqx_h4Ym8ng/видео.html
This might also help.
Azure SQL Database tutorial with an end result of a working PowerApps sql app
ruclips.net/video/CO6xfbjnYwc/видео.html
@@ShanesCows I did both on prem and could SQL. THen I had gpt make it all into a PS script that made it in sharepoint too. Just so its free when it comes time to use it. Great lab! ty again Shane!
@@ShanesCows I haven't given up on signing up for your website and I'm working towards it, but my circumstances changed some this month when I decided to improve my job situation. I've been on about 10 interviews in 2 weeks so I'm just waiting to hear back now. Once I have this all worked out I will def be signing up. =)
@@MichaelDeBlasis1369 Good luck! Did you see this video to help with interviews? ruclips.net/video/cVmd2oFAoFk/видео.html
100% agree! I am a SQL expert but I found out dataverse just goes better with power platform and actually I have never used any data sources but dataverse to build power platform apps.
Awesome!
You will see this link referred to on the forum a lot - just used it today. I also loved your comments and ranking of SharePoint and the 80% use comment - that would be my judgement from forum posts as well. I have never needed to use anything else in the five years I have been writing apps - it is a good solid reliable platform and better still out of the box at no extra cost.
Warren Belz
Thanks Warren. Glad that the video is helpful. I Appreciate all of the hard work you do in the forums. ⭐️
Spot ON!
I am literally having to deal with the data source problem right now. I developed an App that has Dataverse backend and did not realize the cost of the Premium. Well working with a company that has 6K+ users (estimated 1500 that would use my app) I had to nix the idea and go back to working with SharePoint instead. Here is my scenario for anyone else to learn from my mistake...
1. The app is approval flow is only a temporary solution to help our company get off of MOSS and SP 2010 servers (YUCK).
2. Dataverse is great for everything Shane says, but the premium licensing per user per month was going to add up. Until I can save the company money elsewhere, it is not easy to convince them to spend money on the Premium.
3. The app/SharePoint list will see about 5-10 entries a day with the possibility of an occasional bulk import of data.
4. This interim app is only in place until end of 2024. I am confident that speed will not be a huge issue during this time as long as I keep the control that I am doing with my Gallery loads and how I filter the data.
What a relief. Just been migrating an SP-based Powerapp to Dataverse so your video is very reassuring. I’m seeing the performance improvement too.
One important point about SQL-server is that you can run it on-prem and have all the data in your own data center and use the rapid application development capabilities of the power platform without moving everything to the cloud.
Nice point! Thanks for the reminder about on-prem.
One more reason to adopt Dataverse, IMO, is the ability to work Offline. I don't know if any other data source is supported for offline use, but the scenarios where I would encourage adding PowerApps to your toolkit (I'm a retired Access developer) often involve remote locations--think warehouses, job sites, and such--physical locations where an internet connection is not guaranteed.
Scanning barcodes in a warehouse for Stock Take comes to mind as a prime example.
The new way of offline is on DataVerse but we have been building them the old way offline with other datasource for years. 😎 I agree offline is a great tool.
Does offline not only apply to model driven apps ?
Till Dataverse and SQL become free and open or at least reasonably priced specially for for mid/big firms, will continue to use Excel (which outperforms Dataverse in some cases - my actual testing) and SP.
After 20 years of using Excel and 5 years developing automated Power Query solutions, I am an Excel geek, I love Excel.
However, I've literally just started my Power Apps journey in the past few weeks (late to the party I know) and just watching your videos (with others I mentioned in a tweet including you Shane) I have already told my bosses the way to go is (drum roll...), exactly the same priority in your list.
I've said we should be looking at Dataverse moving forward and not Excel or even Lists (albeit I have to start somewhere to learn the basics).
Just seems more logical?
So, as a numpty beginner I agree with your results :)
High five Paul! Enjoy the journey power platform is a lot of fun. 😎
Thanks Shane! Good video for sharing with people who doesn't know differences of data sources, because for many of citizen developers SharePoint is only one thing.
Thanks. Yeah, I tried to get some details in there to get people thinking.
Hi Shane,
We generally get a threshold limit error once the records in an sharepoint list exceed 25000 records which restricts us to add or delete any column fields.. therefore it was recommended to use an excel file saved under a sharepoint folder, apparently i was informed that in an excel we can store more than 100000 records without any threshold limit error. Let me know of your views in this regards.
Nice summary Shane, no surprises which is good. But I feel you could have included a mention for dataverse for teams. I use this extensively without premium license. I think dataverse fot teams is no 1 (taking cost into account).
haha i do too but they could close that loop any time
I just finished a project that takes an Excel upload as a template, stores it in SharePoint, validates it against SAP, handles approvals and configurations in Dataverse, and then writes data to SAP. Shane is 100% right. I think it’s more of a Dataverse as much as possible first approach and then find the best solution for the other stuff. As an example the Excel connector does not work with Dataverse so my next best option was SharePoint (with the client’s use case)
Hi Andy, could you share more ideais about the part where you "write data to SAP" with power apps?
Write to SAP is something that I would love to know about
Love the video!. Thank Shane! Although I have watched most of your videos and have learned a lot from them, I have to disagree a little bit with the ranking. I think the order from 5 to 1 is Excel, SharePoint, almost anything else (1000+ connectors), SQL, Dataverse. This is based on all of the reasons you provided regarding SharePoint, delegation, not a RDBMS, problems with large amount of data.... yada, yada.
Fair enough! SharePoint will just always have a special place in my heart. 😎
Thank you for making this. It's rare that I get requests to build an app. But, when I do, my employer says we can use SharePoint for the back end. I tell them no and why. Now, I can add your voice. Hopefully, they'll believe one of us.
I wish us both good luck.
this ranking helps, thx. need the security, scalability, features, etc, but have been on the fence committing to dataverse for solution
Glad to give you more food for thought. 😎
Thanks Shane, you are right of course, but as we are a non profit organization getting MS365 for free we just don’t have the resources to move to premium. I started building a local Access database than moved the data to a file server and after that moved the data to SharePoint and am now in the process to replace the access frontend with PowerApps we will need to stick with SharePoint.
No harm in that Peter. Not everyone can afford or is ready for premium data sources. That is why I think 80%ish of apps are built on SharePoint. No judgement here. 😎
I fully agree! Thanks Shane!
Yes! Someone who agrees. ⭐️
It depends on the platform (Public vs GCC), what the client has available and the type of data that you are going to be handling. Currently, our client (GCC) does not have DataVerse or SQL server available. We have defaulted to using SharePoint for everything. We are waiting on the approval of DataVerse and will be moving our items to that in the future. None of our items include PII so having someone see the SP list is not as big of a deal. I have never created a PowerApps using excel! LOL! I try to stay out or convert excels to a SP list as much as possible.
Sounds like you have a solid understanding. 😎
Dataverse is a steep learning curve for me 😕😕. Planning to start your university this winter, so hope to get better at it. I still stuck to SharePoint. THX for great video about the subject.
Dataverse is tough to get going but once it clicks you will love it.
Shane, what are your thoughts on building Canvas Apps against SQL Server, where all of the data access has to be done via stored procedures in the SQL Server database? My understanding is that you have to call each stored procedure from a Flow. Is that correct? If so, are there any disadvantages to this approach? If your users have Power Apps per User licences, they can call unlimited flows, right? But, will the Canvas App perform well? Won't Flows introduce latency? Is there another method of executing stored procedures from a Canvas App without calling Flows?
Pete check out the new low code dataverse plugins. I believe calling SQL Stored Procedures is one of their current use cases learn.microsoft.com/en-us/power-apps/maker/data-platform/low-code-plug-ins I haven't built one yet but it is on my list to try.
@@ShanesCows Thanks Shane! Very helpful. Would be a great one for a video!
Thanks Shane. Good staff.
One thing though
Security in sharepoint is a 3 click process
You can lock users from seeing the page and use Power Apps unrestricted
You can hide it but they still have access. They could write their own flow for example that the list would still have access to. There are ways to hide from SharePoint but there a lot of little things to be careful with.
@@ShanesCows Thanks Shane for the video very useful! The way I did it is by using a flow with a service account that has write permissions on the sharepoint list. The user clicks on confirm in the app, the flow starts running and the serviceaccount writes the adjustments in the list. The user has only view permissions for the list.
I’ll head over to your website and have a look keep this good Contant coming
Will do! Thanks :)
Thanks for this great overview @ShaneCows. We use AD-secured Azure SQL with our canvas apps, but have been pulling our hair out with ALM support. i.e. you can't promote an app from Dev to Test to Production environments using the standard Solutions mechanism because the system won't re-wire the data connections to point to the correct SQL instances. Probably a consequence of not supporting AD-secured SQL with Datasource environment variables. Even Microsoft support told us last year that we have to delete and re-add every SQL connection each time we move between environments!!! Can you confirm one way or another if this is true? Thankyou!
Yeah Rich I don't have a better answer for you. Sometime remapping SQL is easy and sometimes it is terrible.
Have you tried the old import/export way instead of SOlutions. Does it work any better?
@@ShanesCows I wonder if the Environment Variables work for SQL server (the way it does for SP) during migration from DEV to UAT to PROD for Datasource connections?
This is not true. Instead of environmental variables, just use connection references of your different SQL sources. You can configure it for each environment DEV, TEST, and Prod. You can configure this with standard setuo but is even better using Azure Pipelines if you have access to that. Can easily create a deployment settings file to manage environment variables and connection references.
Why are'nt I subscribed yet! Done! Brilliant. PowerApps is really taking off these days with Low Code and GPT being a match made in heaven!
Thanks and I agree. 😎
Great stuff! Working on this now
Thanks Jeff! 😎
gracias Shane ... you are the best in this topic ,,, I trust you!!
I appreciate that!
thank you for sharing this video. can you tell which offline database we can use with power apps?
Really any of them. Check out this video that tells you how to build an offline power apps app with any data source ruclips.net/video/NwbqdA9j8Ek/видео.html
Or this video, that is way easier, but only works with Dataverse
ruclips.net/video/LH6-wUPinnw/видео.html
I totally agree with you ranking. Thank you Shane
⭐
You missed the hybrid option where it's plausible - Dataverse for Teams. I have an app for concurrent voting by a small number of users built on SPO. For fun I started porting it over to Teams Dataverse and I was blown away by the response time during testing. The only sticking point is that it can only be run within Teams.
Yeah, someone else pointed out I missed D4T. 😟 I wish I had of thought of it. Here is the comment to their post thought might give you perspective on why I forgot it.
Post:
Controversial take. We almost never use D4T. We find it has so many little "gotchas" that always blow up in our face. I didn't even think about it when making the video. If I had, then it would have probably been 5th and Excel would have went to 6th.
It is sad, I really wanted it to be the perfect data source and thought it would be when it was in Beta. But when it rolled out it just had so many little things that didn't land right, we have not found much use for it.
If you are having good luck with it, then please keep using it. But my ranking puts it pretty low.
Here is the link to LinkedIn if you want to chime in.
www.linkedin.com/posts/cincyshane_powerapps-sharepoint-sqlserver-activity-7076536606081380352-i98B?
@@ShanesCows can you elabore a bit more? I have an app with 300 users running on top of DV4T. This made me worried it will crash and burn. So far I have not seen any issues.
I use D4T for some apps. I find it works great for collecting images as they just save as images. No messing with attachments. Also I set users up with it on their teams ribbon so they can access it really easily.
Thx a lot Shane for that review !!
Happy to help. Have a great day. 🐶
Thanks for sharing!!! If you use Azure SQL Server as data source, Is Blob Storage the best data source for storing files and images?
Yes it is! Never store files in SQL that is for sure. 😎
Hi Shane ! Thanks again for your great work. Learnt so much from you. I would like to connect my SQL Database to Powerapps. It is not a Azure SQL, it is hosted on my cloud server at WHC and i can access throung my Cpanel or WHM interface. I tried eveything but always get a BadGateway error. Is it even possible to connect non Azure database? or does it has to be on premise absolutely like on my personnal computer/server ? Thanks
Cathy if you install the On Premises Data Gateway in the same place that your SQL is hosted, so it was "local", then you could do it. If not I don't have any great suggestion off the top of my head. YOu are probalby looking at hitting it via API.
@@ShanesCows ohh nooo :( thanks anyways for your fast response.
nice share, been struggling with Sharepoint list on delegation issue, considering the case for far more than thousands of record, it seems not working on filtering/sorting & searching... Any suggestion? Should the data source be changed? thank you.
Shane love the video, I’ve been developing with Power Platform and SQL server for a couple years now. The organization I work with is moving to an Azure Managed instance. We work with a lot of Medicaid Data so any suggestions on how to secure the endpoint for the MI so that our end users can access the information within the app? We’re currently using a VPN but that just seems so out dated. I think this is an instance where DataVerse might be the superior datasource or at least it’s the data source that MS wants us to use?
I am not an expert here but I will point you in a general direction for you to decide from. 😎
I think what you want to do is give the Power Platform IPs access to your managed instance. learn.microsoft.com/en-us/power-platform/admin/online-requirements Then your apps should be able to talk to your data without a VPN.
Moving your data to Dataverse would also be an option that would work as Power Apps can always talk to your DV.
But, I am far from an expert here so please get any of my thoughts verified by an adult. 🤣
@@ShanesCows I’d definitely rather not migrate to DataVerse, but I get the feeling MS wants me to regardless. I didn’t realize you could configure the IP of the app! I’ve been trying to figure out how to register our Apps in AZ so I can add them to our subnets but so far my research hasn’t been fruitful. I’ll take a look at your suggestion. Hopefully it turns out better for me. Thank you again for the suggestion and the content! Would be where I am today without your videos back in 2019/2020
Hey Shane. I have a budget tracker app and it runs off SharePoint (Microsoft list). Initially I didn’t expect it to delegation to be an issue. However the more I learn and see the list grow in data, I am worried it is not going to work as intended. Is it easy to convert the list to dataverse or have I got to start over?
It depends. 🙃 Sorry, that is the best answer I got. You would probably start with a data flow to move your SP data to DV. How easy that is depends on how complex your SP data is.
I am going to think about this question more and see if I can make a good video on it. The challenege is there are so many "it depends" the video might be not helpful. Hmmm
My first attempt at dataverse I ran into read/write permissions issues… so reverted back to SP. how do you actually manage the read/write permissions into dataverse? I assume I missed something from the original set up
You have to setup security roles ruclips.net/video/7Qv1Qtsb5ew/видео.html
Quick question
If we dont want the data to be visible to everyone then we should go for dataverse right?
SharePoint and excel we need to grand permission if we do so then they can manually modify it right?
Dataverse gives you the most options for security, yes. Excel is all or nothing. With SharePoint if you want to jump through a lot of hoops check out this video, you can manually control permissions. ruclips.net/video/vGSNrj82JcI/видео.html
You can't ignore excel or csv as there are a lot of external programs and data from there are available only in excel or csv. Excel is good datasource for input. Later you need to save data to Sharepoint or Dataverse. I agree that edit data in Excel bad idea
Fair point. That said. if you have the license, I always recommend storing those files on a sharepoint and then using a dataflow to extract and load the data into dataverse.
Yes, injesting Excel/CSV data is a great use case. Something like this ruclips.net/video/TiFVQ8UrjDM/видео.html
Working for a non profit, which is one of the primary drivers of writing powerapps for stuff rather than purchasing software products in the first place, it's impossible to justify the per user licensing cost of dataverse. "What? You mean we have to buy a license for every user to use the app?"
What's missing is a premium tier between per user and per app licensing. Per env I might be able to sell. we have a premium pbi workspace, so the logic is similar.
Sorry, have you checked your pricing. A lot of non-profits get deep discounts on licensing.
the main issue is how much does the connector cost. We would love to use dataverse or sql but the connectors cost alot when you have thousands of users.
It can but have you talked to Microsoft? Most customers who have 1000s of users get a very large discount in licensing. 😎
So, if possible, limit the number of developers. It's still expensive, but not as bad.
Remember that licensing is enforced on the Users of the app, not the developers. So I can build an app without a premium license but if the app is premium and I share it with 10 people then all 10 will need a premium license to use the app.
@@ShanesCows that's right, I think I started with pro and the users had the premium? I can never keep their licensing straight.
How to work with pipelines and SQL Server?
I mean, if you want to deploy you app in a PROD Enviroment, you should export and re-import it manually, right?
You can not use pipelines since there is no support to enviroment variables and SQL Server.
Great video, thank you so much. I use Dataverse with power apps and MS access. I have a question when power apps sends new data to Dataverse, it does not always update in MS access. Do you know how I can get it to do so?
I am developing an app with Azure SQL DB as datasource but I am having two issues:
1- I can't connect a table that has a stored procedure. It makes things a little bit more complicated
2- I am having delegation warnings with simple functions like filter and summarizing, which in theory are delegable.
Any reccommendation about that?
I found some of your RUclips videos about power apps, but was unable to find one to address my specific problem and was hopeful you might be able to help.
If I already have connections to 2 data sources or excel tables in my app, and I need a drop down to reflect the values of a specific one of those 2 tables, based on the table name value that exists in a gallery, how would I do that?
In the items property of the drop down, I can see where you specify the data source/table name, however I can’t seem to find the right formula/reference so that the text value from my gallery = the data source to be used.
Any thoughts would be greatly appreciated, thank you!
You would have to do some type of If
In a dropdown you would have something like this:
If(Gallery1.Selected.Department = "Executive", ExecutiveTable, NonExecutiveTable)
THat would be the idea.
@@ShanesCows thank you so much! I guess I just had a couple errors in my formula, but this did the trick!
How about dataverse for teams?
Is that a viable option or are the restrictions not worth it.
My options are SharePoint lists vs dataverse for teams.
Check the other comments. I provided my 2 cents on this there. Long story short I think I would pick SharePoint. 😎
What about Powerapps for Teams using Dataverse vs. regular Powerapps using Sharepoint? Not allowed anything premium at this point. My user base hasn't been sold on Teams, though I could get the ones that have to use the apps to use it, but I hate the idea of using Sharepoint as a data source.
Dataverse for Teams is an option I should have mentioned. But honestly we never use it. There are a bunch of little gotchas with it that always seem to get us so we just generally don't use it. I would rank it above Excel but below SharePoint if I was redoing my list. I think there was another comment where we discussed this also but I told them the same thing. Hope it helps, keeping in mind it is all just my opinion. 🙃
Hi Shane Young, thank you for the nice video, for Sharepoint, I do not know how to use it as a relational database, SQL and Dataverse can create a relational data structure, Sharepoint seems do not have the same function,
In SharePoint you can use LookUp columns just like in Dataverse if you want.
What I prefer is to maintain my own key. This video will show you how I do it with SharePoint ruclips.net/video/xgznk4XlPCo/видео.html
@@ShanesCows Yes Shane Young, I just had a look, what I thought is that Sharepoint is not a best practice for relational data, can it be atomic for the process? Can it work perfectly like Dataverse or SQL regarding the data transactions?
I am thinking to create an app (after I learn) for 130 people, only 4 columns, but about 7k items (rows) a year, would Sharepoint be enough as a Datasource? It is simple app for workers adding leaving/returning info: adding text for a leaving reason and adding leaving date, and after they are coming back to add returning date. But I wonder what if it begins to lag or something (errors), maybe I should not event start with an app, or maybe to give edit Sharepoit directly without an app, hmm.
I think SharePoint will be okay to start and learn. If you do start to outgrow it then consider archiving the older items to get the number of items lower.
#5 alone made me wish for a double-like option on this presentation.
That was a last minute change. It was originally 4. I think the last minute change was solid. 😂
@@ShanesCows I'd say you captured the essence perfectly. Everything else and, oh yeah, there's also Excel.
Thanks George!
@@ShanesCowscan we set data validation for dataverse fields at table level instead of powerapps
How many rows can I use with SharePoint in my PowerApp when the filter is delegable?
Hi Shane, how can you use a power bi dataset as a data source in power apps
Not that I am aware of.
You forgot to mention that Dataverse comes with Teams. I have my apps in Teams and it's been great.
I did forget about Dataverse for Teams. I have had a few comment conversations around it now. Glad it is working for you.
If we purchase the premium licence for data verse does all users need that access? Or could this licence just be for the developers of power apps etc?
Licensing follows the user, not the developer. So if you want to share a premium app with 10 people the 10 people need a premiume license.
fantastic video thank you for all you do
Thanks for watching!
Will all users who use the powerapps application have to have the premium license or only those who have direct access to the database for its creation?
Everyone who use the app.
How about virtual tables build integranted with SQL Server?
Fancy! I like it. 🤩
@@ShanesCows I'm struggling with this 😭.
I initiated a project which I will use Wide World Importers in a Azure SQL Database, but I get stuck in populate procedures (that add sample data to the current date)
Hello Shane,
I'm building a Power app from an Excel spreadsheet but I want to change the data source. The problem is I'm struggling to get it all together in one app. Can you help me out with this problem?
I don't hear you mention MS Access is Access not a database? Want to know your opinion? Thanks!
Access is not a data source that you can connect to with Power Apps, so that is why I didn't mention it.
Shane, thanks for the insight. Can you direct me material on learning how to setup dataverse tables in a model driven app?
Everything you need to build a Model-driven Power App
ruclips.net/video/KFjJ97dKzO8/видео.html 😎 start there and then let me know what questions you have next.
Awesome, thank you.. trying to cut the cord with excel a bit.
Love it. It will take some work but will be worth it when you get it done. 😎
I use on-premises SQL Server and frequently utilize Dataverse, powered by a data stream, for historical data that needs to be displayed and searched. It's an excellent combination.
Love this!
I love to switch from SP to dataverse, but thr problem is i might have around 1k user of the app, does that mean i also need to the the same number of premium licenses for each user?
Yes. The app is licensed by the users of the app.
Hi Shane, I'm using dataverse as a connector, but I have a lot of trouble sharing the data with internal users. I've given them access to the security group, environment, the app within the environment, and all the dataverse tables, I've delegated them security roles, dataverse search role and environment maker, still they cannot see the data in the drop downs from the dim tables except for when I give them the "system administrator" security role, and that puts my app at risk. Any advice?
But what is using Excel as the data source is "a business requirement"? Asking for a friend. 😉
On a semi-related note, it seems there's a small resurgence of interest in Access. Microsoft is cautious as ever and won't tell people "it's going away for good", so lots of people are still using it. Maybe consider making an "Access v. Power Apps/Dataverse" video?
Thanks for the idea Chat. I am sure anything Access related would be popular. 😎
I have a question about licensing. If you have a PowerApp licence per App, what is about 10 usd / month, can you use Dataverse as a datasource with basic or standard O365 users?
I have the same question, following;
you can use it for that app only. also every user using the app needs to have at least the same licence (or pay as you go agreement at the environment level)
I think I need to do a licensing video. 🤩
Licensing is for the user of the app, not the creator. So you can build a premium app without a premium license. But everyone you share it with will need a premium License to use it.
@@ShanesCows I was having a discussion with someone last week and they asked me this exact question: do I need a licence to build a premium connector app? I knew they could, but when searching the MS documentation there is no mention of it. How can we prove (except obviously by trying) that this is the case (for example to the IT department)?
I need to find a way to start back up the conversation about moving out of sharepoint. We currently have 4 apps with several hundred users and we are building more. my predecessor built the entire ecosystem and any time i talked about dataverse or sql he said "no, $$$. SharePoint is fine"
Good luck. If your company is using that much Power Apps and growing getting to a better data source sooner than later will be great for you.
I have monthly approx. 8000 rows of items. I cannot use premium accounts. Is there a workaround?
Not really, you will just want to try to archive as much as possible to keep sizes low.
Thank you Shane.
Happy to help.
@@ShanesCows can you recommend resources for advance Dataverse topics. Thanks
every single user needs premium 365 to use a powerapp with dataverse or only de developer? Any way arround it?
Every user of the app, no way around it, sorry.
As always, nice video Shane.
Premium licensing is just not viable imo. With a company of 1000 staff, that'll be an extra $20000/month to fork out. Yikes!
It doesn't work for everyone, but if you have that many people you can usually get a pretty decent discount from Microsoft. 😎
@@ShanesCows True! I also realise many apps do not require the whole organisation to access it, so apps can be designed for specific departments/teams only.
@@ShanesCows I'm kind of curious about getting a discount. How does one go about asking for that? Let's say we already have 365 and we're talking about 5000+ employees, but maybe only 200 or so are actually going to be using the apps?
I have one app that I feel is forces to use Ecel as a data source. If anyone has a better suggestion, I am open to it.
Ideally this should be a SQL connection, because all that is being done is a simple SQL query is being run in power query - and that seems to be the sticking point. Our organizational security structure doesn't allow the vast majority of the end users to access SQL server. So, in order to have refreshed data, every month I need to update the excel file. If the data was static SharePoint would be fantastic, but the data changes somewhat month to month. There is also a delegation issue as there are ~4300 rows and excel will let you get around that, but SharePoint seems to be capped.
Hey Shane, please explain why powerpages theme die on preview
So please please please can someone explain if I build an app on Dataverse and say my users will have 2 or 3 apps they routinely use, what licenses do they need? I can never get it straight!
Then you would most likely by per app license. Then each user would need a license per app.
If you take retail pricing it is $5 per user per month. The per user is $20. So not till you get to 4 apps per user would you want to consider changing.
Hope that helps
Absolutely spot on Shane, especially on excel as database,. It's never been a database and it will never be one, it's an incredible spreadsheet tool and it should be used only as that, a spreadsheet.
Well said!
@@ShanesCows It depends on how you use Excel: Table, or Spreadsheet. Excel has outgrown spreadsheets. It's something else right now.
With SQL you are more independent. And only because MS using Dataverse for nearly everything in PowerApps it does not need to be the best. I prefer to stick to SQL. It has a more flexible interface to other infrastructures
SQL is the best if you are planning to have other systems talk to your data, I agree.
I'm still in two minds whether PowerApps as a platform is a good solution for applications with big data. Seems to me the performance of PowerApps can't compete when comparing it to a classic web frontend and sql backend for instance. Thoughts?
Think of it like this, there are times when it makes sense to write an application in c or other low level languages to eke out the absolute most performance. Doing so though comes at the cost of requiring advanced degrees which is less assessable than something like python. There are many use cases though where python is perfectly reasonable to write code in and it’s much faster to develop than implementing the same thing in c (that’s besides the debate on interpreted vs compiled code which has similar considerations in flexibility of development vs performance at runtime).
This analogy carries over into power apps, except in that case the accessibility is even higher, to the point that people familiar with the data can potentially build/tweak the app/interfaces they have to the underlying data themselves.
Basically, ask yourself how much customization/tweaking do you want non-developers to have?
If I’m an expert in a given field, being able to build/tweak my own interface relatively easily to the underlying data can be huge. The extra second it might take to load/calculate every-time they use it might suck, but the cost in both time/money to hire a dev, communicate a vision, have that vision iteratively realized can easily justify such a decision. Not to mention when a change is desired and you lose access to the original dev, the hours of code scanning required to understand the custom code original dev built a platform in for a task that could take minutes in a standardized platform.
My company won't expose SQL or Dataverse to me as a business user😢. I'm stuck with SharePoint
how to connect with we based api in power apps please guide me
This should help ruclips.net/video/p8ErJU43N74/видео.html
Thank you
You're welcome
i agreed with the point u listed, but these top 2 datasource only applicable for high currency countries western/europe company, for most of the asian SME company they prefer to hire programmer to handle the huge database instead of upgrading to premium package by double up the costing, microsoft marketing is too weak in marketing analyse, if they able to adjust the premium costing and they might able to shaking all other cloud storage services company with this powerapps
I think you should have broken out Dataverse and Dataverse for Teams. I would put Dataverse for Teams second and shift everything down. I would even go so for as saying Dataverse for Teams is the better "free" option.
Sorry I forgot D4T. If I hadn't of I would have put it at 5th and moved Excel to 6th. We have not had good luck with D4T. Glad it is working well for you. 😎 If you check the other comments you can see a few different conversations on it. 😎
@@ShanesCows yeah, I saw that later. Might be a good video for the future showing some of D4T drawbacks compared to Dataverse.
Completely agree with you. Excel is a great tool but a very bad data source. :)
Indeed it is!
Excel online as datasource is bad... But Local excel to handle some app parameters can be surprisingly efficient AND good in a dev process.
Yeah pulling in Excel locally can be super helpful
PowerApps Import from Excel vs. Excel Online Business
ruclips.net/video/TiFVQ8UrjDM/видео.html
this doesnt seem to be available in the stage gov environment im on. nor can i connect ms access to sharepoint to manipulate data without writing flows
Boo! Sorry Cara.
Any reason you didn't mention Dataverse for Teams as an option?
Because we find it to not be a good data source. It is better than Excel but that is it. Too many "gotchas" in our experience.
@Cows This is a bit surprising and disappointing. I work for a small cash strapped NGO so I was thinking that it would be a good option. Would you recommend Sharepoint over Dataverse for Teams then? These are the two options available to us.
I'd love to flip my excel online timesheets from excel to dataverse but I'd then have to get 60 dataverse licences and probably more for those who want to see the powerbi reports. It's a hard sell
Agree. Maybe in your case you would get better performance using SharePoint instead of Excel. Licensing wouldn't change.
Well now I feel a little silly. I use Excel, but really only because it's just how I learned to create my Power BI reports & eventually Power Apps. Don't really have a data science background. I've heard of SQL & Dataverse, but know nothing about them or how to use them. The main reason I use Excel is that I know it (like probably everyone)...and typically manipulate my data before incorporating it into my master data source file.
Rob if it is working for you that is okay. You will just find it has challenges as you learn to build bigger and badder apps. I would recommend replacing your excel Table with a SharePoint list as your first step. 😎
@@ShanesCows you know...my bad. I had Power BI & Power Apps mixed up in my quick comment of your video. How I actually have it built is my Power App is connected to a SharePoint list...to which I export the completed entries to Excel...which I have connected to my Power BI as it's data source. Sorry for the confusion. But...Garrett & Jeff have been super helpful with my App FYI!
How i can use dataverse in power apps
You might find the series of Canvas Apps vs. Model-driven apps helpful. ruclips.net/video/SKSfgyWP2jc/видео.html Shows you how to build the same Dataverse app with both tools. 😎
Laughed hard on " build app with excel if you are leaving job next week" , Actually did once same !😃
😂 Love it. 😂
Understood, but for a management of 9000 thousand products, could you go to the Dataverse?
My english no good kkkkkkkk.
Yes, that would be no problem for Dataverse.
Do You think the best for storing files and pictures is blob storage when using Azure Sql Server?
Yes, never store files in SQL Server. Never. 😎
Im so desperate to switch over from SPL to Dataverse with my project app but sad to say im just a user level in our organization and our admin does not have any idea aside from creating a 365 profile. Grrrrr , I want freeeee Dataverse ahaha
Don't we all, don't we all. 😁
The problem with SQL server is a limit on the number of connections concurrent
SharePoint for version history
Version history is a great feature for sure!
dataverse is free (not premimuim) in the Teams environment. It is not as fully featured, but very good for free.
Yeah, had a few comments about it. We haven't had good luck with it but I am glad it is working for you. 😎
Shane, i just paused your video and started looking for a bird in my House xD
Little suprise - dataverse is built ontop of sql...
Yup. Dataverse has some serious good tech underneath it.
I love the passion for hatred against excel as a datasource 😃
It is a very strong conviction. 🤣
Meh. SharePoint is the starting point for all projects. It might evolve elsewhere as apps expand, but SharePoint is the best choice for a universal answer. Your answer reeks of Microsoft MVP status.
Sorry you feel that way. I wasn't going for the most common answer, I was going for the best answer.
I would say that I have built more Power Apps than just about anyone on the planet, and I can tell you that Dataverse is the best. 80% of apps are built on SharePoint, it is the most popular, but that doesn't make it the best. I used to openly hate Dataverse, but that ship has sailed. It caught up and passed all of the other ones.
Dataverse still isn't perfect by any means, it has plenty of challenges and room for growth. But if you want the fastest, most secure, most functionality data source Dataverse wins by a mile.
I just finished filtering an excel to be able to convert it to a dataverse datasource. I feel why excel are bad datasources.
Glad you have a plan to make it better. 😎 Think of all of the great stuff you learned along the way.
Can someone comment on exactly why Excel is so bad as a data source? The video and the comments are filled with people bashing it, but I never actually heard why it's so terrible. What is likely going to go wrong, and if the scale is small enough is it really going to matter?
Check out this video QTT - Power Apps Excel is a terrible data source
ruclips.net/video/RtmZM3tm2Zs/видео.html