Modeling and working with data on the Dataverse within the Power Platform

The Common Data Model - Is an industry agreed approach to storing commonly used data.  Use it to store applications across your company/organisation.  Results in a single source of truth instead of multiple copies with data contained in different schema's.

Two types of relationships namely: 1:N and N:N (Dataverse hides the associate entity/intersect entity that is created in the background).  

Use "Option Sets" for small static data and use "Lookups" for larger or changing data.  In Multiselect Option sets for N:N relationships, rather use "Lookups".

SSMS can be used to view data using T-SQL, better to use "SQL 4 CDS" as it provides a full ability to work with data within the XrmToolBox.

XrmToolBox - is a 3rd party download that has a ton of contributed tools and there are good options for modelling data.  This is a collection of tools that are unbelievably useful and get continually updated and new tools added.

Excel and browser plugins - Can be used to import/export data and there are some nice Edge/Browser plugins to help such as "Level up for Dynamics 365/Power Apps".  Level up for Dynamics 365/Power Apps is a fantastic tool that I encourage any developer to add to Edge or Chrome as an extension.

Level up for Dynamics 365/Power Apps Browser extension

Dynamics 365 Power Pane Browser extension.

Dynamics 365 Power Pane is also a useful extension shown below:

Power Pane options

The built in browser Dataverse management tool is super easy to use to model your Common Data Model further.

Microsoft Dataverse (CDS) - Overview

Overview:  Dataverse is CDS, there is a long story on the naming but ultimately Dataverse is a data store with a advanced security model, Open API's, workflows, pipeline injection...  It is awesome.

It is high performance, and would take considerable effort and components to deliver similar functionality or even semi close functionality.  It does have limitations mainly around performance but don't let that fool you, Dataverse is fast and powerful but for massive industrialized storage it's not the right option.  The costs are also a key consideration.

The biggest mistake I see is people making the same mistakes as they do with relational databases namely: 

Poor Dataverse implementation down to 1) poor entity relationship design, 2) either too many table containing duplicate data or to few table being expanded for a dev teams capability but ignoring existing systems, 3) poor security 4) too many cooks.

Basically, like any Database service, you need to have owners and try keep the structure logical and expand it appropriately.  The idea behind the data model used by the dataverse is to have centralized secure shareable data like customers or account information.  It's simple, treat dataverse as you would your most precious core database, have an owner that needs to understand and approve changes.

Note:  Microsoft have had some trouble naming Dataverse, it was previously known as the Common Data Service (CDS).

Dataverse logo

Overview: Dataverse helps improve processes.  And Dataverse helps reduce time to build IT capability, remove shadow IT, improve security and governance.  Data is the common data store we need to use to be effective.  As part of the Power Platform, it allows us to build custom software fairly quickly.

Updated 07-July-2022

Dataverse provides relation data storage (actually runs on Azure SQL (Azure Elastic Pools), Cosmos DB, and Blobs), lots of tools e.g. modelling tools.  I think of it as a SQL database with lots of extra features.  Most importantly business rules and workflow.

• Dataverse relies on AAD for security
• Easy data modelling and supports many-to-many relationships NB!
• Easy to import data using PowerQuery compatible data sources
• Role-based data (previously called row) and column (previously called fields) level security.  See Dataverse security in a nutshell at the bottom of this post.
• Provides a secure REST API over the Common Data Model, it's awesome
• Easy to generate UI using PowerApps model driven app
• Ability to inject business rules when data comes in or out of the Dataverse (can also use .NET core code)
• Can also stored files (ultimately in blob storage)
• Search that indexes entities and files
• CDS used tables, Dataverse calls them Entities.  Some of the UI still refers to table.  Just assume Entity and Table are interchangeable terms.

Dataverse basically allows you to have a PaaS data hosting service that mimics what we have done for many years with databases and Open API, has advance features and tooling and it is all securely managed for you.  

The cons are basically: is that it is expensive.  So you need to know your size and keep buying add-ons to the plans.  Scaling Dataverse is expensive.

