To get a “game plan” developed, the first steps were to interview Trean’s IT Data Team to better understand their current processes and databases, as well as what they would like to see for an overall solution. Prior to SCS working with this team, they worked with stakeholders throughout the company to define a list of measures and attributes they would like to see in reports. With the list of fields, SCS consultants also reviewed the 10 most frequently used reports, gathering additional fields stakeholders had not included in their list. The fields were mapped to the physical database tables and fields, to find the true source of the data. Doing so provided the scope for the project, with a solid idea of which tables needed to be exported from their current reporting database into a new operational data store (ODS), before being loaded into the data warehouse.
Based on the requirements, the project was split into two phases, one for their workers’ compensation business and the other phase for the remaining business. From a data perspective, the majority of their data is for workers’ compensation policies and claims, and it is stored and calculated differently than the other lines of business. Two SSIS solutions were created to load the ODS, one loading data from their existing system, which has a lot of data validations already in place. The other SSIS solution loads data from XML files into the ODS, including some data cleansing and then saving the data for historical purposes.
The next step was to create a data mart for workers’ compensation data. For about half of the premiums and claims, dollar amounts associated with them are sent in the XML as an accumulated amount, generally for the life of the policy or claim. The remaining data sent from other systems is for just the last completed month. As the data is loaded into the data warehouse, the accumulated amounts are re-calculated, removing previous months, so it is just the incurred amounts for the current month. By transforming the data once, as it’s going into the data warehouse, all of the incurred amounts are consistent, simplifying the report queries and future analytical processes.
The second phase of the project was to implement the same solution for the remaining lines of business, with data going into a separate ODS and data mart. The approach allows the focus of the ETL development to be on part of their business, and not have to account for every case in one spot. While it takes longer to implement, the overall ETL logic is simplified, as the developers do not need to account for conflicting rules between the two groups of business lines. While there are some differences in the design of the data marts, they are similar, making it reasonable to find the same information in both marts.
The final piece of the puzzle is the Enterprise Data Warehouse (EDW), combining the data from the two data marts. Once populated, the EDW gives users a complete view of the policies Trean Corporation manages. For example, users are able to see if a particular type of policy is exceeding a loss ratio threshold or not, the what percentage of their overall portfolio is defined by that type of policy, and much more.
