SQL guys : why don't we use CDS ?

[FredericForest]

Hey folks,

Having been more involved on these forums for the past few weeks, I realized there are quite a few SQL Guys like me around. (I thought I was an oddity)

Until recently, there was a gentle "live and let live" attitude between SQL and CDS. We didn't get much love (see the ideas on triggers or procs) but we were more or less free to do our own things in a nice cost-efficient manner. Now, with the new licensing, the attitude has switched to a much less subtle "convert or die" message... with the expected pushback from the community as a result.

Yet some folks at MS are curious about why we won't use CDS as our go-to data storage. I have my own reasons, which I'll share in the next post, but I'm curious about yours. Are you able to explain why you simply won't switch to CDS overnight? Obviously, I don't believe the "all-in on CDS" strategy is going to change anytime soon. The goal here is to provide insights and understanding to MS as to why we "resist" CDS (a genuine question I've been asked by different MS employees already).

To temper my comments, let me start by saying that I DO believe that CDS has a part to play in the Power Platform. It's a great low-code DBMS sitting somewhere between Sharepoint lists and full Az SQL DB as a backend option, not unlike front-end sophistication can vary between highly managed model-driven screens all the way to PCF components. As far as I can tell, CDS' main value proposition is : 

• No need to learn SQL (not an advantage to us, but will be for some)
• Common model with Dynamics (if your client uses that)
• Native REST API querying, something vanilla Azure SQL DB doesn't offer

@PaulD1 @MrMarshall  @MagnusGöransson @WillPage @OneThing @NEPatton @Rick72 @AngryBatVoice @sscarcella @digiservice @opit @JohnP @inverse70 @wjhepworth 

[Anonymous]

The CDM (Common Data Model) is a configured Microsoft SQL Server database so I am confused by some of the comments. The benefit to CDS(Common Data Service and CDM) over green field Microsoft SQL Server databases or Azure SQL Server DBs really revolves around Role Based Entitlement and Localization features to name two key deliverables. The database model definitely has fits and gaps, but there are many deeper features that deserve respect for the many years that they have withstood the use and abuse by millions of users and users who are currently using the structure.  

In general I think we all need to continue to educate each other so that the best of the best gets absorbed into the platform.

Cheers 

[FredericForest]

Hey @Anonymous ,

Interesting point on how CDM sits on top of SQL Server. I suspected as much, somewhat similarly to how SharePoint sits on top of SQL as well. That being said, if we can't adress the underlying SQL layer, all the pain points discussed above remain intact no matter the underlying technology... 😕

I like (and hoped for) the fact that you discuss the "benefits" side of the equation. I didn't know much about role-based entitlement. I read up a bit on it. In general, I find it similar to SQL roles (with a nicer GUI) but the addition of column-level security and OOB views limiting rows per users are good perks! The localisation is also interesting, although I haven't used it very often in other platform where something similar is available (say, SSAS cubes).

These two points are certainly interesting, and I'm definetly all hears for more great stuff about CDS. But I can't shake the feeling they are mostly "nice-to-have" features on a platform that is missing it's "must-have" (in our context, that is). Having low-code options to develop and deploy a back-end solution makes great sense in some situation (espescially for a developper who doesn't know any other way), but the cost/benefit expressed above still remains overwhelingly negative in most of my uses-cases.

Case in point : I'm currently building a trainning app where users can search for different types of material (videos, webpages, notebooks, etc) which can be organised into "playlists" and where users tag/rate/comment them for others. Here, 98% of the CDM is unrellavant to my problem (and thus is noise to me), indexing, upserting and search procedures are less than trivial (if you want good performance) and I've got no need for complex security beyond what SQL offers or for localisation. So my reflex is to stick to SQL, which I already know well and can pop up quickly...

