Managing data

Understand how to store and model data in the Common Data Service. Define and use entities that map to your business data and processes, and extend the common data model with custom entities. Use role-based security to control access to entities. Work with data directly in the service, and in Excel.

You will learn how to...

The Common Data Service

Data is at the heart of business applications and processes - data from Excel, from on-premises sources like SQL Server, and cloud sources like Salesforce and SharePoint Online. Data can be related to customers, sales, employees, and many other things, but the common theme is that data is crucial to your business, and it plays a key role in the apps you build in PowerApps. You have seen and worked with different types of data sources so far in the course, and we introduced the Microsoft Common Data Service earlier. In this section, we'll spend some time getting into the details, explaining the benefits, and showing you how to use the service.

Understanding the service

Let's get oriented with a couple of diagrams. You might have seen the first diagram before - it shows the components of the Microsoft business application platform. You're obviously acquainted with PowerApps by this point, but you might have also used Microsoft Flow, Power BI, or other components. What you see is that the Common Data Service and connectors and gateways are relevant for all of these components. Right now, the Common Data Service is used primarily with PowerApps and Microsoft Flow, but it will be available for other components in time.

Business platform diagram

Now that you understand where the Common Data Service fits in, let's look at its parts. Think of the Common Data Service as a hierarchy. At the bottom level, the service stores data in a scalable and reliable way, and makes the data available so that multiple applications can use it. The next level is the common data model that includes many entities used in applications and business processes: entities like Account, Contact, Product, and Sales Order. You can extend the standard entities and create custom ones to meet your business needs.

Common Data Service architecture diagram

An entity is just a combination of the metadata that describes it (field names, data types, and so on) and the data that you store in it. If you know Access or another database, an entity is very much like a table. We will get into entities more in the next topic, but for now consider the benefits of working with entity data in the Common Data Service:

  • 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 share: You can easily share data with your colleagues because PowerApps manages the permissions.
  • 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 entities for different users within your organization.
  • Rich metadata: Data types and relationships are leveraged directly within PowerApps. For example, defining a field type URL will present your data as a hyperlink within your app.
  • Productivity tools: Entities are available within the add-ins for Microsoft Excel and Outlook to increase productivity, and ensure your data is accessible.
  • Picklists: Include picklists from a rich set of standard picklists to provide quick drop downs within your entities and apps.

Create a Common Data Service database

You create a Common Data Service database in an environment. You learned about environments earlier in the course, so just a quick recap: an environment is a container for apps and other resources, like the Common Data Service. Each environment can have one instance of the service associated with it. If you are an Environment Admin, and you want to add the service to an environment, follow these steps.

From the Home tab, click Create Database.

Common Data Service create database

Specify whether you want to restrict access to the database, then click Create my database.

Common Data Service specify access

When the process is complete, you see all the standard entities that are included in the common data model. Some of them are shown below.

Common Data Service standard entities

Some of this topic might have been unfamiliar territory if you haven't worked with databases before. But the general concept is pretty straightforward: the Common Data Service provides a secure and reliable way to store data and to treat that data in terms of common entities like Account, Contact, Product, and Sales Order. In the next topic, we'll dive a deeper into entities.

Understand Common Data Service entities

In the first topic in this section, we introduced you to the Common Data Service, which includes a common data model. The model in turn contains entities. Entities are chunks of shared data that can be modified, stored, retrieved, and interacted with. In this topic, you'll learn more about entities, fields, and data types.

Standard entities

The common data model comes with a set of standard entities that cover a range of common business needs. Some of the standard entities are shown below.

Common Data Service standard entities

The entities are grouped into categories so it's easy to see which ones typically work together in a solution.

Functional group Description
Customer Service The Customer Service entities manage issues from your customers, including tracking, escalation, and documentation.
Foundation The Foundation entities contain information that is relevant to nearly every other entity group. This group contains entities such as Address and Currency.
People, Organizations, and Groups These entities encompass a rich set of people and organizations that you might interact with, including employees, contractors, donors, volunteers, fans, alumni, and families.
Purchasing The Purchasing entities let you create purchasing solutions.
Sales The Sales entities let you create end-to-end sales solutions, from tracking leads and opportunities, to following through with contacts, accepting and delivering orders, and sending invoices.

