Over the past seven years, Ayad Shammout (@aashamout), Principal Business Intelligence Consultant at Beth Israel Deaconess Medical Center (a teaching hospital of Harvard Medical School), and I have worked on a variety of very exciting SQL Server projects including (but not limited to) Healthcare Group Upgrading to SQL Server 2008 to Better Protect 2 Terabytes of Data,  Healthcare Group Improves Availability and Security of Mission-Critical Databases, Healthcare Group to Enhance Information Access with Powerful Business Intelligence Tools, and SQL Server Reporting Services Disaster Recovery Case Study

We’ve worked on some pretty hinke ones including the infamous PowerPivot for SharePoint / Windows Authenticated Users group issue (more info at the post: Delegation, Claims, Active Directory…Oh My!…Aw Crap!).  Through it all, even though Ayad lives in Boston and I live in Seattle, we share a passion for solving complex problems using the Microsoft stack (that and Middle Eastern restaurants throughout Montréal).

Compliance is Important

And one of those problems is the scenario of how to ensure compliance of patient data in this ever more connected world.  For the release of SQL Server 2008 (which now seems ages ago), we had worked closely together on the SQL Server White Paper: SQL Server 2008 Compliance Guide to cover issues from securing the platform, separation of duties, encrypting data, auditing sensitive operations, and using policy-based management.  In addition to the sample scripts and tools for SQL Server compliance, we have an extensive case study detailing how BIDMC enhanced their auditing infrastructure to help it meet HIPAA compliance: Major Hospital Enhances Auditing Infrastructure using SQL Server 2008.  As well, we have a community codeplex project that contains all of the source code we used: Centralizing SQL Audit Logs.

With Big Data, Compliance is even more paramount!

But with all of consuming more time with even more data in all too many different formats, it became imperative that we updated our code base.  As we had hinted in our 24 Hours of PASS (2013 Spring) session Ensuring Compliance of Patient Data with Big Data and BI (you can stream it now by clicking on the link), we’ve created a great way to make sense of all of this audit data so its easier to understand, and we want to share it with you!

As you can see from the above diagram (and/or the streamed session Ensuring Compliance of Patient Data with Big Data and BI), we’re solving this problem by using a SSIS package that extracts the audit log data, store the data into Azure Blob Storage so that we can make sense of the data using HDInsight on Azure via Hadoop and Hive.  To provide fast interactive analysis of this data, we’re making use of PowerPivot, Power View, and SSAS Tabular.

Come Visit us at PASS Business Analytics Conference

If you would like to more, we invite you to join us (and many other experts from the community) at the PASS Business Analytics conference.  Ayad and I will be presenting at the session:

Ensuring Compliance of Patient Data with Big Data and BI [BDII-301-M]

Speaker(s):  Denny Lee  Ayad Shammout

Duration: 60 minutes

Track: Big Data Innovations and Integration

To help meet HIPAA and HealthAct compliance, Beth Israel Deaconess Medical Center (BIDMC) is expanding on the “Reaching Compliance: SQL Server 2008 Compliance Guide” to more easily handle larger volumes of unstructured data and to gain richer and deeper insight using the latest analytics. To achieve this, BIDMC is building a Big Data-to-BI project involving HDInsight, SQL Server 2012, Integration Services, PowerPivot, and Power View. This session will give you the architecture and details behind this project within the context of patient data compliance.

Hope to see you there!