Here we will learn the fundamentals & create an elastic table in Dataverse. Before we start, make sure subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.

Elastic tables in Dataverse leverage the capabilities of Azure Cosmos DB, allowing them to dynamically expand horizontally to manage extensive data volumes and high throughput, all while maintaining low latency. These tables are well-suited for applications characterized by unpredictable, sporadic, or rapidly expanding workloads.

Why should you use Elastic Tables?

Whether to opt for elastic tables or standard tables depends on the specific requirements of your data and application.

Scenarios for using Elastic Tables –

1. You must handle a high volume of read and write requests.
2. Your data might be unstructured or semi-structured, or your data model might constantly be changing.
3. You need horizontal scaling to handle workload growth over time or bursty workload at a given point.

Scenarios for using Standard Tables –

1. Your application requires complex joins.
2. Your application requires strong data consistency.
3. Your application requires relational modeling and needs transactional capability across tables or during plug-in execution.

How do Elastic Tables increase an application’s performance?

Azure Cosmos DB partitioning is used in elastic tables to effectively scale each table and fulfill the performance needs of your application. Every elastic table includes a pre-defined string column known as ‘Partition Id’. This column is characterized by the schema name “PartitionId” and the logical name “partitionid.”

Azure Cosmos DB guarantees the segregation of rows within a table into separate subsets, determined by the values in the “partitionid” column for each respective row. These segregated subsets are referred to as logical partitions.

The choice of the partitionid value is contingent upon the characteristics of your data. Within an elastic table, a logical partition is consists of rows sharing the same partitionid value. Consider a table containing information about various products; you might opt to use the product category as the partitionid value. Consequently, distinct logical partitions are formed, each encompassing groups of items with specific product category values like Clothing, Books, Electronic Appliances, and Pet supplies.

Dataverse seamlessly and autonomously handles the logical partitions linked to a table. There is no restriction on the quantity of logical partitions permissible within a table. Furthermore, there is no concern about the inadvertent deletion of a logical partition when its underlying rows are removed.

The partitionid column for every elastic table must adhere to the following criteria:

1. 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.
2. It spreads data as evenly as possible among all logical partitions.
3. No values are larger than 1,024 bytes.
4. The values in it don’t change. After a row is created that has a partitionid value, you can’t change it.
5. t 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.

Elastic tables ensure strong consistency within a logical session, defined as the connection between a client and Dataverse. Upon executing a write operation on an elastic table, the client obtains a session token that uniquely identifies the logical session. To maintain strong consistency, it is essential to retain the logical session context by incorporating the session token in all subsequent requests.

How to create Elastic Tables in Dataverse?

You can create Elastic Tables from code using SDK for .NET or Web APIs. The following example demonstrate the utilization of the Dataverse SDK for .NET to generate a new elastic table with the schema name “crmcrate_ElasticTable.” To programmatically create an elastic table, employ the EntityMetadata.TableType property, setting its value to Elastic. In the absence of specifying TableType, the default value “Standard” is employed, resulting in the creation of a standard table.

public static CreateEntityResponse CreateElasticTable(IOrganizationService service)
{
    var request = new CreateEntityRequest
    {
        // Define table properties
        Entity = new EntityMetadata
        {
            SchemaName = "crmcrate_ElasticTable",
            DisplayName = new Label("CRM Crate Elastic Table", 1033),
            DisplayCollectionName = new Label("CRMCrateElasticTable", 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 = "crmcrate_Name",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 100,
            FormatName = StringFormatName.Text,
            DisplayName = new Label("Name", 1033),
            Description = new Label("Name field of CRM Crate", 1033)
        }

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

You can create an Elastic Table using the .NET Console Application with reference to the above code.

How to add columns in an existing Elastic Tables?

Additionally, you have the option to create columns using the SDK or Web API. But, there are restrictions on the types of columns that can be added. Currently, the following column types cannot be added:

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

You can create a new column in an Elastic Table with JSON format using SDK for .NET. This example creates a crmcrate_EmailAddress string column with JSON format in the contoso_ElasticTable 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 = "crmcrate_ElasticTable",
        Attribute = new StringAttributeMetadata
        {
            SchemaName = "crmcrate_EmailAddress",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 1000,
            FormatName = StringFormatName.Json,
            DisplayName = new Label("Email Address", 1033),
            Description = new Label("Email address for CRM Crate", 1033)
        },
        SolutionUniqueName  = "examplesolution"
    };

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

    return response.AttributeId;
}

You can create a new column in an Elastic Table using the .NET Console Application with reference to the above code.

Elastic Tables also have some disadvantages such as they do not support alternate keys and N:N relationship, although the Elastic Tables can be used to significantly increase application performance and consistency.

• LinkedIn
• Facebook
• Telegram