Flexible Datamodel for Dataverse pt 1

This series of articles is linked to the proposed session for Dataverse Summit 2022.

As a first step, we identify the goal of a flexible data model. Dataverse is already a flexible data model, but for each variation, it is necessary to republish the data model and this varies considerably also in the output and in the exposed interfaces. You have to modify, test and rebuild your ingestion process for incoming data and the interfaces to extract the data for other systems.

In a correct software lifecycle, adding a field requires modifying the dataverse development branch, modifying the user interface, joining the dev branch to the test branch, Carry out the tests, merge the test branch with that of prod with a new release, carry out the testing and finally rearrange the trunk for new developments.

On complex projects, these activities are extremely expensive and do not allow rapid operation in adding new data to the existing model.

In order to overcome this problem, I decided to study and develop a flexible data model, able to load any type of data, without having to recompile and modify the processes and without having to test the whole system and deploy it as a new release.

What are the fundamental elements of the new data model?

1 - must be typed and allow the correct loading and search by value or text of the data.

2 - it shall be possible to assign the data to several entities in order to minimise space and to make the data more consistent in the event of a change.

3 - it has to expose data correctly natively.

4 - it must be possible to implement a flexible loading process so that information can be added without breaking old loads.

To achieve these goals, you will need different types of configuration entities to create, plugins, virtual entities, Synapse datalink, SQL table ...

Starting

We start creating a NEW environment using admin.powerplatform.microsoft.com and install Virtual Data Provider from appsource.

Select Get it now. In the sign-in dialog, enter work or school account email. If you agree to the terms and conditions, select Continue. The Power Platform admin center will open automatically.

Select the environment where you want to install the solution. If you agree to the terms and conditions, select Install. Once the installation is complete, you'll see the Virtual connectors in Dataverse app installed under Environments -> [your environment name] -> Dynamics 365 apps.

This solution enable a gateway system that able to control your external table with DML operations.

We build our generic flex datamodel

Table Schema

This Schema is Joined with Account Table, but you can Join with Every Table (Custom Table also).

The main table is the property (dynProperty) table. Here we will store and index the data vertically, entering the information in some technical fields. In this example, We use a Virtual Table, but you can add the Property table in Dataverse also, but consider a maximum volume of 200 million lines (after dataverse will begin to suffer a dramatic decrease in performance, eventually you can create a system with a virtual table to overcome the problem) and 10 entities related to it (maximum number of indexable keys).

other tables are necessary to create a sustainable system with enterprise features:

• - property type (dynTipology)

• - property entity reference lookup (dynEntityReferenceLookup)

• - property distribution (dynPropertyDistribution)

• - property admissible values (dynPropertyAdmissibleValue)

To simplify the data model and avoid slowdowns due to security patterns, tables are created such as Organization and not User/Team and set "track modifications".

Create some altkey for increase consistency of datamodel. For example, I added the altkey for accountnumber into Account Table:

After creating this configuration tables, create some PowerAutomate for better Naming of record.

Example Autoname PowerAutomate

Create a New Autoamted Cloud Flow

We do this for each dyn tables except dynProperty (we create in nexts step as Virtual Entity)

Start the work for Virtual Table

First, create an Azure SQL database to store our virtual entity.

When creating, is not important to use an high cost database, you can start with basic features with a lower cost. You can scale this in next time.

In Security Section, check you use MIXED authentication (azure and sql server), this part is important to use this system with Synapse correctly.

Now, try login to database from SQL Management Studio

Now, in your DB create a New Table with name dynProperty:

Remember to create the dynPropertyId as uniqueidentifier and the default value is set as (newId()).

Sql Script for dynProperty

