Advanced integration services on microsoft ssis 1

SKILLWISE-ADVANCED INTEGRATION
SERVICES ON MICROSOFT SSIS

Advanced Integration
Services on Microsoft
SSIS

What is the Problem?
• Resource Contention
• Unreliable access to historical data
• Inconsistent application of
business rules
• Data structure results in slower,
more complex queries

The Data Warehouse Solution
• Initial Load
• Ongoing Load

Dimension Table
Characteristics
Primary key Surrogate Key or
YYYYMMDD for Date Dimension
Business or Natural Key
Dimension columns: Labels, grouping,
filtering, sorting, drilling All data types
allowed

ETL Design Patterns
Create snapshot of
database to simplify
error recovery
Master Extract Package
Master Transform-Load Package

Extract Package
Truncate staging before extraction
Add record to audit table
Extract data from source and load into staging table
Count records in staging table
Update audit record

Extract Data Flow
Connect to source
Store extracted row count in variable
Load extracted records into staging table
Store error row count in variable
Save error records

Load Patterns
Extract only new records
and load into transactional
fact table
Extract all records
Exists? Update if changes (maybe)
Fact Loads Dimension Loads

Fact Extract for Ongoing Load
@[User::ExtractSQL]
=@[$Project::initialLoad] ?
@[User::ExtractSQL] :
@[User::ExtractSQL] + " where
OrderDate > '" + (DT_WSTR, 50)
(DT_DBTIMESTAMP)
@[User::MaxDateTime] + "' "
Create SQL statement for
extract based on initialLoad
value
Look up max date for related fact table,
store in MaxDateTime variable

Slowly Changing Dimensions
• Type 0
• Type 1
• Type 2
Add new record
Expire old record
After LoadBefore Load

Dimension Load Pattern: Type
0
Connect to staging table
Store row count from staging in variable
Add AuditKey into pipeline
Transformation – varies by dimension
Test for dimension record existence with Lookup
Store row count for records to insert in variable
Load record into dimension table
Store error row count in variable
Save error records

Dimension Load Pattern: Type
1 or Type 2
Standard error handling
Update onlyExpire old record
Transformations here
Slowly Changing
Dimension
Transformation
Combine new & Type 2Add Start Date Insert new & Type 2
Connect to staging
Capture row count
Add audit key

Other Slowly Changing
Dimension Patterns
• Merge
• Change Data Capture (CD)
• Custom Components

Dimension Load Pattern
Extract data from staging and load into
dimension table
Count records in dimension table
Update audit record
Add record to audit table
Count records in dimension table

Fact Table Load Pattern
Capture and store last date processed
Fact load & error handling
Surrogate key lookups
Connect to staging
Add audit key

T-SQL MERGE Statement
• Datawarehouse Dimension Table
– Type 1 SCD
MERGE dw.DimProductAS target
USING (
SELECT Name, . . .
FROM tmp.scdProduct) AS source
ON target.ProductAlternateKey= source.ProductNumber
AND target.Status= 'Current'
WHEN MATCHED AND NOT (Source.Name=
ISNULL(target.EnglishProductName, ''))
THEN
UPDATE
SET target.EnglishProductName= source.Name
WHEN NOT MATCHED BY target AND source.SellEndDateIS NULL
THEN
INSERT (ProductAlternateKey, ProductSubcategoryKey, . . .)
VALUES (source.ProductNumber, source.ProductSubcategoryKey,
. . .)
OUTPUT $action as MergeAction, source.*

