A data warehouse helps an ancillary benefits management company improve client reporting — a key to maintaining its market leadership.
In just three months, West Monroe helped the company establish a data warehouse to provide employees with current, accurate and consistent information.

The client is an ancillary benefits management company. Through its comprehensive network, customer service support, and claims management services, the company assists health benefits plan sponsors expand the range of provider choices available to their payers while reducing overall ancillary benefits costs.

A centralized data repository to improve reporting.
Experiencing a period of rapid growth, the client added many new companies—and, in particular, larger companies—to its roster of customers. To deliver the value these customers expect, the company must be able to produce clear, compelling reports that document potential cost savings.

To produce these reports, employees utilized separate billing, provider, and payer systems—each of which wrote similar types of information to independent underlying databases  within the company’s SQL Server environment. This duplication of data produced some inconsistency in reporting, depending on the database used to develop a particular report.

The client sought to develop a centralized repository for all of its data—one that would enable the company to:

  • Use a common data repository across all of its key applications
  • Improve the consistency of its client reports
  • Help users quickly and easily create customized reports as their needs change

For assistance, the client turned to West Monroe Partners, which offered extensive experience in business intelligence—including data warehouse development and customized reporting needs.

Current, accurate, and consistent data—every day.
The West Monroe Partners project team began by:

  • Analyzing the client’s business
  • Understanding users’ reporting needs
  • Developing a new data model based on those needs

In the course of completing these analyses, West Monroe Partners recognized that the client’s employees referred to common data elements by different names. To address this issue, West Monroe Partners worked with the organization to develop a new and consistent naming convention, or “data dictionary,” for all key terms. Then, the project team:

  • Developed the new data model, or data warehouse, using a star schema
  • Developed and implemented an Extract, Transform, Load (ETL) process to pull data from existing systems, “clean” it, and load it into the target data model
  • Built “cubes” that would enable individuals to access the data using Excel pivot tables, which are commonplace in this industry; the cubes allow users to drag and drop data to pre- aggregated measures, such as “amount invoiced to provider,” and to split measures by dimensions such as time, geography or specialty
  • Developed technical and maintenance guides for database administrators
  • Created user guides for key executives and cube users