SQL Server 2016 – Temporal Tables


SQL Server 2016 – Temporal Tables

The temporal table feature in SQL Server 2016 is a breakthrough in data warehousing, because we no longer need to do anything to store the historical values. Data warehouse by approximately 15% half of the ETL development effort which is about 30%; the other 70% is analysis, design, database development, BI/report development, management.

Temporal Table Usage Scenarios: Temporal Tables are generally useful in scenarios that require tracking history of data changes. Visit here

– Data audit/forensic
– Point-in-time analysis (time travel)
– Data anomaly detection
– Slowly changing dimension (type 4!)
– Repairing data corruption at row level

Up to now we always have to build a data warehouse, which stores, the historical changes of attribute values from the source system, and fact snapshots over time. With temporal tables, we no longer need to create a data warehouse. We can do and above within the source system itself, as demonstrated by the above article. This effectively kills the idea of creating a data warehouse, increasing the time we saved from 15% to 95%.

This remaining 5% of effort reflects the extra time required to setup the temporal tables in the source system’s SQL Server, along with memory-optimized tables to store the current data in memory, and the full history of changes on the disk. That arrangement gives us the optimum balance between the performance and the cost.

Slowly-Changing Dimensions
Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. However, some scenarios require you to track data changes in dimension tables as well. Given that modification in dimensions happen much less frequently, in unpredictable manner and outside of the regular update schedule that applies to fact tables, these types of dimension tables are called slowly changing dimensions (SCD).

There are several categories of slowly changing dimensions based on how history of changes is preserved:

Type 0: History is not preserved. Dimension attributes reflect original values.
Type 1: Dimension attributes reflect latest values (previous values are overwritten)
Type 2: Every version of dimension member represented with separate row in the table usually with columns that represent period of validity
Type 3: Keeping limited history for selected attribute(s) using additional columns in the same row
Type 4: Keeping history in the separate table while original dimension table keeps latest (current) dimension member versions

The above article demonstrates that using temporal tables we can do SCD, and we can do snapshot fact tables (called Time Travel). The SCD that temporal tables do is not type 2, but type 4. Type 4 is an old technique where the data changes are kept in the history table. This keeps the original table small and compact as it only contains the latest version. Temporal Tables are generally useful in scenarios that require tracking history of data changes. Visit here for more info

Let me know how it goes for you!

Mehboob
— Microsoft Certified Solutions Associate (MCSA)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s