Topics on this page

Data sets overview

What are data sets?

Data sets are data structures designed for reporting and analysis; think of them as reporting materialized views.

Data sets include more meaningful groupings of information, achieved with the date (similar to a snapshot) from which they were rendered. This date is often referred to internally as the as of date.

Any modern BriteCore report you run will use an as of date, which dictates the archive used. Though you might not specify an as of date when running a report, a meaningful one is chosen. For example, if the report is a date range, the as of date is the date after the to date in the range.

We provide some mechanisms for exploring the metadata for all of the available data sources as data sets.

You can export data sets from Report List in the Reports menu for further analysis. All data sets are stored in .csv format.

 

Figure 1: The Extract, Transform, Load (ETL) process producing data sets (DF) as the output.

Why data sets?

Databases can contain large amounts of data. Queries can take from milliseconds to many hours depending on their complexity. While a query is running, it can significantly slow down; sometimes, new queries are inhibited from being run. This becomes particularly relevant to BriteCore with regard to running reports. Our clients run a multitude of reports that require extensive data manipulation.

To make the reporting process less intensive and time-consuming, we generate nightly views of data. These specialized views are used to build reports more efficiently. Data sets are flat files (such as .csv) that are used by a Python library called Pandas.

Most of our reporting comes from data sets (or data frames/data caches) and not directly from querying the database.

Types of data sets in BriteCore

There are two types of data sets available in BriteCore:

Non-prepared data sets

Non-prepared data sets are based on raw SQL queries that pull directly from the transactional database. These data sets act as a staging area for further processing.

Prepared data sets

Prepared data sets are based on non-prepared data sets. These are based on SQL queries that can include merging and other logic. Prepared data sets don’t have repeating column names among them and they all use revisionId as the merge key.

Prepared data sets are the preferred first data source for reports and are divided into categories:

  • Facts data sets
  • Dimensions data sets

Facts data sets

Facts are also referred to as measures. This is data that is aggregated, summarized, or subtotaled. BriteData treats the facts data set as the base data set for a report. If you don’t select a fact data set for your report, BriteData will use policy_state as the default. The following are examples of data sets that contain facts:

 Accounting

View additional details on Accounting

Dependencies

  • Non-prepared policy
  • Non-prepared policy_terms
  • Non-prepared revision
  • Prepared account_history_journal

Table 1 summarizes what’s included in Accounting.

Table 1: Accounting.

 

Claims Payment

View additional details on Claims Payment

Table 2 summarizes what’s included in Claims Payment.

Table 2: Claims Payment.

 

Claims

View additional details on Claims

Dependencies

  • BriteCore table claim_dates
  • BriteCore table claim_items
  • BriteCore table claims
  • BriteCore table policies
  • BriteCore table properties
  • BriteCore table property_items
  • BriteCore table revision_items

Table 3 summarizes what’s included in Claims.

Table 3: Claims.

 

Commission Accounting

View additional details on Commission Accounting

Dependencies

BriteCore table commission_accounting

Table 4 summarizes what’s included in Commission Accounting.

Table 4: Commission Accounting.

 

Commission Payments

View additional details on Commission Payments

Dependencies

BriteCore table commission_payments

Table 4 summarizes what’s included in Commission Payments.

Table 4: Commission Payments.

 

Files

View additional details on Files

Dependencies

BriteCore table files

Table 5 summarizes what’s included in Files.

Table 5: Files.

 

Item Changes

View additional details on Item Changes

Dependencies

  • item_earned_unearned
  • Non-Prepared committed_revisions
  • Non-Prepared committed_revisions_all
  • Non Prepared files
  • Non-Prepared premium_records
  • Non-Prepared property_items
  • Non-Prepared revision_items
  • Prepared item_transactions

Facts

  • itemChangeEarnedPremium
  • itemChangeEndingLimit
  • itemChangeEndingPremium
  • itemChangeStartingLimit
  • itemChangeTransactionalCustomFees
  • itemChangeWrittenPremium

Table 6 summarizes what’s included in Item Changes.

Table 6: Item Changes.

 

Item Range

View additional details on Item Range

Dependencies

  • Non-Prepared property_items
  • Non-prepared revision_items
  • Prepared item_transactions

Facts

  • annualPremium
  • itemEarnedPremium
  • itemEndingLimit
  • itemEndingPremium
  • itemStartingLimit
  • itemTransactionType
  • itemWrittenPremium

Table 7 summarizes what’s included in Item Range.

Table 7: Item Range.

 

Item State

View additional details on Item State

Dependencies

  • item_earned_unearned
  • Non-prepared committed_revisions
  • Non-prepared committed_revisions_all
  • Non-prepared files
  • Non-prepared premium_records
  • Non-prepared property_items
  • Non-prepared revision_items
  • Prepared item_transactions

Facts

  • itemInforceLimit
  • itemInforcePremium
  • itemUnearnedPremiumitem_state

