cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FredericForest
Kudo Kingpin
Kudo Kingpin

SQL guys : why don't we use CDS ?

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 

21 REPLIES 21
Anonymous
Not applicable

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 

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
Community Champion
Community Champion

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
Not applicable

@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 


@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
Kudo Kingpin
Kudo Kingpin

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 !

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.

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

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
Community Champion
Community Champion

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

Helpful resources

Announcements

Celebrating a New Season of Super Users with Charles Lamanna, CVP Microsoft Business Applications

February 8 was the kickoff to the 2024 Season One Super User program for Power Platform Communities, and we are thrilled to welcome back so many returning Super Users--as well as so many brand new Super Users who started their journey last fall. Our Community Super Users are the true heroes, answering questions, providing solutions, filtering spam, and so much more. The impact they make on the Communities each day is significant, and we wanted to do something special to welcome them at our first kickoff meeting of the year.   Charles Lamanna, Microsoft CVP of Business Applications, has stressed frequently how valuable our Community is to the growth and potential of Power Platform, and we are honored to share this message from him to our 2024 Season One Super Users--as well as anyone who might be interested in joining this elite group of Community members.     If you want to know more about Super Users, check out these posts for more information today:    Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

February 2024 User Group Update: Welcoming New Groups and Highlighting Upcoming Events

It's a new month and a brand-new year, which means another opportunity to celebrate our amazing User Groups!Each month, we highlight the new User Groups that have joined the community. It's been a busy season for new groups, because we are thrilled to welcome 15 New User Groups! Take a look at the list below, shared by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you!   We love our User Groups and the difference they make in the lives of our Community! Thank you to all the new User Groups, new User Group leaders--we look forward to hearing about your successes and the impact you will leave!   In addition to our monthly New User Group spotlight, it's a great time to share some of the latest events happening in our User Group community! Take a look at the list below to find one that fits your schedule and need! There's a great combination of in-person and virtual events to choose from. It's a great time of year to connect and engage with User Groups both locally and online. Please Welcome Our NEW User Groups   Power Platform: Heathcare Power Platform User Group Power Platform Connect Hub Power Platform Usergroup Denmark Mexico Norte- Power Platform User Group Pune Power User Group Sudbury Power Platform User GroupMicrosoft User Group GhanaMPPBLR - Microsoft Power Platform Bengaluru User Group Power Apps:   Myrtle Beach Power Platform User GroupAnanseTechWB PowerApps Copilot Studio: Pathfinders Power Platform Community Dynamics365: Cairo, Egypt MSD 365 Business Central/NAV/F&O User GruopMS Dynamics 365 Business Central LatamCincinnati OH D365 F&O User Group February User Group Events February 2024 Cleveland Power Platform User GroupPortallunsj - Februar 2024Indiana D365/AX February User Group MeetingQ1 2024 KC Power Platform and Dynamics 365 CRM Users Group 

Super Users 2024 Season One is Here!

   We are excited to announce the first season of our 2024 Super Users is here! Our kickoff to the new year welcomes many returning Super Users and several new faces, and it's always exciting to see the impact these incredible individuals will have on the Community in 2024! We are so grateful for the daily difference they make in the Community already and know they will keep staying engaged and excited for all that will happen this year.   How to Spot a Super User in the Community:Have you ever written a post or asked for help in the Community and had it answered by a user with the Super User icon next to their name? It means you have found the actual, real-life superheroes of the Power Platform Community! Super Users are our heroes because of the way they consistently make a difference in the Community. Our amazing Super Users help keep the Community a safe place by flagging spam and letting the Community Managers know about issues. They also make the Community a great place to find answers, because they are often the first to offer solutions and get clarity on questions. Finally, Super Users share valuable insights on ways to keep the Community growing, engaging, and looking ahead!We are honored to reveal the new badges for this season of Super Users! Congratulations to all the new and returning Super Users!     To better answer the question "What is a Super User?" please check out this article: Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

January 2024 Community Newsletter

