Dataverse: introducing the new Elastic tables

I don’ know if you have yet discovered that, but some days ago a new table type option called Elastic appeared on Dataverse when creating a new table object:

If you select this new table object, some limitations on what you can do are automatically applied:

What are Elastic tables?

Elastic tables (actually in preview) are a new type of Dataverse tables physically stored in Azure Cosmos DB (while standard Dataverse tables are physically stored on Azure SQL).

Elastic tables automatically scale horizontally to handle large amounts of data and high levels of throughput with low latency, so they are suitable for applications with unpredictable, spiky or rapidly growing workloads.

If you know something about Azure Cosmos DB, maybe you know that Azure Cosmos DB uses partitioning to scale individual containers in a database to meet the performance needs of your application. The items in a container are divided into distinct subsets called logical partitions. Logical partitions are formed based on the value of a partition key that is associated with each item in a container. All the items in a logical partition have the same partition key value.

All elastic tables in Dataverse contain a system-defined Partition Id string column with the schema name PartitionId and logical name partitionid.

The partitionid value you should use depends on the nature of your data. A logical partition in an elastic table consists of a set of rows that have the same partitionid. When partitionid is not specified for a row, Dataverse uses the primary key value as the default partitionid value. 

Just to give an example: if you plan to load in a Dataverse elastic tables data coming from an external manufacturing device that must then be available to Power Platform apps, you can create a new elastic table in Dataverse called sd_ManufacturingDeviceData and then create a string column called sd_MachineId that can be used as the partitionid value for each row.

When you create a record via code to be loaded into an elastic table, you need to specify the partitionid. A quick example is the following:

var entity = new Entity("sd_ManufacturingDeviceData")
    {
        Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
	       { "sd_qtyProduced", 23 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
    };
    var request = new CreateRequest { 
        Target = entity
    };
    var response = (CreateResponse)service.Execute(request);

The partitionid attribute is also mandatory if you want to read a record (otherwise you will have an error).

var keys = new KeyAttributeCollection() {
        { "sd_machineId", "MACHINE01" },
        { "partitionid", "MACHINE01" }
    };
    var entityReference = new EntityReference("sd_ManufacturingDeviceData", keys);
    var request = new RetrieveRequest { 
        ColumnSet = new ColumnSet("sd_item"),
        Target = entityReference
    };
    var response = (RetrieveResponse)service.Execute(request);

Elastic tables are extremely fast on bulk operations. At the time of writing this post elastic tables supports following messages for bulk operations:

• CreateMultiple
• UpdateMultiple
• DeleteMultiple

Just to give a metric, I’ve done a quick test on creating 500 records with a Create and a CreateMultiple operation.

Create for each record:

for (var i = 0; i  500; i++)
{
  var entity = new Entity("sd_ManufacturingDeviceData")
  {
    Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
		   { "sd_qtyProduced", 23 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
  };
  var request = new CreateRequest { 
        Target = entity
    };
    var response = (CreateResponse)service.Execute(request);
}

CreateMultiple (bulk operation):

var entities = new EntityCollection();
entities.EntityName = "sd_ManufacturingDeviceData";
for (var i = 0; i  500; i++)
{
  var entity = new Entity("sd_ManufacturingDeviceData")
  {
    Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
		   { "sd_qtyProduced", 23 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
  };
  entities.Entities.Add(entity);
}
service.Execute(new CreateMultipleRequest
{
    Targets = entities
});

Result:

Bulk operations are 8/9 time faster!

When to use Elastic tables with Dataverse?

This is a natural question. When should I use this new Dataverse table type?

You should use Elastic tables when:

• you need to handle high volume of data with frequent read and write operations
• you have unstructured or semi-structured data
• you need a big scaling
• you don’t need joins with other tables

My recommendation is to use them when you have records that can live without relations to other tables and when you have a consistent query pattern on the data stored.

Use standard Dataverse tables when:

• Your application requires strong data consistency.
• Your application requires relational modeling and needs transactional capability across tables or during plugin execution.
• Your application requires complex joins.

What about data security?

Elastic tables support the same security roles of a standard Dataverse table and you can create user or team or organization-owned tables.

This post was originally published on this site