8 Days of Power Platform — Day 3

13 min readJan 19, 2020

This is a continuation of a 8-days series of Power Platform. For the rest of the series, please refer to links below:

TL;DR — Day 2 Recap

Day 2 provides an approach when it comes to designing your app using Power Apps. The approach is a data-driven to experience approach — leading to identify the core components of Power Platform. One of the key highlights was the choice on using Common Data Service (CDS).

Before going into CDS walk-through and implementing the discussed data model/entities, please allow me to give an introduction, a deep dive behind CDS and its benefits.

What is Common Data Service?

Based on official documentation, Common Data Service is a cloud based data storage service — similar to a traditional relational database where you can can perform CRUD and data is stored within a set of entities. There is a base set of standard entities which covers a common set of organization scenarios, and give you the ability to create custom entities specific to your organization.

For anyone within the organization that has access to CDS, they can access this data for building applications using Power Apps or populate into rich visualization using Power Query and Power BI.

So, what is the big deal if the end user is comfortable of using a relational database like SQL to handle the data layer for their applications?

Common Data Model Uncovered (CDM)

Common Data Service is built on top of Common Data Model — an open-sourced definition of modular and extensible business entities with semantic metadata that simplify the challenges of application development and data integration.

Microsoft Common Data Model (CDM) Poster

To understand the concept of CDM, I will spend some time to paint you the picture of the environment of a typical enterprise customer.

Data Silos

As more and more customers digitized their businesses, IT still have to manage multiple systems and it’s growing every single day. As a high level overview, an organization can have the following applications — be it on-premises or cloud:

Accounting System
Enterprise Resource Planning System
Customer Relationship Management System
Micro applications and many more…

With that many applications in the environment, each application comes with its own form or shape of a data store/database. There can also be other self-managed “databases” in the form of Microsoft Access, Excel or even CSV file.

Across all these databases, there are commonalities for example — Customer data. All of the above mentioned systems may keep its own form or structure of customer data, resulting in lots of redundancy and overlapping information stored within the organization.

A common scenario I encountered is the challenge of defining the “single source of truth” for customer data between CRM and ERP where both applications have its own form or structure in maintaining the data. Users who have access to both systems can update customer data in either system, resulting in potential multiple record replication and inaccuracy.

Now, I expand this equation to include all applications (including flat files) and it becomes a enterprise nightmare — with data silos all around.

Integration Challenges

So, how do enterprises solve the above issues? Taking the same scenario of a CRM and ERP applications — what would be the possible approach to resolve?

Reconciliation of the databases of both applications, and establish
1 or 2-way integration across both application to achieve a synchronized database to establish data consistency.

Similarly, you expand this equation to all the applications — you have another nightmare to deal with. From the Service-oriented architecture to the modern approach of API-led connectivity, these approaches move into the direction of self-service consumption of reusable assets — allowing users to discover commonality, avoiding replication or re-inventing the wheel to access data. However, it will be a different set of challenges dealing with different API layers (i.e. System, Process, Experience APIs) — design, life-cycle maintenance and control etc.

Shared Data Language

Common Data Model (CDM) is the shared data language used by business and analytical applications to provide semantic consistency and facilitate interoperability. It consists of a set of a standardised, extensible data schemas published by Microsoft and enables consistency of data and its meaning across applications and business processes.

What does that really means to you? Conceptually, it sounds amazing but you would want to know what’s under the hood.

Under the hood

Physically, it comprises of the following building blocks:

Shared Structure— It has metadata files, data files, format and standards.
Shared Concepts or Semantic Types — Representing the data (e.g. Phone number) in a standard way and the meanings of the data.
Shared Definition — A curated set of entities that Microsoft published that represents Microsoft’s perspectives, views and opinion on how entities like customer, account or product will look like. These shared definitions are rooted/integrated in Dynamics 365, Common Data Services and other services from Microsoft.
Custom Definition — Organizations may have a different view on how entities like customer, account or product and allowing users to define entities from scratch
Shared Language — You don’t have to reinvent the wheel, redefining the core concepts whenever you need to use, and Microsoft shipped this as Common Data Model.

What CDM is not

CDM is not a standalone services or server — it is a client library that is integrated into several products and services.
No installation, provision or subscription of services.
It does not store or manage data.

What CDM is

It is all about metadata — period. After understanding the building blocks, you should have a better appreciation of the below definition provided by Microsoft.

