MB 600: Microsoft Dynamics 365 + Power Platform Solution Architect – Design data and security model (Part Two)

I am currently preparing to take the Microsoft Dynamics 365 + Power Platform Solution Architect certification. Or “MB 600” for short! As I prepare I plan to write revision notes in this post I will cover topics around designing the data and security models.

Data is a big topic! I therefore plan to split this information into two post. In this second post I will dive deeper into complex scenarios and discuss the more technical aspects of Dataverse (CDS) security.

Note: Microsoft have recently updated some of their terminology. Therefore we should consider the terms like CDS and Dataverse as interchangeable. In terms of the exam I would expect them to begin using the term DataVerse at some point but that change is unlikely to be immediate. Meaning in the short term either term could be used. I created this blog post before the revised terms were announced. I have tried to apply updates but a few “outdated” references may still exist!

Design complex scenarios

Often a Dynamics 365 application will simply access data that resides in the Dataverse. However Power Automate, Power BI and Power Apps (Canvas Apps) can leverage data connectors to access data from many hundreds of sources. Additionally custom connectors can be created as required. These connectors allow us to leverage existing data source and services.

When an “off the shelf” connector does not exist a custom connector can be created. This might work by making use of an existing API. Or you may need to create a custom API and define your own actions. The connectors can be make use of OAuth (including Azure AD), API key and basic auth. Connectors can be packaged and deployed via solutions. Creating as connector that is then available by users for re-use is a great way to empower then to extend the solution using the Power Platform.

Data modelling on the Power Platform should therefore look at the whole data architecture picture and include a logical look at data from the Dataverse, Data Lakes and external sources using connectors.

Azure Data Lake is a hyper-scale repository for big data analytics. Azure Data Lake can store data from all disparate sources, including cloud and on-premise sources of any size and structure. The Data Lake can include CDM and can be integrated with Dataverse. The huge scale of the Data Lake may help support complex scenarios like machine learning, AI and complex reporting when extremely large data volumes maybe required.

  • Dataverse – used for transaction data that apps will consume and maintain. Structure typically based on the common data model.
  • Azure Data Lake – ideal for data from other / multiple systems, read focused and can leverage the common data model.
  • Connectors – great for leaving the data where it is. Supports accessing external sources to make their data available in apps.

There are many drivers which may influence data model decisions, including;

  • Security requirements
  • User experience – it’s easy to forget that as we add normalization and relationships we create new constructs users need to navigate to be successful
  • Data location – some data must be stored in a given GEO
  • Retention policies – not all data can be stored indefinitely!
  • Self-service reporting – if the data model becomes complex can a “regular” user still extract the data for their reporting purposes? Or will all reports end up needing a data architect to design them??
  • Roadmap – what are the plans for the future?
  • Existing systems – are we going to connect with any existing systems or does any existing data need to be “squeezed” into the new data model.
  • Localization – Multi-region, multi-lingual, multi-currency requirements

Custom Activities

Out of the box we have activities like phone call, email and task. But custom activities can be created as required. The advantage of creating a custom activity is that they show in the timeline lists with other activities. But you need to be aware that security is applied consistently across all activities. So if a user is given the ability to delete tasks, this would actually mean they can delete any activity type. (Including your custom activity.) Activities can be regarding any table that is enabled for activities, meaning a custom activity could be regarding “anything”. (Although you could consider some control by limiting which entities are available to users in your model-driven app!)

Calculated and Rollup Fields

The Dataverse provides us with options to create calculated or rollup fields.

Calculated fields are populated on the retrieve of records. They are read-only. They can be based on fields within the table or its parent.

Rollup fields are stored on the table. Like calculated fields they are read-only, they are calculated (re-calculated) based on a update schedule or on demand. The values on the parent are rolled up from child records. (1:N only!) Filtering on the related table can be applied.

In complex scenarios …. it is possible to include rollup fields in calculated fields. And it is possible to rollup “simple” calculated fields.


Often we will work with one to many (1:N) relationships. So one order can have many order lines etc.

There are situations when you may need a many to many relationship (N:N). For example one patient could be seen by many doctors. And at the same time each doctor would have many patients.

Deciding when an N:N relationship is needed can sometimes be harder than you’d think! Creating a POC of your data model can help identify many to many relationship requirements.

Tip: If you aren’t fully familiar with the types of relationships in the Dataverse and the possible cascade logic then some additional revision into relationships may be beneficial.

You may wish to review this post in which I explain table relationships.

Alternate Keys

You can also define alternate keys. Alternate keys can contain decimal, whole number, text fields, dates and lookup fields. Entities can have up to 5 alternate keys. An index is created behind the scenes which is used to enforce uniqueness. An alternate key can be made up of multiple fields but its total length cannot exceed 900 bytes or 16 columns per key.

