Dynamics 365 for Time Travel: The Data Export Service and SQL Temporal Tables

Kudos to Microsoft for adding the Data Export Service replication service to Dynamics 365 Online and using the common SQL database for its source. The service has the potential to be a game changer for BI initiatives, providing the ability to view and analyze a near real-time replicated view of critical business data from the cloud. But what about looking at historical data?

Enter Temporal Tables

With SQL Server 2016 and above, SQL Server adds the concept of a system-versioned temporal table, which tracks the full history of changes, and allows point-in-time queries. In addition to SQL Server, SQL Azure now supports this concept and can be extended to existing tables – like the entity tables created through the Data Export Service, including ClickDimensions data!

Set up the Data Export Service

The basic concepts include (1) setting up an Azure Key Vault to securely store the connection string to your database, (2) installing the Data Export Service solution from AppSource and (3) running through the setup wizard in Dynamics. This is well-documented and the next steps assume a Data Export Service profile has been configured.

One important note on SQL Change Tracking: in the configuration of each entity intended to be synced to the Data Export Service, the Change Tracking option must be enabled. Note: this is labeled “Enable change tracking for Flow” in the PowerApps experience.

Convert Data Export Service-Generated Tables to Temporal Tables

Using a script like the template below on each Data Export Service-generated table, substitute the entity’s table name in the two “ALTER TABLE” statements, as well as a name for the history table we’re adding:

–Add two PERIOD datetime2 columns and PERIOD definition

ALTER
TABLE dbo.tablename

ADD StartTime DATETIME2
GENERATED
ALWAYS
AS
ROW
START

HIDDEN
NOT
NULL
DEFAULT
GETUTCDATE(),

EndTime DATETIME2
GENERATED
ALWAYS
AS
ROW
END

HIDDEN
NOT
NULL
DEFAULT

CONVERT(DATETIME2,
‘9999-12-31 23:59:59.9999999’),

PERIOD
FOR
SYSTEM_TIME (StartTime, EndTime)

–Enable System Versioning

ALTER
TABLE dbo.tablename

SET (SYSTEM_VERSIONING
=
ON (HISTORY_TABLE=dbo.tablenameHistory))

Start Using Data

To start using temporal queries, the statement “for system_time as of…” goes through the history table and finds what records are valid for that specific time. A very simple example of this answers the question, “How many email opens did an email send have on a certain date?”

Current Count of Email Opens:
select cdi_openscount
from cdi_emailsend
where cdi_name=Email Send Name’
––For simplicity, example assumes a unique name

Count of Email Opens as of 4pm Eastern Daylight Time (20^th hour in UTC) on July 1, 2019:
select cdi_openscount
from cdi_emailsend
for
system_time
as
of
‘2019-07-01 20:00:00.000000′
where cdi_name=’Email Send Name’ ––For simplicity, example assumes a unique name

Final Thoughts

This approach is a flexible way to start providing answers to questions about the history of your business data. Because it leverages existing SQL Server functionality, it quickly creates a data source that can be used by any BI or analysis tool, without impacting performance of your production Dynamics and ClickDimensions instance.

Note: The content in this post involves customizing native Dynamics entities and related services. If you have questions about customizing your Dynamics environment from the out-of-the-box settings or need assistance doing so, please contact your Dynamics administrator or partner for assistance. ClickDimensions cannot provide support on this functionality.