Fields and data types

Each entity contains a set of default fields that you can't change or delete. Some of those fields, like Contact ID, are specific to an entity. Others, like Created on date time, are common to all entities. You can extend standard entities by adding fields. Just click or tap Add field and specify the new field's properties.

Contact entity fields and data types

If you need an entity that is completely different (i.e. extending a standard entity isn't enough), you create a custom entity. We will cover that in the next topic.

The fields in an entity each have a data type, like Number. Having different data types, rather than a single generic data type is helpful because it lets your apps to do all sorts of cool things. For example, when you have a field of type Number, your apps can use a slider control when a user edits that field. You can choose from more than a dozen data types - the following list shows some representative types:

  • Basic types, like Text and Number
  • More complex types, like Email and Phone
  • Special types, like Lookup (for creating relationships) and Picklist (to hold a fixed set of values for a field)

Working with entities

When you open an entity, you see a lot of information and several actions that you can take. We'll briefly look at the tabs that are available, and the actions you can take to manage entity data.

Entity tabs

  • Fields: see fields and data types, and add fields, all of which we discussed above.
  • Key: the field that identifies each row in an entity, like Contact ID for the Contact entity.
  • Relationships: connections between related entities like Product and Product category. We'll see an example in the next topic.
  • Field groups: used to control various behaviors, like which fields to automatically show when you create an app screen in PowerApps.
  • Data: browse sample data and your own data after it's imported.

Entity actions

  • Open in Excel: if you have the PowerApps add-in installed, use this option to explore and edit your data in Excel.
  • Import data: bring in data from Excel and CSV files.
  • Export data: export data to an Excel file.
  • Export template: export the structure of an entity to an Excel file so you can populate the file and import it back into the entity.
  • Settings and Delete: not available for standard entities.

Connecting to a standard entity in PowerApps Studio

Now that you understand what entities are, we'll look at how to connect to the Contact entity in PowerApps Studio. Click New, then under Common Data Service, click Phone layout. You see available data connections on the left and the list of entities on the right. Try connecting on your own, and generate an app from the entity.

Connect to entity in PowerApps Studio

In the next topic, we'll show you how to create custom entities, as well as relationships between entities.

Create custom entities

The Common Data Service is designed for all our business customers, from the smallest shops to the largest enterprises. The common data model includes a set of standard entities that address many common business scenarios, and you saw in the previous topic that you can extend those standard entities if necessary. But sometimes you need something completely different to solve problems specific to your business. In that case you need a custom entity, and we'll show you how to build one in this topic.

There are two ways to create an entity:

  • Create the entity from scratch. This is what we'll do in this topic.
  • Create an entity that is based on another entity, by copying the fields and settings of that entity, but not its data.

Creating an entity from scratch

For this example, we'll create a custom entity called Product review, from scratch. To start, on the Entities tab click New Entity. Enter an Entity name (no spaces or special characters), a friendly Display name, and a meaningful Description. Then click Next.

New entity

On the next screen, you see the five default fields that all standard and custom entities contain. Click Add field to start adding your own.

Default entity fields

For this example, let's add four fields:

  • Review Date, which is a date field, and is required.
  • Product Rating, which is an integer field, and is required. We could use a picklist here that allows you to specify only certain values (like 1-5), but we'll keep it simple right now.
  • Reviewer Name, which is a text field, and isn't required
  • Reviewer Comment, which is a text field, and also isn't required.

When you're happy with the entity, click Create. When the entity is created, it doesn't have any data in it. We'll show how to import data in the next topic.

Custom entity fields

Creating a relationship between two entities

Because we want to associate each review with a particular product, we need to create a relationship between the Product review entity and the Product entity. In the Product review entity, on the Relationships tab, click New relationship. Then select a Related entity, and enter a Name, a Display name, and a Description. Click Save to create the relationship.

Create relationship between entities

Connecting to a custom entity in PowerApps Studio

Connecting to a custom entity in PowerApps Studio is just like connecting to a standard entity. Click New, then under Common Data Service, click Phone layout. You see available data connections on the left and the list of entities on the right.

Connect to entity in PowerApps Studio

In the next topic, we'll show you how to manage data in the Common Data Service.

Manage entity data

In this topic, we'll cover data management in the Common Data Service. We have touched on importing and exporting data in other topics, but we'll spend more time now on working with data in Excel.

Import data from Excel or CSV

In this example, we'll import data from Excel into the Product review entity that we created in the last topic. You can also import data from CSV files, which is a common format to move data around. Here's a reminder of what the entity looks like; the highlighted area is what we'll focus on in this topic.

Product review entity

In an entity, click Import data, then navigate to the file you want to import from. Click Show mapping and make sure the columns in the Excel file are associated with the right fields in the entity. When you're happy with the mappings, click Save changes. Back on the main import screen, click Import.

Import data from Excel

Export data to Excel

Export data if you need access to it outside the Common Data Service. In an entity, click Export Data, and then wait for the zip file to be generated. Open the zip file and you see the exported data. Export data to Excel

Export a template to Excel

In addition to downloading data, you can download a template. A template is an Excel file with a structure that matches the fields of an entity, but without the data. After you download the template, you populate it manually or programmatically, and import it back into the service. In an entity, click Export Template, then specify the fields you want (in this case I selected a single field). Click Export to Excel, and then wait for the Excel file to be generated. Open the Excel file and you see the exported template with the fields you selected.

Export a template to Excel

Open and work with data in Excel

The last thing we'll look at is the Open in Excel option. If you have the PowerApps add-in installed, you can use this option to explore and edit your data in Excel. In an entity, click Open in Excel, then open the file. Enable editing, then the add-in establishes a live connection to the entity in the service and populates the workbook. You edit directly in the workbook, and can add and delete rows. Click Publish to save changes. You can also refresh data to make sure you have an up-to-date copy; and filter data, which is especially handy if an entity has a lot of data in it.

Open in Excel

That wraps up the topic on managing data in the Common Data Service—importing, exporting, and working with data in Excel. In the next topic, we'll talk about managing data security.

Common Data Service security

In this topic, we'll cover security in the Common Data Service. The service uses a role-based system to grant users permissions to data. The security model is a hierarchy, with each level representing a different level of access. At the lowest level are individual create, read, update, and delete permissions on a single entity. A collection of these entity-level permissions forms a permission set. One or more permission sets can then be used by a role. A role is at the top level - encompassing all the permissions needed by a user or a group of users.

Understanding roles and permission sets

In most of this course, we've focused on powerapps.com and the PowerApps Studio. In this topic, we will be in the PowerApps admin center. If you click on an environment in the admin center, under Security you see tabs for Environment roles (whch we looked at in a previous topic), User roles and Permission sets. By default, there are two user roles:

  • Database Owner is an administrative role that gives full access to all entities.
  • Organization user is the default role assigned to all users. This role provides all users access to entities that contain public data.

Admin center user roles

By default there are two permission sets for each entity

  • Maintain gives full control: create, read, update, and delete permissions.
  • View gives read-only access.

The following image shows the default permission sets for the Account entity.

Admin center permission sets

In the video, we show you how to create additional roles and permission sets so you can enable fine-grained access for your applications. We create a Maintain product review permission set that gives full access to the custom entity we created in an earlier topic and a ReviewApp Owner role that we assign the permission set to.

Restrict access to a database

When we created a database in an earlier topic, we stayed with the default of open access to the database. To change the access, on the Database tab click Restrict access, and then confirm that you want to make the change.

Restrict database access

In restricted mode, every user must have one or more role assigned to them. A role can be set up for a given position within your company, and assigned to any person who is in that position. Users can also be automatically added to a role based on the Azure Active Directory groups they belong to.

Wrapping it up

Security can be a complex topic, but just remember the hierarchy of permissions. It starts with create, read, update, and delete permissions on an entity, which can form permission sets, which are then assigned to roles. It's a flexible system that enables you to control data access in a fairly granular way.

Well, this brings us to the end of our section on the Common Data Service and also the end of this Guided Learning course. We hope you've enjoyed it and learned a lot - let us know if you have any feedback, and check back because we plan to add content over time. For more in-depth content right now, check out the PowerApps documentation.

Congratulations!

You've completed the Managing data section of Microsoft PowerApps Guided Learning.

You learned how to...

Contributors

  • Michael Blythe