Alternate keys are often useful for fields like account number (etc) that may be primary references supplied to / from external systems. And would therefore need to have uniqueness strictly enforced.

Design business unit team structure

Business Units are a fundamental building block in the Dynamics security model. They define the structure of the organization.
Business units provide a great way to manage record access for large numbers of users with permissions being granted to the current business unit and / or its children.

Therefore the first security construct to consider are business units, these allow you to define the structure of your organization. Users, teams and security roles will be linked to business units making them a fundamental building block in the security model. Business units can have a hierarchy. This might reflect the organizations actual hierarchy but that isn’t always the case. In actual fact the hierarchy is designed to “drive” which users get access to what records.

Each business units has a default team that includes all users assigned to that business unit. Each user can only be in one business unit but they can be added to additional teams as required.

Teams have several uses in Dynamics 365 / Power Platform. Teams are essentially lists or groups of users and can be used to help manage security. Teams may also be useful when creating reports. Additionally, teams can be useful when you wish to share “assets” with a group of users. (Assets could include views, charts, dashboards or records.)

Owner teams within the Dataverse can simply include lists of users. But a Dataverse team can also be connected to an Azure Activity Director Office Group or Security Group.

We also have a concept of Access Teams. Access teams are dynamically created on a per record basis. The access granted to the members of the team to the specific record is based on an access team template.

If you need to revised the concepts of business units and teams in greater depth, try reading my post on the subject here.

when designing the data model consider its manageability. Creating large numbers of busi
ness units and security roles may give granular control of security options but could also result in a solution which is a nightmare to manage. You should use business units to restrict access as required but do not be tempted to simply match the organisations structure.

Security Hierarchy

An additional approach to security is to use a security hierarchy. With this approach a manager is granted access to the records of their team members. As a multi-level team structure could exist the permissions can be inherited through multiple levels. This way a manager can read records for their direct reports and further into the hierarchy. The manager however can only maintain records owned by their direct reports.

The security hierarchy can be based on a concept of manager or position. With manager the manager field from the systemuser record is used. With a position approach custom positions are defined and users assigned to each position. (The position approach allows crossing of business unit boundaries.)

When defining the hierarchy settings we can decide how many levels to target. The number of levels implemented should typically be kept as low as possible, maybe 4 levels. Although the theoretical maximum number of levels is 100.

Note: You cannot combine manage and position hierarchies at the same time.

Design security roles

Security roles within the Dataverse provide users with access to data. As a general principle users should only be granted access to data that they really need to access.

The Solution Architect will need to decide what approach should be taken to security roles. Dynamics 365 does ship with a number of out of the box security roles, commonly these will be copied and then amended as required. There are some common strategies for building security roles;

  • Position specific – each person who holds a given position is given one specific role. (All users have one role.)
  • Baseline + position – a baseline role is created which grants access to the entities needed by all users. Then an additional role is added specific to the additional requirements for a given position. (All users have at least two roles.)
  • Baseline + capability – again a baseline role is created. Then roles for each capability / feature that might be required. (All users would have multiple roles.)

As part of your revision you may wish to consider the advantages and disadvantages of each of the strategies above. Additionally maybe review how security is achieved in your current organization!

Each role is linked to a business unit. The roles then contain many options that govern the access to entities and features for the combination of business unit and user (or team).

Often roles will be inherited from a parent business unit. Meaning when I use the manage roles on a user linked to a child business unit, I still see the roles from the parent business unit. This happens even though I haven’t created any roles specific for the child unit!

Each user (or team) can have multiple roles. The Dynamics 365 security model works on a least restrictive security model. Meaning a “sum” of all the roles assigned to the user would be applied. Additionally when a role is assigned to a team, we can select if the privileges apply to “just” the team or if they get applied directly to the user. Meaning members of the team inherent the privileges as if the role is applied directly to their user record.

Each role is made up of several options (actions) for each table / feature in Dynamics 365. Entities typically have multiple actions covering functions including, create, read, write, delete, append, append to, assign and share.

Each table can be organization owned or user / team owned. Organization owned entities have to security privileges, meaning each user either is isn’t granted access. Whilst user / team owned entities support access be granted to the user, business unit, child business units or organization.

If you need to revised the concepts associated with security roles in greater depth, try reading my post on the subject here.

Design column (aka field) security

Often it will be sufficient to only control access to an table. But it maybe that a particular column on a table contains sensitive data that only certain users can see and maintain. We can hide columns on forms but this does not actually secure the data.

In these scenarios Dynamics 365 and the Power Platform supports column level security.

For example you may store information on contact about their disabilities / special needs. A limited number of users may need access to this information but maybe most users don’t need to see what disabilities a person might have.

You can read about how this operates here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s