Consuming Mart Data

The mart data can be queried using SQL from any compatible client. Tables based on Time Series or Slowly Changing models are version handled and implemented as Temporal Tables in the mart database.

When querying issuing a standard SELECT statement on the table, the current state of the table is returned. This can be verified by inspecting the _VersionValidTo column, that is set to “end-of-time” in the year 9999 for each record.

To query a previous version of the data, a special query syntax is used to identify the point or period to query.

To query a version of the data at a specific point in time, use the FOR SYSTEM_TIME AS OF -syntax:

SELECT *
FROM [<schema>].[<table>]
FOR SYSTEM_TIME AS OF '2020-01-01 T10:00:00.7230011';

To query all versions of the data during a period of time, use the FOR SYSTEM_TIME BETWEEN-syntax:

SELECT *
FROM [<schema>].[<table>]
FOR SYSTEM_TIME BETWEEN '2020-01-01 T10:00:00.7230011' 
  AND '2020-02-01 T10:00:00.7230011';

Note that this refers to version-handled data, where the same data exists in multiple versions, not different time slices of the time series. Time series provide views on the same aspects (e.g. market value per portfolio) of the data from different perspectives i.e. the figure date. The current version of a time slice can be queried using common SELECT WHERE statements:

SELECT * FROM [prod].[TIMESERIES]
WHERE [prod].[TIMESERIES].[Figure_Date] = '2020-06-12';

Backdating can cause a time slice to exist in different versions. To query a previous version of a time slice, the FOR SYSTEM_TIME-syntax is used:

SELECT * FROM [prod].[TIMESERIES]
FOR SYSTEM_TIME AS OF '2020-06-16'
WHERE [prod].[TIMESERIES].[Figure_Date] = '2020-06-12';