Choose your language:

France
Germany
Hong Kong
India
Ireland
Japan
Malaysia
Netherlands
New Zealand
Singapore
Sweden
United Kingdom
United States

Building a Centralized Data Repository for an Insurance Administrator

INSURANCE | DATA SERVICES



Download PDF

TEKsystems helped our client integrate a new benefit management system by building a centralized data repository.

Established in 1979, our client is a joint powers authority that provides stable, cost-effective insurance solutions for state government employees in the western United States. Their system collects and manages nearly $1 billion in monthly premiums.


SQL Server 2008R2/2012, SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SharePoint, Team Foundation Server, Visual Studio


Core administration systems are critical to operations, business continuity and an organization’s bottom line. To effectively meet the needs of today’s fast-moving marketplace, these systems need to be flexible, scalable and responsive. While business leaders recognize the efficiencies of newer, modernized systems over legacy systems, the transition between the two is a challenge—namely ensuring historical data is moved accurately and reporting capabilities are available without downtime to operations. Without support, this obstacle can leave organizations tethered to legacy systems and unable to meet business demands or grow their business.

State and local government agencies are investing heavily in legacy application replacement and modernization—yet with limited budget and resources, they must be fiscally prudent. Direct cutover from a homegrown, legacy solution to a new, out-of-the-box solution system is unlikely—organizations need to understand the time and resources required. A pilot program, where systems are run simultaneously, can ensure all the capabilities from the old system are fully functional in the new one and that information is being properly migrated before retiring the legacy system.


Our client is a joint powers authority that administers benefits to public school employees for one of the largest states in the U.S. Since they are an insurance provider for teachers, both school districts and insurance companies use the client’s insurance administration system to retrieve or send data (e.g., eligibility information) daily. The system is responsible for more than $1 billion in monthly transactions, making it a business-critical system for our client.

The client had been administering all its products (e.g., healthcare insurance, retirement benefits) on a homegrown Microsoft Access / SQL Server system for over a decade. Generating reports was labor-intensive and the system had become very complex and difficult to maintain and enhance. Moreover, it limited which products the client could offer their customers. The client had grown significantly and the system could no longer support the needs of a much larger—and still growing—organization.

The client purchased a new insurance administration system to support key business processes and replace the legacy system. They purchased the system from a third-party software vendor. However, since the legacy system possessed a significant amount of historical data and had become very large and complex, a straight cutover to the new system was not possible. In order to ensure business continuity and minimal loss of productivity, the client needed a thorough validation period where a group of internal customers could be live on both systems.

The client began the integration project on their own and identified several issues with the new system’s reporting and analysis functions. They realized they would need to expand the functionality of the new system in order to support new products and build in existing functions from the legacy system (e.g., operational and strategic reports). The new system also had a complex data model and back-end processes, meaning changes and enhancements would be challenging for the client’s internal team and costly to implement through the vendor.


Our client uses the county superintendent’s office for various IT services. When the project reached an impasse, the client contacted the superintendent’s CIO for support. Based on a strong relationship with the CIO and successful delivery on previous projects together, the CIO engaged TEKsystems Global Services® to perform a current-state assessment with recommendations for future state, as well as a roadmap and a detailed plan for the early phases of the program.

Assessment and Solution Design

An architect from our Data Services practice provided the client with a detailed assessment that uncovered several issues that would prevent the new solution from ever being implemented. He developed a tactical solution that would make the new system the “system of record,” while keeping the legacy system for reporting and use of various interfaces. The customized data structure would be able to support operational reporting for day-to-day needs (e.g., generating a bill), as well as analytical reports that depict trends and progress (e.g., growth rate by product for the past few years)—critical for strategic planning.

To address the gaps between the new and old systems, we recommended creating a centralized and locally managed data repository that would be fed from various internal and external systems. This would help reduce dependency on expensive customizations from the vendor and increase flexibility for reporting and analytics.

We proposed a solution involving the following dashboards, technologies and tools:

SQL Server 2008R2/2012: This would act as a database and complete business intelligence (BI) solution.

