1. What Are Elastic Tables?
Elastic tables are a special type of Dataverse table optimized for massive data ingestion and high-volume, time-series, or log-style workloads.
They use Azure Cosmos DB under the hood, allowing horizontal scalability beyond the limits of traditional Dataverse storage.
🔹 Think of them as Dataverse tables designed for IoT, telemetry, or event data — built on Cosmos DB, with native Dataverse integration.
2. Key Characteristics
| Feature | Description |
| Purpose | Store high-volume, append-only data (e.g., logs, telemetry, events) |
| Storage Backend | Azure Cosmos DB (NoSQL, partitioned) |
| Scalability | Scales horizontally — billions of rows possible |
| Data Access | Via Dataverse API (Web API, SDK), TDS endpoint, or Power BI |
| Cost | Billed separately as elastic data storage (outside standard Dataverse capacity) |
| Latency | Near real-time read/write (~milliseconds) |
| Retention | Configurable retention policies to auto-delete old records |
| Limitations | Append-only, no relationships, no joins, limited indexing |
3. Architecture Overview
+—————————+
| Dataverse (SQL Storage) |
| – Standard Tables |
| – Relational Data |
+—————————+
│
▼
+—————————+
| Elastic Tables |
| – Cosmos DB Backend |
| – Append-only records |
| – Partitioned storage |
+—————————+
│
▼
+—————————+
| Power BI / Azure Synapse |
| – Analytics Integration |
+—————————+
Internally:
- Each elastic table record is stored in a Cosmos DB document.
- Data is automatically partitioned for performance and scalability.
- Retention policies help manage lifecycle (e.g., delete after 90 days).
4. Creating Elastic Tables
Elastic tables can be created from the Power Apps Maker Portal or using the Dataverse API.
Steps in Power Apps Maker Portal:
- Go to Tables → New Table.
- Under Advanced options → Table type, select Elastic.
- Define:
- Primary Name
- Retention Period (e.g., 30, 90, 180 days)
- Partition Key (auto-managed)
- Save and publish.
Naming Convention Example:
telemetry_eventlog
audit_useractivity
sensor_readings
Using Web API
POST https://yourorg.api.crm.dynamics.com/api/data/v9.2/tables
Content-Type: application/json
{
“LogicalName”: “new_eventlog”,
“TableType”: “Elastic”,
“PrimaryNameAttribute”: “new_name”,
“RetentionPeriodInDays”: 90
}
5. Data Ingestion
Elastic tables are designed for high-throughput inserts (thousands per second).
Ways to insert data:
- Dataverse Web API (standard)
- Power Automate (Create record action)
- Custom Azure Function using Dataverse SDK
- Dataverse Plug-in or Logic App
Bulk insert is recommended for performance-critical ingestion (e.g., via HTTP batch requests).
Example Record Insert (Web API)
POST https://org.crm.dynamics.com/api/data/v9.2/new_eventlogs
Content-Type: application/json
{
“new_name”: “LoginAttempt”,
“new_timestamp”: “2025-10-08T08:30:00Z”,
“new_userid”: “pradip@onmicrosoft.com”,
“new_result”: “Success”,
“new_location”: “India”
}
6. Querying Elastic Tables
Elastic tables support standard Dataverse query methods, but with some restrictions.
🔹 Supported
- RetrieveSingle
- RetrieveMultiple (filter, select, orderby)
- Power BI TDS Endpoint
- Dataverse Search
🔸 Not Supported
- Relationships (no lookup fields)
- Joins across tables
- Advanced FetchXML joins
- Server-side business logic (Workflows, Plugins)
Example Query (OData)
GET https://org.crm.dynamics.com/api/data/v9.2/new_eventlogs?$filter=new_result eq ‘Success’
Example Power BI Connection
Use the Dataverse connector → select the elastic table → DirectQuery mode for near real-time visualization.
7. Retention Policies
To avoid unbounded growth, each elastic table can define an automatic data retention policy.
| Retention Option | Behavior |
| 30 days | Data auto-deleted after 30 days |
| 90 days | Default option for telemetry |
| Custom | Define 7–730 days |
| No Retention | Must manually purge old data |
⚙️ Old records are soft-deleted by Cosmos DB TTL policy.
8. Use Cases
| Use Case | Description |
| IoT Data Storage | Collect sensor readings or machine telemetry in near real-time. |
| System / Audit Logs | Store high-volume operational or diagnostic logs. |
| Application Telemetry | Capture custom app metrics or API transactions. |
| Event Processing | Record all CRM webhook or integration events for downstream analytics. |
| Marketing Analytics | Store event-level campaign or clickstream data. |
9. Integration Patterns
| Integration Type | Description |
| Power BI | Real-time dashboards for telemetry or KPI monitoring |
| Azure Synapse Link | Stream elastic data into Synapse for advanced analytics |
| Power Automate | Trigger downstream actions on event ingestion |
| Azure Functions | Bulk insert or purge old records programmatically |
| Application Insights | Write logs from CRM plugins into elastic tables for centralized monitoring |
10. Security & Access Control
- Elastic tables use Dataverse security model (table- and field-level access control).
- Users must have at least Read/Create privileges for the elastic table.
- Security roles can control visibility and operations like standard tables.
11. Limitations
| Area | Limitation |
| CRUD Operations | Append-only; updates/deletes limited |
| Relationships | No lookup, no cascade |
| Business Rules | Not supported |
| Plugins / Workflows | Not triggered on create/update |
| Search | Basic only (no relevance search) |
| Indexing | Only partition and timestamp fields indexed |
Elastic tables are not for transactional CRM data — use them only for large, non-relational, or time-series datasets.
12. Comparison: Standard vs Elastic Tables
| Feature | Standard Table | Elastic Table |
| Backend | SQL-based Dataverse storage | Azure Cosmos DB |
| Data Type | Relational | Non-relational (document) |
| Volume | Up to millions | Up to billions |
| CRUD | Full | Append-only |
| Relationships | Supported | Not supported |
| Business Logic | Plugins, Workflows | None |
| Analytics | Power BI, FetchXML | Power BI, TDS |
| Cost | Counts toward DB storage | Separate elastic data capacity |
| Best For | Master/Transactional data | Event/Log/Telemetry data |
13. Real-World Example
Scenario: IoT sensor data collection for 500 devices sending updates every minute.
- Create elastic table iot_sensordata
- Define fields: DeviceId, Temperature, Humidity, Timestamp
- Retention = 90 days
- Data ingested via Azure Function → Dataverse Web API
- Power BI dashboard shows live trend analysis
This allows millions of records daily without impacting CRM transactional performance.
14. Monitoring & Maintenance
- Use Power Platform Admin Center → Capacity → Elastic Data to monitor usage.
- Audit ingestion latency and Cosmos RU (request units).
- Periodically export to Data Lake for archival (if needed).
15. Best Practices Summary
✅ Use Elastic Tables for high-volume, low-relationship data.
✅ Always define retention policies to control cost and growth.
✅ Use Power BI DirectQuery for real-time dashboards.
✅ Don’t use Elastic Tables for core CRM transactions (Accounts, Contacts, etc.).
✅ Combine with Azure Functions or IoT Hub for automated ingestion pipelines.
✅ Monitor capacity consumption and query performance regularly.
Leave a comment