Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Answered

Is CDS to be used with complex datamodels?

(0) ShareShare
ReportReport
Posted on by 593

Dear,

 

I wonder if the CDS (in combination with PowerApps) can be used to accommodate a complex datamodel (as a replacement for let's say SQL server)?
For example a (relational) datamodel with 100+ tables, where some tables can have more than 100+ columns...

 

Or is it better to stick with a relational database and connect to that from within PowerApps instead of trying to create a lot of custom entities in CDS?

And what about non-relational (nosql, document, xml) datamodels?

 

Thanks,

Koen

  • Verified answer
    EricRegnier Profile Picture
    EricRegnier 8,714 on at
    Re: Is CDS to be used with complex datamodels?

    Hi @KoenJanssensPD,

    Agree with @DavidJennaway and to supplement on, CDS can support complex models, for instance, I’ve implemented a few of cases with over 400 entities/tables and over 1B records in total. There are some points to consider when working with those volumes and complexity like what David mentioned and also security contraints and performance considerations particularly around activities and activity pointers. Also when designing CDS data model there’s more to consider as opposed to SQL, most importantly UX, ensure the entities/data make sense to users and can easily navigate within the apps if you’re using model-driven apps.

     

    Querying is not as obviously as flexible as SQL Server but CDS does offer rich APIs (rest and soap), SDK and also now in preview use SQL to query data: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query


    hope this helps...

     

  • Verified answer
    David Jennaway Profile Picture
    David Jennaway 716 on at
    Re: Is CDS to be used with complex datamodels?

    CDS can work well with fairly complex relational data models, though it's not as flexible as SQL Server. The areas where you're most likely to see limitations compared to SQL Server are:

    • CDS has a query language (FetchXML) which is OK for simple queries, but not as powerful as the SQL query language. As a general rule of thumb, if you have SQL queries that need more than one SELECT statement (e.g. Unions, Sub-queries, CTE), you can't do this in FetchXML
    • When accessing attributes from related entities within the UI and workflows, you typically can only access attributes through one level of relationships. This means you may end up with denormalised design, or duplicating data across entities to make it accessible

    You can store file content in CDS, but have no tools to help process the content. There is also nothing to help with storing XML 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard

Featured topics