SQL Server Reporting Services (SSRS): This would be used to develop standard reports—per a schedule or on demand—that could be delivered via multiple channels and in various formats.

SQL Server Integration Services (SSIS): This would allow a development team to build transformative packages that meet a multitude of data needs.

SQL Server Analysis Services (SSAS): Microsoft’s online analytical processing solution would be a backend to Excel, Power Pivot and SSRS, among other solutions. It would support complex analysis—including write-back capabilities for financial planning and complex statistical functions—as well as have predictive analytic capabilities.

SharePoint: Microsoft’s portal and document management solution would integrate with SSRS, Excel and other Microsoft tools. It would be the container for all BI presentation content.

Team Foundation Server: This project management and source control solution would integrate with SharePoint and Visual Studio and be the focal point of all Microsoft BI development.

Visual Studio: This would be used for .NET development to customize and extend operational applications.

While TEKsystems was initially engaged for an assessment and solution design, the CIO was impressed with the thorough understanding of the issues expressed in our deliverables, and decided to retain our services to successfully complete the execution.

Execution

We built a data warehouse that pulls data from multiple systems into a central repository to enable required analysis and reporting. We utilized a managed services delivery model that combined flexibility, practice support and engagement management. An engagement manager provided project management and oversight, and the practice architect who designed the solution acted as an advisor and technical lead to support key decisions. The core technical team consisted of two on-site ETL developers skilled in SSIS, SSAS, SSRS and SQL Server development. We brought on Microsoft Access / BI developers to augment the team and support gaps when necessary.


TEKsystems’ support has helped our client meet day-to-day data and reporting demands while moving this business-critical system transformation project forward. We helped our client deploy a robust insurance administration system. We implemented a synchronization process between the legacy and new system that runs daily and monthly. By automating various aspects of loading rates, data migration and reporting, TEKsystems allowed our client to move to the new system without having to sacrifice key functions or the ability to combine historical data from the legacy system with new data. We also created the data warehouse server and are currently building the staging area with the help of a part-time TEKsystems database administrator.

Several districts are now live on the new system—and we are close to transitioning the entire portfolio of districts. Deployment of new districts onto the new system has allowed our client to offer new products to their customers that the legacy system was unable to support.

In addition to the in-scope work, we provided added value to our client by helping mitigate the risk of future costs. Since the system’s third-party vendor charges for support and changes, we encouraged them to utilize application program interfaces (APIs) supplied by the vendor. Specifically, we emphasized the value of purchasing an API that synchronizes their financial information with the vendor’s system, meaning if the vendor alters its data model, they will be required to use the API to push data into the new model, sparing the client the trouble and expense of updating any customizations. Since the client will always look at the same interface, they have insulated themselves from any negative impact from future changes from the vendor.

Our managed services delivery model helped us establish a key partnership with the client, providing a channel for “just-in-time” architectural oversight as well as a broad technical resource pool to support a wide range of services. We were able to help our client with additional projects, including electronic data interchange (EDI) eligibility imports and exports.

This project is ongoing as we work toward a long-term vision of replacing the reporting and analytical functions of the legacy system with a data warehouse and presentation layer, and comprehensively addressing the additional functionality not covered by the new system with custom development or off-the-shelf software.

Trusting partnership

We have built a solid, long-tern relationship with the client team and the superintendent’s CIO. Our proven track record of technical guidance and ability to deliver on our word, has led to us receiving multiple add-on projects and extensions.

Flexible delivery model

We established a key partnership with the client’s technology services organization, providing a channel for “just-in-time” architectural oversight and a broad technical resource pool that takes advantage of our strengths as both an IT services and IT staffing provider and allows us to support a wide range of services. For example, when one of our client’s billing resources fell ill, we stepped in to help document how billing would take place to ensure over $1 billion in monthly transactions would be handled correctly. We successfully ran the billing operation for a month while their resource was out, which the client greatly appreciated.

Strong, stable technical team

Our core ETL developer has been aligned to the project since the beginning (over two years). During this time she has retained and documented a significant amount of organizational and technical knowledge of the client’s systems, and has served as a critical part of onboarding new members to our team.