8 Days of Power Platform — Day 3

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

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?

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)

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

  • 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

  1. Reconciliation of the databases of both applications, and establish
  2. 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

High level view of Common Data Model

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

Under the hood — Building blocks of Common Data Model

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

  • No installation, provision or subscription of services.
  • It does not store or manage data.

What CDM is

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

  • 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.
CDM Landscape in Microsoft

Walk-through: Common Data Service

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

Exploring Power Apps web
  • 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/.
Power Platform Admin Center
  • 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.
Detailed information of the environment

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.

Expanding the Data tab
  • 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

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

Creating a new entity — Ang Bao
  • 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

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

Creating a new custom Option Set

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

One-to-Many relationship (1:M)

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

Create the “One-to-many” relationship
  • 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

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.

Closet geek, on the cloud journey with @Azure. Love Baseball. Git + LinkedIn: jenzushsu

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store