/****** Object:?Table [dbo].[FlexProperties]???Script Date: 12/11/2022 00:57:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FlexProperties](

?[FieldLabel] [nvarchar](200) NULL,

?[Tipology] [uniqueidentifier] NOT NULL,

?[ReadOnlyValue] [nvarchar](max) NULL,

?[StartValidity] [datetime] NULL,

?[EndValidity] [datetime] NULL,

?[PropertyId] [uniqueidentifier] NOT NULL,

?[RelatedPropertyId] [uniqueidentifier] NULL,

?[internal_account] [uniqueidentifier] NULL,

?[TechnicalValue] [nvarchar](max) NULL,

?[transactioncurrencyid] [uniqueidentifier] NULL,

?[Value_money] [money] NULL,

?[Value_boolean] [bit] NULL,

?[Value_string] [nvarchar](max) NULL,

?[Value_numeric] [numeric](18, 5) NULL,

?[Value_datetime] [datetime] NULL,

?[Value_choice] [uniqueidentifier] NULL,

?CONSTRAINT [PK_FlexProperties] PRIMARY KEY CLUSTERED ([PropertyId] ASC)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[FlexProperties] ADD?CONSTRAINT [DF_FlexProperties_PropertyId]?DEFAULT (newid()) FOR [PropertyId]

GO

When the table is created, go to make.powerapps.com, under Dataverse, create a new connection with SQL Server

Important note, use SQL Server authentication using Username and Password of SQL Server Mixed authentication.

Now, create a new Solution in Dataverse and add a new provider for virtual table

Now we have to configure a new Connection Reference

Now, opening advanced property on dynamics interface, under settings/administration/Virtual Table Origins we have to add a new Origin as Virtual Connector Data Provider.

Select?New. In the pop-up dialog, select the?Virtual Connector Data Provider.

Leave the Dataset Value field Empty

Select SAVE.

With the connection reference and the virtual table data source setup, an?Entity Catalog?is automatically generated. The?Entity Catalog?is specific to the data source and will list all the tables that are in the respective data source. You found this catalog after a few minutes.

Now, from Default solution, found the Entity Catalog for [connectionname] and click on "Create app" button.

Set a temporary name:

The app is automatically generated using the entity catalog table.

Once the app is completed, you can select?Publish?to complete the app and use it later, or you can select?Play?to create your virtual table now without publishing the app.

All eligible data sets from your data source will be provided in the app view.

• SQL: All tables in the database that are eligible are shown.
• SharePoint: All lists in the site are shown.
• Excel: All tables in the Excel file are shown.

Select the data set you wish to use from the entity catalog, and then select?Edit?in the navigation bar.

Wait for the form to fully load before editing. When loaded the form will appear like this:

In the provided form set the?Create?or?Refresh Entity?column to Yes.

Select the?Primary Key?and?Primary Field?of the virtual entity by using the dropdown lists to find the columns you want to use.

Save the record to create the virtual table.

Note: After the save completes, the form will "reset" with all fields shown as blank, this is normal.

Return to the Power Apps home page and select?Data. Your virtual table is now created with a "Custom Entity" prefix. It may take a few moments for the creation to complete.

Add dynProperty Table to our new solution.

When over steps is complete, add relation to Virtual entity. to do this, is better to use the old solution view.

When add a relation you have to add the column in dynProperty where Dyn save the relation. After that, and before use the table, you have to add this column as uniqueidentifier into SQL server.

You have to create any relation that you want to use this property on one or more than one simultaneuosly a property record. In this example I added only a reference on Account Table. You must to add other 2 reference: dynTipology and dynAdmissibleValues.

Now your dynProperty Column are:

After this, add a clustered index into SQL for each table-tipology:

Now, we are able to READ and WRITE records into dynProperty from Dataverse.

Configure Form and Associated Grid to use and show correclty the dynProperty table:

Configure the configuration table for use correctly the model:

Open advenced search (from admin center, new advanced search is very very very slow).

select dynEntityReferenceLookup and create a record for Account Table:

select dynTipology and create new record:

Now, add the grid (only related) for properties under Account form:

Now, opening the Account Form, you can Add a new record:

set the fields as:

Save and Close, now yo show the info in property grid, and you can modify and delete. this DML operations modify SQL Azure data.

In next article, we write about automation with trigger on SQL Azure, use Synapse and integrate it into Azure SQL, use Azure SQL as entry point for large data load.