1 / 18

CDC+SSIS = SCD

CDC+SSIS = SCD. Patrick LeBlanc, SQL Server MVP. #49 Orlando. Contact Information. Email: pleblanc@pragmaticworks.com Twitter: patrickdba Blog: http://bidn.com/blogs/patrickleblanc. Overview. A Few Warehouse Terms Detecting Change Data (OLD SCHOOL) Change Data Capture

hachi
Download Presentation

CDC+SSIS = SCD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CDC+SSIS = SCD Patrick LeBlanc, SQL Server MVP #49 Orlando

  2. Contact Information • Email: pleblanc@pragmaticworks.com • Twitter: patrickdba • Blog: http://bidn.com/blogs/patrickleblanc

  3. Overview • A Few Warehouse Terms • Detecting Change Data (OLD SCHOOL) • Change Data Capture • SQL Server Integration Service • Slowly Changing Dimension

  4. Warehouse Terms • Data Warehouse • Centralized repository of data. • Organized for analytics and reporting • Dimension • Mechanism for slicing or categorizing data. • Enables filtering and grouping of DW data. • Slowly Changing Dimension (SCD) • Type 0 (Fixed Attribute) • Type I (Changing Attribute) • Type II (Historical Attribute)

  5. Type 1 (Changing Attribute) Update Simple UPDATE statement applied: UPDATE DimCustomer Set AddressLine1 = ‘123 Main St’, ZipCode = ‘54276’ WHERE CustomerID = 5000017302

  6. Type 2 (Changing Attribute) Update

  7. Type 0 (Fixed Attribute) Update Update Ignored or Failure

  8. Detecting Change Data

  9. Old School Change Detection DEMO TIME Create tables with RowVersion column Create table to track RowVersion Build SSIS Package

  10. Slowly Changing Dimension Task

  11. Change Data Capture Solution

  12. Key CDC Components • Change Tracking Tables • Based on schema of tracked table • Copy of “before” and “after” data for each record changed • Log Reader Job • Near real-time capture of transactions • Same internal code as Transactional Replication • Transactions tracked by LSN • Clean Up Job • Purges tracking tables of data older then 72 hours (3 days) • CDC Created Functions • sys.sp_cdc_help_change_data_capture • cdc.fn_cdc_get_all_changes_Sales_SalesOrderDetai • cdc.fn_cdc_get_net_changes

  13. New School Change Detection DEMO TIME • Enable Database for CDC • Enable Tables for CDC • Update User Tables • CDC Records LSN and matching Data • Query Changes in _CT Tables based on LSNs

  14. Use CDC + SSIS • Extract • Transform • Load • To Data Warehouse DEMO TIME

  15. Biography • BI Architect for Pragmatic Works • SQL Server MVP • Decade of SQL Server Experience • Founder SQL Lunch (www.sqllunch.com) • Leader Baton Rouge, LA SQL Server User Group • Twitter:@patrickdba • Blog: http://www.bidn.com/blogs/PatrickLeBlanc • Author: Knight’s Microsoft Business Intelligence 24-Hour Trainer

  16. ? Questions

  17. Contact Information • Email: pleblanc@pragmaticworks.com • Twitter: patrickdba • Blog: http://bidn.com/blogs/patrickleblanc

  18. @ SQL Saturday #49 Orlando CDC+SSIS = CDC Patrick Leblanc (blog|twitter)

More Related