Dataverse Elastic tables

Hello Power Enthusiats and Technology Evangelists, here is my another blog. This blog focuses on the details that developers need to know while using elastic tables.

What is an Elastic Table?

Unlike normal Dataverse tables, which are driven by Azure SQL Database, Elastic tables are powered by Azure Cosmos Database, which is built to manage massive amounts of data and high levels of throughput with low latency.

Elastic tables are ideal for applications with unpredictable, spiky, or rapidly increasing workloads. Like standard tables, elastic tables have the same well-known user interface and API.

They have many of the same features and settings as standard tables, but they also have special features and capabilities that are made possible by Azure Cosmos DB.

Here are some circumstances where elastic tables can be used.

• Your data may be unstructured or semi-structured, and your data model may be continually changing.
• You require automated horizontal scaling.
• You must manage a high volume of read and write requests.

How to create Elastic tables without Code?

To build an elastic table, simply follow the standard steps given below:

• In Power platform select Tables > + New table dropdown > Table(advanced properties.

• Enter Display Name as Country and select Advance options dropdown.
• Select Elastic from the Type dropdown.

Currently, the elastic tables do not support several common Dataverse table features:

• Please select Save for creating the elastic table.

• When the elastic table is created, we can see that two additional new
  column Partition Id and Time to Live are automatically added.

Individuals who are unfamiliar with Azure Cosmos DB may struggle to figure out how it works. Let us go into more depth, and I will do my best to provide a comprehensive explanation.

Partitioning and horizontal scaling

Azure Cosmos DB partitions individual containers in a database to satisfy the performance requirements of your application. The items in a container are separated into discrete subsets known as logical partitions. Logical partitions are created based on the value of a partition key associated with each item in a container. All of the elements in a logical partition share the same partition key value.

For example, an Azure Cosmos DB container holds items and each item has a unique value for the CountryName property. If CountryName serves as the partition key for the items in the container and there are 10 unique CountryName values, 10 logical partitions are created for the container.

Additionally each item in a container has an item ID, which is unique within a logical partition. Combining the partition key and the item ID yields the item's index, which uniquely identifies the item. Choosing a partition key is a critical decision that affects your application's performance.

This partitioning strategy enables Azure Cosmos DB to distribute data across numerous physical servers, guaranteeing that the database can handle a huge number of objects while providing rapid access. Azure Cosmos DB divides the data into logical divisions based on the partition key and may scale each partition separately to meet your application's performance requirements. I wont go details on this topic, because our focus is on Dataverse Elastic tables. To learn more about Azure Cosmos DB's backend storage logic, click here:https://learn.microsoft.com/en-us/azure/cosmos-db/partitioning-overview

Now that we've covered the basics, let's get back to Dataverse.

When we construct an Elastic table, Dataverse maps it to an Azure Cosmos database container. The Partition Id value we defined for each row will serve as the Partition Key values, which will be utilized to group the data saved in the elastic table.

To achieve the best performance possible with elastic tables, you must select and continuously implement a partitioning plan. If you do not set a partitionid value for each row, the value will remain null and cannot be changed later.
If you utilize a custom partitionid value, the primary key value lacks a unique constraint. In other words, you can construct many records with the same primary key but distinct partitionid values. It is critical to realize that unique references for elastic tables are a combination of the primary key and partitionid value.

For all elastic tables, the partitionid column must match the following criteria:

• The values in it don't change. After a row is created that has a partitionid value, you can't change it.
• It has a high cardinality value. In other words, the property should have a wide range of possible values. Each logical partition can store 20 gigabytes (GB) of data. Therefore, by choosing a partitionid value that has a wide range of possible values, you ensure that the table can scale without reaching limits for any specific logical partition.
• It spreads data as evenly as possible among all logical partitions.
• No values are larger than 1,024 bytes.
• No values contain slashes (/), angle brackets (<, >), asterisks (*), percent signs (%), ampersands (&), colons (:), backslashes (), question marks (?), or plus signs (+). These characters aren't supported for alternate keys.

How to create elastic tables using code?

You can create and edit elastic tables without writing code. But in sometimes you can create and edit elastic tables with code is helpful. Using the Dataverse SDK for.NET and Web API, the following examples demonstrate how to build a new elastic table with the schema name customer_SensorData. If you want to build an elastic table in code, set the value of the EntityMetadata.TableType attribute to Elastic. If you don't specify TableType, a standard table is produced using the default value Standard.

public static CreateEntityResponse CreateElasticTable(IOrganizationService service)
{
    var request = new CreateEntityRequest
    {
        // Define table properties
        Entity = new EntityMetadata
        {
            SchemaName = "customer_SensorData",
            DisplayName = new Label("CustsensorData", 1033),
            DisplayCollectionName = new Label("CustsensorData", 1033),
            Description = new Label("Stores IoT data emitted from devices", 1033),
            OwnershipType = OwnershipTypes.UserOwned,
            TableType = "Elastic",
            IsActivity = false,
            CanCreateCharts = new Microsoft.Xrm.Sdk.BooleanManagedProperty(false)
        },

        // Define the primary attribute for the entity
        PrimaryAttribute = new StringAttributeMetadata
        {
            SchemaName = "customer_SensorType",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 100,
            FormatName = StringFormatName.Text,
            DisplayName = new Label("CustSensor Type", 1033),
            Description = new Label("Type of sensor emitting data", 1033)
        }

    };
    return (CreateEntityResponse)service.Execute(request);
}

CreateEntityResponse has these properties:

AttributeId: The ID of the customer_SensorType primary name string column.
EntityId: The ID of the customer_SensorData table.

Adding Columns

You can add columns to elastic tables without knowing any code by utilizing Power Apps.

Additionally, you can use the Web API or SDK to build columns. There are restrictions on the kinds of columns you can include, though. At this time, the following kinds of columns cannot be added:

• Money (MoneyAttributeMetadata)
• MultiSelectPicklist (MultiSelectPicklistAttributeMetadata)
• State (StateAttributeMetadata)
• Status (StatusAttributeMetadata)
• Image (ImageAttributeMetadata)
• Calculated, Rollup, or Formula Columns

Elastic tables support string columns that store JavaScript Object Notation (JSON) data.

Create a column with JSON format

This example creates a customer_Name string column with JSON format in the customer_SensorData elastic table. For cases where a large amount of JSON data must be stored, you can use the MemoAttributeMetadata column type with JSON format instead of using the StringAttributeMetadata column type.

public static Guid CreateJsonAttribute(IOrganizationService service)
{
    var request = new CreateAttributeRequest
    {
        EntityName = "contoso_sensordata",
        Attribute = new StringAttributeMetadata
        {
            SchemaName = "contoso_EnergyConsumption",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 1000,
            FormatName = StringFormatName.Json,
            DisplayName = new Label("Energy Consumption", 1033),
            Description = new Label("Contains information about energy consumed by the IoT devices", 1033)
        },
        SolutionUniqueName  = "examplesolution"
    };


    var response = (CreateAttributeResponse)service.Execute(request);

    return response.AttributeId;
}

This function creates a StringAttributeMetadata column by using the CreateAttributeRequest class.

Alternate Keys

Custom substitute keys cannot be made for elastic tables.

One alternate key is used to generate each elastic table, and it uses the following values:

• Display Name: Entity key for NoSql Entity that contains PrimaryKey and PartitionId attributes
• Name: KeyForNoSqlEntityWithPKPartitionId
• LogicalName: keyfornosqlentitywithpkpartitionid

Including Relationships

Creating many-to-many relationships with elastic tables is not supported by Dataverse at this time.

Elastic tables can handle one-to-many relationships, however they have the following restrictions:

There is no support for cascading. Cascade must be selected for cascading behavior.

If you are using a partitioning strategy for your elastic table, and you want to retrieve the related elastic table record, you can't rely on the value of the lookup column alone. You must also include the partitionid value from the pid column to uniquely identify the related table.

Expire data with Time to live

The Time to Live column is an integer/whole number column that specifies the time in seconds after which the row will be removed from the database. If no value is specified, the row is not erased automatically. You can adjust the Time to Live value before the row expires. When you change the value, the timer will restart.

Assume you are developing a solution that involves displaying all available coupons for a certain consumer. In this instance, Customer Id is the ideal option for Partition Id since coupons will be distributed equitably to each customer, and the most common coupon query is to return all coupons for a single customer. We can add Time to the live column value during coupon generation to provide expiry logic that the backend will handle automatically.

Using Elastic table

Unless you want to utilize the default primary key because the Partition Id or data in the table will never expire, you can either explicitly or automatically populate the Time to live and Partition Id when using Elastic Table in a Canvas application. To stop users from changing Partition Id, it should only be read while the form is in Edit mode.

Using Patch function to Create/Update a record in an elastic table will be similar to the Standard table

The only catch is that you have to pass the Partition Id when updating an existing item. Otherwise, you will end up creating a new record with empty Partition Id

The same behaviour happens in the Power Automate cloud flow. You have to provide Partition Id value to update a row properly.

Summary

In this blog, we explored the elastic table feature in Dataverse and delve into its underlying storage technology.

Elastic tables expand the table types available in dataverse, but they should be used with caution in my opinion because these pretty tables aren't the way to go for standard development in a relation database context, because table lookups aren't available out of the box, and many other table features aren't available, limiting the scope of action of these tables significantly.

Finally, they are not the best option for enhancing CRUD performance, unless you use many CRUD operations.

So, in conclusion, elastic tables are most suited for storing big amounts of data, particularly IoT data, which would be handled by a service layer capable of performing many CRUD operations and then consuming the data via a business intelligence environment.

Hope you enjoy the session.

Please leave a comment below if you have any further questions.

Happy Sharing !!!
Keep Learning | Spread Knowledge | Stay blessed |