Power Platform Dataverse 101
Tags: dataverse, powerplatform, powerapps, lowcode
Published: 2024-06-24 05:57:50

Everyone knows about the key pillars of the Power Platform:

- Power BI
- Power Apps
- Power Automate
- Power Pages
- Copilot Studio (Formerly Power Virtual Agents)

But they are the pillars, what's the foundation that they are built on, well that's Dataverse (Except Power BI, but thats in the Power Platform in name only). Why do I say this, well 2 reasons:

- Data is the foundation to most systems
- The Platform is built on Dataverse.

The last one in interesting, as soon as something is solution aware its built on Dataverse

- Solution - solution table
- Flow - workflow table
- Connection Reference - connectionreference table

and the list goes on. So with this in mind I want to talk about Dataverse, and the basics to understand to get started with it are:

- History
- Using the Data
- Field/Data Types
- Access Controls
- Licenses

---

## History
Dataverse started out as the database for Dynamics before being recondition for the Power Platform. Its built on the [Common Data Model](https://learn.microsoft.com/en-us/common-data-model/), which is a shared schema designed to introduce standardisation (it's why it use to be called the Common Data Storage).

![common data model](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ksiqivq72m2irqal4os7.png)

For some this is great, a bunch of prebuilt tables, for me personally its like all templates, great in theory but never 100% of what I need so I prefer to make my own.

## Using the Data
There are 5 main ways to access/update Dataverse data:

- Model driven App
- Canvas App
- Flow
- Table Designer
- API

_I know they are all really API but in this case I meant using the [Web API](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/overview) and there are more like Power Query but I think these are the main ones for now_

**Model Driven**
The main way is a Model Driven app, as that is what it designed for, a front end to a Dataverse Table(s). Model Driven Apps are basically a Form and a Table view (I know there is a lot more but that is the core), so they allow you to create/update/delete in the form and view/delete in the list view. The classic designer, how to put this, was not user friendly, but the new designer is very simple and easy to use (if you have used SharePoint lists it will be even easier).

![model driven app](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uawp4rr1zu32xi2o9df9.png)

**Canvas App / Flows**
Canvas Apps and Flows access the table with the Dataverse Connectors.

![dataverse connectors](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/weug026pc6roh07qqlbl.png)

These are API wrappers, prepackaged for easy use. There are 25 actions and counting (plus duplicates that allow access to different Dataverse environments).

**Table Designer**
The Table designer allows direct editing to the table (like Excel), along with uploading excel. Its not the best place to access or edit data as it is it really for configuring the table, but its good place for pre loading data when setting up the table.

**API**
Finally there is the API, this is probably the least used but also pretty cool. The API is built on [OData](https://www.odata.org/), which is a set of standards for the API structure, which makes it a lot easier to use (more info [here](https://dev.to/wyattdave/power-automate-get-items-parameters-25ac)). The API allows procode solutions/other systems to integrate with Dataverse.

## Field/Data Types
Dataverse has multiple different field types, starting with the standard:

- Text
- Number
- Date and Time
- Currency
- File

They also have preset formats for each:

![data type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/brrazflgf69zuio9pg5i.png)

Additionally have some more complex type:
- Choice
- Lookup
- Autonumber
- Formula

**Choice** are simple dropdown, with the added benefit of them being solution aware so can be reused across tables,
**Lookup** is a link to another table, with the added benefit that this allows Expanding (so you can pull fields from the lookup table into your table views).
**Autonumber** allows a sequential number/Date to be automatically created (like the SharePoint ID), more useful then you realise.
**Formula** is Power FX code that is related to the other values in the record (like SharePoint calculated column).

## Access Controls
The big strength on Dataverse is the access controls. SharePoint may be the database of choice for most people but they all know the pain, there is no row or field level security. So in SharePoint its view/edit all or edit own records, thats it.

For Dataverse we use Security Roles (yep the same roles we use to access the platform, everything in the Power Platform is Dataverse under the hood), we create new roles and then add rights to the table. We have full granularity to set if the role can Create, Read, Update, Delete records, and if they can see just their own records, your business groups or the entire org. The role can also be set to just one business group or org (set at the point of giving the role to a user/team).

![security role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7szpw6nkirdiwxps67px.png)

The roles are solution aware, so can be added to solutions to be migrated across environments (and created/edited in the maker studio, not just admin center).

**Row Level**
Row level allows users to see groups of records (e.g UK team can see all only UK records). The ability to use row level secuirty must be turned on in features and then each record set to a business group (so entry in the form or a business process/plugin is needed).

![settings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4zkeoa0i4o3yo4yyfm99.png)
_Business groups are created in each environment in the Admin center and permission set when giving the user a security role_

**Field Level**
Field level allows specific fields to break inherentance from the row. So a user may be able to edit a record but not a specific field (e.g. you create an approval request, they cant edit the field that says approved/not approved).

The field level security is enabled in the field settings
![enable settings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fg22uyx7agez21mfu0g2.png)

And then created it in the Admin Center under Column security profiles (this is also solution aware).

## Licenses
Nothing good in life is free, and its the same with Dataverse.

For starters to access any custom/cdm tables you need a premium license, thats same for Model Driven, Canvas and Flows (though I dont think for API).

Next is storage costs, you are provided some per premium license (250mb data, 2gb file) and some out of the box for the entire tenant, (10gb data, 20gb files, 20gb logs), there is no way of ring fencing per environment.

You can also buy storage addons 
gb per month
Data: £30
Files: £1.50
Logs: £7.50

As an FYI Azure Data storage costs something like 20p per gb per month (with some conditions/caveats, especailly as this does not include compute costs, but you get the idea).

---

There is a lot more to go into but hopefully this covers the basics and the most important parts.