Data - Warehousing A Legacy Collections System 

 

A Manual For Leaving Manual Entry Behind

Our client managed Accounts Receivable for over 200 different hospitals and health systems (groups of hospitals). A common "early out" A/R management engagement (a.k.a. project) followed this process: 

    1. The hospital performed the initial billing and collected the majority of payments in the first 60 days. Unpaid accounts after 60 days were then assigned to the client for resolution. The client received regular placements (batches) of aged accounts, and staff worked the accounts to maximize income for hospital customers. They used an off-the-shelf collections workflow system for tracking inventory and revenue on accounts. The system tracked detailed financial transaction data for only the current month.

    2. Transaction detail was purged at the end of each month. Sometimes the client had multiple projects for the same hospital, each treated as a separate hospital in the collections system. The legacy system had no data-integration capabilities outside of flat-file exports.

    3. Transaction detail and A/R data from the collection system were downloaded each month and used to manually update excel spreadsheets. Charts and summary tables were embedded in the spreadsheets and sent to customers. The process typically took two weeks to complete. Variations in source data and project configuration made it difficult to perform "apples-to-apples" comparisons based on industry-standard categories.

The Task:

Our client wanted to get a glimpse of their performance over time with each client hospital engagement — plus each hospital. At the same time, they wanted to assess the value of an engagement (the quality of the accounts assigned to them). Within their Accounts Receivable management service, key metrics included total A/R outstanding, cash collections and total write-offs due to bad debt. The client also wanted to share analytics with its own customers interactively, allowing them to dig into aggregated numbers and view the detail.

Questions We Asked:

  • What is the average age of accounts from bill date to when they were paid?

  • What is the average age of accounts from assignment date when paid?

  • What is the average age of accounts assigned by a hospital?

  • How long does it take to work down a placement of accounts to $0?

  • How much cash did a project/hospital/health system bring in since last year? Is it trending up or down?

  • For a given project/hospital/health system, what is the average number of times an account needs to be reviewed before payment is receive

 
 

The Cimetre
Solution

We built a secure website for our client, allowing for the presentation of performance data to both management and customers. By fully automating the data transfer process, the client reduced turnaround time of official monthly numbers from two weeks to just one day after approval. Now, our client’s customers are able to view daily performance data as needed, reducing the need for account managers to field questions and create ad-hoc reports.


 

Architecture:

  • Created a data warehouse using SQL Server RDBMS

  • Defined date dimensions to slice metrics by Day, Month, YTD, same time last year, and the last 90 days

  • Created conformed lookups to allow for comparison of common categories across hospitals

  • Introduced mapping of client projects to conformed organizational hierarchy, enabling summary roll-ups by hospital, contract/project, or hospital group


Data Sourcing/Loading:

  • Set up scheduled flat-file extracts of account snapshot and transaction data from the legacy system on a daily basis

  • Created SSIS package with file-watching function to pick up files as they are generated

  • Incorporated a "done" tracer file to indicate complete delivery of all daily files from the legacy system

Presentation:

  • Built a secure website that offered SQL Server Reporting Services reports

  • Provided configuration control panel, allowing users to define which reports are available for customer access versus internal use only

  • Provided ability to roll-up values at the Project, Hospital, and System levels

  • Enabled drill-down and drill-through functionality where appropriate


Products:

Microsoft ASP.Net, C#

Microsoft SQL Server

Microsoft SQL Server Integration Services (SSIS)

Microsoft SQL Server Reporting Services (SSRS)