The Data Extract Service utilises Stored Procedures to return data from specific datasets. There are key features of the stored procedures which must be understood before the service can be used.

Extract Method

Users of the service extract data by specifying a date range. This can be a maximum of 1 month. When executing the stored procedures it is the responsibility of the user to ensure that there are no gaps between the dates specified from one execution to the next.

Dataset Selection

A stored procedure is provided for each dataset, totalling 18. To ensure that all of the information is retrieved, it is necessary to execute them all. The datasets available are as follows:

Please contact the Service Desk if you would like to obtain a copy of the data schema for these datasets.

Data Relationships

The data exported will relate to all Divisions associated with your Company in Tide. It is therefore necessary to account for this in downstream reporting to avoid inaccurate figures/values.

Please contact the Service Desk if you would like to obtain a copy of the entity relationship diagram and/or details of the field links for the datasets above. 

Reporting Basis

Data is presented in one of the following methods (based upon the dataset):

• Transactional
  Each record exported represents the change from the last reported position of the entity. Therefore, all records relating to the same entity can be aggregated to produce the in-force position.

• In-Force
  Due to the nature of both entities, each record exported represents the state of the entity at the time the record was created. The in-force position is therefore the record with the youngest created date/time.

Contra Entries

The output data may contain Contra entries, which represent a reversal of approved data. It is only the Transactional datasets which include these entries. As of DE v2.2 onwards, bordereau records will include ‘Deleted’ status records if a bordereau is deleted in Tide.

Data Warehouse Timestamp

The [DWH_InsertedDateTime] field has the timestamp for when a record is created in Data Warehouse.

When a market participant (Coverholder, Broker, Insurer or TPA) is added to an existing contract, the [DWH_InsertedDateTime] for any applicable existing Contract, Bordereau, Risk, Premium and Claim records in the Data Warehouse will have the date/time when the Contract was assigned a 'Signed' [Status] with them as a participant. Contract, Bordereau, Risk, Premium and Claim records created after this time will have the usual [DWH_InsertedDateTime] value.

A record is applicable to a market participant based upon the inception/expiry date of the contract, and the effective date of any endorsements. 

Stored Procedure Parameters

The stored procedures require the following parameters in order to execute successfully: