Transcript Slowly Changing Dimensions and SSIS

Colorado PASSCamp 2006
Slowly Changing Dimensions and SSIS
Brad Younge
Statera
What is SSIS
• SQL Server Integration Services
• Successor to DTS
• Robust way to:
 Load Data (ETL)
 Perform Tasks in a Workflow
• Business Intelligence Development Studio
 Visual Studio 2005
Colorado PASSCamp 2006
Slowly Changing Dimensions
• Dimensional Data Changes  Need
History
• Type I: Overwrite History
• Type II: Maintain History
• Type III: Preserve Last History
Colorado PASSCamp 2006
Demo
Source Database - AdventureWorks
Slowly Changing
Dimension
SalesOrderDetail
PK,FK1
PK
SalesOrderID
SalesOrderDetailID
ProductCategory
CarrierTrackingNumber
OrderQty
ProductID
SpecialOfferID
UnitPrice
UnitPriceDiscount
LineTotal
rowguid
ModifiedDate
PK
SalesOrderID
FK1
FK2
RevisionNumber
OrderDate
DueDate
ShipDate
Status
OnlineOrderFlag
SalesOrderNumber
PurchaseOrderNumber
AccountNumber
SalesPersonID
TerritoryID
BillToAddressID
ShipToAddressID
ShipMethodID
CreditCardID
CreditCardApprovalCode
CurrencyRateID
SubTotal
TaxAmt
Freight
TotalDue
Comment
rowguid
ModifiedDate
ProductSubcategory
ProductCategoryID
PK
ProductSubcategoryID
Name
rowguid
ModifiedDate
FK1
ProductCategoryID
Name
rowguid
ModifiedDate
Product
SalesOrderHeader
PK
Destination STAR Schema
StateProvince
PK
PK
StateProvinceID
StateProvinceCode
CountryRegionCode
IsOnlyStateProvinceFlag
Name
TerritoryID
rowguid
ModifiedDate
Address
PK
FK1
ProductID
AddressID
AddressLine1
AddressLine2
City
StateProvinceID
PostalCode
rowguid
ModifiedDate
FK1
Name
ProductNumber
MakeFlag
FinishedGoodsFlag
Color
SafetyStockLevel
ReorderPoint
StandardCost
ListPrice
Size
SizeUnitMeasureCode
WeightUnitMeasureCode
Weight
DaysToManufacture
ProductLine
Class
Style
ProductSubcategoryID
ProductModelID
SellStartDate
SellEndDate
DiscontinuedDate
rowguid
ModifiedDate
Location_Dim
Product_Dim
PK
PK
Product_Key
CategoryName
SubcategoryName
ProductName
ProductID
ProductNumber
ListPrice
Color
Weight
CurrentStatus
Sales
PK
PK
SalesOrderID
SalesOrderDetailId
FK1
FK2
Product_Key
Location_Key
OrderDate
OrderQuantity
UnitPrice
Location_Key
SourceAddressId
CountryRegionCode
StateProvinceCode
StateProvinceName
City
PostalCode
Address1
Address2
Time
PK
Time_Key
Year
Quarter
Month
Day
Colorado PASSCamp 2006
Questions?
Slowly Changing Dimensions and SSIS
Brad Younge
Statera