cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenJanssensPD
Helper III
Helper III

Is CDS to be used with complex datamodels?

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
DavidJennaway
Solution Supplier
Solution Supplier

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 

View solution in original post

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

 

View solution in original post

2 REPLIES 2
DavidJennaway
Solution Supplier
Solution Supplier

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 

View solution in original post

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

 

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,599)