PL400 : Design a data model

In the era of digitization, data plays an important role in the market. Social media, e-commerce and many more companies are messing with the data and trying to understand the behavior and psychology of the customer. This means that data is playing an important role in the business world for strategy decisions and forecasting. The accuracy of decisions and predictions depends on the data model which basically determines the behavior and nature of the data. This means that the data model is an important part of every decision-making business application. Let's start with the question, what is a data model?

By the definition of Wikipedia: 

A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities. 

Edgar Codd was created 12 Rules for Database and its relationships in 1980 and was composed of three components: 1) data structures, 2) operations on data structures, and 3) operations and constraints of integrity for structures. Data modeling makes it easy to integrate high-level business processes with data rules, data structures, and technical implementations of your physical data. Data models provide the synergies for how your business operates and how it uses data in a way that everyone can understand. In other words, the success of the application depends on the accuracy of the data modeling. For solutions based on Microsoft Power Platform, the data model is critical to the design of the solution as it will impact app design, especially model-driven apps. Data modeling can be difficult, and having experience with many different projects will enhance your data modeling skills.

 Microsoft Dataverse “Common data service” is a cloud-based storage space that organizations can use to store business application data.

Why use Dataverse?

Standard and custom tables within Dataverse provide a secure and cloud-based storage option for your data. Tables let you create a business-focused definition of your organization's data for use within apps. If you're not sure whether tables are your best option, consider these benefits:

• Easy to manage – Both the metadata and data are stored in the cloud. You don't need to worry about the details of how they're stored.
• Easy to secure – Data is securely stored so that users can see it only if you grant them access. Role-based security allows you to control access to tables for different users within your organization.
• Access your Dynamics 365 Data – Data from your Dynamics 365 applications is also stored within Dataverse, allowing you to quickly build apps that use your Dynamics 365 data and extend your apps with Power Apps.
• Rich metadata – Data types and relationships are used directly within Power Apps.
• Logic and validation – Define calculated columns, business rules, workflows, and business process flows to ensure data quality and drive business processes.
• Productivity tools – Tables are available within the add-ins for Microsoft Excel to increase productivity and ensure data accessibility.

Dataverse empowers organizations to work with any type of data and any type of app, and use the data within it to gain insights and drive business action. As part of Microsoft Power Platform, Dataverse requires no or little code to be written, so it can easily be used by everyone from knowledge workers to professional developers. Knowing that it's built on Azure, organizations choosing Dataverse can be confident that it's globally available, compliant, scalable, and secure.

Dataverse features for Data Model:

Reuse : Leverage the Common Data Model
Extend: Add custom entities, relationships, fields
Summarize: Leverage calculated and rollup fields

Interact: Customize activities for Real-life interaction 

In the Dataverse, Microsoft introduced a new concept of shared Data Model which is known as Common Data Model. it develops modern solutions, application and analytics that share a common understanding of business data.

Common Data Model Provides an easy and customizable business database, which can be used to power business apps.

GitHub carries the CDM repository. https://github.com/Microsoft/CDM. 

Common Data Model has the following features:

• A shared data model allows applications and data integrators to interoperate through a unified definition of data
• A rich metadata system with standard entities, relationships, hierarchies, traits, and more
• Originated from Microsoft Dynamics 365/CRM apps, that are shown as open source, in GitHub with over 260 standard entities
• Multiple systems and platforms currently implement Common Data Model
• Internal and external partners, including Dataverse, Microsoft Power BI dataflows, Microsoft Azure data services, Informatica, and more.

Data modeling on Microsoft Power Platform examines the whole data architecture picture and includes a logical glimpse of data from Dataverse, data lakes, and external sources by using connectors. Data models for data-structures generally fall into two general categories:

Logical data models: 

Logical data models are high-level diagrams that show how data flows through the system.

Physical data models:

Physical data models are lower level than logical data models. They generally include column-level detail and, more precisely, design relationships. The physical data model is created when the high-level logical design is translated to physical entities.

Data Modeling Strategies:

• Start with core tables and relationships
• Over normalization
• Current needs
• Proof of concept

Data Model influencers:

• Security requirements
• User experience
• Data location and retention
• Self-service reporting
• Existing systems
• Localization

We can retrieve and store data three different ways.

• New data
• Read/write from an existing system
• Make a copy of the data.

New data

If your app is creating data that doesn't already exist, such as in situations where the existing business process was done by using paper, we recommend that you store the data in Dataverse.

Read/write from an existing system

Data that reads/writes from an existing system is a type of data where you need to retrieve the latest information from an existing database or system. In these cases, data needs to be requested at the time when you need it.

Make a copy of existing data

In situations where original data should never be modified or overwritten, you can copy the data to another data store such as Dataverse. This approach ensures that the data in the original system won't be changed, yet your app can work with it.

 

Where to store data

Data modeling on Microsoft Power Platform should look at the whole data architecture picture and include a logical look at data from Dataverse, data lakes, and external sources by using connectors.

Dataverse

Dataverse abstracts your apps from how data is stored. All data in Dataverse is accessed through REST APIs. Currently, the tables in Dataverse are relational tables. Dataverse stores its data in a mixture of stores and formats:

• Microsoft Azure SQL Database elastic pools
• Microsoft Azure Cosmos DB
• Microsoft Azure Storage
• Microsoft Azure Data Lake Storage in Common Data Model folders

Existing data

Apps in Power Apps have three ways of using existing data:

Connector - A connector allows the app to connect to various systems and sources, such as SharePoint, SQL Server, or Microsoft 365, and directly retrieve data from them or save data to them.

Dataflows - Dataflows extract, transform, and load data from another system to Dataverse or Azure Data Lake Storage. Unlike a connector, it fetches data in a scheduled batch. Instead of retrieving the data as-is from the data source, you can use Microsoft Power Query Online to manipulate, cleanse, and transform data before you store it to the target storage.

Virtual tables - A virtual table is a custom table in Dataverse that contains data from an external data source. Virtual tables appear in your app to users as regular table rows, but they contain data that is sourced from an external database, such as an Azure SQL database. Virtual tables are no longer read-only, and apps can create and write data to virtual tables.

Choose where to store data

When choosing where to store data for your solution, consider the following recommendations:

Dataverse - For transactional data that your apps will consume and manipulate.

Azure Data Lake - For data from other systems, read-focused, and brought into a Common Data Model structure.

Connectors - For leaving existing data where it is and accessing other services that make their data available.

Thanks to below links:

Common Data Service - Data Modeling 
Microsoft Dataverse documentation
Solution architect series: Model data for Power Platform solutions