cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Highlighted

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

Highlighted

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

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (8,099)