Real Time BI and Change Data Capture (English Version)

Real-time business intelligence (RTBI) is the process of delivering information about business operations as they occur.

Business operations as they occur are fed into the RTBI system in real time (Or Near real time) which shows the current state of the company; allowing not only the classical visions of the past of classical DW system. Providing real-time tactical support to boost shares of companies to immediately respond to events as they occur.

One of the technologies that best provides the ability to load data and Real Time CDC (Change Data Capture).

CDC is a set of software design patterns used to determine (and track) the data that has changed so that action can be taken using the changed data. Also, Change data capture (CDC) is an approach to data integration that is based on the identification, capture and delivery of the changes made to enterprise data sources.

Example of how to enable CDC in SQL Server 2008:

1 - The SQL Server Agent service needs to be active.

2 - Code to enable the CDC.

/* Enables the database to use the CDC*/

USE DB_NAME

GO

exec sys.sp_cdc_enable_db

-- Shows DBs with active CDC

select name, is_cdc_enabled from sys.databases

/* Enables the table for use CDC */

exec sys.sp_cdc_enable_table

    @source_schema = 'dbo',

    @source_name = 'TABLE_NAME' ,

    @role_name = 'CDCRole',

    @supports_net_changes = 1

/* This example enables the columns to be tracked*/

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TABLE_NAME',

@role_name     = NULL,

@captured_column_list = '[COLUMN_NAME1],[ COLUMN_NAME2]'

   

/* Tables listed to be tracked by CDC*/

select name, type, type_desc, is_tracked_by_cdc from sys.tables

where is_tracked_by_cdc = 1

-- or

EXEC sys.sp_cdc_help_change_data_capture