How Does ODE Deal with Start End Dating of Satellites - OptimalBI/optimal-data-engine-mssql GitHub Wiki

ODE Satellites Record a Start and End Date for all rows.

New Rows, which have no Prior Version, use System Time as the Start Date.

New Versions of a Row use the End Date of the Prior Row as the Start Date.

New Rows, which have no Prior Version, are given an End Date as specified as the "Global High Date" in the ODE Defaults Table.

As installed, this is 31 Dec 9999.

This Default can be customised during the Install process, after which it should never be altered.

Rows, which are End Dated due to a new Version being created, are given System Time as an End Date.

The Result Looks Like:

The above Customer was loaded into the Vault on the 1st of March 2015. As yet, the Customer has never been altered.

The above Customer was loaded into the Vault on the 15th January 2015.

On the 7th of April 2015, the Customer moved into a new Sales Territory and has remained ever since.

In this example, the Customer was loaded into the Vault on the 7th January 2015.

The Customer changed Territory on the 10th January 2015 and was Deleted (logically) from the Vault on the 20th of January 2015.

In order to obtain a snapshot of a Satellite, at a desired Point in Time, the following typical SQL may be used:

SELECT [h_Customer_key]
,[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
FROM [Demo_Vault].[sat].[s_Customer]
where '6 Apr 2015' >= [dv_rowstartdate]
and '6 Apr 2015' < [dv_rowenddate]
and [dv_is_tombstone] = 0

Returns:

For selecting the latest Version of Each Row in the Satellite, either:

SELECT [h_Customer_key]
,[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
FROM [Demo_Vault].[sat].[s_Customer]
where [dv_row_is_current] = 1
and [dv_is_tombstone] = 0

or:

SELECT [h_Customer_key]
,[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
FROM [Demo_Vault].[sat].[s_Customer]
where [dv_rowenddate] > '30 Dec 9999'
and [dv_is_tombstone] = 0

returns:

Also check these Point-in-time satellite functions you can create.