BriteData is a business intelligence tool in which users can build and amend reports.
BriteCore Stock Reports vs. BriteData Reports
BriteCore Stock Reports
Reports available to all clients that retrieve data from the database via specific MySQL queries. These reports meet an insurance company's basic premium, billing, payments, and claims reporting needs. Amendments to these reports occur by changing the MySQL query in code; therefore, changes are limited to IWS staff or client engineers. Rather than updating the queries on a per request basis, resulting in many variations of the same stock report, these reports are being migrated to BriteData for client customization.
Like stock reports, data for these reports is retrieved via a MySQL query. However, rather than being a specific query, these queries pull all data related to a given domain, like claims, policies, or accounting. Then, code is applied to the data in preparation for business intelligence use. Finally, user-defined rules are applied to further transform the data to a user's specific needs. In other words, users can build and manipulate reports on an as-needed basis. The core concepts that follow describe this process.
Operational and Analytical Systems
BriteCore is an operational system that helps clients process insurance contracts. In this type of system information is added, changed, or removed within a MySQL database as snapshots in time. The MySQL database processes operational data well but is not suited for transactional reporting for at least four reasons.
Some data is stored as JSON objects, which are hard to parse when querying.
Data for reports typically pull from various database tables, making some queries large and untenable.
Data is stored as snapshots in time rather than as transactions. This paradigm makes it difficult to accurately reconstruct a history of transactions. For example,
- Policy 10-2017-1 is written for $500
- Mid-way through the term, the written premium is endorsed to $600
- BriteCore stores the premiums of $500 and $600 rather than the transactional difference of the two ($100)
Running reports directly against the MySQL database can cause performance issues.
By contrast, BriteData is an analytic information system, which evaluates or measures business processes. In this type of system, information is retrieved rather than added, changed, or deleted. Data is extracted from BriteCore's operational MySQL database and reorganized to make the data easier to report against.
ETL stands for extract, transform, and load. It describes the process of extracting data from BriteCore's MySQL database tables, organizing it into a standard format (e.g., moving data from a JSON object into individual columns), and applying business rules. The end result of this process is the creation of data frames.
Data frames contain data from one or more database tables. At present, BriteCore produces two categories of data frames. The dataframes are stored as CSVs to allow manipulation via other tools such as Excel and Jupyter.
The initial extraction of data creates data frames, which are defined in the
BriteDataFrame class of
lib/reports/utils.py. A few business rules are applied to these frames but for the most part they are extracts of database tables. For example, deleted revisions (
revisions.deleted=1) are automatically excluded from the
revisions data frame as deleted revisions are not reportable data.
Prepared Data Frame
Applies reporting logic to the data frame in preparation for BriteData. These are defined in the
PreparedDataFrame class of
The tables within the data frames can be fact or dimension tables.
Fact tables contain the measurements. When querying, fact data is that which is aggregated, summarized, or subtotaled.
Dimension tables provide the context for the measurements. When querying data, the dimension table serves as filters or groupings.
Data Within the Tables
The data within the data frames meet all reporting needs. View the complete list of data points.
The fact and dimension tables within a data frame and prepared data frame can be organized. Dimension tables are grouped around fact tables, which results, loosely, in an image of a star.
A single day is the lowest granularity of data available in BriteCore.
Date entry is specific to the data points you choose. If you choose data that only makes sense in a range (e.g., written premium) then BriteData prompts you for a range. If you mix range and point-in-time data, the point-in-time data pulls at the end date of the range. To get the point-in-time (a.k.a. "as of") prompt, you can only have numerical columns from the point-in-time data set.
If the report takes a single date then the End-Of-Day, or 11:59:59 p.m., of the report's run date is referenced.
Point in time reports need a hint to know what fact table to pull from. For example, you need a report that shows the policy status as of a given date. Use the Policy Inforce Premium, a numeric data point (fact), along with the Policy Status, a non-numeric data point (dimension). Then, when you enter the date, you will receive data that was true on that date.
If the report takes a date range then the first date is Beginning-Of-Day and the second date is End-Of-Day. In this way, all data is reported within the date range.
BriteData can create XLS, CSV, PDF, HTML, TXT, and XML reports. When using PDF, HTML, TXT, or XML, a template can be built using the Jinja templating language.
Organization of Reports
You may have a large number of reports, so establishing a good organizational structure is helpful. When you navigate to Reports > Report List, you will see default categories such as Agency, Billing, Premium, and Claims. Within these categories are the stock BriteCore reports. Another category, Customized, are pre-loaded BriteData reports.
- Once your departments are (nearly) finalized during the BriteCore implementation, rename the report categories according to those deparments
- When creating a new report, make a new report category with the user's name. In this way, users can keep track of his/her reports. For example, your new category could be John Smith's Reports. Once a report is finalized, move the report to the applicable category
When building a report, the third step, Define Rules for your Data, allows you to apply rules, such as filtering and transforming data, removing columns, and performing calculations. The order in which the rules are listed is the order in which the system will apply the rules. The exception is the Remove rule, which is always applied last.
Don't Forget Other Tools
Your BriteData report is 95% complete but you hit a roadblock. When this occurs, remember tools like Excel can help you complete the report. For example, if you are having trouble applying a filter in BriteData, run the report, and then use Excel's filter function. There are a variety of online tutorials to help you brush up on your Excel skills. One helpful resource is Excel Exposure.
Certificate of Mailing
Identify Deliverables that Should be Sent by Certified Mail
- Navigate to Settings > Deliverables
- Check the This document must be sent by certified mail box under each applicable deliverable
Configure the Report
In order to configure the report, notate the cost of postage in your area and the certificate fee per parcel:
- Navigate to Reports > Report List
- Type Certificate into the search box
- Click Search
- Click on the report name
- Click Edit Report
- Click 2:Choose your Data
- You will see a number of data points. Scroll to the bottom of the data list
- Enter the Mailing Certificate Fee amount per parcel
- Enter the Mailing Postal Cost amount per parcel
- Navigate back to the top of the screen
- Click Finish
- Click Save Report
Run the Report
See the Run a Report how-to below.
After a weekend or holiday, run the report for the weekend or holiday dates to capture deliverables to be sent by certified mail.
This report allows carriers to audit unwritten applications. See the Share a Report how-to and access the Unwritten Applications report, which can be amended to suit your specific needs.
See the video below.
Start a Report
- If you do not have a dedicated category for the report, create a new report category in Reports > Report List > Add Category. If you already have a category for the report, skip to the next step
- If you want to copy an already existing report, click Add Report on Reports > Report List > Add Report; if not, either click the No button or click directly on the BriteData tab in the Reports module
- Click Lock Report to restrict other users from editing your report
- Complete the form
- Click the question mark icon for additional information on each section
- Use a short Report Name such as Homeowners Endorsements, Deductible Profile, May 2017 Tornado, or Reinsurance Data Call
- Use the Description to comprehensively describe and update the status of the report. You may know the purpose of the report now, but when you review it a year later, you may not recall its intended purpose. Also, you may start a report one day but not complete it until a few weeks later. Use the description to remind yourself where you left off the last time you edited the report
- This report was requested by John Smith at Mutual Reinsurance in an email on 8/1/17. In order to provide the most competitive reinsurance rates, he needs a report that shows risk details (zip, county, latitude and longitude), form, occupancy, protection class, construction type, year of construction, and limits/premiums broken out by coverage. The report is due 10/1/17. I started this report on 8/15/17 but need to add occupancy and protection class
Build a Report
Building a report consists of two or three steps, depending upon the type.
Choose Your Data
Data is categorized as logically as possible in major headers such as Accounting, Claims, and Policies. You can search within each category or use the Data Choices search box. Click on the
i icon to see a definition of the data point. Drag and drop each data point into the green data field.
- Categories and supplemental questions are captured using the Item Question data point
- Once you move a datapoint to the data field, you can rename it by clicking on the data point title. For example, if you use the Item Question data point to pull Protection Class then rename Item Question to Protection Class
- There is a difference between the Primary Policyholder and Policyholder data points; the former references the first named insured whereas the latter references all named insureds. When using reports to sum numerical data, use the Primary Policyholder data points. Doing so limits the result to one entry per policy, and mathematical operations performed on the numeric data will equate correctly. If the more generic Policyholder data points are used in combination with numeric data, there will be an entry in the report for each policyholder on every policy and the numeric data will be duplicated. Therefore, any mathematical operations performed will not be accurate since duplicate records exist. The most common use of the Policyholder data point is to obtain a list of all policyholders where no mathematical operations are needed
- System tags are data points within BriteData. Search using the keyword Tags and you will see all available options. BriteCore evaluates tag values, in regard to BriteData, once every 3 hours for better system performance. If tagged items are tagged or edited, those tags may not be immediately available for selection in BriteData
Define Rules for Your Data
You can apply various rules to the data. This is similar to applying rules to data in a spreadsheet.
As a general rule, start with no filters, run the report, and then add one filter at a time to make sure that you're only filtering out rows that you don't want.
Allows you to create new columns based on mathematical expressions.
A Policy Renewal report will look at policies renewing X days into the future. You can use a mathematical expression to customoize how far into the future you want to review renewals. On the Choose your Data section, add the Report Start Date and Report End Date data points. Your setup will then look like the below. You may also download an example of the Policy Renewal report from the Share a Report section below.
Then, use Filter to complete the report.
If a report requires a Date Range, users will want to use (Report Start Date + X) and (Report End Date + Y) for the date filters since the report will have two date inputs. If a report requires an As Of Date, users will want to use (Report Start Date + X) and (Report Start Date + (X+Y)) for the date filters because the report will only have one date inputted by the user to calculate off of (there will not be an end date specified).
Similar to a conditional fill, this rule allows you to replace the value in a field with a pre-defined value.
You have a report that is pulling the month as a number. You would prefer to represent the month as a name rather than a number. Your setup would look like the below.
Allows you to replace the values in a field that match certain criteria.
You need to report data within an Other Structures Limit column but you need to target specific line items. Your setup would then look like the below.
If you rename a column, ensure you reference the renamed column in the conditional fill.
Allows you to filter data just like you would on a spreadsheet. For example, a report is needed for Coverage A - Dwelling only. To accomplish that, you would filter out every line item but Coverage A - Dwelling.
Filters are case sensitive.
This video (2:08 minutes) explains the Group By concept.
Allows you to order report rows in ascending or descending order. Click on the icon arrow following the data point name. This is similar to a spreadsheet's Sort.
Allows you to remove a column(s) from the final report. This rule is always the last to be applied.
If a report contains duplicate rows undesirably, this rule will remove them.
Design the Template
If the report type is PDF, HTML, TXT, or XML users may further define the report using the Jinja templating language.
Run a Report
- Navigate to Reports > Report List
- Type part of or the entire name of the report into the search box
- Click Search
- Click on the report name
- When applicable, enter a date or date range
- Click Run Report
If the report does not generate immediately, you will receive an email when it is complete. The report will be accessible in the email or in Reports > Attachments
Share a Report
Clients can build and share BriteData reports.
Share a Report
- Build your report in BriteData
- On the Finish tab, click the Download Report button
- Upload the report to this folder
You may need to add the folder to your drive first in order to upload the file. When you access the folder, in the upper-right
- Click Add to Drive
- Click Open with Drive
- Upload the report
Access a Report
- Visit this folder
- Click on a report
- Download the report
- In BriteCore, go to Reports > BriteData
- Navigate to the Define your Report tab
- Add a Report Name
- Select a report Category
- Click the Upload Report button
- Upload the report
Ability to combine two or more column names into one column by using quotes (“ “) in an expression.
You need Agency Name and Policy Type Name returned as one value in one column instead of separate values in two columns. Using a Computed Column rule, your setup would look like the below.
Schedule a Report to Automatically Run
Only BriteData-created reports can be scheduled. Stock reports, other than those automatically run at month end, must be run as needed:
- Navigate to the Reports module
- Search for the report
- Click the report's name
- Click Edit Report
- Scroll to the Run Report Automatically section
- Select from the dropdown list
- Scroll to the top of the report builder
- Click Finish
- Click Save Report
- Navigate to Reports > Export Queue
- Enter a date prior to today. Today's dataframes are incomplete until today's business activity has finished
- Click View Available Files for Selected Date
- Check the box for each desired dataframe, or click Select All to select all dataframes
- Click Process X Selected Files at the top or bottom of the page
- The file(s) will process
- When processing is complete, click Download Files
- Save the ZIP file to your computer
- Click on the file to unzip it
- Click on the folder that is created
- Access the CSV file(s) with a program like Excel
View Data Frames
Data frames are viewable via a direct URL:
- Login to your BriteCore site
- Navigate to Reports
- In the URL, replace
Exclude Empty Values
To exclude certain data, use a Filter where the data does not equal Empty.
A CAT report is needed that only displays claims with an associated CAT. Add a Filter where the Cat title does not equal Empty.
Why am I not receiving an email when a report completes?
- Ensure your contact in the Contacts module has an email address assigned
- For month-end reports, ensure your email is entered in Settings > System Wide > Administrative Alerts > Month-End XLS Reports
Once a report is locked, can it be unlocked?
As an engineer, should I use data frames or prepared data frames when writing reports?
Each method has tradeoffs. The prepared data frames apply reporting logic for the standard business user. This logic was derived from years of user story research with our current clients. If you have a specific reporting case, the applied logic may inadvertantly filter information in a way that does not meet your reporting needs. For example, our client community agreed that when reporting against policy revision data, the greater of
revisions.commitDate should be used. One client, though, needed the logic to reference
revisions.commitDate only. Rather than change the prepared data frame logic, the client applied their specific logic to the data frame.
Report unclear or missing documentation.