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

Start Dates:

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.

End Dates:

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:

[h_Customer_key]   [CustomerID]   [PersonID] [TerritoryID] [dv_row_start_date]  [dv_row_end_date]

799991                      001                    001              099               1 Mar 2015                  31 Dec 9999

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

217773                      615                    023              099               15 Jan 2015                 7 Apr 2015

217773                      615                    023              555               7 Apr 2015                   31 Dec 9999

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.

644444                      926                    683              021               7 Jan 2015                   10 Jan 2015

644444                      926                    683              555               10 Jan 2015                 20 Jan 2015

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.

Point in Time Queries:

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:

[h_Customer_key]   [CustomerID]   [PersonID] [TerritoryID] [dv_row_start_date]  [dv_row_end_date]

799991                      001                    001              099               1 Mar 2015                   31 Dec 9999

217773                      615                    023              099               15 Jan 2015                   7 Apr 2015

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:

799991                      001                    001             099               1 Mar 2015                   31 Dec 9999

217773                     615                    023              555               7 Apr 2015                    31 Dec 9999