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.
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
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.
| Column Name | Description | Type |
| changeInPaidCustomFee | The change in paid custom fees on a transaction. | float |
| changeInTransferredReturnPremium | A return premium that has been transferred from one policy to another. | float |
| dueDate | The due date of the invoice. | date |
| changeInPremiumWriteOff | A premium waived off after the term is in effect. | float |
| endingPremiumAccountsReceivable | The ending premium accounts receivable as of a transaction date. | float |
| changeInExportedReturnPremium | Return premium that has been processed and sent back to the insured. | float |
| accountingTransactionDate | The account history transaction date. | date |
| changeInAdvancedPremium | A premium paid in advance of the policy term's effective date. | float |
| endingSystemFeeAccountsReceivable | The ending system fee accounts receivable as of a transaction date. | float |
| changeInGrossPayments | The change in gross payments on a transaction. | float |
| changeInCustomFeesInCourseOfCollection | The change in custom fees in course of collection on a transaction. | float |
| changeInPaidSystemFee | The change in paid system fees on a transaction. | float |
| policyTermId | A primary key for policy terms. | str |
| changeInPaidPremium | The change in paid premium on a transaction. | float |
| changeInSystemFeesInCourseOfCollection | The change in system fees in course of collection on a transaction. | float |
| changeInAdvancedPremiumWriteOff | A premium waived off before the term is in effect. | float |
| changeInDeferredPremium | The change in deferred premium on a transaction. | float |
| changeInPendingReturnPremium | A return premium that is still in the Return Premium queue. | float |
| changeInSuspenseAccount | The change in suspense account on a transaction. | float |
| changeInPremiumInCourseOfCollection | The change in premium in course of collection on a transaction. | float |
| changeInAppliedTransfers | Return premium that is being applied to a policy. | float |
| accountingTransactionType | The account history transaction type. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| changeInAdvancedBilledPremium | A premium that has been billed in advance of the policy term but not yet paid. | float |
| endingAdvancedPremium | The ending advanced premium as of a transaction date. | float |
| policyId | A primary key for policies. | str |
| endingCustomFeeAccountsReceivable | The ending custom fee accounts receivable as of a transaction date. | float |
Claims Payment
View additional details on Claims Payment
Table 2 summarizes what’s included in Claims Payment.
Table 2: Claims Payment.
| Column Name | Description | Type |
| claimPayeeAddressCity | The first claim payee's city. | str |
| claimPaymentTransactionDate | The date that the claim payment was created. | date |
| claimPaymentVoided | Whether or not the claim payment has been voided. | boolean |
| claimPayeeName4 | The full name of the fourth claim payee. | str |
| claimPaymentLegalPaid | The amount of legal paid for the claim payment. | float |
| claimPaymentAmount | The amount of the claim payment. | float |
| claimPayeeName5 | The full name of the fifth claim payee. | str |
| claimPayeeName6 | The full name of the sixth claim payee. | str |
| claimPayeePhone | The first claim payee's phone number. | str |
| claimPayeeName1 | The full name of the first claim payee. | str |
| claimPayeeName2 | The full name of the second claim payee. | str |
| claimPayeeName3 | The full name of the third claim payee. | str |
| claimPayeeAddressZip | The first claim payee's ZIP. | str |
| claimPaymentAdjustingPaid | The amount of adjusting paid for the claim payment. | float |
| claimPaymentReason | The claim payment's summary. | str |
| claimPayeeAddressState | The first claim payee's state. | str |
| claimPayeeVendorNumber | The vendor number associated with the primary claim payee. | str |
| claimPayeeAddressLine1 | The first claim payee's first line address. | str |
| claimPayeeAddressLine2 | The first claim payee's second line address. | str |
| claimPaymentClassificationName | The claim payment classification name shows how the payment was made. | str |
| claimPaymentExported | Whether or not the claim payment has been exported. | boolean |
| claimPaymentCheckDisplay | The first claim payee's check display. | str |
| claimPaymentCheckNumber | The check number associated with the payment. | str |
| claimPaymentClaimTransactionId | The unique id of claim payments transaction | str |
| claimPayeeAddressCountry | The first claim payee's country. | float |
| claimPaymentLossPaid | The amount of loss paid for the claim payment. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| claimId | The foreign key that ties the payment to the claim. | str |
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.
| Column Name | Description | Type |
| dateReopened | The date the claim was reopened. | date |
| catStartDate | The start date that applies to a catastrophe. | date |
| lossAddressCounty | The county of the loss address. | str |
| plaintiffAttorneyName | The plaintiff attorney on the claim. | str |
| legalReserveBalance | The balance of legal reserve at the end of the period. | float |
| catTitle | The title given to the catastrophe. | str |
| supervisorName | The name of the supervisor on the claim. | str |
| lossAddressLine2 | The second line of the loss address. | str |
| lossAddressId | A foreign key to loss address. | str |
| changeInLegalReserve | The change in legal reserve during the period. | float |
| lossCauses | All loss causes listed for this claim in a comma-separated list. | str |
| reinsuranceReserveBalance | The balance of reinsurance reserve at the end of the period. | float |
| dateSuitClosed | The date the suit closed. | date |
| claimDescription | The claim description. | str |
| changeInSubrogationReserve | The change in subrogation reserve during the period. | float |
| itemId | A foreign key to property/revision items. | str |
| reinsuranceLossReserveBalance | The balance of reinsurance loss reserve at the end of the period. | float |
| lossReserveBalance | The balance of loss reserve at the end of the period. | float |
| revisionId | The revision ID. | str |
| subrogationReserveBalance | The balance of subrogation reserve at the end of the period. | float |
| catEndDate | The end date that applies to a catastrophe. | date |
| adjustingReserveBalance | The balance of adjusting reserve at the end of the period. | float |
| subrogationReceived | The amount of subrogation recovery that was recieved during the period. | float |
| defendantAttorneyName | The defendant attorney on the claim. | str |
| historical | A value indicating whether or not a transaction was marked as historical. | boolean |
| lossAddressState | The state of the loss address. | str |
| contractorName | The name of the contractor on a claim. | str |
| lossAddressLine1 | The first line of the loss address. | str |
| changeInReinsuranceLaeReserve | The change in reinsurance LAE reserve during the period. | float |
| changeInSalvageReserve | The change in salvage reserve during the period. | float |
| claimType | The type of claim: property, liability, casualty, personal auto, etc. | str |
| adjusterName | The name of the adjuster on the claim. | str |
| claimTransactionDate | The date of the claim transaction. | date |
| lossAddressZip | The ZIP code of the loss address. | str |
| lossPaid | The amount paid for loss expense during the period. | float |
| lossAddressCity | The city of the loss address. | str |
| lossAddressCountyName | The county of the loss address. | str |
| legalPaid | The amount paid for legal expense during the period. | float |
| reinsuranceLaeReceived | The amount of reinsurance LAE recovery that was received during the period. | float |
| claimantName | The name of the claimant on a claim. | str |
| dateAssigned | The date the claim was assigned. | date |
| salvageReceived | The amount of salvage recovery that was received during the period. | float |
| policyId | A primary key for policies. | str |
| adjustingPaid | The payment amount made under the adjustment category during the period. | float |
| salvageReserveBalance | The balance of salvage reserve at the end of the period. | float |
| reinsuranceReceived | The amount of reinsurance recovery that was received during the period. | float |
| changeInReinsuranceLossReserve | The change in reinsurance Loss reserve during the period. | float |
| claimNumber | The claim number. | str |
| lossDate | The date of loss. | date |
| claimStatus | The most recent status of the claim. | str |
| reinsuranceLossReceived | The amount of reinsurance Loss recovery that was received during the period. | float |
| claimId | The claim ID. | str |
| claimSystemTags | Any system tags that have been added to the claim. | str |
| changeInAdjustingReserve | The change in adjusting reserve during the period. | float |
| dateReported | The date that the claim was reported. | date |
| changeInLossReserve | The change in loss reserve during the period. | float |
| reinsuranceLaeReserveBalance | The balance of reinsurance LAE reserve at the end of the period. | float |
| dateClosed | The date the claim closed. | date |
| changeInReinsuranceReserve | The change in reinsurance reserve during the period. | float |
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.
| Column Name | Description | Type |
| commissionInsertedBy | Who generated this commission transaction. | str |
| commissionIsManualEntry | Was this commission transaction manually entered. | boolean |
| policyId | The foreign key to Policies. | str |
| commissionDelayed | Reason why this commission transaction may be delayed: 'Negative Amount', 'Missing Payment Method', 'Term Not Yet Effective', or 'Not Delayed'. | str |
| commPayId | Foreign key to Commission Payment, may be null if transaction is delayed. | str |
| commissionSource | What generated this commission transaction. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| commissionableAmount | The amount of premium that the commission rate applied to. | float |
| commissionAmountToBePaid | The amount of commission to be paid for this policy event. | float |
| commissionRate | The rate used for this commission. | float |
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.
| Column Name | Description | Type |
| commissionPayeeAgencyNumber | The agency number of the commission payee. | str |
| commissionPaymentAmount | The amount of the commission payment. | float |
| commissionPayeeAddressZip | The commission payee's ZIP code. | str |
| commissionPaymentDateExported | The date that the commission payment was exported. | date |
| commissionPayeeSystemTags | The commission payee's system tags. | str |
| commissionPaymentTransactionDate | The date that the commission payment was created. | date |
| commissionPaymentIsElectronic | Whether or not the commission payment is electronic. | boolean |
| commissionPaymentReviewed | The date that the commission payment was reviewed. | str |
| contactId | The key that attaches commission payments to the payee contact. | str |
| commPayId | Primary key for commission payment, used by commission accounting. | str |
| commissionPayeeAddressState | The commission payee's state. | str |
| commissionPayeeName | The full name of the commission payee. | str |
| commissionPayeeAddressCity | The commission payee's city. | str |
| commissionPayeeAddressLine1 | The commission payee's first line of address. | str |
| commissionPayeeAddressLine2 | The commission payee's second line of address. | str |
| commissionPaymentExported | Whether or not the commission payment has been exported. | boolean |
Files
View additional details on Files
Dependencies
BriteCore table files
Table 5 summarizes what’s included in Files.
Table 5: Files.
| Column Name | Description | Type |
| fileAddressZip | The addressee's file address zip. | str |
| fileAddressLine1 | The addressee's file address line 1. | str |
| fileAddressLine2 | The addressee's file address line 2. | str |
| certifiedMail | Whether or not a file is certified mail: (Y/N) | boolean |
| filePrintState | The file's print state: (do not print, tbd, silenced, sent, printed, failed, void, not mailed, transferred). | str |
| customName | The custom name of a report file. | str |
| fileAddressCity | The addressee's file address city. | str |
| fileDateAdded | The date the file was generated: (YYYY-MM-DD) | date |
| fileAddressState | The addressee's file address state. | str |
| fileActualPrintDate | The files print date: (YYYY-MM-DD) | date |
| reportType | The type of a report file: (Policy App., Notice, etc.) | str |
| policyId | A foreign key to policies. | str |
| contactId | A foreign key to contacts. | str |
| revisionId | A foreign key to revisions. | str |
| fileLabel | The label of the file: (Declaration, Renewal, etc.) | str |
| Revision Id | A foreign key to revisions. | str |
| fileId | A primary key for files. | str |
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.
| Column Name | Description | Type |
| itemChangeEarnedPremium | The amount of premium that was earned for a particular item during the transaction. | float |
| itemChangeEndingLimit | The limit at the end of the transaction, even if the policy was canceled. | float |
| itemChangeEndingPremium | The premium at the end of the transaction, even if the policy was canceled. | float |
| itemChangeStartingLimit | The limit at the beginning of the transaction. | float |
| itemChangeTransactionDate | The date of the last transaction that occured within the specified date range. | date |
| itemChangeTransactionType | The type of premium transaction that occurred on the item. | str |
| itemChangeTransactionalCustomFees | The change in a particular custom fee during the transaction. | float |
| itemChangeWrittenPremium | The amount of premium that was written for a particular item during the transaction. | float |
| itemId | A foreign key to items. | str |
| policyId | A foreign key to policies. | str |
| propertyId | A foreign key to properties. | str |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| annualPremium | The premium amount over a year for the policy. | float |
| itemEarnedPremium | The amount of premium that was earned for a particular item over the date range specified. | float |
| itemEndingLimit | The limit at the end of the range, even if the policy was canceled during the date range. | float |
| itemEndingPremium | The premium at the end of the range, even if the policy was canceled during the date range. | str |
| itemId | A foreign key to items. | str |
| itemStartingLimit | The limit at the beginning of the range. | float |
| itemTransactionType | The type of premium transaction that occurred on the item. | str |
| itemTransactionalCustomFees | The change in a particular custom fee over the date range specified. | float |
| itemWrittenPremium | The amount of premium that was written for a particular item over the date range specified. | float |
| lastItemTransactionDate | The date of the last transaction that occured within the specified date range. | date |
| policyId | A foreign key to properties. | str |
| propertyId | A foreign key to properties. | str |
| reportingDate | Date of report. | date |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| itemId | A foreign key to items. | str |
| itemInforceLimit | The limit that is inforce for particular item at a point in time or at the end of the date range. | float |
| itemInforcePremium | The premium that is inforce for a particular item at a point it time or at the end of the date range. | float |
| itemUnearnedPremium | The amount of unearned premium for a particular item at the end of the date range specified. | float |
| policyId | A foreign key to policies. | str |
| propertyId | A foreign key to properties. | str |
| revisionId | The foreign key that binds nearly everything | str |
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.
| Column Name | Description | Type |
| policyChangeEarnedPremium | The amount of premium that was earned for a policy during the change. | float |
| policyChangeEndingPremium | The premium at the end of the change, even if the policy was canceled. | float |
| policyChangeTransactionDate | The date that the policy change occured. | date |
| policyChangeTransactionType | The type of transaction that occurred: new, renewal, canceled, reinstated, non-renewed. | str |
| policyChangeTransactionalCustomFees | The custom fees change in the revisions. | float |
| policyChangeWrittenPremium | The written premium for the change. | float |
| policyId | A foreign key to policies. | str |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| policyEarnedPremium | The amount of premium that was earned for a policy over the date range specified. | float |
| policyEndingPremium | The premium at the end of the range, even if the policy was canceled during the date range. | float |
| policyId | A foreign key to policies. | str |
| policyPremiumTransactionDate | The date that the premium transaction occurred. | date |
| policyPremiumTransactionType | The type of premium transaction that occurred: new, renewal, canceled, reinstated, non-renewed. | str |
| policyWrittenPremium | The written premium over a date range. | float |
| premiumRecordsWrittenPremium | The written premium during the date range from premium records. | float |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| policyAnnualCustomFee | The annual custom fees for a particular policy at a point in time or at the end of the date range. This is what the custom fees would have been if no pro-rata calculation had taken place. | float |
| policyAnnualPremium | The annual premium for a particular policy at a point in time or at the end of the date range. This is what the policy premium would have been if no pro-rata calculation had taken place. | float |
| policyId | A foreign key to policies. | str |
| policyInforceCustomFeeProRata | The sum of all custom fees that are inforce for a particular policy at a point in time or at the end of the date range. | float |
| policyInforcePremium | The premium that is inforce for a particular policy at a point in time or at the end of the date range. | float |
| policyUnearnedPremium | The amount of unearned premium for a policy at the end of the date range specified. | float |
| revisionId | The foreign key that binds nearly everything. | str |
| policyAnnualCustomFee | The annual custom fees for a particular policy at a point in time or at the end of the date range. This is what the custom fees would have been if no pro-rata calculation had taken place. | float |
Quotes
View additional details on Quotes
Table 12 summarizes what’s included in Quotes.
Table 12: Quotes.
| Column Name | Description | Type |
| createDate | The date when the revision for this quote was initially created. | date |
| policyId | A foreign key to policies. | str |
| quoteStatus | The policy status for a given policy at the date requested by the report: Submitted, Unsubmitted, Quote. | str |
| quotedAnnualPremium | Annual premium for a quote. | float |
| quotedWrittenPremium | Pro-rata premium for a quote. | float |
| revisionId | Primary key for quotes. | str |
Return Premiums
View additional details on Return Premiums
Table 13 summarizes what’s included in Return Premiums.
Table 13: Return Premiums.
| Column Name | Description | Type |
| policyId | An additional foreign key for joining to policies, agencies. | str |
| returnPremiumAmount | The amount of the return premium. | float |
| returnPremiumCanceled | Whether or not the return premium has been canceled. | boolean |
| returnPremiumCheckNumber | The check number associated with the return premium payment. | str |
| returnPremiumExported | Whether or not the return premium has been exported. | boolean |
| returnPremiumId | ID of the return premium. | str |
| returnPremiumPayeeAddressCity | The return premium payee's city. | str |
| returnPremiumPayeeAddressCountry | The return premium payee's country. | str |
| returnPremiumPayeeAddressLine1 | The first line of the return premium payee's address. | str |
| returnPremiumPayeeAddressLine2 | The second line of the return premium payee's address. | str |
| returnPremiumPayeeAddressState | The return premium payee's state. | str |
| returnPremiumPayeeAddressZip | The return premium payee's ZIP code. | str |
| returnPremiumPayeeName | The full name of the return premium payee. | str |
| returnPremiumPayeePhone | The phone number for the return premium payee. | str |
| returnPremiumPayeeVendorNumber | The vendor number on the return premium payee. | str |
| returnPremiumPaymentMethod | The return premium's payment method. | str |
| returnPremiumReason | The return premium's summary. | str |
| returnPremiumTransactionDate | The date that the return premium was created. | date |
| returnPremiumTransferred | Whether or not the return premium was transferred to another policy. | boolean |
| revisionId | The foreign key that binds nearly everything. | str |
| returnPremiumPolicyNumber | The policy number for which the return premium was issued. | str |
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.
| Column Name | Description | Type |
| agencyContactAgentLastLogIn | The agent's last log in. | date |
| agencyContactAgentTerminationReason | The agent's termination reason. | str |
| agencyContactAgentContactId | A foreign key to contacts, for the agent contact. | str |
| agencyContactAgentPermissionLevelName | The agent's permission level name. | str |
| agencyContactAgencyNumber | The agent's agency number. | str |
| agencyContactAgentName | The agent's name. | str |
| agencyContactAgentProducerNumber | The agent's producer number. | str |
| agencyContactAgentTerminated | Whether or not the agent has been terminated | boolean |
| agencyContactAgentConfirmationEmail | The agent's address city. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| agencyContactAgentEmails | The agent's emails, separated by ';'. | str |
| agencyContactAgentDob | The agent's date of birth. | str |
| agencyContactAgencyContactId | A foreign key to contacts, for the agency contact. | str |
| agencyContactAgentPosition | The agent's position. | str |
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.
| Column Name | Description | Type |
| additionalInterestAddressState | The policyholder's additional interest's address state. | str |
| additionalInterestAddressCity | The policyholder's additional interest's address city. | str |
| additionalInterestAddressLine1 | The policyholder's additional interest's address line 1. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| additionalInterestAddressZip | The policyholder's additional interest's address zip. | str |
| additionalInterestAddressLine2 | The policyholder's additional interest's address line 2. | str |
| additionalInterestName | The policyholder's additional interest's name. | str |
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.
| 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
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.
| Column Name | Description | Type |
| dateToRememberAlertTimeUnit | The alert time unit of the dates to remember. | str |
| dateToRememberAlertTimePeriod | The alert time period of the dates to remember. | str |
| dateToRememberContactSystemTags | The contact's System Tags of the dates to remember. | str |
| dateToRememberAlertEmail | The alert email of the dates to remember. | str |
| dateToRememberContactName | The contact's name of the dates to remember. | str |
| dateToRememberTouchpoint | The touchpoint of the dates to remember. | str |
| dateToRemember | The date of the date to remember | date |
| dateToRememberFrequency | The frequency of the dates to remember. | str |
| dateToRememberContents | The content of the dates to remember. | str |
| dateToRememberLastExecuted | The last time dates to remember was executed. | date |
| dateToRememberAlertStatus | The alert status of the dates to remember. | str |
| dateToRememberEnteredBy | The user that entered this dates to remember. | str |
| dateToRememberId | The Id of the dates to remember. | str |
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.
| Field | Description | Type |
| deductible | The deductible of the item, if the deductible is setup within the line item. This will not cover deductibles that are setup as categories. | float |
| itemCategoriesSystemTags | Any system tags that have been added to the item categories. | str |
| itemId | Primary Key for items. | str |
| itemName | The name of the line item. | str |
| itemSortOrder | The numeric order that items are given in lines. | float |
| itemSystemTags | Any system tags that have been added to the item. | str |
| itemType | The type of item: coverage, adjustment, fee, non-rate. | str |
| lossExposureGroup | The loss exposure group that an item belongs to. | str |
| manuallyRated | Whether or not an item was manually rated. | boolen |
| policyId | A foreign key to policies. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| subLineInstanceId | The subline ID. | str |
| subLineName | The name of the subline, if any. | str |
| subLineSystemTags | Any system tags that have been added to the subline. | str |
| subLineType | The type of the subline, if any: property, liability, inland marine. | str |
Lines
View additional details on Lines
Dependency
Non Prepared lines
Table 18 summarizes what’s included in Lines.
Table 18: Lines.
| Column Name | Description | Type |
| lineId | Line ID. | str |
| lineOfBusinessName | The name of the line of business. | str |
| lineOfBusinessSystemTags | Any system tags that have been added to the line of business. | str |
| revisionId | The foreign key that binds nearly everything. | str |
Mortgagees
View additional details on Mortgagees
Dependency
Non Prepared mortgagees
Table 18 summarizes what’s included in Mortgagees.
Table 18: Mortgagees.
| Column Name | Description | Type |
| mortgageeAddressCity | The mortgagee's address city. | str |
| mortgageeAddressLine1 | The mortgagee's address line 1. | str |
| mortgageeAddressLine2 | The mortgagee's address line 2. | str |
| mortgageeAddressState | The mortgagee's address state. | str |
| mortgageeAddressZip | The mortgagee's address ZIP code. | str |
| mortgageeName | The mortgagee's name. | str |
| revisionId | The foreign key that binds nearly everything. | str |
Policies
View additional information on Policies
Dependency
Non-prepared policies
Table 19 summarizes what’s included in Policies.
Table 19: Policies.
| Column Name | Description | Type |
| agencyContact | The agent associated with the agency. | str |
| policyId | A primary key for policies. | str |
| policyNumber | A policy number. | str |
| policySystemTags | Any system tags that have been added to the policy. | str |
| Policy Group Billing Number | The group billing number for the policy's policy group. | str |
| Policy Account Number | The account number associated with the policy. | str |
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.
| Column Name | Description | Type |
| policyTypeName | The name of the policy type. | str |
| policyTypeState | The state associated with this policy type. | str |
| policyTypeSystemTags | Any system tags that have been added to the policy type. | str |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| contactId | The contact ID for policyholder. | str |
| policyId | A foreign key to policies. | str |
| policyholderAddressLine1 | The first line of the policyholder's address. | str |
| policyholderAddressLine2 | The second line of the policyholder's address. | str |
| policyholderAddressType | The policyholder's address type: mailing, billing, mailing/billing, other. | str |
| policyholderCity | The policyholder's city. | str |
| policyholderContactType | Indicates whether the contact is a company or an individual. | str |
| policyholderCountry | The policyholder's country. | str |
| policyholderCounty | The policyholder's county. | str |
| policyholderDateAdded | When this contact was associated with this policy revision. | date |
| policyholderEmail | The policyholder's primary email address. | str |
| policyholderFirstName | The policyholder's first name. | str |
| policyholderLastName | The policyholder's last name. | str |
| policyholderName | The policyholder's name. | str |
| policyholderPhone | The policyholder's primary phone number. | str |
| policyholderState | The policyholder's state. | str |
| policyholderSystemTags | Any system tags that have been added to the policyholder. | str |
| policyholderWebsite | The policyholder's website. | str |
| policyholderZip | The policyholder's ZIP code. | str |
| revisionId | The foreign key that binds nearly everything. | str |
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.
| Column Name | Description | Type |
| primaryPolicyholderAddressLine1 | The first line of the primary policyholder's address. | str |
| primaryPolicyholderAddressLine2 | The second line of the primary policyholder's address. | str |
| primaryPolicyholderAddressType | The primary policyholder's address type: mailing, billing, mailing/billing, other. | str |
| primaryPolicyholderCity | The primary policyholder's city. | str |
| primaryPolicyholderContactType | Indicates if the primary policyholder contact is a company or an individual. | str |
| primaryPolicyholderCountry | The primary policyholder's country. | str |
| primaryPolicyholderCounty | The primary policyholder's county. | str |
| primaryPolicyholderEmail | The primary policyholder's primary email address. | str |
| primaryPolicyholderLastName | The primary policyholder's last name. | str |
| primaryPolicyholderName | The primary policyholder's name. | str |
| primaryPolicyholderPhone | The primary policyholder's primary phone number. | str |
| primaryPolicyholderState | The primary policyholder's state. | str |
| primaryPolicyholderSystemTags | Any system tags that have been added to the primary policyholder. | str |
| primaryPolicyholderWebsite | The primary policyholder's website. | str |
| primaryPolicyholderZip | The primary policyholder's ZIP code. | str |
| revisionId | The foreign key that binds nearly everything. | str |
Properties
View additional information on Properties
Dependency
- Non-prepared inspectors
- Non-prepared properties
Table 23 summarizes what’s included in Properties.
Table 23: Properties.
| Column Name | Description | Type |
| airDataGrossArea | The risks's AIR property value. | float |
| airDataPropertyValue | The risks's AIR property value. | float |
| airDataStories | The risk's AIR stories. | float |
| airDataYearBuilt | The risk's AIR year built. | float |
| inspectorName | The risks's inspector's name. | str |
| propertyId | The primary key for properties. | str |
| propertyName | The name of the property. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| riskAddressLine1 | The first line of the risk address. | str |
| riskAddressLine2 | The second line of the risk address. | str |
| riskCity | The risk's city. | str |
| riskCounty | The risk's county. | str |
| riskInspectionRequestedDate | The risk's requested inspection date. | date |
| riskIsDeleted | Whether or not the property has been deleted from the policy. | boolean |
| riskLatitude | The risk's latitude. | float |
| riskLongitude | The risk's longitude. | float |
| riskNextInspectionDate | The risk's next inspection date. | date |
| riskState | The risk's state. | str |
| riskZip | The risk's ZIP code. | str |
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.
| Column Name | Description | Type |
| itemId | Primary key for items. | str |
| itemRatingDescription | A description of the rating being done on an item. | str |
| itemRatingRunningTotal | The running total of rating being done on an item. | str |
| itemRatingType | The type of rating being done on an item. | str |
| propertyId | A foreign key to properties. | str |
| revisionId | The foreign key that binds nearly everything. | str |
Revisions
View additional information on Revisions
Dependency
Non-prepared committed_revisions_all
Table 25 summarizes what’s included in Revisions.
Table 25: Revisions.
| Column Name | Description | Type |
| billingSchedule | The billing schedule name for the policy, such as Annual, Monthly, etc. | str |
| cancelDate | The effective date of a cancellation. | date |
| commitDate | The date the revision was committed. | date |
| effectiveDate | The effective date for the policy term. | date |
| expirationDate | The expiration date for the policy term | date |
| inceptionDate | The date that the policy was first issued. | date |
| newOrRenewalTerm | Contains the value "New" or "Renewal." | str |
| policyCancellationReason | The reason a policy was cancelled. | str |
| commissionRate | The commission rate on the policy as a decimal number, such as 0.1500. | float |
| policyId | The policy's ID. | str |
| policyStatus | The policy status for a given policy at the date requested by the report: Active, Cancellation Pending, Non-Payment of Premium, Canceled, Non-Payment of Premium, Canceled, etc. | str |
| policyTerm | The term length of the policy. | str |
| renewalStatus | Indicates a Renew or Non-Renewal status. | str |
| renewalStatusDescription | The non-renewal status description, such as Underwriting Requirements. | str |
| renewalStatusReason | The non-renewal status reason, such as "dwelling is unoccupied." | str |
| revisionDate | The date the revision takes effect. | date |
| revisionId | Primary key for revisions. | str |
Reinsurance Contracts
View additional information on Reinsurance Contracts
Table 26 summarizes what’s included in Reinsurance Contracts.
Table 26: Reinsurance Contracts.
| Column Name | Description | Type |
| reinsuranceContractId | The reinsurance contract primary key. | str |
| reinsuranceContractLiabilityRetention | The reinsurance contract liability retention. | str |
| reinsuranceContractCededFactor | The reinsurance contract ceded factor. | str |
| reinsuranceContractLossesIncurredThreshold | The reinsurance contract losses incurred threshold. | str |
| reinsuranceContractPropertyRetention | The reinsurance contract property retention. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| claimId | An additional foreign key primary key for joining to claims. | str |
| reinsuranceContractStartDate | The reinsurance contract start date (YYYY-MM-DD). | date |
| reinsuranceContractRetention | The reinsurance contract retention. | str |
| reinsuranceContractType | The reinsurance contract type. | str |
| reinsuranceContractDescription | The reinsurance contract description. | str |
| reinsuranceContractEndDate | The reinsurance contract end date (YYYY-MM-DD). | type: date |
| reinsuranceContractReinsurerName | The reinsurance contract re insurer name. | str |
| reinsuranceContractRetentionType | The reinsurance contract retention type. | str |
Agencies Contacts
View additional information on Agencies Contacts
Table 27 summarizes what’s included in Agencies Contacts.
Table 27: Agencies Contacts.
| Column Name | Description | Type |
| agencyCountry | The agency's country. | str |
| agencyContactType | Company vs. Individual | str |
| agencyZip | The agency's zip. | str |
| agencyPhone | The agency's primary phone number. | float |
| agencyPasswordAttempts | The number of incorrect password attempts for the agency. | str |
| agentName | The name of the agency or agent (whichever is tied to policies). | str |
| agencyWebsite | The agency's website. | str |
| agencyUsername | The username of the agency. | str |
| agencyGroup | The name of the agency group (blank if there isn't one). | str |
| agencyArchived | Whether or not the agency is archived. | boolean |
| producerNumber | The producer number | str |
| agencySystemTags | Any system tags that have been added to the agency | str |
| agencyCommissionStructure | The name of the commission structure attached to an agency. | str |
| agencyLastLogin | The Date and Time of the agency's last login. | date |
| agencyInactive | Whether or not the agency is active. | boolean |
| agencyAddressLine2 | The agency's second line address. | str |
| agencyBillingEnabled | Whether or not agency billing is enabled. | boolean |
| agencyNumber | The agency number. | str |
| agentLicenseNumber | The agent's license number. | str |
| agencyCity | The agency's city. | str |
| agencyName | The name of the agency. If agencies are tied to policies, 'Agency Name' will match 'Agent Name'. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| agentSystemTags | Any system tags that have been added to the agent | str |
| agencyAddressType | The agency's address mailing, billing, mailing/billing, other. | str |
| agencyState | The agency's state. | str |
| agencyFein | The agency's FEIN. | str |
| agencyAddressLine1 | The agency's first line address. | str |
| agencyCounty | The agency's county. | str |
| agencyEmail | The agency's primary email address. | str |
| agentDOB | The agent's date of birth | date |
Loss Run
View additional information on Loss Run
Table 28 summarizes what’s included in Loss Run.
Table 28: Loss Run.
| Column Name | Description | Type |
| lossRunsClaimNumber | The claim number. | str |
| lossRunsAgencyCity | The agency's city. | str |
| lossRunsDateReported | The date that the claim was reported. | date |
| lossRunsAdjustingPaid | The payment amount made under the adjustment category during the period. | float |
| lossRunsInceptionDate | The date that the policy was first issued. | date |
| lossRunsLossAddressCity | The city of the loss address. | str |
| lossRunsAdjustingReserveBalance | The balance of adjusting reserve at the end of the period. | float |
| lossRunsAgencyGroupName | The agency's group. | str |
| lossRunsRiskLongitude | The risk's longitude. | str |
| lossRunsExpirationDate | The expiration date for the policy term. | date |
| lossRunsLossAddressLine1 | The first line of the loss address. | str |
| lossRunsLossAddressLine2 | The second line of the loss address. | str |
| agentContactId | A foreign key to contacts, for the agent contact. | float |
| lossRunsReinsuranceReceived | The amount of reinsurance recovery that was received during the period. | str |
| lossRunsLossCauses | All loss causes listed for this claim in a comma separated list. | str |
| lossRunsDateClosed | The date that the claim was closed. | str |
| lossRunsRiskLatitude | The risk's latitude. | str |
| revisionId | The foreign key that binds nearly everything. | str |
| policyId | The primary key for policies. | float |
| lossRunsSubrogationReserveBalance | The balance of subrogation reserve at the end of the period. | str |
| lossRunsPolicyNumber | The policy number. | str |
| lossRunsAgencyZip | The agency's ZIP code. | float |
| lossRunsSubrogationReceived | The amount of subrogation recovery that was recieved during the period. | str |
| lossRunsLossAddressZip | The ZIP code of the loss address. | float |
| lossRunsSalvageReserveBalance | The amount of salvage recovery that was received during the period. | str |
| lossRunsInsuredCity | The insured city. | str |
| agencyContactId | A foreign key to contacts, for the agency contact. | str |
| lossRunsLegalPaid | The amount of legal paid for the claim. | float |
| lossRunsClaimStatus | The most recent status of the claim. | str |
| lossRunsAgencyName | The agency's name. | str |
| lossRunsSalvageReceived | The amount of reinsurance recovery that was received during the period. | float |
| lossRunsPolicyTypeName | The name of the policy type. | str |
| lossRunsLossPaid | The amount of loss paid for the claim. | float |
| lossRunsAgencyNumber | The agency's number. | str |
| lossRunsLossDate | The date of loss. | str |
| lossRunsAgentName | The name of the agency or agent (whichever is tied to policies). | str |
| lossRunsAgencyStreetLine2 | The second line of the agency address. | str |
| lossRunsLossAddressCounty | The county of the loss address. | str |
| lossRunsInsuredName | The insured name. | str |
| lossRunsInsuredState | The insured state. | str |
| lossRunsAgencyStreetLine1 | The first line of the agency address. | str |
| lossRunsLossReserveBalance | The balance of loss reserve at the end of the period. | str |
| lossRunsClaimDescription | The claim description. | float |
| lossRunsLegalReserveBalance | The balance of legal reserve at the end of the period. | str |
| lossRunsInsuredZip | The insured ZIP code. | str |
| claimId | A foreign key that binds the claim to the peril. | str |
| lossRunsReinsuranceReserveBalance | The balance of reinsurance reserve at the end of the period. | str |
| lossRunsInsuredStreetLine2 | The second line of the insured address. | str |
| lossRunsAgencyPhone | The agency's phone. | str |
| lossRunsInsuredStreetLine1 | The first line of the insured address. | str |
| lossRunsLossAddressState | The state of the loss address. | str |
| lossRunsAgencyState | The agency's state. | str |
Claims Status Change
View additional information on Claims Status Change
Table 29 summarizes what’s included in Claims Status Change.
Table 29: Claim Status Change.
| Column Name | Description | Type |
| claimStatusFrom | The Revision Id attribute | type: str |
| claimStatusChangeDatetime | The Claim Status Change Datetime attribute | type: date |
| claimStatusTo | The Peril Name attribute | type: str |
| claimId | The Claim Id attribute | type: str |
| claimStatusChangeDate | The Claim Status Change Date attribute | type: date |
Claims Peril
View additional information on Claims Peril
Table 29 summarizes what’s included in Claims Peril.
Table 30: Claims Peril.
| Column Name | Description | Type |
| revisionId | The foreign key that binds nearly everything. | str |
| claimPerilAplusCode | Claim Peril Aplus Code | str |
| Claim Id | A foreign key that binds the claim to the peril | str |
| claimPerilLossCode | Cause number code listed for this claim | str |
| claimPerilLossCause | Cause name listed for this claim | str |
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.