Organizations today have data spread over multiple locations and formats. For example, your CRM data application is cloud based, your sales and inventory system is an Oracle application, and your personnel system is a home-brewed Access application. Choosing best of breed applications makes these types of data silos unavoidable. These complicate leveraging cross-system views of data.
A second related challenge is sharing data with business partners. These types of transactions vary by industry, but from medical to manufacturing industries the sharing of electronic records is a growing trend.
In this context SQL Server can help. Included in any SQL license is a program called SQL Server Integration Services. This is a powerful tool for designing data transformation and import logic that can be scheduled to occur on a recurring basis. The tool is able to connect to nearly all data sources. The act of transforming and migrating data in this way is called data wrangling.
A common theme here is harnessing the computer to perform mechanical, tedious and staff-demoralizing tasks. This type of automation frees your staff for higher purposes. The staff-time-savings is often the clearest business use-case for recovering our expense.
The paragraphs below describe services we provide in this area.
Data Wrangling Variations
These are data transformations that need to be run on a recurring basis. They may be part of a data warehouse load or represent partner data sources that are published nightly on an FTP server and imported into a local database. SQL Server Integration Services (SSIS) is used for nearly all of these data wrangling tasks. SSIS readily supports munging of data from various destinations and publishing them into SQL Server or other destinations. Daily ETL tasks can also involve creation of data byproducts such as Excel / text-file extracts that make it easier for employees to work with company data.
Ongoing data capture through hardware device integration such as barcode readers, Scantron devices, biometric sensors, RFID hardware, etc. This also includes web service, reference services, and SOAP type integrations.
We are occasionally asked to perform one time data migrations from two different vendor systems. This typically occurs when a customer is transitioning from one software product to another. These projects require becoming familiar with the specifications of the giving and receiving data systems, and also require data standardization and clean up as part of the process. Typically, we can do these types of migrations at lower costs than the giving or receiving vendors.
These are one time or ongoing cleanup of data. Typical use cases are address standardization, de-duplication (identification and processing of duplicate customers for example), and use of reference data sources (Dun and Bradstreet data sources for example.) There are a variety of helpful tools in this area, including: SQL Server Data Quality Services, Master Data Services, regular expression or SQL scripts.
These are typically health-care specific transactions in which medical records are electronically distributed in an HL7 file format. Mirth Connect is a powerful open-source toolbox that greatly simplifies the management of these machine-to-machine communications. This sort of project places high value on security and fault-tolerant messaging between remote servers. The features and open-source licensing of this product makes it a compelling choice for any organization tasked with managing these information exchanges.
Provided at right are scenarios and tools that further explain the data wrangler tasks. Let us know if we can help you with a similar task.