Search results matching tags 'SSIS' and 'Elegant Design'

Little Data Remains Important in Healthcare IT

andyleonard — Tue, 30 Apr 2013 14:31:24 GMT

In his article Healthcare's Big Problem With Little Data, author Dan Munro raises salient points about the state of health-related data. Electronic Health Records (EHR) were promoted as the end-all-be-all solution for the industry – a standardization that, I suppose, many thought would organically and naturally occur, stabilize, and be maintained.

It hasn’t. At least not yet.

My doctor and I speak about this almost each time I visit with him. The corporation that operates his practice nowadays seems endlessly locked in cycles of changing billing and EHR systems in search of low-cost compliance and integration. They’ve (literally) spent millions of dollars and my doctor hates the interfaces forced upon him and his patients (well, one, at least) hates the complexity of the billing and patient records systems. Can’t these systems all just get along?

The result? Higher medical data management costs. I’ll give you one guesses who pays these costs.

Munro posits the following from his article:

By at least one estimate (here) there are now about 500 independent EHR vendors. Out of that large group is a subset of about 400 with at least one customer that has applied for Federal stimulus dollars through the labyrinthine process of meaningful use attestation. That would suggest a “first-cut” of about 100 vendors who made some commitment around certification – but have no reported customers (at least to date). That’s a staggering number of single-purpose software vendors for any industry to support – even bloated healthcare. The simple fact is it can’t. While there have been a few high-profile cases of EHR vendors shutting down, this last week was the first high-profile example of a vendor that was effectively decertified by the Feds for both their “ambulatory” and their “inpatient” EHR products. From the HHS.gov website last Thursday:

“We and our certification bodies take complaints and our follow-up seriously. By revoking the certification of these EHR products, we are making sure that certified electronic health record products meet the requirements to protect patients and providers,” said Dr. Mostashari.“Because EHRMagic was unable to show that their EHR products met ONC’s certification requirements, their EHRs will no longer be certified under the ONC HIT Certification Program.”

You may ask yourself, well, how did we get here? This, folks, is a mess. What’s missing? Applied standards.

“But Andy, you’ve told us standards slow down development!”

And I stand by that statement; standards do slow down development…unless you’re building interfaces. And then standards become the means for decoupled snippets, functions, methods, applications, and even platforms to communicate with each other. In some cases, we simply cannot be productive without standards – like TCP/IP. What would happen if everyone coded their own version of internet traffic? If that was the case, very few of you would reading this post.

Yes, standards slow things down. And yes, they are necessary to insure base functionality. In my humble opinion, we have to get this right with healthcare data. We simply must. While we see similar issues of data management across many fields, medical data is too important to mess around with; it’s (often literally) life and death. And it is certainly a high cost.

More to Consider

Standards exist. Administering and certifying 400-500 vendor solutions is hard.

Part of the Solution

From the actions of the Department of Health and Human Services last week, one can ascertain HHS is taking steps to address the matter. But will all 400-500 companies voluntarily congeal their schemas? Possibly, but doubtful.

My experience delivering US state Medicaid ETL solutions informs me there will be a need for data integration – regardless of the existence of standards and in spite of certification. Why? Standards are not static. The idea of de facto standards emerges from the life cycle of software because software is organic. Even if everyone agreed on the same interpretation of rigid standards (and they won’t), versions 2.0 through n.n will – at a minimum – add fields to the schema. And with additional fields comes additional data.

Standards will be revised when enough product schemas adopt the de facto, and this will drive the need for yet more integration. Don’t take my word for it, examine the entropic history of ICD-9 and ICD-10 codes – the direction of progress is more data, not less.

Learn More

This is one reason we at Linchpin People are focusing on Medical Data Integration. The recording of our first (free!) webinar about Medical Data Integration with SSIS 2012 is available here. Kent Bradshaw and I continue the series tomorrow presenting Medical Data Integration with SSIS 2012, Part 2 in which we focus on loading Provider and Drug data.

I hope to see you there!

:{>

SSIS Snack: Name Those Connections!

andyleonard — Thu, 09 Feb 2012 12:00:00 GMT

When creating Connection Managers in SSIS, take a moment to click the “All” page and set a value in the Application Name property. In SSIS 2008+, a default value is applied to this property. It’s useful but it contains a GUID. Yuck. I prefer to enter a more concise identifier like the one shown here:

“Why should I enter this information, Andy?” I’m glad you asked! When troubleshooting performance or locking / blocking issues, DBAs start with a query to ascertain what is currently executing calls to the SQL Server instance. As shown below, sp_who2 is one way to obtain a peek into the inner workings of SQL Server. Note the column labeled “Program Name” displays the value we configured into the Application Name property of the SSIS Connection Manager. This gives the DBA a fighting chance at isolating SSIS packages from other applications and jobs executing.

I use the following format:

[SSIS].<Package Name>.<Connection Manage Name>

The format you choose isn’t all that important. Being consistent is very important.

Happy Integrating!

:{>

How Many SSIS Packages Should I Create?

andyleonard — Mon, 28 Dec 2009 12:00:00 GMT

Introduction

I received the following questions (paraphrased) from a friend:

I'm building an ETL process with SSIS in which I pull from about 40 tables in DB2. I put the data into staging tables in a SQL Server database.

Is it better to have one package with 40 data flows, 40 packages with one data flow each or something in between? Or should there be one data flow with a lot of source->destination modules? What the advantages and disadvantages of each approach?

Excellent Questions!

The answer is: "It depends." That's one of the reasons you're having trouble finding a definitive answer online. It's not a bad question, so let me walk through the factors that would drive my design decisions:

First, are there dependencies? Are there foreign key relationships in the source database? Is referential integrity enforced (keep in mind there are non-database ways to enforce RI)? If there are dependencies or referential integrity exists (whether it's enforced or not), I load the parents first and then the children.

Second, I think about Operations: How much visibility do I want into this process? If it's going to take three hours to load, I probably want to break that down into several packages so I can at least see steps in the process completing. This will also assist in troubleshooting: "Package1.dtsx failed" isn't a pleasant message to troubleshoot if it contains 40 Data Flow Tasks. "Load Orders and OrderDetails.dtsx failed" is a much better starting place.

You've inspired another blog post - thank very much! Please keep the questions coming. And let me know what you decide.

:{> Andy