Type 1 SCD
USING (
SELECT Name, . . .
ON target.ProductAlternateKey= source.ProductNumber
WHEN MATCHED AND NOT (Source.Name=
ISNULL(target.EnglishProductName, ''))
THEN
UPDATE
SET target.EnglishProductName= source.Name
WHEN NOT MATCHED BY target AND source.SellEndDateIS NULL
THEN
INSERT (ProductAlternateKey, ProductSubcategoryKey, . . .)
VALUES (source.ProductNumber,
source.ProductSubcategoryKey, . . .)
Define join between tables:
Business key
Status indicator or date range

• Type 2 SCD
USING (
SELECT Name, . . .
ON target.ProductAlternateKey=
source.ProductNumber
WHEN MATCHED AND NOT (Source.ListPrice=
ISNULL(target.ListPrice, ''))
THEN
UPDATE
SET target.Status= NULL, target.EndDate=
GETDATE()
WHEN NOT MATCHED BY target AND
source.SellEndDateIS NULL
THEN
INSERT (ProductAlternateKey,
ProductSubcategoryKey, . . .)
VALUES (source.ProductNumber,
source.ProductSubcategoryKey, . . .)

Auditing and Type 2 Inserts
CREATE TABLE #DimProduct(MergeAction
NVARCHAR(10), Name NVARCHAR(50), . . .)
INSERT INTO #DimProduct
SELECT * FROM (
MERGE dw.DimProductAS target . . . ) mergeOutput
INSERT INTO dw.DimProduct
SELECT Name, . . . FROM #DimProduct
WHERE MergeAction= 'UPDATE'
SELECT
SUM(CASE WHEN MergeAction= 'INSERT'THEN 1 ELSE 0
END)
AS RowCountInsert,
SUM(CASE WHEN MergeAction= 'UPDATE'THEN 1 ELSE 0
END)
AS RowCountUpdate
FROM #DimProduct
SELECT
SUM(CASE WHEN MergeActionIN ('INSERT','UPDATE')
THEN 1 ELSE 0 END)
AS RowCountInsert,
SUM(CASE WHEN MergeAction= 'UPDATE'THEN 1 ELSE 0
END)
AS RowCountUpdate
FROM #DimProduct
Type 2 Inserts
Type
1
Audit
ing
Type 2
Auditi
ng

Error Handling
BEGIN TRY
BEGIN TRANSACTION
CREATE TABLE #DimProduct(MergeActionNVARCHAR(10),
Name NVARCHAR(50), . . .)
INSERT INTO #DimProduct
SELECT * FROM (
MERGE dw.DimProductAS target . . . ) mergeOutput
SELECT
SUM(CASE WHEN MergeAction= 'INSERT' THEN 1 ELSE 0
END)
AS RowCountInsert,
SUM(CASE WHEN MergeAction= 'UPDATE'THEN 1 ELSE 0 END)
AS RowCountUpdate
FROM #DimProduct
COMMIT
END TRY
BEGIN CATCH
. . . <Error Handling code> . . .
END CATCH

Dimension Load Pattern with MERGE

Change Data Capture (CDC)
• In Enterprise Edition

Types of Dirty Data
• Column Problems
• Record Problems
• Business Rule Problems

SQL Query or Integration
Services?
But…. What if the source isn’t
relational? What if the
resulting query is very
complex?
But…. Packages can be
tedious to build However,
logging or data viewer
helpful

Column Problems: Missing
Data Default
• Problem: Missing Data
• Solution: Add Default

Column Problems: Derive
Missing Data
• Problem: Missing Data
• Solution: Use Lookup Query to
Derive Data

Column Problems:
Translation
• Problem: Code to Translate
• Solution: Lookup (optionally with
Cache Transform)

Columns Problems: Data Type
• Problem: Incompatible Data Types
• Solution: Data Conversion

Column Problems: Truncation
• Problem: Input Column Size > Destination
Column Size
• Solution 1: Derived Column with Left() or
Substring()
– But…Consider the impact on downstream
operations
• Solution 2: Flag Record for Manual
Intervention

Record Problems: Missing Dimension
Data
• Problem: Lookup Failures
– Fail
– Flag
– Fix
• Solution: Inferred Members
– Option 1: Insert unmatched records in advance of fact
load
– Option 2: Use script component to insert record and
return key
– Option 3: Use partial cache lookup task

Record Problems: Lookup Failures
• Problem: Inconsistent Data Across Sources
• Solution: Fuzzy Lookup Transformation

Record Problems: Duplicate Data
• Problem: Similar Data in Same Data Set
• Solution: Fuzzy Grouping Transformation
– Enterprise Edition Only

Business Rule Problems: Out of Range
Values
• Problem: Invalid Values
• Solution: Conditional Split to Flag

Data Quality Services
• DQS Connection Manager
• DQS Cleansing Transformation

Advanced integration services on microsoft ssis 1

Advanced integration services on microsoft ssis 1

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Advanced integration services on microsoft ssis 1

Similar to Advanced integration services on microsoft ssis 1 (20)

More from Skillwise Group

More from Skillwise Group (20)

Recently uploaded

Recently uploaded (20)

Advanced integration services on microsoft ssis 1