BriteData is a business intelligence tool in which users can build and amend reports.

Core Concepts

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.

BriteData Reports

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

Operational System

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.

JSON

Some data is stored as JSON objects, which are hard to parse when querying.

Multiple Tables

Data for reports typically pull from various database tables, making some queries large and untenable.

Snapshots

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)

Performance

Analytic System

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

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

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.

Categories

Data Frame

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 lib/reports/utils.py.

Table Types

The tables within the data frames can be fact or dimension tables.

Fact

Fact tables contain the measurements. When querying, fact data is that which is aggregated, summarized, or subtotaled.

Dimension

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.

Organization

Star schema

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.

Granularity

A single day is the lowest granularity of data available in BriteCore.

Dates

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.

Single Date

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.

Date Range

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.

Report Types

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.

Important notes

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

Rule Order

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.

Start a Report

First Steps

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

Important notes

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.

Important notes

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.

Computed column

Allows you to create new columns based on mathematical expressions.

ExampleA 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).

Mapped Column

Similar to a conditional fill, this rule allows you to replace the value in a field with a pre-defined value.

ExampleYou 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.

Conditional Fill

Allows you to replace the values in a field that match certain criteria.

ExampleYou 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.

Filter

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.

Combine Text

Ability to combine two or more column names into one column by using quotes (“ “) in an expression.

ExampleYou 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

Download Dataframes

Navigate to Reports > Export Queue

Enter a date after your intended end date as yesterday's reporting data lives in a file under today's date. For example, if you want 1 July data, enter 2 July.

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 reportList with dfmap

Exclude Empty Values

To exclude certain data, use a Filter where the data does not equalEmpty.

Example

A CAT report is needed that only displays claims with an associated CAT. Add a Filter where the Cat titledoes not equalEmpty.

Custom Name an Exported Report

If a user selects Put in Export Queue or Export Automatically from the Export Options list when creating a new report in BriteData, the user can add an Export Filename. This field is optional. If this field is left blank, the report will use Report Name for the exported filename.

To add an Export Filename, complete the following steps:

In 1: Define your Report, select either Put in Export Queue or Export Automatically from the Export Options list

Add the custom filename to the Export Filename (Optional) textbox

Examples

Report_{%Y%m%d) would give Report_20180110 (the run date)

Report_{%RUN_DATE_Y%RUN_DATE_m%RUN_DATE_d} would give Report_20180110 (the run date)

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.revisionDate and 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.