SharePoint List v/s CDS - User/dev experience

[ManishM]

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.

Thanks

ManishM

[mogulman]

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.

[Yobeekster]

I am new to Sharepoint and its capabilities, but have decided to use it as my data source for powerapps after extensive research and testing.
CDS is unfortunately not currently at a stage where it can be used by non-developers, and any real developer has steered me away from that to Azure.

Since I am not a developer, I have steered myself away from Azure. I did a thorough attempt with Azure, but realized over time that you need to be an educated developer to use it as well as maintain it.

I am a big believer in Excel and that's where I started with Powerapps as a data source, with moderate but encouraging results.

I decided to move to Sharepoint because the data can be looked upon by everyone without opening a spreadsheet.

My philosophy is now to use Sharepoint as a data source, and use Powerapps as the control interface for pushing and pulling data.

I do have high hope's for CDS, but if the goal for CDS is to be used by non-developers, it has a long way to go. I dont see the point to it at all anymore, sorry to say.
Part of the issue with CDS is that if there is an issue like not being able to delete a relationship because there are dependencies, good luck finding those dependencies, even through solution explorer, it doesn't always work.
That is just one small issue!

In short CDS is currently for developers, but most developers will steer you away from it if you want to hire them, so again, what is the point to it?

Use the data source that you are comfortable with when things go wrong, not the one where how easy it is to put together when things go right.

[Anonymous]

This is a solid response, and is in line with what I am seeing. Another example is this: CDS automates many -to-many relationships, but does so with a hidden table, thus eliminating the possibility of including other applicable fields in the table other than IDs from each member of the relationship.

[mogulman]

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'.

[WarrenBelz]

All,

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.

[mogulman]

@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. 

[WarrenBelz]

Thanks @mogulman ,

I actually suggested this (it was SQL) in a post yesterday to a user with cost restraints.

[Anonymous]

@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.

[Yobeekster]

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.