Data Mart

Data Mart provides a relational view of your book of business in Socotra. You can issue SQL queries across a broad set of tables, including policies, transactions, field values, and financials.

Getting Started with Socotra Data Mart

Prerequisites

  • Socotra-provided credentials and connection details

  • IP registration with Socotra to allow connections from your IP

  • A MySQL-compatible client, such as MySQL Workbench

Contact your Socotra representative for onboarding details.

Note

Popular analytical tools such as Tableau or Microsoft Power BI can also be used with their respective MySQL connectors.

Data Mart Schema

Data Mart schema

Note

Renewals and endorsements in the “accepted” state will appear with state invoiced in Data Mart. Accepted reinstatements have state accepted.

Data Mart Sample Queries and Reports

See the Socotra Data Mart Demo GitHub repository for sample queries, along with examples of reports run with the socotra-datamart-reports Python package.

You can run the full set of standard reports from Data Mart: see the aforementioned demo repository for equivalent queries.

Frequently Asked Questions (FAQ)

Can I connect with Tableau, Microsoft Power BI, or other such analytical tools?

Yes, you can connect with any MySQL/ODBC-compatible client. Both Tableau and Microsoft Power BI publish instructions for connections to MySQL:

How can I use datetime values in Data Mart?

Data Mart datetimes are stored as MySQL bigints, housing 13-digit Unix timestamps. You can use the standard MySQL date and time functions to work with these values; for example, to display selected policy issue dates in YYYY-MM-DD format, you can use from_unixtime:

SELECT from_unixtime(p.policy_start_timestamp / 1000, '%Y-%m-%d') FROM policy p;

Note

Depending on your use case, you may be able to rely on the MySQL implicit conversions between integral timestamps and the MySQL DATE, TIME, TIMESTAMP, and DATETIME types. Refer to the following MySQL documentation for details.

What is the relationship between the Data Mart schema and entity representations in the Socotra API?

While we aim for consistency in data representations, you should expect to see variations between these two systems that reflect different purposes. In comparing Data Mart’s policy and the API’s PolicyResponse object, for example, you can see that they expose many of the same attributes – locator, product name, timestamps, etc. – but that the naming conventions differ slightly. Two significant commonalities between the API and Data Mart include (1) consistent presence of locator identifiers for referential relationships; and (2) a relatively small set of data types for the full range of fields. Not all Data Mart tables will have a corresponding analogue in the API data model, and some tables will have fields not seen in a related API representation, often introduced to facilitate simplified SQL queries against the Data Mart schema.

How do group field entries differ from regular field entries?

Group fields, group field child entries, and non-group fields all coexist in a single policy_, exposure_, or peril_characteristics_fields table.

  • If an entry is a group field, the is_group value will be 1 (true) to indicate that it represents a top-level group field. The field_value will be a special locator value. This locator value will match the parent_locator for all child values for that entry.

  • If an entry is a child of a group field, the parent_locator will have a value that matches the field_value of the corresponding group field entry. The child’s is_group value will be 0 (false) since it is not itself a top-level group field value.

  • If an entry is neither a group field nor a child of a group field, parent_locator will be null, and is_group will be 0 (false).

How do field values relate to characteristics?

Consistent with the Socotra data model, a set of field values is associated with a specific characteristics object for the policy, exposure, or peril. The _fields tables provide a corresponding _characteristics_locator to draw a link to the corresponding characteristics, which can then be used to link back to higher-level entities (i.e. policy, exposure, peril, policyholder). Because a set of field values is linked to a specific set of characteristics, those values inherit the temporal properties of their corresponding characteristics. When querying against field values or characteristics, you should consider relevancy in a given time period, leveraging the characteristics’ start_, end_, issued_, and replaced_timestamps, as needed.

Why do some tables have an id field while others do not?

id fields help to uniquely identify rows in Data Mart in the absence of any other unique field, aiding the processes that insert and update Data Mart entries. They are distinct from locators in that they are (1) not intended to help link to other entities, and (2) not necessarily surfaced by or consistent with identifiers provided by the Socotra API. A locator value from Data Mart will be identical to the value obtained via API or exposed in the UI. Not all entities in Data Mart will have a locator.

How does claim_version relate to the claims table?

A new claim version is created every time a claim is updated. This allows you to track a clear history of a claim as it moves through different states. The current status for a claim is reflected in the claim version with the latest created_timestamp.

What version of MySQL is Data Mart on?

Version 8.0.x; you can check the specific version at any time with the SELECT VERSION(); query.

What time zone are the Data Mart ‘created’ and ‘updated’ timestamps in?

datamart_created_timestamp and datamart_updated_timestamp are recorded in Coordinated Universal Time (UTC).

How often is Data Mart data updated?

Data Mart is continuously loaded with new data from the corresponding tenant, usually within minutes and at most 2 hours.