10.0 updatedAfter feature (timestamp updated/inserted or deleted)
As Frederik,
I want to use the updatedAfter SDMX rest parameter in the .Stat Suite NSI web service in order to retrieve the latest version of what has changed in the database since that point in time (updates and revisions)
So that I can use for an efficient database synchronisation by only getting the "delta" compared to the previous request, e.g. when the retrieved message is used as direct input in another target database or to push it to data collectors for data synchronisation purposes (observation and attribute values).
The NSI ws should provide the following functionality:
In a data request, the user can specify a datetime which should be used to create a response with only the changes (updated/inserted and deleted) that have happened after that point. The result should produce separated datasets in the SDMX message, specifying the change type in the message 'action' header value.
-
Implement the usage of the “updatedAfter” SDMX URL parameter feature in the context of the .Stat Suite use of the default nsi-plugin (see SDMX specifications here: https://github.com/sdmx-twg/sdmx-rest/blob/v1.5.0/v2_1/ws/rest/docs/4_4_data_queries.md#parameters-used-to-further-filter-the-desired-results. Also: https://github.com/sdmx-twg/sdmx-rest/blob/master/doc/tips.md#get-only-what-you-need) -
Make sure that the information about deleted observations and ref. metadata together with the change date is persisted in the DB in order to include that info in the response.
Parameter | Type | Description |
---|---|---|
updatedAfter | xs:dateTime | The last time the query was performed by the client in the database. If this attribute is used, the returned message should only include the latest version of what has changed in the database since that point in time (updates and revisions). This should include observations that have been added since the last time the query was performed (INSERT), observations that have been revised since the last time the query was performed (UPDATE) and observations that have been deleted since the last time the query was performed (DELETE). If no offset is specified, default to local time of the web service. If the information about when the data has been updated is not available at the observation level, the web service should return either the series that have changed (if the information is attached at the series level) or the dataflows that have changed (if the information is attached at the dataflow level). |
Note that the updated after represents the last time when any observation level component (measure or attribute) where last modified (deleted, inserted or updated)
The generated response message must always allow achieving to replicate the exact same punctual data content as currently stored in the queried data source.
For the .Stat Suite context the following approach is proposed because only the Append/Merge and Delete actions are supported yet. When the updatedAfter rest URL parameter is used then:
- All (since the given date) fully deleted observations (where observation values and related observation-level attributes were deleted) will be exposed in the result data message (xml: in a dataset) with action=”Delete”.
- All (since the given date) added and revised observations together with their higher-level attribute values will be exposed in the result message (xml: in a dataset) with
action=”Append” (still used for compatibility with external systems)action="Replace".
Technical Notes
MS SQL Server now provides an in-built time machine through "temporal tables":
See: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15
This out-of-the-box feature allows knowing what has been changed when.
Partitioning large datasets by the lastmodified column, could improve query performance. Note also: https://docs.microsoft.com/en-us/sql/relational-databases/tables/partitioning-with-temporal-tables?view=sql-server-ver15
SDMX Standard
<xs:simpleType name="ActionType">
<xs:annotation>
<xs:documentation>ActionType provides a list of actions, describing the intention of the data transmission from the sender's side. Each action provided at the data or metadata set level applies to the entire data set for which it is given. Note that the actions indicated in the Message Header are optional, and used to summarize specific actions indicated with this data type for all registry interactions. The "Informational" value is used when the message contains information in response to a query, rather than being used to invoke a maintenance activity.</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:NMTOKEN">
<xs:enumeration value="Append">
<xs:annotation>
<xs:documentation>Append - this is an incremental update for an existing data/metadata set or the provision of new data or documentation (attribute values) formerly absent. If any of the supplied data or metadata is already present, it will not replace that data or metadata. This corresponds to the "Update" value found in version 1.0 of the SDMX Technical Standards.</xs:documentation>
</xs:annotation>
</xs:enumeration>
<xs:enumeration value="Replace">
<xs:annotation>
<xs:documentation>Replace - data/metadata is to be replaced, and may also include additional data/metadata to be appended. The replacement occurs at the level of the observation - that is, it is not possible to replace an entire series.</xs:documentation>
</xs:annotation>
</xs:enumeration>
<xs:enumeration value="Delete">
<xs:annotation>
<xs:documentation>Delete - data/metadata is to be deleted. Deletion occurs at the lowest level object. For instance, if a delete data message contains a series with no observations, then the entire series will be deleted. If the series contains observations, then only those observations specified will be deleted. The same basic concept applies for attributes. If a series or observation in a delete message contains attributes, then only those attributes will be deleted.</xs:documentation>
</xs:annotation>
</xs:enumeration>
<xs:enumeration value="Information">
<xs:annotation>
<xs:documentation>Informational - data/metadata is being exchanged for informational purposes only, and not meant to update a system.</xs:documentation>
</xs:annotation>
</xs:enumeration>
</xs:restriction>
</xs:simpleType>