Search results matching tag 'ETL'

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!

:{>

ETL is dead, long live AP2 ?

jamiet — Fri, 15 Feb 2013 15:45:33 GMT

Three days ago I posted What would a cloud-based ETL tool look like? where I wondered out loud about the sorts of tools data integration dudes like myself would be using in the future. I got some good feedback and already have a list of “stuff” to go and look at including:

Boomi – They claim 1million cloud integrations (whatever one of those is) per day
AWS Data Pipeline – A web service that incorporates a scheduler, a workflow engine and (as the name suggests) a data pipeline engine
Informatica Cloud – An extension to Informatica’s market-leading PowerCenter for SalesForce.

Most interesting to me though was a link that Joe Harris provided to a a blog post by Mike Reich entitled Rethinking ETL for the API age. Mike outlined a number of points that really struck a chord with me; the key one was his message that the Extract-Transform-Load (ETL) mantra that has been trumpeted for years should be replaced by something that is more pertinent for “the cloud” – Mike offers Acquiring, Processing and Publishing (AP2) as a new acronym (we all love acronyms, right?). The idea of publishing data rather than loading it really resonated with me as making data easily available in non-proprietary formats so that people can consume it in whatever manner they choose has long been an interest of mine.

Here are some other bulleted thoughts that came into my head as I read Mike’s blog post:

“Flows are fluid and flexible, unlike structured, point-to-point ‘pipelines’” – My interpretation of “fluid and flexible” is that these “flows” can be plugged together to create a greater whole. This gives rise to the notion of composability; imagine being able to leverage flows that other people have constructed in your own flows. Yahoo Pipes (which I first blogged about almost five years ago in Taking Yahoo Pipes for a test drive) was an early incarnation of this notion of composability and is a great demonstrator of what the future holds for us.
Composability further gives rise to the notion of a marketplace where one could sell “flows”. For example, one could build a flow that aggregated data for a given search term from both Google and Bing, deduplicated the results then made them available as a single feed; expose that feed via a marketplace and charge on a pay-per-use basis. Its a simplistic, contrived example but in my opinion aptly demonstrates the opportunity here. I think data marketplaces, perhaps more pertinently data integration marketplaces, are going to be huge, I really do. Given the technology agnostic nature that is being proposed here these marketplaces would be totally interoperable too, unlike the hateful app stores that today’s xkcd expertly satirises.
“by using APIs to move information around, we decouple the data from the underlying technology and vendor” Absolutely true. An API is essentially a well-understood interface/abstraction over a proprietary data store so really there’s nothing new here (isn’t this what SOA was all about?) but there’s no harm in reiterating the point.
“information is stored in multiple structures and formats. Any effort to manage information should focus on translating between structures rather than trying to develop a common schema” I worked on a project from 2005-2008 where we attempted to adhere to a supposed industry standard schema. Eventually we realised that those attempts were futile given that no business can be fitted neatly into an industry-standard-shaped-box and that dovetails nicely with Mike’s point here.
“There are four common processing tasks; combining multiple streams, translating data formats, QA information, integrate third party processing” – I wonder if there is a fifth that we might refer to as data caching; after all, if we’re pulling data out of multiple APIs we are at the mercy of the speed at which those APIs can provide the data – is a person going to be prepared to wait for the data or do we need regularly cache the transformed data for easy retrieval?
“Publishing should be application/technology agnostic” It would be hard for me to agree more with this point.

As you can tell this is an area that I’m particularly interested in and shall continue to keep a watching brief.

@Jamiet

What would a cloud-based ETL tool look like?

jamiet — Tue, 12 Feb 2013 14:15:17 GMT

Given that “moving data to the cloud” is, rightly or wrongly, currently in vogue in our industry I have to think that pretty soon there will be a glaring need for tools that help us to move data between these heterogeneous sources – a cloud-based ETL tool for cloud-based data if you will. Perhaps such a thing already exists -I’ve talked about Kapow in the past which may well be considered a form of cloud ETL tool given that it fetches data from the web– if you know of anything that might fit this very loose description feel free to let me know in the comments.

I started to ponder what capabilities a cloud ETL tool should have and here’s a quick brainstormed list:

Data transformation would be done “in the cloud” i.e. I wouldn’t need to own my own hardware in order to run it
Ability to consume data from/push data to* the following types of data protocols:

ATOM (application/atom+xm)
RSS (application/rss+xml)
OData
GData
JSON
ODBC
JDBC
RDF (application/rdf+xml)
FTP

Ability to consume data from/push data to the following MIME types:

text/html (gives rise to the idea of screenscraping as a source of data)
text/plain
text/xml

Adapters (possibly with a plug-in model) for the following cloud storage and API providers:

Job scheduler
An IDE (open to debate whether the IDE should be “in the cloud” as well)
Ability to carry out common transformations (join, aggregate, sort, projection) on those heterogeneous data sources
Ability to authenticate using different authentication mechanisms
Configurable logging
Ability to publish transformed data in a manner that makes it consumable rather than insert it into another data store

Any thoughts here? As I said this is a brainstormed list so I don’t mind being told that I am approaching this from the wrong angle or even that I’m completely wrong . Should I be concentrating on scenarios rather than technologies?. I’m only too aware that given my ETL heritage my brain is already wired to consider how traditional ETL tools could be supplanted into the cloud (my mention of a job scheduler sold me out there) – perhaps that is completely wrong too and that my heritage is actually a disadvantage here.

I’m interested to know what people think and hopefully trigger a conversation. I’m especially keen to hear about scenarios that you might have where you need to move and transform data that lives “in the cloud”.

@Jamiet

*where applicable

UPDATE. Within seconds of publishing this post I’d already been alerted to InformaticaCloud.com & AWS Data Pipeline. Checking those out now!

I’ve been recommended to check out the following articles:

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

Have You Downloaded SQL Server 2012 Evaluation Edition? Why Not?!

andyleonard — Thu, 08 Mar 2012 19:00:00 GMT

I am installing SQL Server 2012 Evaluation Edition on a virtual machine as I type.

You can do this. Here’s one way:

Grab some virtual machine software. I like Oracle VirtualBox. It’s cool. It’s free.
Install VirtualBox.
Download the 180-day free trial of Windows Server 2008 R2. Also cool. Also free.
Once Windows Server 2008 R2 is downloaded, build a VirtualBox VM.
Download and install SQL Server 2012 Evaluation Edition!

That’s all there is to it. You can get started today, no need to wait until 1 Apr 2012 for media to ship. And, you don’t have to worry about making changes to your laptop or workstation (other than installing VirtualBox).

You can explore the cool new features of SQL Server 2012 for the next six months… for free! What are you waiting for?

:{>

Updated SSIS Framework, Now Includes Reports

andyleonard — Wed, 07 Mar 2012 12:00:00 GMT

I have built a beta version of Linchpin People’s SSIS Framework, now including much-requested reports. You can grab the updated copy here.

I appreciate any feedback you care to share.

:{>

Simplifying CSV Data Loads

KKline — Thu, 27 Oct 2011 17:55:00 GMT

Data files containing comma separated values, or CSV, are some of the most common data formats used for data representation and storage outside the database. When it comes to loading CSV data into the database, many options exist, however, few make it as simple as CSVexpress, powered by expressor software. I recently visited www.csvexpress.com to check out just how simple it could get. In short, CSVexpress offers a repeatable and quick way to load any CSV file into SQL Server (or any other database). For those whose data quality is not as pristine as it should be, CSVexpress also offers a wide variety of built-in functionality to repair the data issues. These are in addition to the data transformation components available out of the box, but let’s not get ahead of ourselves.

The first thing I notice when I visit CSVexpress is that there are some video tutorials available on the main page. I found it pretty straightforward to load a CSV file into the database without watching the tutorials beforehand. However, by watching the tutorials I was able to learn more about some neat features and functions that I had not previously noticed.

For my test, I grabbed a simple CSV data file containing the following data:
City,User_ID,Name,Street_Address,Status
"Dallas",47,"Janet Fuller","445 Upland Pl.","Trial"
"Lyon",38,"Andrew Heiniger","347 College Av.","Active"
"Dallas",43,"Susanne Smith","2 Upland Pl.","Active"
"Berne",22,"Bill Ott","250 - 20th Ave.","Active"
"Boston",32,"Michael Ott","339 College Av.","Trial"
"New York",41,"Bill King","546 College Av.","Deleted"
"Oslo",45,"Janet May","396 Seventh Av.","Active"
As you can see from the diagram below, the import of the data to create a schema was not difficult at all:
Once the schema is configured, I can create the following simple data flow to move data from my CSV input file to my target table in SQL Server:

While there are other tools available for performing similar tasks, CSVexpress makes it very simple and intuitive. However, as I mentioned earlier, where it starts getting really interesting is when you need to pre-process and clean-up the data prior to loading it. Whether it involves enriching the data from external data sources or web services, or identifying and repairing bad data, CSVexpress maintains a simple interface for all of that.

The best part - it’s all free of charge. The version you can download from www.csvexpress.com is expressor’s free Community Edition. expressor also offers a licensed Desktop and Standard Edition with even more advanced features, which are available for a 30-day trial. As a matter of fact, at the end of November, expressor will be introducing Salesforce support into their commercial editions and CSVexpress will feature a 30-day trial version. You will be able to load Salesforce just as easily as if loading to SQL Server, or download your Salesforce contact, lead, and opportunity data and transform it before generating the right CSV output file (or files) that meets your daily, weekly, and monthly Excel reporting and analysis needs. Now that’s easy!

- Kev

Thoughts on Data Explorer

jamiet — Mon, 24 Oct 2011 09:55:00 GMT

To my mind the most interesting piece of news to come out of the recent PASS conference was the unveiling of a new SQL Azure Labs project coming from the SQL Server organisation that has the codename "Data Explorer" (not a very imaginitive codename I'm sure you'll agree) and for which there is information available at http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx (in case you've surfed on here a few months on from when I originally wrote this blog post you should expect that that that URI will have become a dead link).

My good buddy Chris Webb (blog | twitter) has already blogged about Data Explorer at Pass Summit 2011 - Day 1 Keynote and Self-Service ETL with Data Explorer in which he made a very telling observation:

It allows you to mash up data from various different sources then publish the result as an OData feed – very similar to Yahoo Pipes

I couldn't agree more with that assertion. I blogged about Yahoo Pipes over four years ago at Taking Yahoo Pipes for a test drive and I referred to it then as "ETL for RSS feeds"; it interested me greatly because here was a tool that enabled non-developers to pull data from multiple sources and make it available as a single data source that could be easily consumed; moreover it ran as a cloud service which has also long been an interest of mine. Granted, it only did this for RSS feeds but the premise was still really interesting to me; I believe that making data easily consumable is far more important than the tool chosen to consume it hence why I'm such a massive advocate of iCalendar for BI and why you'll rarely find me talking about the likes of Business Objects, Cognos, Qlikview, Tableau and Power View on this blog (no disrespect intended to those tools or the people that use them - they're just not what floats my boat).

Where Yahoo Pipes consumes RSS feeds and provides RSS feeds, Data Explorer consumes from loads of different places and provides OData feeds (something I've been banging on about for a while now) and if you're in the Microsoft ecosystem OData is increasingly looking like the lingua franca for platform and device independent data integration. Moreover, according to recent blog post Creating a custom RSS reader in Montego (cloud) by project lead Tim Mallalieu Data Explorer will also be able to pull data directly out of web pages and that is stepping firmly into the territory of Kapow which, again, is a tool that Chris and I have blogged about before at Kapow – ETL for HTML and Kapow Technologies. Chris referred to Kapow as:

a cross between a screenscraper and an ETL tool

and again I wouldn't disagree. Data Explorer looks like filling the missing link that I was alluding to in the final paragraphs of my June 2009 blog post Enterprise Mashups.

Are you spotting a common theme here? Data Explorer is an ETL tool and given my obvious SSIS affiliations that makes it very interesting to me. That it runs as a cloud service and will be available to non-developers only makes it more intriguing and I can't wait until Data Explorer becomes available for us to tinker with later this year. No doubt Chris will be keeping a watching brief too.

@Jamiet

UPDATE:Some further thoughts...

It would be interesting to see what else could be done with this data once its exposed as a feed. I'll wager that in the not too distant future you'll be able to (for example) sell the output from your Data Explorer mashup on Azure Datamarket or view geocoded feeds on Bing Maps (note that Geospatial support is coming to OData in the very near future). There are lots of possibilities I'm sure and I'm looking forward to seeing what ideas others have for using and sharing this data.

I'm also wondering whether there will be an option to host Data Explorer (and hence Data Explorer mashups) inside the enterprise. Today most enterprise data is contained within the corporate firewall thus will not be accessible from a Data Explorer service provided via SQL Azure; it would be a shame if such data could not be accessed by Data Explorer and hence why I hope there will be an on-premise version available. I can think of many scenarios at my past clients where the ability to easily make data consumable over HTTP and behind the firewall would have been invaluable.

Stairway to SSIS Step 3–Adding Rows in Incremental Loads

andyleonard — Fri, 16 Sep 2011 11:00:00 GMT

If you haven’t yet, check out the Stairways – a series of tutorial articles at SQL Server Central. They. rock. I was really excited when Steve Jones (Blog | @way0utwest | SQLPeople) contacted me to write the Stairway series of tutorials for SSIS! The third article is live this morning. Altogether, the published articles are:

I sincerely hope you enjoy reading these articles as much as I enjoyed writing them!

:{>

Designing an SSIS Framework

andyleonard — Fri, 16 Sep 2011 11:00:00 GMT

In preparation for SQL Saturday #84 – Kalamazoo, SQLBits 9, and the PASS Summit 2011; I’ve been honing my presentation called Designing an SSIS Framework. I delivered this presentation a few times over the past couple months. Each time, I promised the attendees I’d send them the code if they emailed me… and a couple things got in the way:

First, a lot of people requested the presentation. Second, I got really busy! Both of those beat their respective alternatives.

I finished building and barely testing the code this evening. You can grab an updated copy here. The zip file contains the SSIS 2008 R2 solution and two other folders: Config and Sql. Config holds a configuration file that points to a database named SSISConfig on the default instance of your local workstation or server (edit SSISConfigParent.dtsConfig if you want to change this location).

To make it all work dynamically, you need to create a System Environment Variable named SSISConfig (case-sensitive) and set the value to the full path of SSISConfigParent.dtsConfig on your workstation or server. Next, run the “Create SSISConfig.sql” script in the Sql folder, followed by the “Add An SSIS Applications.sql” script (edited to reflect the folder that contains the SSIS packages on your workstation or server). This should get everything ready for the SSISConfig SSIS solution. When you execute Parent.dtsx, it should call Template1.dtsx, then Template2.dtsx, and finally ErrorTest.dtsx.

Let me know how it goes!

:{>