PowerApps Dev team and experts
Would you say it is a better dev/user experience so move data from SharePoint to CDS when possible?
My question stems from my current multiple experiences of how erratic the data access is when reading from a SharePoint. Just until last week, my PM system which is developed in PowerApps with SP, stopped showing basic results in a gallery but is storing any new data to SharePoint list.
Such uncertainty does beg a question on the reliability of the existing code set version and approach.
Any insight will help me decide whether to move all my data to CDS for expected reliability.
Solved! Go to Solution.
When I posted this Azure SQL DB was a standard connector. Since Oct 2019 Microsoft made the Azure SQL DB a premium PowerApps connector so if you have 1 app it is $10/month/user if you have 5 or more apps it is $40/month/app. You also need pay to use Azure SQL DB. The cost depends on the bandwidth you need. I have one app with 5 users and the basic plan has enough bandwidth and works great. The standard plan is $15/month. You can pay a lot more if you need a lot of bandwidth.
Don't sell yourself short on Azure SQL DB. Although SQL Azure SQL DB is very powerful and can be intimidating the basics of what you need for a PowerApp is somewhat simple. You said you know Excel. With Excel VBA you can easily connect to Azure SQL and create very powerful reports. I suggest you be become familiar it even if you use CDS or Sharepoint. You can setup a basic database for $5/month or you can install SQL Server Express (free) on a local machine. You can connect a PowerApp using the data gateway. I developed my app this way and eventually moved it to Azure SQL DB. What was cool about this approach you can use SQL Profiler to see exactly how PowerApps talks to the database. This is very helpful for optimizing performance and debugging problems. There are numerous online courses you can take. There are 100's of books. I have several including 'Azure SQL Database Administration'.
I will throw in my humble opinion here if it assists. Firstly the issue of cost is varying in relevance from insignificant to overwhelming, depending on the depth of the organisation's pockets and the number of users that will access the app. My experience and summary below is based on the premise that it is a very important factor.
We have a Power Apps installation with a complete SharePoint backend (and a couple of older Excel/OneDrive reference lists). It has about 50 active apps using SharePoint lists which also use the Power Apps integrated forms (which in the main are simply copied from the standalone app).
We have in excess of 100 lists and Libraries, some with over 10,000 items and documents in the tens of thousands, including over 30,000 photos in one. Users are in excess of 100 in the field as well as numerous office staff.
The one thing that needs to be managed is the data structure. Anything that is likely to be filtered is plain text or numeric, with all lookup/choice work done in Power Apps, mainly in collections.
Pretty much all things we need this setup has been able to achieve easily.
@WarrenBelzI agree on cost issue. If you look at a medium-large organization PowerApps can be cost prohibitive. Especially if you have numerous apps requiring the $40/month plan. Some users will only have occasional use for some apps. They really need a usage based model like Azure Functions. SQL Server is a very powerful tool for reports, getting information and transforming it. If I did a PowerApp using Sharepoint Lists I would probably create an Azure Function to replicate data in Azure SQL DB on a nightly basis. This way I would have the cost benefit of Sharepoint Lists and reporting benefit of SQL Server.
@Yobeekster Have to disagree that CDS is for 'developers'. You can create a complete application with PowerApps, CDS, and if needed Power Automate without ever writing a single line of code.
Hi @Anonymous ,
Yes, I agree that things can be developed without code (or very little) with CDS, but I should clarify, that what I am speaking of has to do with architecture development and troubleshooting. I am also leaning on what developers with CDS experience have said. It is difficult to invest time in CDS when developers don't want to use it OR that they suggest not using it in place of Azure. As time goes on, this will change, I hope.
It is my opinion, that at the time of this writing, CDS be left for developers, and it is something that I steer other non-developers who are contemplating a data source to consider. On the surface, CDS is great, but once you need to peel back a layer underneath, it can get convoluted (like any program). But if its Azure, then there are alot more resources available to help (people and books), as was mentioned by other users.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!