Note that in the population of dbo.SalesTerritory the key values of -1 (Not Assigned) and 0 (Not Applicable) are manually inserted (line 514). If the data needs to be revised for these tables, I generally use a one-time script to make the revisions. As shown in this script they include dbo.DateDim, dbo.ProductCategoryDim, dbo.ProductSubCategoryDim, dbo.SalesTerritoryDim, and dbo.GeographyDim. The slowly changing dimensions are relatively static tables and in this design, were not candidates for change tracking. As stated in Part 2, this schema is used to identify all tables and stored procedures used in the ETL process.ĭimension tables can be categorized as either slowly changing dimensions or current dimensions. Line 36 shows the creation of the ETL schema.
CODEPLEX ADVENTUREWORKS2012 SERIES
Since the purpose of this entire series is to illustrate Change Tracking ETL, this author decided on a subset of data movement from the OLTP database to the new Data Warehouse. Originally my intent was to include all the tables within the original AdventureWorksDW2012 database, but there is almost no documentation for the table mappings from the OLTP AdventureWorks2012 database to AdventureWorksDW2012. It also allows easier trouble-shooting of errors as well as an ETL restart capability in case of failure. This allows immediate process flow to determine if an existing row is to be updated and a new row inserted. This is a personal preference that the table function should come at the end of the name rather than the beginning.Īs stated in Part 2 of this series, the source Primary Key was added to each of the tables referenced for change tracking. Note the transformation of the original name from FactInternetSales to InternetSalesFact. It contains only one fact table – InternetSalesFact. It is patterned after the AdventureWorksDW202 database but with fewer tables, some table revisions, and a revised naming convention. It is recommended as you read this article, you also have a separate window open with the appropriate script for comparison and review.ĬT_Script1 creates a new database named AdventureWorksDW2012lite.
CODEPLEX ADVENTUREWORKS2012 CODE
Not to say my way is right, but just represents code and procedural steps that I am comfortable with from data integrity and process maintainability (reliability) design. It is patterned after code implemented this last summer for a production database with a few changes per this author’s design philosophy. The code used to implement the data warehouse is discussed below. The architecture design of the data warehouse was discussed in part 2 of this series. There are links to several scripts at the end of this article and the discussion below will detail the contents of these scripts. This is the third and last part of a series on SQL Server Change Tracking and focuses on the code to implement a data warehouse from creation to actual change tracking ETL.
NOTE: This three-part article series is based on a presentation originally given by Mike Byrd at SQL Saturday #234 on Augyou can read Part 1 here and Part 2 here.ġ.
0 kommentar(er)