(ps : now I'm dreading migrating this solution to a SPO-based one since there's no way we'll pay 10$/month/user for it... Aaah, the joys of licensing ! )

[PaulD1]

Thanks @Anonymous 

>>In general I think we all need to continue to educate each other so that the best of the best gets absorbed into the platform.

Very much agree.

As mentioned, I'm a noob on CDS, and interested to hear about benefits / use-cases (if you can point to any good resources comparing CDS to SQL and showing the pros/cons of each that would be interesting to see).

I do have to support @FredericForest's point that even though CDS may sit atop SQL, that isn't any help if we can't use our SQL knowledge and skills to get the most out of the platform (views, indexes, stored procs).

[Anonymous]

@PaulD1  I would start with switching hats and looking at the Enterprise Accounts using Dynamics 365 CE/CRM, because these clients have been maturing with the platform anywhere from 2004 through to new users today. Many projects in Enterprise are xRM or (Any Relationship Management) what has happened is that the power of that platform has now exploded even more outside the standard integrations originally shipped and matured. 

https://dynamics.microsoft.com/en-us/customer-stories/

As an original design and long term SDK offering Dynamics 365 CE was always about open integrations and ships with integrations to Active Directory, Outlook, Excel, Word, OneNote, SharePoint and more. 

It was also built (starting in 2004) to be highly configurable AND Extendable and can now be extended with over 100 different development languages (don't ask me to list them).

So now we shift from the "Dynamics World" into a full platform offering on the same platform. So you take this great "HEART"  from the product and offer it to the world of people who have not put the "Dynamics" hat on. 

I think there is a part of me that considers it similar to the 6th or 7th or 8th ... I lost count some time ago level of a development language world.

What is even more interesting is that given the product is now on AZURE and SaaS, the product teams are now incorporating and adopting tons of AZURE functionality into the platform. So instead of being restricted by Microsoft SQL Server, they can now use Microsoft SQL Server AND Azure Functions (logic apps, search, etc. etc. ) and offer OOB platform that pulls the entire Microsoft Stack together. 

It is important to be a guru on a technology like Microsoft SQL Server, but we all have to understand the choices and architecture of other technologies so that we can recommend or recruit the right experts for the problem of the day. 

Is technology going the way of medicine? Where you have a general practioner and referrals to experts? 

Cheers 

[FredericForest]

---

@Anonymous wrote:

@PaulD1  I would start with switching hats and looking at the Enterprise Accounts using Dynamics 365 CE/CRM, because these clients have been maturing with the platform anywhere from 2004 through to new users today.

---

While its de facto what we HAVE to do, that's a bit sad to me. It more or less comes down to saying "PowerApps will now be positionned mainly as an extension of Dynamics" and therefore drastically limits the outreach of the platform. Dynamics is a great product, but it covers a fraction of the potential market.

[FredericForest]

FYI : I'm currently building a 3-day Power Apps training course which will force me to have long and deep look into CDS (rather than reading docs and checking videos). If you're interested, I'll let you know if my opinion on CDS changes after I've had some real up-close-and-personal time with it.

Cheers !

[PaulD1]

Thanks @FredericForest  - interested to hear your war stories!

I'm in the midst of building a canvas app for mobile on Dynamics / CDS and my opinion has not improved. Main sticking points for me:

* Although data is stored in a relational structure, you cannot do relational queries, for example, I'm working heavily with Appointments which are for Jobs (a job will have one or more appointments), I want to filter on a combination of Appointment and Job fields (appointments of a certain status on a certain day for a particular sort of job) but cannot. I have to ask the Dynamics guys to replicate data from Jobs up to Appointments via work flows as CDS can't do relational/set based querying (e.g. JOINS). This is tedious and duplicates data, bloating (further) the data model and running the risk of data getting 'out of sync'.

Edit: I believe first level joins can be made in some circumstances - in this case, because the link between Job and Appointment is 'polymorphic' (an appointment might be for a job, it might be for something else) then the 'filter by a first level join' functionality does not work... Don't get me started on the use of polymorphic relationships from a data modelling perspective!

* The data model is horrendous. I need to show a balance on screen - there are at least 6 to choose from (I believe these are OOTB), updated by different work flows and not always agreeing with one another. The Accounts entity has two sets of address fields, but then there is an Address entity as well with a key to Accounts in case there are more, so you need to look in multiple places for the same data.

* Refreshing of data is problematic, I believe because you have to rely on workflows to copy data between entities (to make up for the absence of join queries) - so you write data, but there is a delay before the record is available/complete as the workflow may be asynchronous and take a while.

* You can have multiple fields with exactly the same display name in an entity and which field PowerApps gets appears to be random, changing each time I start the App (have to use the internal field name to get around this).

* There are some entities that I just don't seem to be able to write to with a Patch statement but can write to via a Form, so I need to use a hidden form, set the values as needed and then submit.

* If I want a simple thing like a count, I can't do it in a delegable fashion because I can't write my own server side queries - I have to filter to get several hundred records, then count how many I got back.

* Because the built-in entities are so bloated and Explicit Column Selection cannot be relied upon to actually return the fields you need, it is advisable to put a ShowColumns on every data call so you only pull down the 6 or so columns you need rather than 100+ fields of guff (with many of these columns being 'wide' because they link to other entities and all IDs in CDS appear to be GUIDS).

I get that Dynamics has been so horrible to work with for so long, that to Dynamics folks, CDS must seem like paradise. But coming from SQL, CDS feels like the pits of doom.

Just as a follow up to the original trigger for this thread. Since SQL Connectors were reclassed as premium we have had zero new projects green-lit using SQL. We do have some maintenance and enhancement work for existing systems benefiting from the 'grandfather' clause/exemption but all this systems are planned to be replaced before they are hit by the new license model.

[FredericForest]

@PaulD1 ouch...

I'll wait to make my own opinion, but I saw an Ignite 2019 video on how to model on CDS and the guy explained how he put keys everywhere between different tables because a query couldn't a second-level join (you can only join with tables have direct links with your current table). If that's truly the case, it does illustrate quite well why most of use won't move over anytime soon ! 😅

Before starting this little journey on CDS, my current view is something like this :

CDS is a low-code RDBMS somewhat akin to a modern, cloud-based version of MS Access tables. It is well-equiped to serve power-users on self-service projects. As a storage option, it fits in a feature/fonctionality continuum like this : 

Excel tables < SPO Lists < CDS < SQL

If this view is confirmed through tests and usage, I'll do a little decision tree on when to use what between SPO Lists, CDS and SQL, with licensing costs and technical expertise being the 2 main factors.

[NEPatton]

Correct me if I'm wrong, but CDS is still a premium connector, right?  So - there's no difference in the cost of PowerApps when using CDS or SQL?

[PaulD1]

>>Correct me if I'm wrong, but CDS is still a premium connector, right?  So - there's no difference in the cost of PowerApps when using CDS or SQL?

I believe CDS licensing comes with some Dynamics plans, so if you are using Dynamics you likely have CDS.

All my (limited) experience with CDS is in organisations who have Dynamics licenses - I don't think they are paying again for CDS.