Data Warehouse: Medical Information

A firm providing professional healthcare services to a large number of community hospitals asked B–Three to propose a solution for efficiently generating its internal reports.

Pre–Proposal Analysis
The client firm receives a substantial volume of medical data each day.  Initially received in XML format, the information is translated into fields and records and stored in a transactional database.

During the pre–proposal analysis, B–Three determined that a data warehouse, structured to optimize generation of reports, would be the key component of the solution.  B–Three would also develop six internal reports to the client firm’s specifications.

Data Security — HIPAA Compliance
Having completed other projects for clients in the healthcare field, B–Three is familiar with the strict requirements for privacy and data security mandated by the Health Insurance Portability and Accountability Act (HIPAA).

On this project, to ensure compliance with HIPAA, B–Three imposed a number of restrictions upon its software development procedures, including the following:

  • All project–related tasks requiring use of actual data were performed on–site at the client firm, using the client firm’s machines.
  • No actual data was copied to B–Three machines.
  • No actual data was taken off–site, either electronically or by hard copy.
  • Project–related tasks performed off–site either utilized no data (examples:  building ETL routines, preparing documentation and training materials) or utilized test data (example:  testing ETL routines).

Software Design and Development
When the project got underway, B–Three designed and built the data warehouse — using SQL Server and SQL Reporting Services Server (SRSS) — and installed it in a testing environment.  B–Three then began to develop Extract/Transform/Load (ETL) routines to populate the data warehouse with records from the transactional database.

While the work on the ETL routines was going on, B–Three began to develop the six internal reports specified by the client firm.

On each new report, B–Three worked with client personnel to test the report and confirm that it met the firm’s specifications.  As this process went on, B’Three also prepared the documentation describing how to add, edit, and administer the reports.

When it came time to put the data warehouse into production, B–Three modified the firm’s daily processing routines, so that the data warehouse would be automatically updated from the new records that are added to the transactional database.

With the roll–out of the data warehouse and the six new reports, B–Three wrapped up a project that provided an efficient and cost–effective internal reporting system for the client firm.

Soon afterwards, the client firm followed up on the project by contracting with B–Three for 13 additional internal reports to be run against their data warehouse.