It is an open-sourced metadata system that includes standard entities representing commonly used concepts and activities across a variety of business and application domains.

Benefits of using CDM

Unifies data in a well-known schema with semantic consistency — having a “single source of truth” exposing to all applications in the ecosystem without the need to perform directly integration to other applications.
Decoupling of applications and data sources
Enables machine understanding of data semantics — Machine learning algorithms do not have to make any guesses about the data, having an explicit understanding and as the foundation for OOTB AI (i.e. Customer Insights, AI Builder etc.).
Services from Microsoft like Office Graph, Power BI Dataflows, CDS / Dynamics natively speak CDM metadata model — speaking the same language without the need of any heavy lifting when you are moving the data across these services.
Bridging “No code, low code” to “Low to High code”, reducing the barrier for better collaboration across the different groups of users. As shown below, the CDM landscape in Microsoft provides completely OOTB “No code” capabilities built in the product to having the ability to build custom solutions using the same technologies, allowing folks from both ends of the spectrum to collaborate better.

Walk-through: Common Data Service

We have been waiting for this moment — getting our hands-on with the platform.

Below are the tasks for the day

Create a new Common Data Service environment
Explore the platform and standard entity
Create a custom entity
Create and manage a custom field in an entity
Create and manage a custom Option Set
Create and manage the entity relationship

I will use another post to give a breakdown on the prerequisites on the Power Platform licensing.

Create a new Common Data Service environment

I will create a new environment for this application.

Open a browser of your choice and go to Power Apps https://make.powerapps.com/.
Click on “Setting” and the Setting navigation pane opens up on the right.
Under the options, you should see “Admin Center” and click on it. It will navigate to the Power Platform Admin Center (provided you have the right licenses). Alternatively, you can direct go to the Admin Center via this URL https://admin.powerplatform.microsoft.com/.

In the admin center, in the left navigation pane, click on “Environment”.
Under “Environment”, you should be able to see all the existing environment(s) created. Click on “New”.

Creating a new environment with a database

Enter a name, select the production type, select the nearest region to where you are location (“Asia” for myself) and purpose.
Check on the toggle to create a database for this new environment.
Click “Save”.

Adding a new database for the environment

Select the Language and the Currency which will be used for reporting. Click “Save”.
There are additional toggle where you can now also deploy Dynamics 365 Apps (if you have the licenses), Sample data gives you something to experiment with as you learn. You must select No for Enable Dynamics 365 apps for this setting to appear.
You can also restrict the environment by selecting the Security Groups to give the right group of members to the access of this environment. Security Groups can be defined in Microsoft 365 Admin Center.

Once the environment is successfully created, it will appear under Environment. Click on the environment you created and it will display all the information of the environment.

Go back to https://make.powerapps.com and in left navigation pane, expand “Data” and you should be able to see a list of tabs under it. This shows that a database has been successfully created for this environment “jenzus-angbao-env”.

Explore the platform and standard entity

Out-of-the-box, Common Data Service comes with a set of standard entities as shown below. As described above, these entities are based on the shared definitions that Microsoft’s perspectives, views and opinion of the common scenarios within an organization.

To understand the types of entities presented, you can refer to this link.

Standard entities in Common Data Service

Before creating any entity, you can explore the standard entities and the entity reference for a description of the available standard entities. As mentioned, these entities should cover typical scenarios in an organization, you should KISS and use the entities (with minimal modifications if necessary),

Based on our approach, one of the entities which I would need is “Place”. From the list of entities, the closest one which I can map to is “Appointment”.

Appointment entity in Common Data Service

Click on “Appointment” under Entities and more information regarding to the selected entity will be displayed.
Based on the first observation, it has the relevant fields which I need for “Place” — Start and End Time, Location, Subject to describe the visitation.

Click on “Relationship” tab and it shows all the relationships that this entity has with other entities.

Based on this exploration, I will use “Appointment” for the initial identified entity “places”.

Changing the entity from “Place” to “Appointment” from the initial Entity Relationship Diagram

Likewise, I will just use the default “User” entity to represent the user of this application.

Create a custom entity

For the proposed entity “Ang Bao”, I will need to create a custom entity as none of the standard entities meet the requirements.

