Overview

SQL Server 2016 introduced system-versioned temporal tables, that brings us built-in support for retrieving data stored in a table at any point in time, rather than only data at query time. Temporal is a feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016.

Predictive Analytics Usage Scenario

In scenarios such as Predictive Analytics, you need to track data changes in tables, so you can re-build the customer status at a given point in time, in order to properly use historic data to train your predictive models. Traditional methods for doing this includes TRIGGERS and Data Warehousing techniques such as SLOWLY CHANGING DIMENSIONS or user managed TEMPORAL TABLES.

Example

Lets create a very simple example of an analytical table, with a customer summary of attributes and a mark with his Churn status

 

First of all it´s necessary to add the versioning feature to your existing table as follows:

ALTER TABLE [dbo].[CustomerSummary]
        ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN
         CONSTRAINT DF_SysStart DEFAULT DATEADD(second, -1, SYSUTCDATETIME()),
        SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
         CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
 
ALTER TABLE [dbo].[CustomerSummary]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CustomerSummary_History]));

Now, we will simulate changes in the analytical table. In the example the changes occur every minute, however in real scenarios is can be daily or weekly.

INSERT INTO [dbo].[CustomerSummary]
VALUES (2,'Macarena','F','IL',20,10,1,0)
 
WAITFOR DELAY '00:01:00'
 
UPDATE [dbo].[CustomerSummary]
SET  [NumberOfTransactions]= 14
       ,[NumberOfWebLogins] = 6
       ,[NumberOfComplaints] = 3
       ,[Churn] = 0
WHERE  [CustomerID] = 2
 
WAITFOR DELAY '00:01:00'
 
UPDATE [dbo].[CustomerSummary]
SET  [NumberOfTransactions]= 11
       ,[NumberOfWebLogins] = 4
       ,[NumberOfComplaints] = 6
       ,[Churn] = 1
WHERE  [CustomerID] = 2

Now we can query our table for their current and past status. In the example we will run 3 queries:
  1. The current customer status
  2. The customer status at a given point in time
  3. Customer at all the times
DECLARE @now datetime2 = sysutcdatetime()
DECLARE @PastTime datetime2
SET @PastTime = DATEADD (minute, -15, @now)
 
SELECT  *
FROM   [dbo].[CustomerSummary]
where [CustomerID] = 2
 
SELECT  *
FROM   [dbo].[CustomerSummary]
FOR SYSTEM_TIME AS OF @PastTime
where [CustomerID] = 2
 
SELECT  *
FROM   [dbo].[CustomerSummary]
FOR SYSTEM_TIME ALL
where [CustomerID] = 2

The 3 query results are listed below:

Training Data Set Creation

As mentioned, this feature can be very useful in predictive analytics, since it re-build the customers status at different points in time. Imagine if we put many customers at many points in time all together, and we use it as a training data set for a Churn predictor. Hopefully a predictive algorithm will find interesting patterns on it, that might generalize well to new cases, allowing proactive management.

Summary

SQL Server 2016 introduced temporal tables that allows retrieving data stored in a table at any point in time. This feature allows to easily build the analytical historical tables that are necessary when building a predictive model.