Data Warehousing SSIS Demo Michael A Fudge Jr Recall Kimball Lifecycle SSIS Demo Using the Fudgemart Employee Timesheets Dimensional Model from before DW HighLevel Source to ID: 427164
Download Presentation The PPT/PDF document "IST722" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
IST722 Data Warehousing
SSIS DemoMichael A. Fudge, Jr.Slide2
Recall: Kimball LifecycleSlide3
SSIS Demo…
Using the Fudgemart Employee Timesheets Dimensional Model from beforeSlide4
DW
High-Level Source to Target
Map
Fudgemart
External
Sources
Employees
Employee_timesheets
Date
Dimension
DimEmployees
DimDate
FactEmployeeTimesheets
Supervisors
Stage
stgDate
stgFudgemartEmployees
stgFudgemartEmployeeTimesheets
STAGING
Stage_EmployeeTimeSheets.dtsx
“Truncate and Load Patten”
LOAD TO DW
DW_EmployeeTimesheets.dtsx
“Type 1/2 SCD Pattern”
SOURCE
STAGE
DWSlide5
The
ETL PackagesDateDimensionImport.dtsx Imports the Date Dimension. (One time deal)
One package to go from source to stage to target.
Stage_EmployeeTimesheets.dtsx
Stage Dimension and Fact Data as-is using the truncate and load pattern.
DW_EmployeeTimesheets.dtsx
Transform staged data into the required Dimensions and Facts.
Load with Type 2 or 1 SCD pattern, as to not re-process the same data.
Package.dtsx
Combine steps 1-3 into one package.Slide6
1. DateDimensionImport.dtsx
We will walk through how it works.We’ll skip making it because it’s covered in the lab!Slide7
2. Stage_EmployeeTimesheets.dtsx
Staging Process for Truncate and load:Data Flow – From Source to StageSource – Use an SQL Command to match target attributes
Target – Create new staged table and import data as-is.
Include an SQL task to truncate the table before
import
Repeat for each Source to Stage
1
2
1.1
1.2
3Slide8
Demo: Stage Fudgemart
Timesheet DataSlide9
3. DW_EmployeeTimesheets.dtsx
Type 2 SCD Processing of Dimensions
Data Flow from Stage To
Dimension
Load Data Source from Stage
Transform Data from Source to Match Target
Lookup Surrogate Key
Pipeline
Process changes using SCD Type
2
Repeat these steps for each dimension
Steps 1.2 and 1.3 vary based on the dimension
1
1.2
1.3
1.3
1.3
1.4
1.1Slide10
Demo: DW Fudgemart
Timesheet Data Dimension ProcessingSlide11
3.
DW_EmployeeTimesheets.dtsx Type 1
SCD Processing of
Facts.
Data Flow from Stage To
Fact
Load Data source from stage
Transform
Data from Source to Match
Target – Calculate facts.
Lookup Surrogate Key Pipeline
Process changes using SCD Type
2
All steps are required for most fact tables.
2
2.1
2.2
2.3
2.3
2.4Slide12
4. Package.dtsx
Once package to execute the others.This package would get scheduled to execute on a routine basis.Production Changes:No Date Dimension Do not stage all data, but stage based on last processed.Slide13
Demo: Create Main PackageSlide14
IST722 Data Warehousing
SSIS DemoMichael A. Fudge, Jr.