Table 8 summarizes what’s included in Item State.

Table 8: Item State.

 

Policy Changes

View additional details on Policy Changes

Dependencies

  • Non-prepared premium_records
  • Non-prepared revisions
  • Prepared item_transactions

Facts

  • policyChangeWrittenPremium
  • policyChangeEarnedPremium
  • policyChangeTransactionalCustomFees
  • policyChangeEndingPremium

Table 9 summarizes what’s included in Policy Changes.

Table 9: Policy Changes.

 

Policy Range

View additional details on Policy Range

Dependencies

  • Non-prepared committed_revisions
  • Non-prepared committed_revisions_all
  • Non-prepared files
  • Non -prepared premium_records
  • Non-prepared revisions
  • Prepared item_transactions
  • Prepared written_premium

Facts

  • policyWrittenPremium
  • policyEarnedPremium
  • policyEndingPremium
  • premiumRecordsWrittenPremium

Table 10 summarizes what’s included in Policy Range.

Table 10: Policy Range.

 

Policy State

View additional details on Policy State

Dependencies

  • Non-prepared committed_revisions
  • Non-prepared committed_revisions_all
  • Non-prepared files
  • Non-prrepared premium_records

Facts

  • policyAnnualCustomFee
  • policyAnnualPremium
  • policyInforceCustomFeeProRata
  • policyInforcePremium
  • policyUnearnedPremium

Table 11 summarizes what’s included in Policy State.

Table 11: Policy State.

 

Quotes

View additional details on Quotes

Table 12 summarizes what’s included in Quotes.

Table 12: Quotes.

 

Return Premiums

View additional details on Return Premiums

Table 13 summarizes what’s included in Return Premiums.

Table 13: Return Premiums.

 

Dimensions data sets

Dimensions provide the context for the facts or measurements so when querying data, the dimensions serve as filters or groupings.Below are some examples of data sets containing dimensions.

Additional Interests

View additional details on Additional Interests

Dependencies

  • BriteCore table addresses
  • BriteCore table contacts
  • BriteCore table revisions
  • BriteCore table roles
  • BriteCore table x_contacts_roles
  • BriteCore table x_revisions_contacts

Table 14 summarizes what’s included in Additional Interests.

Table 14: Additional Interests.

 

Agencies

View additional details on Agencies

Dependencies

  • Non Prepared addresses
  • Non Prepared emails
  • Non Prepared phones

Table 15 summarizes what’s included in Agencies.

Table 15: Agencies.

 

Credit Reports

View additional details on Credit Reports

Dependency

BriteCore table credit_reports

Table 16 summarizes what’s included in Credit Reports.

Table 16: Credit Reports.

 

Dates to Remember

View additional details on Dates to Remember

Dependency

BriteCore table dates_to_remember

Table 17 summarizes what’s included in Dates to Remember.

Table 17: Dates to Remember.

 

Items

View additional details on Items

Dependency

  • Non-prepared builder_obj_sys_tags
  • Non -prepared property_items
  • Non-prepared revision_item

Table 18 summarizes what’s included in Items.

Table 18: Items.

 

Lines

View additional details on Lines

Dependency

Non Prepared lines

Table 18 summarizes what’s included in Lines.

Table 18: Lines.

 

Mortgagees

View additional details on Mortgagees

Dependency

Non Prepared mortgagees

Table 18 summarizes what’s included in Mortgagees.

Table 18: Mortgagees.

 

Policies

View additional information on Policies

Dependency

Non-prepared policies

Table 19 summarizes what’s included in Policies.

Table 19: Policies.

 

Policy Types

View additional information on Policy Types

Dependency

  • BriteCore table business_locations
  • BriteCore table policy_types
  • BriteCore table revisions

Table 20 summarizes what’s included in Policy Types.

Table 20: Policy Types.

 

Policyholders

View additional information on Policyholders

Dependency

  • Non Prepared addresses
  • Non Prepared emails
  • Non Prepared phones

Table 21 summarizes what’s included in Policyholders.

Table 21: Policyholders.

 

Primary Policyholders

View additional information on Primary Policyholders

Dependency

Non-prepared policyholders

Table 22 summarizes what’s included in Primary Policyholders.

Table 22: Primary Policyholders.

 

Properties

View additional information on Properties

Dependency

  • Non-prepared inspectors
  • Non-prepared properties

Table 23 summarizes what’s included in Properties.

Table 23: Properties.

 

Property Item Ratings Details

View additional information on Property Item Ratings Details

Table 24 summarizes what’s included in Property Item Ratings Details.

Table 24: Property Item Ratings Details.

 

Revisions

View additional information on Revisions

Dependency

Non-prepared committed_revisions_all

Table 25 summarizes what’s included in Revisions.

Table 25: Revisions.

 

Reinsurance Contracts

View additional information on Reinsurance Contracts

Table 26 summarizes what’s included in Reinsurance Contracts.

Table 26: Reinsurance Contracts.