Common Data Service Entities Vs SharePoint List

[dave8]

Hello,

As far as I know the key difference between CDS and SP List will be as follows:

1. CDS comes under premium connectors while SP List comes under Standard connector
2. CDS entities can have relationships while SP List can not

Apart from above, is there any further difference through which we can conclude what to use when ? Why should we use CDS over SP List?

Looking forward to hearing from you!

Thanks and Regards,

[timl]

Hi @dave8 

The first point I would make is that with SharePoint, we can create relationships by creating SharePoint lookup columns.

Some of the reasons I can think of as to why we should use CDS over SharePoint include:

• CDS provides far better query delegation support compared to SharePoint - there is less chance of us running into problems that are caused by the '2000 rows limit'.
• CDS provides better support for aggregate calculations. For example, it provides 'rollup columns ', and functions such as Sum, Min, Max are delegable with CDS
• CDS is the native data store for Dynamics 365, we can more easily integrate with this platform if we build apps on the CDS
• We can more granularly control security access with the CDS
• We can build more sophisticated 'business rules' and processes with the CDS
• The CDS is the requisite data source for model driven and portal apps. We can't build these app types on a SharePoint list

[eka24]

Also look at this blog with regards to flow CDS SharePoint

https://www.google.com/amp/s/sharepains.com/2019/01/09/microsoft-flow-powerapps-common-data-services...

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[JohnP]

As someone mentioned SharePoint lists have lookup columns and for all practical purposes it works as a normal database. SharePoint is also "free" and the technical/learning/implementation curve is lower than CDS.

If you plan to use your apps on a larger scale, forget CDS or any other premium connectors unless your company has unlimited money. With SharePoint you can have thousands of users without thinking about cost. My biggest pain with SharePoint is easy, built-in backup/restore functionality, but there are 3rd party tools for that.

[timl]

I agree with @JohnP  that for many people, the cost of licensing is a prohibitive factor. This would start at $10/user/month for a per app plan.

On the topic of backup/restore, another couple of CDS features I can think of are:

• The CDS includes a native backup facility. We can restore backups to a sandbox environment, and we can promote sandbox environments to a production environments. Essentially, the CDS better supports the "dev, test, live" life-cycle of app deployment in large enterprise organisations.
• To move an app between tenants and different organisations, we can package a CDS database and app into a solution. Importing and exporting a single CDS solution is easier than manually recreating a SharePoint list in a new tenant, importing the app, and re-establishing the data connections.

[NewcombR]

CDS is a superior platform for the apps with respect to data security.

Role-based security, Record-based security and Field-Level security.  All of those can be associated with Azure AD security groups, so you only need to define security once and then you can use existing processes for managing AD group membership.  You have powerful Audit options that can be as narrow or wide as you define.

The initial cost up front might be bothersome for some folks.  However, once you have. CDS license the value begins to scale as you add more and more solutions onto the environment.

Some users might only need the $10 per month cost because they are only using 1 or 2 apps; that is the least value and in that scenario it probably would be better to use SPO.

The better value is the user with the $40 per app plan because now the user can use unlimited apps with premium connectors. 

[mogulman]

I am considering using Sharepoint lists.  I currently have a grandfathered Azure SQL DB app.  The way I think about it is do the limitations justify the cost of using a premium connector.  I am in a small organization (<10) but we have many users who barely use the app.  I just can't justify the cost.  If they come out with a usage based model, which they should, I'll relook at a premium connector.  When I started I compared CDS and Azure SQL DB.  For me the hands down winner was Azure SQL.  It has incredible support.  It is supported by SQL Server Management Studio (SSMS).  You can connect to it from any programming language including Excel VBA.  I also really like Azure Functions.  Azure SQL works great with Azure Functions.  If I move to Sharepoint lists I plan to write an Azure function that migrates the data to Azure SQL daily.  For what I need a basic plan is more than enough ($5/month).  I'll write reports and other capabilities based on Azure SQL.  For example I currently have a complex query that creates a JSON file that I FTP to a website.  Portals also interest me.  There is CDS SDK so I believe I can purchase 1 premium license and migrate data daily to CDS and create a portal.  Still not sure I can justify the cost of portals. 

[JohnP]

---

@NewcombR wrote:

Some users might only need the $10 per month cost because they are only using 1 or 2 apps; that is the least value and in that scenario it probably would be better to use SPO.

The better value is the user with the $40 per app plan because now the user can use unlimited apps with premium connectors. 

---

Only $10 per user per month? With 100s or 1000s of potential users that quickly adds up. If you have more than one app you quickly need the $40 license. Ouch! I love how some users here belittle the licensing costs. The minority here works at a Fortune 500 company.

As someone mentioned here there should be a usage based license where you pay for the actual usage/traffic like most of Azure works. Taxing the Azure SQL connector is beyond ridiculous since the customer already pays for the Azure usage.

[WarrenBelz]

Hi @mogulman ,

We run a substantial facility on SharePoint (100 plus lists with up to 10,000 items, Libraries with more than 30,000 files in some, 50 plus apps both field and office and over 100 users).

All has worked well for a couple of years as long as you manage the data structure as a Power Apps backend.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Uttam]

---

@JohnP wrote:

---

@NewcombR wrote:

Some users might only need the $10 per month cost because they are only using 1 or 2 apps; that is the least value and in that scenario it probably would be better to use SPO.

The better value is the user with the $40 per app plan because now the user can use unlimited apps with premium connectors. 

---

Only $10 per user per month? With 100s or 1000s of potential users that quickly adds up. If you have more than one app you quickly need the $40 license. Ouch! I love how some users here belittle the licensing costs. The minority here works at a Fortune 500 company.

As someone mentioned here there should be a usage based license where you pay for the actual usage/traffic like most of Azure works. Taxing the Azure SQL connector is beyond ridiculous since the customer already pays for the Azure usage.

---

My understanding is - with per app plan - you don't need the end users to have the license, the per app license is attached to the environment in which app lives and allows end users to access the app. 

About Power Apps per app plans 

Excerpt from above page:

 Important

Although, Power Apps per app plans appear in the Microsoft 365 admin center, you shouldn't attempt to assign them to users there. Power Apps per app plans must be allocated to an environment (and not to users) by an admin in the Power Platform admin center. After per app plans are allocated to an environment, they are assigned when apps are shared with users in the environment.