Welcome to our January Newsletter, where we highlight the latest news, product releases, upcoming events, and the amazing work of our outstanding Community members. If you're new to the Community, please make sure to follow the latest News & Announcements in each Community and check out the Community on LinkedIn as well! It's the best way to stay up-to-date in 2024 with all the news from across Microsoft Power Platform and beyond.      COMMUNITY HIGHLIGHTS Check out the most active community members of the last month! These hardworking members are posting regularly, answering questions, giving (and receiving!) kudos, and consistently providing top solutions in their communities. We are so thankful for each of you--keep up the great work! If you hope to see your name here next month, make it your New Year's Resolution to be more active in the community in 2024.   Power AppsPower AutomateCopilot StudioPower PagesWarrenBelzWarrenBelzPstork1saudali_25LaurensMPstork1stephenrobertLucas001AARON_ClbendincpaytonSurendran_RANBNived_NambiarMariamPaulachanNikhil2JmanriqueriosANBJupyter123rodger-stmmbr1606Agniusstevesmith27mandelaPhineastrice602AnnaMoyalanOOlashynBCLS776grantjenkinsExpiscornovusJcookSpongYeAARON_CManishSolankiapangelesPstork1ManishSolankiSanju1Fubar   LATEST NEWS Power Platform 2024 Release Wave Highlights This month saw the 2024 Release Wave 1 plans for Microsoft Power Platform and Microsoft Dynamics 365- a compilation of new capabilities planned for release between April and September 2024. Click here to read Corporate Vice President Maureen (Mo) Osborne's detailed breakdown of the upcoming capabilities, and click the image below to check out some of the Power Platform 2024 Release Wave 1 highlights.     "What's New" Power Platform Shorts Series This month we also launched our brand-new 'Power Shorts' series on YouTube - a selection of super sweet snapshots to keep you in the loop with all the latest trends from across the Power Platform and beyond. Click the image below to check out the entire playlist so far, and don't forget to subscribe to our YouTube channel for all the latest updates.   Super User In Training (S.U.I.T) It was great to see the Power Platform Community officially kick off Season One of their Super User in Training (SUIT) program this month! Their first meeting saw an amazing turnout of over 300 enthusiastic participants who started their dynamic journey toward becoming Super Users. Huge thanks to Manas Maheshwari, Eric Archer, Heather Hernandez, and Duane Montague for a fantastic kick-off. The first meeting also saw seasoned Super User, Drew Poggemann, share invaluable insights on navigating the #MicrosoftCommunity with finesse. Many thanks to Drew for setting the stage and emphasizing the importance of active engagement and the art of providing thoughtful community solutions. If you want to learn more about the features and benefits of gaining Super User status, click the image below to find out more, and watch this space for more info about Season Two and how you can SUIT UP in the community!     UPCOMING EVENTS Microsoft 365 Community Day - Miami - February 1-2, 2024 It's not long now until the Microsoft 365 Community Day Miami, which will be taking place at the Wolfson Campus at Miami Dade College on 1-2 Feb. 2024. This free event is all about unlocking the full potential of Power Platform, Microsoft 365, and AI, so whether you’re a tech enthusiast, a business owner, or just curious about the latest Microsoft advancements, #M365Miami is for you.   The event is completely free and there will sessions in both English and Spanish to celebrate the vibrant and diverse make-up of our amazing community. Click the image below to join this amazing Community Day in Miami and become a part of our incredible network of learners and innovators!     Microsoft Fabric - Las Vegas - March 26-28, 2024 Exciting times ahead for the inaugural #MicrosoftFabric Community Conference on March 26-28 at the MGM Grand in Las Vegas. And if you book now, you can save $100 off registration! The Microsoft Fabric Conference will cover all the latest in analytics, AI, databases, and governance across 150+ sessions.   There will be a special Community Lounge onsite, interactive learning labs, plus you'll be able to 'Ask the Experts' all your questions to get help from data, analytics, and AI specialists, including community members and the Fabric Customer Advisory Team. Just add the code MSCUST when registering for a $100 discount today. Click the image below to find out more about the ultimate learning event for Microsoft Fabric!     Microsoft 365 Conference - Orlando - April 30 - May 2, 2024 Have you added The Microsoft 365 Conference to your community calendar yet? It happens this April 30th - May 2nd in Orlando, Florida. The 2024 Microsoft 365 Conference is one of the world’s largest gatherings of Microsoft engineers and community, with a strong focus on Power Platform, SharePoint, Azure, and the transition to an AI-powered modern workplace.   Click the image link below to find out more and be prepared to be enlightened, educated, and inspired at #M365Conf24!   LATEST COMMUNITY BLOG ARTICLES Power Apps Community Blog Power Automate Community Blog Copilot Studio Community Blog Power Pages Community Blog Check out 'Using the Community' for more helpful tips and information: Power Apps, Power Automate, Copilot Studio, Power Pages  

Did You Attend the Microsoft Power Platform Conference in 2022 or 2023? Claim Your Badge Today!

If you were one of the thousands of people who joined us at the first #MPPC Microsoft Power Platform Conference in 2022 in Orlando--or attended the second-annual conference in Las Vegas in 2023--we are excited to honor you with a special community badge! Show your support for #MPPC Microsoft Power Platform Conference this year by claiming your badge!         Just follow this link to claim your badge for attending #MPPC in 2022 and/or 2023: MPPCBadgeRequest    Want to earn your badge for 2024? Just keep watching our News & Announcements for the latest updates on #MPPC24.

Microsoft Power Platform | 2024 Release Wave 1 Plan

Check out the latest Microsoft Power Platform release plans for 2024!   We have a whole host of exciting new features to help you be more productive, enhance delegation, run automated testing, build responsive pages, and so much more.    Click the links below to see not only our forthcoming releases, but to also try out some of the new features that have recently been released to market across:     Power Apps  Power Automate  Copilot Studio   We can’t wait to share with you all the upcoming releases that will help take your Power Platform experience to the next level!    Check out the entire Release Wave: Power Platform Complete Release Planner 

Top Kudoed Authors
Users online (4,145)