Click on “New entity” and in the right pane, the “New Entity” pane will open.
Enter the following details as shown above in the “New Entity” pane.
Check the “Enable attachments (including notes and files) first.
You can expand “More settings” if required. In the interests of time, I will just stick with the default steps required.
Click “Create” and it will take you to the new entity “Ang Bao” while provisioning the rest of the components in the background.

Create and manage a custom field in an entity

With new custom entity “Ang Bao”, I will need to create the custom fields as well.

Adding a new field to the entity “Ang Bao”

Click on “+ Add Field” and in the right pane, the “Field Properties” pane will open on the right.
Enter the following details as shown above in the pane.
For the field “Amount”, it will be a data type of “Whole Number”, making it a required field for creation. Take note of the naming convention of custom entity, there is a prefix which has been automatically generated by the system. This prefix is intended to give your objects a unique name if you wish to import them into another environment in the future (which would have a different prefix).
For this entity “Ang Bao”, there can be multiple ways to represent the amount based on the different denomination given in a red packet. I have also catered fields to record the number of different denominations ($2/$5/$10/$50/$100) in a red packet. As such, it will be easier to auto-calculate the amount based on the different number of denominations entered for the entity and also to prevent any inconsistency of data (total amount != sum of all denominations).
Under “Calculated or Rollup”, click “+ Add” and select “Calculation”. The system will have to save the entity first before allowing the user to enter the logic for the calculation.

Click on the field “Amount” and the right pane opens. Click on “Open Calculation” and a pop up window will open.

Adding the logic for the desired action to take place

Based on the assumption that the different denomination fields have been created, I can enter the relevant actions under “Action”. Once completed, click “Save and Close”.

Return back to the main screen and click on “Done” from this pop up. The window will refresh and reflect the latest changes in the entity.

Highlighting the new fields added to the entity before saving

You may go ahead to add other custom fields — for all the new created custom fields, it will be “BOLD” by the system to show the new changes to this entity.
Click “Save Entity” for the changes to take place.

Create and manage a custom Option Set

For Appointment, I need a field named “Category”. From the left navigation pane under “Data”, click on “Option Sets” and it will display all the existing option sets available.

From the OOTB option set list, there is an existing “Category” option set. I will choose to create a new custom option set and named it as “Profile Category”.

Click on “+ New Option Set” and in the right pane, the “New option set” pane will open on the right.
Enter the following details as shown above in the pane.
Click “Save”.

Adding the custom Option Set to the entity “Ang Bao”

Return to the entity “Ang Bao”.
Click on “+ Add Field” and in the right pane, the “Field Properties” pane will open on the right.
Enter the following details as shown above in the pane.
Under “Data Type”, select “Option Set” and a left pane opens with a list of all available option sets in the database.
Select “Profile Category” and click “Done”.

Remember to click “Save Entity” for the changes to take place.

Create and manage the entity relationship

With all the necessary entities created (or reused), I will need to define the entity relationships. For this example, I will define the relationship between the User and Ang Bao entity.

I need to establish the 1:M relationship between User and Ang Bao as shown above.

Locate the “User” entity, click on “Relationship” tab and it shows all the relationships that this entity has with other entities.
Click “+ Add relationship” and it will have a drop-down with the option of the different relationship types. Select “One-to-many” and it will open a pane on the right side of the window.
Enter the following details as shown above. Under “Related (Many)”, select the entity “Ang Bao”.
Expand the “Advanced options” and it will show the Entity Relationship behaviors. Behaviors for related entities is important because it helps ensure data integrity and can automate business processes for your company. You can refer to this link to under the types of behavior.
For the above settings — Referential, Remove Link refers to any related records can be navigated to, and actions taken on one will not affect the other. In short, deleting an user will have no impact on the related ang baos.
Click “Done” and “Save Entity” for the changes to take place.

Conclusion of Day 3

Day 3 focuses on a deep dive into Common Data Service and Common Data Model, giving you a better idea about the idea behind the service — how it is being used and the benefits.

The next step is to start creating an environment, exploring the main features of platform and start creating the entities for the application.

Day 4 will be focusing on Canvas app, how Power Apps can connect with data source, quickly create the necessary views and perform CRUD. I will also be touching on the experience aspect with some simple UI design/flow for a better visualization or walk-through.

Happy #powerhacking away!

The opinions and views expressed here are those of my own and do not necessarily state or reflect those of Microsoft Singapore or Microsoft Corporation.