Overview

This utility automatically extracts the data from selected assessments into either CSV files or SQL tables.  The resultant file is a flat, normal, easy to use file, which can then be easily used by normal reporting or Business Intelligence tools. It also provides a SQL statement that can be used to create a SQL view to access the data directly, rather than through an exported copy.

Details

The utility consists of two components.  The “front end” component allows you to select which assessments or pages you want to extract, and allow you to select which items from those pages you want.  It also allows you to choose to output that assessment to either a CSV file or to dynamically create a SQL table.  The column headers for the output file are based on the labels from your form and you can be overridden in the extract definition. 

The output data will automatically lookup and expand all radio buttons, checklists, and selection list items as well as narrative text content.

Checklists are expanded to multiple columns, one for each item, and the data contains a “Y” or “N” depending on if the item was checked.

It also has the option to save the SQL statement that it uses to a file so that you can create a SQL view from it and pull data live at any time.

The other component is the “engine” that actually runs the data extract.  The engine can be run automatically each night via Windows Task Scheduler to refresh the data extracts, or it can be interactively run from the “front end” program.

Use Case – On going reporting for assessment data

Accessing CBH Assessment data is very complicated because the data is stored in many tables.  This program removes the complexity by finding all of the data and putting it in a single file. 

This makes it very easy to do reports or data analysis using business intelligence tools like PowerBI or Tableau because those tools simple access that single file.

This tool can be run automatically on a daily basis to refresh the extracted data tables so that your reports are always based on data that is current as of the prior evening.

This process makes responding to data requests a minor task rather than a multiple hour task for an experienced data person.

Use Case – Data Migration

This tool can also be valuable when you are migrating to another EHR software package.  This tool can be used to pull all your assessment data into a “normal” SQL database for migration or simply for historical reference without having to keep CBH up and running.  It can also be used to generate the SQL statement needed to pull all the data and look up values.

SAMPLE SQL AND DATA VIEW

See the attached PDF which includes the information above, but also includes screenshots of a step-by-step walk-through of the process.