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.
The Extract, Transform, Load (ETL) process produces data sets (DF) as the output.
BriteCore Operational DB > Backup Tables > Premium Records > Non-Prepared DFs > Prepared DFs
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) 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
- Prepared data sets
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
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.
Examples of facts data sets:
- Accounting
- Claims Payment
- Claims
- Commission Accounting
- Commission Payments
- Files
- Item Changes
- Item Range
- Item State
- Policy Changes
- Policy Range
- Policy State
- Quotes
- Return Premiums
- Go to Dimensions Data Sets
- Go to Factless Data Sets
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
Table 2 summarizes what’s included in Claims Payment.
Table 2: Claims Payment.
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
Dependencies
BriteCore table commission_accounting
Table 4 summarizes what’s included in Commission Accounting.
Table 4: Commission Accounting.
Commission Payments
Dependencies
BriteCore table commission_payments
Table 5 summarizes what’s included in Commission Payments.
Table 5: Commission Payments.
Files
Dependencies
BriteCore table files
Table 6 summarizes what’s included in Files.
Table 6: Files.
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 7 summarizes what’s included in Item Changes.
Table 7: Item Changes.
Item Range
Dependencies
- Non-Prepared property_items
- Non-prepared revision_items
- Prepared item_transactions
Facts
- annualPremium
- itemEarnedPremium
- itemEndingLimit
- itemEndingPremium
- itemStartingLimit
- itemTransactionType
- itemWrittenPremium
Table 8 summarizes what’s included in Item Range.
Table 8: Item Range.
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 9 summarizes what’s included in Item State.
Table 9: Item State.
Policy Changes
Dependencies
- Non-prepared premium_records
- Non-prepared revisions
- Prepared item_transactions
Facts
- policyChangeWrittenPremium
- policyChangeEarnedPremium
- policyChangeTransactionalCustomFees
- policyChangeEndingPremium
Table 10 summarizes what’s included in Policy Changes.
Table 10: Policy Changes.
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 11 summarizes what’s included in Policy Range.
Table 11: Policy Range.
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 12 summarizes what’s included in Policy State.
Table 12: Policy State.
Quotes
Table 13 summarizes what’s included in Quotes.
Table 13: Quotes.
Return Premiums
Table 14 summarizes what’s included in Return Premiums.
Table 14: 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.
Examples of dimensions data sets:
- Additional Interests
- Agencies
- Credit Reports
- Dates to Remember
- Items
- Lines
- Mortgagees
- Policies
- Policy Types
- Policyholders
- Primary Policyholders
- Properties
- Property Item Ratings Details
- Revisions
- Reinsurance Contracts
- Agencies Contacts
- Loss Run
- Claims Status Change
- Claims Peril
- Go to Factless Data Sets
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 15 summarizes what’s included in Additional Interests.
Table 15: Additional Interests.
Agencies
Dependencies
- Non Prepared addresses
- Non Prepared emails
- Non Prepared phones
Table 16 summarizes what’s included in Agencies.
Table 16: Agencies.
Credit Reports
Dependency
BriteCore table credit_reports
Table 17 summarizes what’s included in Credit Reports.
Table 17: Credit Reports.
Column Name | Description | Type |
creditReportPolicyNumber | Policy number the insured is associated with. | str |
policyholderId | The policyholder associated with the credit report. | str |
creditScoreReasonCode4 | Additional reason for the credit score. | str |
creditReportEffectiveId | The policyholder associated with the credit report. | str |
creditScoreReasonCode3 | Additional reason for the credit score. | str |
creditScoreReasonCode1 | The primary reason for the credit score. | str |
creditReportPolicyTypeId | The policyholder associated with the credit report. | str |
creditReportDateAdded | The date on which the credit report was pulled. | date |
creditReportLinesEffectiveDate | The effective date the credit tier is associated with. | str |
creditReportPolicyId | The policyholder associated policy. | str |
creditReportRevisionDate | The latest revision date/term this credit report is applicable to. | str |
creditReportPolicyTypeName | The name of policy type the credit tier is associated with. | str |
creditTierGlobal | If true, flag indicates that assigned credit tier was defined at the policy type level. | boolean |
creditScore | The reported credit score. | str |
creditTier | The credit score translated into client defined tiers. | str |
creditScoreReasonCode2 | The secondary reason for the credit score. | str |
creditReportRevisionId | The revision the credit report is associated with. | str |
Dates to Remember
Dependency
BriteCore table dates_to_remember
Table 18 summarizes what’s included in Dates to Remember.
Table 18: Dates to Remember.
Items
Dependency
- Non-prepared builder_obj_sys_tags
- Non-prepared property_items
- Non-prepared revision_item
Table 19 summarizes what’s included in Items.
Table 19: Items.
Lines
Dependency
Non Prepared lines
Table 20 summarizes what’s included in Lines.
Table 20: Lines.
Mortgagees
Dependency
Non Prepared mortgagees
Table 21 summarizes what’s included in Mortgagees.
Table 21: Mortgagees.
Policies
Dependency
Non-prepared policies
Table 22 summarizes what’s included in Policies.
Table 22: Policies.
Policy Types
Dependency
- BriteCore table business_locations
- BriteCore table policy_types
- BriteCore table revisions
Table 23 summarizes what’s included in Policy Types.
Table 23: Policy Types.
Policyholders
Dependency
- Non Prepared addresses
- Non Prepared emails
- Non Prepared phones
Table 24 summarizes what’s included in Policyholders.
Table 24: Policyholders.
Primary Policyholders
Dependency
Non-prepared policyholders
Table 25 summarizes what’s included in Primary Policyholders.
Table 25: Primary Policyholders.
Properties
Dependency
- Non-prepared inspectors
- Non-prepared properties
Table 26 summarizes what’s included in Properties.
Table 26: Properties.
Property Item Ratings Details
Table 27 summarizes what’s included in Property Item Ratings Details.
Table 27: Property Item Ratings Details.
Revisions
Dependency
Non-prepared committed_revisions_all
Table 28 summarizes what’s included in Revisions.
Table 28: Revisions.
Reinsurance Contracts
Table 29 summarizes what’s included in Reinsurance Contracts.
Table 29: Reinsurance Contracts.
Agencies Contacts
Table 30 summarizes what’s included in Agencies Contacts.
Table 30: Agencies Contacts.
Loss Run
Table 31 summarizes what’s included in Loss Run.
Table 31: Loss Run.
Claims Status Change
Table 32 summarizes what’s included in Claims Status Change.
Table 32: Claim Status Change.
Claims Peril
Table 33 summarizes what’s included in Claims Peril.
Table 33: Claims Peril.
Factless
Factless data sets are used to join dimensional data but don’t contain any measures or facts. An example of a factless data set is the Credit Reports data set.