2.1Table types
standard, activity, virtual and elastic
A table's type belongs to the table definition, not to the rows in it, and it decides which of the platform's behaviours your table is eligible for — transactions, relationships, ownership, and the columns the platform adds on the table's behalf.
Dataverse presents tables as one of four types, and the docs describe each by the case it is for.citedsource: Power Apps docs — Types of tables › the four table types · artifacts-ch02.md
| Type | What the documentation says it is for |
|---|---|
| Standard | Tables included with a Dataverse environment; account, business unit, contact, task and user are the documented examples. Most of them can be customised, and a table imported in a managed solution and marked customizable also appears as standard. |
| Activity | Rows with an activity-based element, which can include a subject, start time, stop time, due date and duration. |
| Virtual | A table populated with data from a source outside Dataverse. |
| Elastic | A table storing a dataset in excess of tens of millions of rows. |
Elastic tables, which are backed by Azure Cosmos DB, do not support multi-record transactions: several write operations executed as part of one request are not transactional with each other, so, in the documented example, an error raised in a synchronous plug-in step registered on PostOperation for the create message does not roll back the row that was created.citedsource: Power Apps docs — Create and edit elastic tables › Considerations › transactions · artifacts-ch02.md
The documentation lists by name the features an elastic table does not support. Among them: N:N relationships to standard tables, alternate keys, calculated and rollup columns, currency columns, the cascade operations delete, reparent, assign, share and unshare, access teams, and table connections.citedsource: Power Apps docs — Create and edit elastic tables › Features currently not supported · artifacts-ch02.md
A virtual table is organization owned and does not participate in the row-level security concepts described in §2.5; it holds a Name and an Id column by default, its columns cannot be used in rollups or calculated columns, it does not support auditing, and an existing table cannot be converted into one.citedsource: Power Apps docs — Create and edit virtual tables › Considerations · artifacts-ch02.md
An activity table can be owned by a user or a team and cannot be owned by the organization, which removes one of the two ownership models in §2.4 from the choice.citedsource: Power Apps docs — Types of tables › Activity tables › ownership · artifacts-ch02.md
Guidance
Read the elastic table's unsupported list before you read its scale figures. Pick a table for throughput, then find you need a rollup column or an alternate key, and you are replacing the table — which means migrating its rows.
2.1.1Creating a standard table from a solution project
A table is a solution component, so you can author the whole of its definition as XML and import it. The lab's table was made that way: pac solution init wrote the project, an Entity.xml went under src/Entities/ch23_Widget/ with a primary key and a primary name column, and the packed solution went into the development environment.
Evidence — the import, and the query the new table answered
Dataverse solution project with name 'ch23lab' created successfully in: '.../scratchpad/lab/ch23lab' Dataverse solution files were successfully created for this project in the sub-directory Other, using solution name ch23lab, publisher name ch23lab, and customization prefix ch23. Please verify the publisher information and solution name found in the Solution.xml file. Packing Solution... Packing .../scratchpad/lab/ch23lab/src to .../scratchpad/lab/ch23lab/ch23lab_widget.zip Processing Component: Entities - ch23_Widget… eleven further component headings, then Processing Sharded Component Files …Unmanaged Pack complete. Packed Solution. Connected as skydude@itrancyngergmail.onmicrosoft.com Connected to... skydude's Environment Solution Importing... Solution Imported successfully. Publishing All Customizations... Published All Customizations.
The imported table answers a FetchXML query under its logical name ch23_widget, with ownerid, owningbusinessunit and statecode accepted as attribute names on a table whose authored definition named none of them.verifiedartifact: ch02-widget-fetch · artifacts-ch02.md
Connected as skydude@itrancyngergmail.onmicrosoft.com Connected to... skydude's Environment No results returned.
Caution
pac org fetch adds its own paging attributes to the query it is given, so a top attribute on the fetch element is rejected with The top attribute can't be specified with paging attribute page. Passing the same query through --xml rather than --xmlFile terminated with an XmlException before the service was reached.verifiedartifact: ch02-fetch-top · artifacts-ch02.md
2.2Columns and keys
what a column's kind decides at read time
A column's kind decides three things at once: how the value is stored, what shape the API hands back to your code, and which query operations the column is eligible for.
The three text column types are bounded by counts of characters. Text and Text Area default to 100 characters and reach 4,000; Multiline Text defaults to 150 and reaches 1,048,576. Reducing the maximum does not truncate rows that already exist; the reduced maximum applies to new rows.citedsource: Power Apps docs — Column data types › Text columns › maximum lengths · artifacts-ch02.md
Evidence — the generated model, and the runtime type each kind resolves to
Begin reading metadata from MetadataProviderService Begin Reading Metadata from Server Read 1 Entities - 00:00:01.146 Read 0 Global OptionSets - 00:00:00.000 Read 0 SDK Messages - 00:00:00.000 Completed Reading Metadata from Server - 00:00:01.405 Completed reading metadata from MetadataProviderService - 00:00:01.406 Begin Writing Code Files Processing 1 Entities Wrote 1 Entities - 00:00:00.0695884 Processing 0 Messages Wrote 0 Message(s). Skipped 0 Message(s) - 00:00:00.0000105 Processing 0 Global OptionSets Wrote 0 Global OptionSets - 00:00:00.0000039 Code written to .../model/Entities/account.cs. Code written to .../model/EntityOptionSetEnum.cs.… a note about ProxyTypesAssemblyAttribute, then Completed Writing Code Files …Generation Complete - 00:00:01.489
The class generated for account in the lab environment resolves each column kind to a runtime type, read off the property the generator emitted for a column of that kind.verifiedartifact: ch02-account-cs · artifacts-ch02.md
| Column | Kind | Runtime type in the generated class |
|---|---|---|
accountcategorycode | choice | OptionSetValue, behind a generated enum |
primarycontactid | lookup | EntityReference |
revenue | currency | Money |
createdon | date and time | System.Nullable<System.DateTime> |
2.2.1Choice columns compared with lookups
A choice and a lookup both show your user a list to pick from. They differ in what the picked value is: an integer defined in metadata, or a reference to a row in another table.
Adding a lookup column creates an N:1 table relationship between the table and the lookup's target row type. The documented lookup kinds are simple, customer, owner, partylist, regarding and custom multi-table; partylist lookups are created by the system and a custom one cannot be authored.citedsource: Power Apps docs — Column data types › Different types of lookups · artifacts-ch02.md
Three consequences follow from that difference, and in practice they are what decides your column.experientialbasis: repeated redesigns where a choice column acquired attributes and had to become a lookup, and where a lookup's target privileges changed a view's results
- A choice's values are metadata, so changing the set is a solution change that moves between environments through §3.3's layers. A lookup's values are rows, so changing the set is data movement.
- A lookup can carry columns of its own, because the target is a row. A choice carries a label and an integer.
- A lookup brings the target table's read privileges into the query, because the reference resolves to a row the reader may or may not see (§2.5). A choice brings no second privilege check.
Guidance
Prefer a choice where the set is small, changes on your release cadence, and belongs to the application. Prefer a lookup where the set is owned by the business, changes between releases, or needs attributes of its own.
2.2.2Calculated, rollup and formula columns
Calculated, rollup and formula columns hold a value the platform derives rather than one a user typed. They differ in when the derivation runs, and that is what decides how the column behaves when you read it.
A rollup column's value is produced by scheduled system jobs running asynchronously in the background. The Mass Calculate Rollup Field job runs 12 hours after the column is created or updated by default, and the incremental Calculate Rollup Field job has a default minimum recurrence of one hour.citedsource: Power Apps docs — Define rollup columns › Rollup calculations · artifacts-ch02.md
The documented restrictions on rollups bound both the count and the shape: a maximum of 200 rollup columns for the environment and 50 per table by default; a rollup over a rollup column is not supported; a rollup can be done over a 1:N relationship and not over an N:N; and business rules, workflows and calculated columns use the last calculated value rather than a fresh one.citedsource: Power Apps docs — Define rollup columns › Rollup column considerations · artifacts-ch02.md
A formula column, built on Power Fx, performs an operation that returns a value during the fetch.citedsource: Power Apps docs — Column data types › Fx Formula columns · artifacts-ch02.md
Formula columns are on the documented list of columns that cannot be secured, alongside lookup columns, primary name columns, columns in virtual tables, and system columns such as createdon, modifiedon, statecode and statuscode.citedsource: Power Platform docs — Column-level security › which columns can be secured · artifacts-ch02.md
Caution
The refresh semantics bound what you can read a rollup for. A rule that reads a rollup reads a figure computed at some earlier point, and the interval between that point and the read is not one the rule can see.
2.2.3Alternate keys
An alternate key names a column, or a combination of columns, that identifies a row as well as the primary key does. It exists for integration: an upsert from an external system can address a Dataverse row by the identifier that system already holds.
A table may hold up to ten alternate key definitions. A key is validated against SQL index constraints such as 900 bytes per key and 16 columns per key, and its columns must be of type decimal number, whole number, single line of text, date time, lookup or option set, with no column-level security applied. Alternate keys are not supported on virtual tables.citedsource: Power Apps docs — Work with alternate keys › constraints · artifacts-ch02.md
Alternate keys use database indexes to enforce uniqueness and optimise lookup performance, and the documentation's instruction, so that the customization UI and solution import stay more responsive, is to create that index in a background process. The key's index status then reads pending, in progress, active or failed.citedsource: Power Apps docs — Work with alternate keys › Monitor index creation · artifacts-ch02.md
Solution import treats an unrecognised element in an entity definition as absent rather than as an error, so a hand-authored key that never existed reports the same success as one that did.experientialbasis: the lab attempt described in artifacts-ch02.md, where an EntityKeys element hand-authored into an imported Entity.xml produced no key and no error
Guidance
Confirm a metadata change by re-exporting the solution and reading the file back. The importer's exit status will not tell you.
2.3Relationships and cascade behaviour
what happens to the children when the parent moves
Dataverse has two relationship types. A 1:N relationship is a lookup column on the referencing table; an N:N relationship is a row in an intersect table the platform maintains for you. N:1 is the same 1:N read from the other end, and it shows up in the designer because the designer groups relationships by table.
Table relationships are metadata; the documented name for a less formal kind of relationship between rows is a connection, and the examples given are two contacts who are married, or friends outside work, or a contact who used to work for another account. An N:N relationship depends on a relationship table, sometimes called an intersect table.citedsource: Power Apps docs — About table relationships › connections · artifacts-ch02.md
A relationship's cascade settings decide its runtime behaviour. A 1:N relationship carries one setting per action, and the setting says what the platform does to the related rows when that action happens to the parent.
The documented behaviours are cascade all, cascade active, cascade none, cascade user owned, remove link and restrict, and the actions that can trigger them are assign, reparent, share, delete, unshare, merge and rollup view. Delete takes cascade all, remove link or restrict; restrict prevents the parent row from being deleted while related rows exist.citedsource: Power Apps docs — About table relationships › Behaviors · artifacts-ch02.md
A 1:N relationship whose cascade settings are among the ones the documentation marks parental is a parental relationship. The platform will not let a row have two cascading parents, which is why a cascade setting is sometimes greyed out in the designer with no explanation on the screen.
A new relationship cannot set any action to cascade all, cascade active or cascade user-owned if the related table already sits as the related table in another relationship that does, which is the platform preventing a row from having two cascading parents; the documentation adds that this usually means one parental relationship for each pair of tables.citedsource: Power Apps docs — About table relationships › Parental table relationships · artifacts-ch02.md
The settings are written into the solution file, so you can read a table's whole cascade configuration by exporting the solution and unpacking it. Two commands did that to the lab's table.
Evidence — the export, and the cascade settings the unpack wrote out
Connected as skydude@itrancyngergmail.onmicrosoft.com Connected to... skydude's Environment Starting Solution Export... Solution export succeeded. Unpacking Solution... Extracting .../out/ch23lab.zip to .../unpacked Skipping localization Processing Component: Entities - Account - ch23_Widget Processing Component: EntityRelationships Unmanaged Extract complete. Unpacked Solution.
Other/Relationships/Owner.xml, BusinessUnit.xml, SystemUser.xml and Team.xml for the lab's table: the relationships an ownership model implies.In the unpacked solution, each generated relationship on the lab's table carries a cascade element for six actions — assign, delete, archive, reparent, share and unshare. The owner, user and team relationships hold NoCascade on each of the six; the business unit relationship holds Restrict on delete and archive, and NoCascade on the rest.verifiedartifact: ch02-cascade-owner · artifacts-ch02.md
<EntityRelationship Name="owner_ch23_widget"> <EntityRelationshipType>OneToMany</EntityRelationshipType> <ReferencingEntityName>ch23_Widget</ReferencingEntityName> <ReferencedEntityName>Owner</ReferencedEntityName> <CascadeAssign>NoCascade</CascadeAssign> <CascadeDelete>NoCascade</CascadeDelete> <CascadeArchive>NoCascade</CascadeArchive> <CascadeReparent>NoCascade</CascadeReparent> <CascadeShare>NoCascade</CascadeShare> <CascadeUnshare>NoCascade</CascadeUnshare> <ReferencingAttributeName>OwnerId</ReferencingAttributeName> </EntityRelationship> <EntityRelationship Name="business_unit_ch23_widget"> <ReferencedEntityName>BusinessUnit</ReferencedEntityName> <CascadeAssign>NoCascade</CascadeAssign> <CascadeDelete>Restrict</CascadeDelete> <CascadeArchive>Restrict</CascadeArchive> <CascadeReparent>NoCascade</CascadeReparent> <CascadeShare>NoCascade</CascadeShare> <CascadeUnshare>NoCascade</CascadeUnshare> <ReferencingAttributeName>OwningBusinessUnit</ReferencingAttributeName> </EntityRelationship> <EntityRelationship Name="user_ch23_widget"> <ReferencedEntityName>SystemUser</ReferencedEntityName> <CascadeAssign>NoCascade</CascadeAssign> <CascadeDelete>NoCascade</CascadeDelete> <CascadeArchive>NoCascade</CascadeArchive> <CascadeReparent>NoCascade</CascadeReparent> <CascadeShare>NoCascade</CascadeShare> <CascadeUnshare>NoCascade</CascadeUnshare> <ReferencingAttributeName>OwningUser</ReferencingAttributeName> </EntityRelationship> <EntityRelationship Name="team_ch23_widget"> <ReferencedEntityName>Team</ReferencedEntityName> <CascadeAssign>NoCascade</CascadeAssign> <CascadeDelete>NoCascade</CascadeDelete> <CascadeArchive>NoCascade</CascadeArchive> <CascadeReparent>NoCascade</CascadeReparent> <CascadeShare>NoCascade</CascadeShare> <CascadeUnshare>NoCascade</CascadeUnshare> <ReferencingAttributeName>OwningTeam</ReferencingAttributeName> </EntityRelationship>
Guidance
When you are auditing an environment, read the cascade settings out of an unpacked solution rather than out of the relationship designer. The file gives you the settings for four relationships in one place; the designer gives you one relationship at a time.
2.4Ownership
the columns an ownership model adds, and what they are for
Your table declares one of two ownership models, and the security model in §2.5 filters on that declaration.
A custom table is declared user-or-team owned or organization owned, and the ownership type cannot be changed once the table has been created. Organization-owned data belongs to the organization and access to it is controlled at the organization level; user-or-team-owned data belongs to a user or a team and actions on those rows can be controlled at the level of a user.citedsource: Power Apps docs — Types of tables › Table ownership · artifacts-ch02.md
The declaration is one element in your table's definition, and the platform turns it into columns. The lab's table declared UserOwned and authored a primary key and a primary name column; the export shows what came back.
Evidence — the columns an ownership declaration added
The exported definition of the lab's user-owned table holds eighteen attributes. Two were authored by hand — the primary key and the primary name column — and the platform added sixteen, among them ownerid as an owner-typed column declaring the lookup types 8 and 9, and owningbusinessunit, owningteam and owninguser as lookups.verifiedartifact: ch02-widget-columns · artifacts-ch02.md
<attribute PhysicalName="OwnerId">
<Type>owner</Type>
<Name>ownerid</Name>
<RequiredLevel>systemrequired</RequiredLevel>
<IsCustomField>0</IsCustomField>
<LookupTypes>
<LookupType id="00000000-0000-0000-0000-000000000000">8</LookupType>
<LookupType id="00000000-0000-0000-0000-000000000000">9</LookupType>
</attribute>
<attribute PhysicalName="OwningBusinessUnit">
<Type>lookup</Type>
<Name>owningbusinessunit</Name>
<RequiredLevel>none</RequiredLevel>
<IsCustomField>0</IsCustomField>
<LookupTypes />
</attribute>
ownerid and that user's business unit in owningbusinessunit.Owner teams and access teams are rows of the same table, told apart by a column on the row rather than by a separate table.
An owner team owns records and carries security roles; an access team does neither, and instead has records shared with it and is granted access rights over them, which include read, write and append. Each business unit has its own owner team, and those teams are managed by the system.citedsource: Power Platform docs — Teams in Dataverse › Types of teams · artifacts-ch02.md
Evidence — the teams a fresh environment already holds
In the lab environment the team table returned rows of both kinds: an owner team named org85700aeb flagged as the default team, and three access teams under generated names. The kind is the value of teamtype on the row.verifiedartifact: ch02-fetch-team · artifacts-ch02.md
Connected as skydude@itrancyngergmail.onmicrosoft.com Connected to... skydude's Environment name teamtype isdefault teamid 12704e77bd5b4f3082ed3c99b3e23387_2 Access No 9543483d-1c8c-f111-ab0f-70a8a5b2d5ff 12704e77bd5b4f3082ed3c99b3e23387_1 Access No 7c43483d-1c8c-f111-ab0f-70a8a5b2d5ff org85700aeb Owner Yes da8edf98-dd8b-f111-ab0f-70a8a5b2d5ff 12704e77bd5b4f3082ed3c99b3e23387_0 Access No 2743483d-1c8c-f111-ab0f-70a8a5b2d5ff
Guidance
Choose organization ownership when the rows are reference data every reader sees on the same terms. Choose user or team ownership when a query will at some point need scoping to the reader. You cannot revisit this without rebuilding the table, so guessing wrong costs you a migration.
2.5The security model at read time
business units, roles, privilege depth, column security
The security model is a set of rows in Dataverse, read on the way out of the database. Business units, security roles, and the assignment of a privilege to a role at a depth are each stored as rows, so you can audit the whole model with a query.
2.5.1Business units and privilege depth
A business unit is a node in a tree, and an environment starts with one node.
The organization, also called the root business unit, is the top of the hierarchy, and its name cannot be deleted. Each business unit can have only one parent and may have several children; every user is assigned to one business unit and only one, newly provisioned users land in the root, and each business unit has a default team whose name cannot be changed.citedsource: Power Platform docs — Create or edit business units › the hierarchy · artifacts-ch02.md
A security role grants privileges, and each grant carries a depth. The depth is what turns a privilege into a filter over the business unit tree.
The documented access levels are organization, parent-child business unit, business unit, user and none, and the documented nesting is stated for two of them: a user with business unit access has user access as well, and a user with organization access has each of the other types.citedsource: Power Platform docs — Security roles and privileges › Access levels · artifacts-ch02.md
The depth is stored on the role-to-privilege row rather than on the role or on the privilege, which is why auditing it means joining three tables.
A user may hold several security roles, and role privileges are cumulative: the user is granted the privileges available in each role assigned to them.citedsource: Power Platform docs — Security roles and privileges › cumulative privileges · artifacts-ch02.md
The table privileges a role can grant are create, read, write, delete, append, append to, assign and share. Append and append to are directional — append is required to attach the current record to another, append to is required to be attached to — and associating rows across an N:N relationship requires append on both tables.citedsource: Power Platform docs — Security roles and privileges › Table privileges · artifacts-ch02.md
Evidence — what one environment's business units and role depths hold
A freshly provisioned environment in the lab held a single business unit, named after the organization and with no parent: the query requested parentbusinessunitid and the column was omitted from the rendering because the one row had no value for it.verifiedartifact: ch02-fetch-businessunit · artifacts-ch02.md
In the lab environment the Basic User role held the create, read, write and delete privileges for account through roleprivileges rows carrying privilegedepthmask of 1.verifiedartifact: ch02-fetch-role-depth · artifacts-ch02.md
Connected as skydude@itrancyngergmail.onmicrosoft.com Connected to... skydude's Environment name roleid rp.privilegedepthmask p.name Basic User 2d93df98-dd8b-f111-ab0f-70a8a5b2d5ff 1 prvReadAccount Basic User 2d93df98-dd8b-f111-ab0f-70a8a5b2d5ff 1 prvWriteAccount Basic User 2d93df98-dd8b-f111-ab0f-70a8a5b2d5ff 1 prvDeleteAccount Basic User 2d93df98-dd8b-f111-ab0f-70a8a5b2d5ff 1 prvCreateAccount
role to roleprivileges to privilege; the depth arrives as a column of the middle table because that is where the platform stores it.Caution
A security role adds privileges to the ones a user already holds. A role you write to restrict a group has no effect while those users hold a second role granting the same privilege at a greater depth.
2.5.2Column-level security
Security roles decide which rows a reader sees. Column-level security decides which columns of a row they see inside it. You configure it on the column and grant it through a separate profile.
A column security profile grants read, read unmasked, update and create on a secured column to named users and teams. Until a profile is assigned, a secured column is readable by the system administrator role and by nobody else.citedsource: Power Platform docs — Column-level security › column security profile permissions · artifacts-ch02.md
Column-level security is configured organization-wide and applies to every data access request, and it does not apply to users holding the system administrator role — data is never hidden from a system administrator, and the documentation says verifying a configuration requires an account without that role.citedsource: Power Platform docs — Column-level security › which columns can be secured · artifacts-ch02.md
Securing a column is rarely finished at the column. Copy the value into a calculated column, summarise it into a rollup, or sort a view on it, and the original stays protected while the information walks out.experientialbasis: field practice on model-driven apps where a secured column was reachable through a calculated column and through a view's sort order
Guidance
Audit the paths out of a secured column as well as the column itself: the calculated columns that read it, the rollups that summarise it, and the views that sort on it.