Common Data Model: Collection of tables (previously called entities and most CRM people still call them entities) e.g account, email, customers for a business to reuse.  Comes from CRM originally, the starting point consists of hundreds of entities pre-created.  Common standard for holding data.

Each Power Platform Environment has a single Dataverse associate to it.  It's a good idea to have more than one environment but at it's simplest, use a trial to learn and progress to production.

Once I have a new environment, I can use Power Apps to access my environments Dataverse and model out a new table to store info, I am storing people tax returns.

Go into the Dataverse and model directly

Model the table in you Dataverse instance

Dataverse Security in a Nutshell:

1. A user is linked from AAD to the User entity in the Dataverse.
2. User Entity record is aligned to the AAD User.
3. AAD Users can be part of AAD security groups.
4. Dataverse Teams (Dataverse Group Teams) can have Users and or Security Groups assigned.
5. Dataverse Group Teams are aligned to Business Units.
6. Business Units have roles (rights).

"Security is additive" in Dataverse (generally the whole MS and security world these days).  i.e. no remove actions.  If you have permission in any of the groups you can access the data/behavior.

Business Units used to restrict access to data.  Can be hierarchical i.e. Enterprise > Audit > EMIA > UK (Don't use it like this, keep it simple)

Security Roles define a users permissions across the Dataverse entities i.e role can read only from Accounts entity 

Teams consist of users and security groups.  That get assigned roles.  There are two types of Teams in Dataverse: Owner teams & Access Teams

Field-level security, only allows specified users to see the field data

https://learn.microsoft.com/en-us/power-platform/admin/wp-security-cds (Good clear post on Dataverse security, core concepts are Business, Units, Teams, Roles, Users & OAuth/AAD)

Options Layering API's on Data Sources - Micrososervices kind of

Hasura takes data sources such as SQL, Postgress & MySQL and converts it into GraphQL API's.  SQL Server is in preview.  Service is available on Azure and hooks into AAD and AAD B2C.  Hasuru looks extremely interesting and useful.  Potentially a great time saver.

CDS/DataFelx/Oakdale - Allows for Entity creation and provides REST API's.

SharePoint lists provide HTTP API's for CRUD operations.

REST API's vs GraphQL

OpenAPI specification (previously known as the Swagger specification) is my default for an API, this allows for a known RESTful API that anyone with access can use.   Open API has set contracts that returned defined objects which is great, you can work with the API like a database with simple CRUD operations as defined by the specification.  The issue is that the returned objects are fixed in structure so you may need 2 or more queries to get the data you are looking for.  Alternatively, GrapghQL allows the developer to ask for the data exactly as the want it.

Open API example:

/api/user/{2} returns the user object  // Get the user object for user 2

/api/users/{2}/orders/10  // Returns the last 10 orders for the user

GraphQL example:

Post a single HTTP request.

query {

 User(id: "") {

    name

    email

    orders(last: 10 {

      orderid

      totalamount

      datemodified

    }

 }

} 

Database Option Notes:

PostgreSQL - Hybrid relation database (Communitity edition free, standard and enterprise higher loads) sits on Linex but can also use Windows OS.  Replicas are used for additional read-only nodes (I think up to 5 geo relicas allowed) 

Can be hosted on Azure or AWS IaaS.  PostgreSQL is also offered as a fully managed service (PaaS) on Azure (either single server or hyperscale).

MariaDB community fork off MySQL - Open source relation database used in the LAMP app stack.  Azure MySQL manage service can have up to 5 replicas for read heavy workloads.

Working with CDS data structures for non CRM types

Overview:  I am working on a Power Platform solution and I need to use CDS.  Basically, I need to be able to see and edit values within CDS.

Option 1: Microsoft SQL Server Management Studio (SSMS) version 18.6 allows connectivity and read-only access.  Here are the instructions.

Option 2:  XrmToolBox has fantastic tools for Dynamics and Power Apps.  There are a lot of individual tools from various contributors.

Here I am using "Entity Relationship Diagram Creator" to look at the relationships between the CDS entities.

Reducing Power Apps Dynamic calls and where to store Power Apps data.

Overview:  Power Apps is driven by data and generally that data comes from Connectors.  So the great news is there are a lot of different connectors and if in trouble I always find the custom connector can be relied upon.  When working with Power Apps, it is not as simple as just having a data source and consuming it, one needs to consider all the data sources, do we need live data, performance.  Basically, going and dynamically pulling lots of dynamic sets of data repeatedly leads to poor performance.

CDS performance has relatively few layers when retiring or updating data so it generally is fast to work with.  Custom connectors have more layers so can be slower and you are dependent on the underlying REST API so watch for performance.  The on-prem azure data gateway is pretty slow but amazing if you need it.

Identify data sources:  CDS, Azure SQL, SharePoint SQL on-prem., CosmosDB, RSS, Open API's...

Understand the security, and the amount of data being pulled.  For example, if we need all the airport codes in the world for a drop down so the user can choose their closest airport e.g. JFK is for New York John F Kennedy airport.  There are roughly 4000 commercial airports in the world. 

Options: Call an Open API service.  Power App by default returns sets of 500, Power Apps max return count is 2000.  You still need to perform 2 calls with paging to get the full data set.  You could use a type ahead if the API supports it, but their will be a lag after each keystroke when Power Apps runs out to the service.  And there will be a lot of calls.  More suitable would be to do 2 calls with 2,000 record for each call and bind the control to the returned data.

A further improvement would be to store the airport lookup on data load or on first request, then subsequent requests would use the table/collection.  In effect, you are locally caching all the airport codes using 2 calls for each Power Apps user session.

For the airport example, one could also store the data using a Excel import, but beware the data is imported into Power Apps and store locally.  Big issue is the data is static in Power Apps, to update it, you need to re-import the Excel table.  So brilliant for sat days of the week or Months of the year as these never change.  Fairly static data like airport codes work well, but require a publisher level overwrite to update the list.  Also, storing extreme amounts of static data leads to bloat of the app and that data still needs to be loaded.  So I would not consider it for 100k+ items as a general rule.  

FYI Microsoft recommends to use less than 30 connections per application.

More Info:

Todd Baginski has a great video on using Excel import and creating language variations/multi-lingual Power Apps using Excel imports.

Alagunila Meganathan on C# Corner has a good post on Excel Imports for Power Apps.

Power Platform Notes - Power Apps, CDS, Power BI & Power Automate

Power Apps

Variables:
Microsoft Docs "PowerApps and Excel both automatically recalculate formulas as the input data changes".

• Contextual variables - scoped at a screen level
• Global variables - scoped app level
Fx> Set(MyUniqueClientNo, 12)

Functions:
Fx> UpdateContext({MyTimesheetId: 34})

Tip from Shane Young:  Note the setting variable may be the reference, so for a control use:

Fx> UpdateContext({MyTimesheetId: txtTimesheetId.Text}) not
Fx> UpdateContext({MyTimesheetId: txtTimesheetId.Text}) unless you want the context to float

Pass a variable to another screen use the Navigate overload, OnSelect property of a button
Fx> Navigate(Screen2, ScreenTransition.None, {TSvar: MyTimesheetId}
MyTimeSheet Id is a contextual variable

If Statement:
Fx> If(MyUniqueClientNo = 12, lblAns.text = 'yes', lblAns.text = 'No')

Environments:

1. Power Platform allows you to create multiple environments, each environment is connected to you AAD tenant.
2. Policies can be applied to prevent DLP against all environments or specific environments.
3. Use Environments for DTAP, business units, or Extranet vs Intranet.
4. Updated June 2022: Environments can be of type: Default (don't use and rename), Developer, Trial, Sandbox or Production.
5. Each Power App environment can have no CDS/Datavserse or a single CDS/Dataverse connect.

Manged vs Un-Managed Solutions:

Solution are useful for packaging and moving assets between environments or tenants.  Manged restricts who can edit the applications in the solution.  I like to keep each solution with a single app in it.  On large projects, it's a good idea to keep environment variables, custom connectors, cloud flows, apps in separate solutions.  It makes it easier to deploy pieces.    

Managed Environments:

Don't mix up with Managed solutions as I did recently.  Managed environments allow for a host to control all the environments.  You also should use a dedicated host production environments with Dataverse database to control all the environments.  You need to install solution on the host.  Managed environment have no correlation to managed solutions.  You need managed environments is you are going to use Power Platforms simple ALM for App deployment.

ALM:
Power Apps has it's own source control and you can manually export and import entire projects "Export package" not connections are not export as part of the package.  Solutions are the best way to move code between environments.  It's a good idea to use environment variables  Get ADO, more secure, better tooling using solutions over manual exporting code using packages.  Power Apps Build Tools allows for ADO/DevOps integration generally using solutions to deploy.  At a minimum you should have your own dev env (Sandbox or Developer type), a test (Sandbox type) and production (production type env) for any serious app you build.

Add "Power Platform Tools"

Example Power Platform ALM deployment using DevOps - 5 environments

Update Jan 2022: 

Solutions are used to in Power Apps to package and move code and resources such as flows, env variables, canvas apps, Dataverse tables between environments.  It's a good idea to not use the default environment as everyone has access to the environment.  The DTAP around ALM can get pretty complex and I like to keep it fairly simple.  Connectors can get nasty in packaging.  ADO using AppBuild Tools makes for a good CI/CD solution for Power Apps.

Environment variables are a great way to manage configuration, they can also easily be configure in the pipelines to replace with the appropriate values.  Tip:  Don't ever set the current value in an env var it get carried thru to the next DTAP env.  Also env vars support Azure Key Vault, use it for secrets.

Custom Connectors and getting Connection References correct when using managed environments to deploy code need to use environment variables so they can easily be set for each environment using CI/CD.  It's a good idea to create connections in solutions and use a dedicate Owner account.  Connection references in solutions - Power Apps | Microsoft Learn

There is also a great tool to unpack .msapp solutions for raw review and storage in git.  https://powerapps.microsoft.com/en-us/blog/source-code-files-for-canvas-apps/ for me, it's main use is verifying code and standards and comparing versions.  Post on unpacking solutions.  You can also see how many controls you have in you Canvas app.  MS have the guideline of 500 controls in an app.  

Have a: 

1. Dev environment that is of the "sandbox" type.  Have an un-managed solution that will act as your base canvas app for all new Power Apps.  When you export the solution remember to export as a managed solution and ensure environment variables are emptied before publishing the export.  Good place to backup to a repository such as TFS Azure DevOps/Git.
2. Test env of the type "production" or "sandbox".  Only allow managed solutions to be deployed.  Watch out for custom connectors, I can't get them to deploy correctly in solutions.
3. Prd env must be of type "production".

Manual ALM i.e. Use PowerApps to store Dev versions, Exporting solutions and unpacking managed solutions into UAT and production.  Manual ALM is good even manually moving the applications by hand is simple and safe.  There are backups and manual backups to avoid a source code repository.  I also like the testing framework in Power Apps.  Using the build tools getting full ALM/CI/CD it is easy to then move your Power Platform into a higher ALM CI/CD level using the Power Platform Build tools, DevOps, and PowerShell cmds.  

Update April 2022: Great simple video on ADO for Power Platform 

Licencing:
Updated: Power Apps Licencing Summary as of 30 December 2019

"To run standalone apps, guest users need the same license as users in your tenant."  Microsoft Docs.  

• PowerApps using AAD B2B user (both members and guest) using standalone Power Apps shall require the Power Apps licence (not necessarily Portal Apps).  Steps to Share Power Apps with Guests. 
• SharePoint user interacting with a Power Apps form on a SharePoint list do not require a Power Apps licence.

Coding Standards for Power Apps:

• Microsoft provide a whitepaper as a suggestion for using naming/coding standards.
• Controls and variables should use prefixed Camel case e.g. txtContactEmail
• Data sources use Pascal Case e.g. ContactUsers 
• Screen must be Full names e.g. Edit Contact User Screen as they are read as named by screen readers like JAWS and Microsoft reader.
• Prefix controls and collections (Label is lbl, Textbox is txt, Button is btn, Collection is col, drop down lists are ddl, Form is frm, Group is grp, Gallery is gal, Icon is ico, Images are img, Table is tbl.  I also prefix my components with com.
• Variable naming is: gbl for Global and context variables are var
• Use Containers for layout and groups for control aggregation.
• Use TabIndex so keyboard navigation makes sense.  Use the AccessibleLable over HintText.  Ensure colour contracts and use the approved colour templates stored in global variables.  All fonts, sizing and colouring must come from global variables so the app can be easily changed.  Use the App Checked and complete all identified accessibility issues/concerns.  Test on actual devices and use screen readers.
• Ensure App Insights is setup, capture and log errors, also use the monitoring (maybe not in production).

Power Apps Portals:
Build responsive website using CDS.  Allow anonymous access or implement multiple Identity Providers (IdP) such as AAD B2C, AAD, Google+, LinkedIn.

Updated: 28 July 2018:
Common Data Service (CDS): Comes from Dynamics CRM Sales, pretty much used like CT's in SharePoint.

• Based on Azure SQL & uses CosmosDB with a nice API that support REST/ODatav4;  
• Has Row, field RBAC and uses AAD for authentication;
• Allows Power BI, Power Apps (previously labeled as PowerApps) & Flow (Power Automate - new name since Ignite 2019) to work with CDS (renamed to Dataverse);
• Use Power Query to Import data easily or data flows
• Multiple data source such as SQL Server, Cosmos, files, Excel, Search, 3rd parties such as SAP.
• CDS is not part of the O365 licencing (including E5), you need to get a PowerApps P1 or P2 (Now Power Apps for Users as licencing changed circa Oct 2019).  Note Power Apps licencing included with O365 is extremely limited and for instance does not cover CDS;
• One Power Platform Environment ties to one CDS/Dataverse database
• Any type of data; any type of app.  
• Easy to use; secure.

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-intro

Examples: 

Insert or Update using Patch to CDS:

Patch(

        Vote,

        Defaults('Employee Sentiment'),

        {

            userid: UserID,

            vote: ThisItem.Vote,

            votedate: Now()

        }

    );

Select from CDS:

UpdateContext( { locVotes: LookUp('Vote', votedate = <date>) });

Tip: XrmToolBox is a fantastic tool.  Multiple contributors have added to the client application, there are so many useful features for instance FetchXMLBuilder allows you to query the Dataverse tables.

Power Automate

Updated 11 December 2019:
Microsoft Power Automate (previously called Microsoft Flow, is an user friendly workflow tool/service): Simple workflow engine sitting on Azure Logic App.
Basic business process flows are:

1. Automated Flows - used when event/status change e.g. CDS row status changes
2. Button Flow/Instant Flow - Events such as a click triggers the flow;
3. Scheduled Flow - in effect timer jobs;
4. Business Process Flow - ensures users enter  data consistently (think Windows Presentation Framework) & follows the same steps every time; and
5. UI Flow (preview) - Provides RPA capabilities;

https://docs.microsoft.com/en-us/power-automate/get-started-logic-flow

Building Blocks for Power Automation are:

• Trigger - what starts the workflow, can be manual trigger or an automated trigger
• Actions - ,
• Conditions,
• Data operations, 
• Expressions - basic operations like adding two numbers together, making a string upper case, and
• Flow Type - see the 5 flow types above.

Display directions using Map on a Image control in PowerApps:
https://dev.virtualearth.net/REST/v1/Imagery/Map/road/Routes/driving?waypoint.1=51.525503,-0.0822229&waypoint.2=SE9%4PN&mapSize=600,300&key=<key>
https://dev.virtualearth.net/REST/v1/Imagery/Map/road/Routes/driving?waypoint.1=" & EncodeUrl(txtDriverLocation.Text) & "&waypoint.2=" & EncodeUrl(txtDriverLocation.Text) & "&mapSize=600,300&key=AsR555key