PACX ⁓ Data model manipulation

We introduced PACX here, as a toolbelt containing commands to streamline the application development on Dataverse environments.

The idea behind PACX is to speed up the development experience towards Dataverse, and one of the first item we wanted to address is the data model manipulation.

Pain points 💢

make.powerapps.com portal is a great tool to create tables, columns and relations with an easy-to-use, easy-to-learn UI that can be used also by early users, but... you have to add them one at time, manually. Moreover, when you create new tables, column and/or relations, there are several info you need to provide to the platform (e.g. name, schema name, type and format, config flags, and so on), and often the defaults provided by the platform are not ideal or they don't match the best practices driven by experience.

Problems arise when you are a system integrator creating a lot of customizations for a given client. Often you negotiate the structure of the data model in advance, using excel files (or other media) to define which tables/fields to add, and then you have 10, 100, 500 fields to be created or updated, manually.

I tend to emphasize the word "manually" because, by experience, is a proxy for "error prone".

🗒️ For a long time we discussed the possibility to script the operations against the data model. Sure you can do everything via REST API, but they're not so handy to script...

🚀 PACX to the rescue! 🚀

Benefits of scripting data model manipulations 💪🏻

A tool that allows to script all the data model manipulation operations provides several benefits:

• 🏃🏻 Fast setup: scripting everything and running via console is A LOT FASTER than using keyboard and mouse to create each table/column/relation manually. In my personal experience, I gained almost 70-80% of performance improvement using PACX commands vs make.powerapps.com
• 📜 Scripts can be automatically generated from data model excel files: if you have the list of fields to create in excel format, with type and specs, you can simply tweak with excel formulas and automatically generate the commands to create each of those fields. Then you can just run it in a console. This alone saves hours of work ⌛.
• 🕒 Scripts can be versioned: you can keep track of the data model changes using your favorite ALM tool (Azure DevOps, GitHub, ...)
• 👩🏻‍💻 You can automatically enforce the best practices: if the command is built to be able to accept the bare minimum information required to perform the operation, and all the other info is automatically inferred using best practices as guidelines, you reduce the risk of errors by newbie devs
• ✂️ Creating similar columns is a matter of cut&paste: I often had the need to create, on a given table, 10-12 similar columns. E.g. on a custom entity called "Fiscal Year", I wanted to track the target revenues for each month of the fiscal year in columns: 12 equal columns, the only difference is in the name. Scripting it you can create the first and then copy&paste the command (or arrow up ⬆️ on windows terminal), change only the name, and you're good to go.
• 🤖 You can provide advanced data manipulation features not available via UI: for instance:
  • creation of polymorphic lookups
  • creation of explicit n-n relationship tables in one step
  • automatically drop a column from forms and view before deleting it

PACX approach to data model manipulation 🧑🏻‍🏫

PACX provides several commands to manipulate the datamodel, grouped in the 3 distinct namespaces:

• pacx table
• pacx column
• pacx rel

pacx table contains commands that can be used to work with tables. It allows to create new tables, delete existing tables, but also export the table metadata (for documentation purposes), generating E-R diagrams of the tables contained in a given solution. There are also a couple of advanced capabilities such as:

• script: reverse engineers the structure of a given table and outputs the pacx style commands that can be used to recreate that table from scratch. Useful when you want to document the contents of a previously created environment.
• defineMigrationStrategy: it analyzes the structure and the relationships of a given list of tables, and provides the sequence of operations required to perform a data migration of records onto those tables, ensuring compliance with referential integrity constraints.

pacx column contains commands designed for efficient manipulation of Dataverse columns. You can create or delete columns (forcing also the pruning of column dependencies, if needed), exporting the metadata of a column (for documentation purposes), retrieve where a given column is being used, and also programmatically set the seed of an autonumber column (useful in devops scenarios).

pacx rel namespace contains commands designed to create or delete relationships between tables. It has a couple of advanced, useful, features:

• create n-n explicit relationships: an n-n explicit relationship is a standard dataverse table whose primary, logical, key is made by a pair of lookups to two other dataverse tables; it's used often instead of standard n-n relationships (that we call implicit) when you need to provide additional attributes to the relationship itself.
• create polymorphic relationships: this can be achieved, and it's fully supported, only via SDK or Web API, as documented in the official article

We'll deep dive on each of those commands in the upcoming posts of this series... stay tuned! 😎