In the first blog post of this series, I discussed the increasing pressures on analytics, reporting and data warehousing departments in the healthcare provider industry to feed more and more data to all manner of hungry (and generally well-intentioned) requesters, and this despite budgets constantly getting trimmed. In short, we’re being asked to do more with less. So to start, we need to ask the obvious question: is it possible for us to do more with less? Yes and no. Let’s start with the Yes part because it will be less controversial than the No part.
Yes we can do more with less if we use the right tools for the job. For instance, we need to start using reporting tools for reports and stop using them for extracts. You and I both know this is happening at your organization. If a report writer needs to make all the fields on a report one pixel wide to fit all 400 fields on the report so they can be exported to a comma-separated file and submitted to some regulatory or accreditation body, you are not using the right tool for the job. There are ETL tools (like SSIS and Informatica) that are made for this type of task. By using a screwdriver to do a hammer’s job you are not getting any of the efficiencies you can get from those tools, and eventually the tool is going to reach its limit and break.
Some of the things an ETL tool can provide that a reporting tool can’t (at least not nearly as well):
- Step-by-step processing – In an ETL tool you can work with your data in an unlimited number of logical steps, whereas with a reporting tool you for the most part need to do everything in one big step
- Encapsulation – It is much easier to set up an ETL tool in such a way that if you need to make an adjustment to a single data element, it will only affect that data element and not risk unintentionally (and sometimes unknowingly) breaking other data elements as is more often the case with a reporting tool
- Exception handling – ETL tools allow you to handle common issues with your data instead of having the whole extract break every time the common issue pops up again. For instance let’s say you need to map your department names to different values required by whomever you are submitting the data to. If a new department without a mapping comes in, with an ETL tool you can set that record aside in a temporary table until someone can perform the mapping.
- Alerts – To keep with our department mapping example, with an ETL tool you can set up alerts via email or otherwise to let someone know that mapping needs to occur. Not something you can do with a reporting tool.
To summarize, reporting tools are not ETL/extract tools, so get yourself the right tool and it will easily pay for itself in efficiency gains and the reduction of ibuprofen costs (for headaches, get it?). Next time we’ll offend all you “use Excel for absolutely everything” people!