Database Decision Tree: Dataverse, Azure SQL, MS Access & SharePoint for Power Apps

Database Decision Tree: Dataverse, Azure SQL, MS Access & SharePoint for Power Apps
Decision TreeDataverseSharePoint
Model-Driven AppsSupports building model driven and canvas appsCanvas apps friendly data sources and not easier to utilize model driven business process flow
DevOpsPower platform supports ADO and GitHub for CI/CD and seamless code management. Power Apps provides a set of Azure DevOps build tasks that allow development teams to manage their Dataverse schema in source control and automate the deployment of apps and DATAVERSE assets to various environments. framework supports ADO but the data sources such as SharePoint list is not effective to handle enterprise grade CI/CD operations
SQL Developer EnabledSQL endpoint for Dataverse is available for SQL developers to use Dataverse entities/tables for development: – this enables developers to write SQL statements against Dataverse (still processed through the WebAPI layer).N/A
Low Code Data Model DevelopmentBuilt in capability to design tables and manage permissions to data via a web-based designer, no need to install/learn SQL Management Studio. Access to pre-built data structures/entities for common tasks. The ability to access data through an Excel add-in provides added flexibility.SharePoint is not an app development framework.
AI Enabled DataWith Azure powered AI Builder and Azure services, enable machine learning and AI to consume your data and understand the meaning and intent (prediction, vision, language). Not needed for DigitTier MVP, but items such as predictions and text detection assumed valuable in future. Out-of-box ML models helps business users to get started faster on their use cases.N/A
AuditingDataverse supports rich and detailed auditing, including which users accessed or changed data, including the previous and new values for all fields. Refer:
SharePoint Online supports auditing of different actions. Refer:
Datatypes supported by Power Apps
Power BI Direct Provides ability to have rich interactive dashboards that aligns with your security Power BI support SharePoint Online as a data source, but it has limits on
Query Supportmodel. No need to move data from SharePoint to SQL Server, as is common with SharePoint based. number items it could retrieve from SharePoint Online Lists.
Throttling Limits1000 API calls per connection not per app.600 API Calls per connection
Relational DatabaseSupports nativelyData relationship within SP isn’t robust within the core architecture.
Data sourcesSupports Blob storage, Cosmos DB, RDBMS, structure/unstructured data basesSharePoint support Azure SQL database / RDBMS
CostList price: File Capacity $2 PUPM besides the entitlementPart of the Office 365 Entitlement
Data Model ExtensionDataverse is built on the extensible Common Data Model (CDM) – an open source and shared data model that provides semantic consistency for data. This is achieved through partnership with SAP & Adobe. Allows makers to develop applications and integrators more quickly to interoperate across multiple systems more easily.N/A

Datastore: Dataverse & Azure SQL & MS Access – Decision Tree

Data Choice – Decision Points

Decision PointsDescription
Is the primary goal to provide a data store that can be accessed through an API?If the goal of the existing Access Database is to store data and make it accessible through an HTTPS API then this is a requirement that will dictate the potential Data Store.

  • YES – This should be chosen where there is an existing / well-defined and explicit need for accessing the data via and API / HTTPS

  • NO –This should be chosen where there is no need for accessing the data via and API / HTTPS or if the need is unclear (i.e., it might be necessary) [DEFAULT]

Is the data mainly tabular or does it include files and images?The type of data stored within the Access Database, specifically whether it is structured tabular data or completely unstructured data such as files or images will dictate the potential Data Store.
• TABULAR – If all the data is structured / tabular or if there are files and images, but they can be removed, then this should be chosen [DEFAULT]
• FILES AND IMAGES – If there are files or images that will need to be migrated and maintained then this should be chosen