Snapshots and Snapshot Groups
Snapshots are predefined dates calculated during the ETL process and used in Snapshot Transformations to combine historicized data.
By default, there is always at least one snapshot, referred to as the "Actual Date", which represents the current timestamp. Additional snapshots can be defined as needed.
Below is a typical snapshot definition:
SQL Expression for Calculating the Previous Date
This SQL expression calculates the previous date relative to a given @ActDate
. It uses the DATEADD
, CONVERT
, and DATEPART
functions to adjust the date by subtracting days and converting between data types.
DATEADD(ms, -2, CONVERT(datetime, CONVERT(date, DATEADD(dd, 1-DATEPART(d, @ActDate), @ActDate))))
Each snapshot must have a unique name. An SQL statement is used to calculate the snapshot value, and the predefined variable @ActDate
(representing the current timestamp) can be used in this statement.
Multiple snapshots can be organized into snapshot groups for better management and usability, as shown below:
Working with Multiple Snapshots
When working with multiple snapshots, a Snapshot Dimension can be defined and used as a common dimension in the data mart layer.
To create a Snapshot Dimension, use the context menu:
Right-click over the Core Layer → Add → Snapshot Dimension
Snapshots are used in regular snapshot transformations to combine historicized data based on predefined dates. These transformations rely on snapshot values to accurately represent the historical context of the data.
Using Snapshot Groups and Individual Snapshots
Both snapshot groups and individual snapshots can be selected and applied during the transformation process.