Managing data with Enterprise Integration Management and SQL Server

Microsoft tools for SQL Server can help build a comprehensive enterprise integration management platform.

Microsoft has been pushing its Enterprise Integration Management (EIM) technologies as a way to manage data across the enterprise. Built on SQL Server 2012 components, Microsoft's EIM offers IT professionals and data stewards tools to integrate system and application data, cleanse and standardize data, manage data governance and gain insights that lead to better business decisions.

At the heart of Microsoft's EIM lies a set of SQL Server components. SQL Server Integration Services (SSIS) transforms and consolidates the data, Data Quality Services (DQS) matches and cleanses the data and Master Data Services (MDS) manages and organizes the data. Together these components make it possible to work with data from a wide range of sources stored in a variety of formats.

SQL Server Integration Services

If there's one component that keeps the EIM heart pumping, it's SSIS, a workflow and data flow engine that can perform a variety of extract, transform and load (ETL) operations. With SSIS, you can automate tasks like importing data, managing files, performing look-ups, cleansing data or sending notification emails.

Built into SSIS is the ability to set up ETL operations that can integrate data from various systems: SQL Server databases, SQL Database, SQL Server Analysis Services (SSAS), other database management systems, enterprise applications, cloud services and data files. During your ETL operations, you can merge, consolidate, aggregate, pivot and transform the data in several ways.

SSIS also provides workflow capabilities that let you control your data flow operations and how they're integrated with other tasks, which includes running T-SQL statements, sending emails, moving files or managing database objects. In addition, SSIS includes features that let you extend its built-in capabilities so you can better integrate with and transform data from non-standard sources, like a legacy application or data stores.

Data Quality Services

In some cases, the data cleansing capabilities in SSIS might not be enough. Data coming from multiple heterogeneous sources can be full of inconsistencies and errors. Standards, constraints and formats can differ from one system to the next, resulting in incorrect or duplicated data. For example, a customer's name might be spelled differently in different systems within the enterprise -- DQS can help address those issues.

New to SQL Server 2012, DQS provides the tools that data stewards and IT professionals need to resolve inaccuracies, inconsistencies and duplications. DQS takes a knowledge based approach to defining the types of supported data and the validation rules needed to cleanse and standardize that data.

For example, a business user at an automobile retailer might create a knowledge base that defines the domains -- fields -- commonly used in automobile records. The knowledge base would include such domains as make, model, year, color and so on. For each domain, the business user can define validation rules that restrict what values can be included in a particular domain. For instance, a validation rule on the year domain might limit the permitted values to the range 1960 through 2016.

A data steward can also identify synonyms -- "Chevy" and "Chevrolet" --and designate one of those synonyms as the lead value, that is, the correct one to use in place of all others. In addition, the business user can create matching policies that help identify duplicate records. DQS has also been integrated into SSIS so your ETL operations can include a component that applies DQS rules to the data during the data flow process.

Master Data Services

In many enterprises, data is spread across multiple systems and stored in various formats without regard to integrity and consistency, resulting in inaccurate reports and data analytics. MDS can help to bring order to the chaos by providing the platform necessary to establish and maintain data governance. Data can be imported from a wide range of sources and managed within a common unified infrastructure.

At the core of MDS is a central hub where business users can implement and maintain the infrastructure as well as validate and standardize data supported by that infrastructure. Users start by creating data models that reflect the company's business entities. Each entity contains a set of attributes that define the type of data that can be stored in that entity, similar to how a database table is made up of related columns.

Business users can then organize the entities into hierarchical relationships. In addition, they can create business rules that validate the imported data. The MDS system serves as the definitive master record for each business entity, ensuring consistency across the enterprise.

Business users can implement an MDS solution on any type of business entity. They can designate an existing data store as the master system of record or create a master hub from scratch. MDS adheres to a relational database architecture, with SQL Server in the background and SSIS providing the data flow. MDS also includes an Excel add-in so that Excel can be used to manage the data.

Together, SSIS, DQS and MDS provide a set of tools for implementing an EIM solution, but these services don't work in a vacuum. Other components can play an important role in an EIM solution. The SQL Server database engine, for example, makes it possible to warehouse and manage the data. SQL Server Analysis Services supports sophisticated data analytics and mining. SQL Server Reporting Services lets you generate a wide range of reports that present data in multiple formats. Then you have SharePoint Server, PowerPivot, Power View and Microsoft Office, all of which let you bring the data to a variety of stakeholders. Together, these components make it possible to implement a comprehensive EIM solution that incorporates a wide range of enterprise data.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation.

Dig Deeper on Microsoft SQL Server Integration Services (SSIS)