Where Learning is Pleasure

Jayaram Krishnaswamy

Subscribe to Jayaram Krishnaswamy: eMailAlertsEmail Alerts
Get Jayaram Krishnaswamy via: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: Oracle Journal, Business Intelligence, Microsoft Developer

Microsoft SQL Server : Blog Post

Fortifying Microsoft SQL Server Integration Services

Change Data Capture For Dynamic Business Decisions

Beginners guide to Microsoft SQL Server Integration ServicesChange Data Capture is the most recent important change in the way the ETL Process is perceived for a dynamic business environment. In a generic sense CDC may be understood as, "What did change since the last time I looked it up?" In an image recognition situation this may mean what has changed in the scenery from the previous frame (it could be a few pixels or, a major part of a scene). In a database with huge amount of data one may want to know what has changed in the last ten days? ten minutes? ten seconds? to make a intelligent business decision.

However for businesses to make decisions and beat the competition this information or its immediacy is getting shorter and shorter and approaching zero. It is therefore not enough for business analysis software to make decisions based on previous month's or week's data but data which can be as short as now ( What is it now?).

Generally speaking to get information on what has changed is faster than getting the whole data and then looking at what has changed. This is what at the heart of change data capture.

The ability to capture data that has changed most recently.

Since the solution would depend on the event (perhaps a change to a single value in a table) one need to setup a trap for the event in the form of a trigger or some other programmatic construct.

However this may impact the resources and speed of data recovery. Another way would be to look at data as a continuous function of time which would also impact data base storage resources. Hence what is needed is a strategy to look at changed data, only the data of interest in a non invasive faster way which does not hog resources. This is the region where most of the innovative product developers would be looking at.

Microsoft SQL Server Integration Services (SSIS) that shipped with SQL Server 2005, an improved but totally different form of the previous Data Transformation Services provided a whole new paradigm for the ETL processes and provides a totally integrated product which can work with many third party databases. SSIS 2008 has few more enhancements added to SSIS 2005. With SSIS, SSRS and SSAS Microsoft laid down a solid foundation for Business Intelligence.

Given such a product what is natural for a innovative company is to add further enhancements to SSIS to address the specifics needed for CDC and enable dynamic business decisions. Preferably in a way that is totally integrated with SSIS.

This is what Attunity has achieved with its award winning data warehousing product Attunity CDC for SSIS. It has added streaming data to be processed by SSIS. The two new products from Attunity are,

  • Attunity Oracle-CDC for SSIS
  • Attunity SSIS CDC Accelerator

If you are new to SSIS or, one who has used DTS in the past, ramp up on SSIS by reading my (illustrated) book "Beginners guide to Microsoft SQL Server Integration Services 2005".

More Stories By Jayaram Krishnaswamy

Jayaram Krishnaswamy is a technical writer, mostly writing articles that are related to the web and databases. He is the author of SQL Server Integration Services published by Packt Publishers in the UK. His book, 'Learn SQL Server Reporting Services 2008' was also published by Packt Publishers Inc, Birmingham. 3. "Microsoft SQL Azure Enterprise Application Development" (Dec 2010) was published by Packt Publishing Inc. 4. "Microsoft Visual Studio LightSwitch Business Application Development [Paperback] "(2011) was published by Packt Publishing Inc. 5. "Learning SQL Server Reporting Services 2012 [Paperback]" (June 2013) was Published by Packt Publishing Inc. Visit his blogs at: http://hodentek.blogspot.com http://hodentekHelp.blogspot.com http://hodnetekMSSS.blogspot.com http://hodnetekMobile.blogspot.com He writes articles on several topics to many sites.