One of the myriad of new requirements tucked inside the Affordable Care Act is for healthcare service providers to implement strategies to reduce the number of inpatient readmissions, which in many cases are deemed to be costly and indicative of poor quality of care.
One way to drive such a reduction strategy is to enable analysts and providers with business intelligence tools that put various readmissions metrics at their fingertips. Additional value is garnered when those metrics can be filtered, sliced, diced and compared against a number of useful dimensional attributes. Developing and automating such tools helps business users avoid having to write monotonous queries, piece together disparate data from various sources, and manually compile things like month end readmission rates.
To accomplish this goal at a recent client engagement, as a member of a larger Perficient consulting team, I chose to build a Microsoft SSAS Tabular Model, a new feature of SQL Server 2012, paired with Power View to enable a self-service BI visualization layer. Additionally, this particular client chose to leverage the fairly new Epic Cogito Data Warehouse (CDW), and thus the semantic and visualization layers were built on top of that existing data model.
A tabular model was chosen in lieu of an OLAP cube for a few reasons. The engine that runs tabular models is columnar based and fully in-memory. In short this means queries execute extremely fast. Additionally, tabular models tend to be simpler and faster to develop than cubes, which is good for future maintenance and extensibility. Finally, tabular models offer the bulk of features expected from a multidimensional data source.
Early client conversations were organized into 3 main topics:
- Measures: what are all the various types of calculations needed regarding readmissions
- Dimensional attributes: what are all the ways the client would like to filter, slice, and aggregate these calculations
- Security: who are the various audiences that will access this data, and what should their level of granularity be, especially regarding HIPAA compliance, patient and provider level detail, etc
Once I felt confident with the requirements I’d gathered, I started reverse engineering the Cogito DW to figure out what fact and dim tables I would need to leverage.
Inside the tabular model, I decided to go with two customized fact tables: one at the encounter/admission grain, and one at the readmission grain. This second fact table involved self-joining encounters back onto themselves on patient id, and building some date logic to include only those patients readmitted within 30 days. From there, I developed around 40 various DAX calculations that performed rollups in different ways. Examples include various permutations of: admission counts, discharge counts, distinct patient counts, all cause readmissions, cause readmissions, readmission rates, non-readmitting discharges, and readmission percent of total.
For dimensional attributes, many existed as part of Cogito DW natively, and it was just a matter of trimming records down to inpatients and newborns only. Examples of such dimensions include: date, department, patient, provider, coverage, primary diagnosis, drg, billing account service profile, and admission profile.
Some dimensional attributes were trickier, however. Some were not part of native CDW, and therefore had to be added as extension tables first in the data warehouse. Some, such as all discharging diagnosis, had many-to-many relationships to fact records, and therefore had to be massaged into comma-delimited lists that became 1-to-many related. Finally some attributes incorporated extensive business logic, for example Unit, which was based on an AdmissionDischargeTransfer fact table not part of native CDW.
The final security implementation could likely be an entire blog article on its own, but in short, the strategy involved: slightly customizing fact tables for different audiences, creating limited attribute dimension tables, varying relationships per audience, limiting certain calculations, and leveraging perspectives for an overall clean user experience.
The final readmissions tabular model was leveraged via SharePoint in multiple ways: Power View dashboards we developed along with the “self-service” capability for business users to create their own dashboards, SSRS integrated reports, and direct model browsing in Excel with classic pivot tables. The platform was very positively received by our client, and I performed a series of trainings to enable members of their internal IT to build tabular models on their own.
