SQL SERVICE INTEGRATION SERVICES

RECAP

Last time we loaded data from .csv files into our Northwind DW

This time we are going to load data from Northwind (transactional

database) to our Northwind DW
Data Users
Sources

Operational Database
Reports

Data
Warehouse

Flat data Analytically Processed

Database (Cube)
Dashboards

Other sources
Excel workbooks
SSIS SSAS SSRS
SOME PREPARATION FIRST
In each of our DimProduct & DimCustomer tables, we need a new column to
store the original primary key from the transactional database.
• This is so we can “lookup” existing data when loading data into our warehouse
• Note we do not need one for our DimTime table as it never had primary key to
begin with from the transactional database (the columns from DimTime are all
derived values)

[DimProduct]: [DimCustomer]:
LOADING DATA INTO DATA WAREHOUSE

Load into DimProduct

LOADING DATA INTO DATA WAREHOUSE

Load into DimCustomer

LOADING DATA INTO DATA WAREHOUSE

Load into DimTime

LOADING DATA INTO DATA WAREHOUSE

Load into
FactSales
CONTROL FLOW

1. Using our previous solution, 2. Create the following Data Flows:

Create New SSIS Package
1. LOAD [DIMPRODUCT]

In the “Load into DimProduct” data flow:

1. Create a OLE DB Source: 2. Edit the OLE DB Source. Choose Northwind connection
manager (you may need to create one) and type in SQL
Command to find product details:
1. LOAD [DIMPRODUCT]
3. Create a OLE DB 4. Edit the OLE DB Destination. Choose NorthwindDW
Destination: connection manager (you may need to create one) and choose
the [DimProduct] table:
1. LOAD [DIMPRODUCT]
3. Create the corresponding
mappings:

Note that the source

ProductId is mapped to
the destination
ProductAlternateKey
(not ProductId)

This is because the

ProductId in the
destination is used as
the primary key in the
DW.

The
ProductAlternateKey
(the primary key from
the operational
database) is saved to
lookup later.
2. LOAD [DIMCUSTOMER]

Try it!

(Instructions on next slides if you get

stuck)
2. LOAD [DIMCUSTOMER]

In the “Load into DimCustomer” data flow:

1. Create a OLE DB Source: 2. Edit the OLE DB Source. Use Northwind connection manager
and choose the [Customers] table:

We can just choose the table instead of typing SQL

command because we do not need to join tables, unlike
with the product table
2. LOAD [DIMCUSTOMER]
3. Create a OLE DB 4. Edit the OLE DB Destination. Choose NorthwindDW
Destination: connection manager and choose the [DimCustomer] table:
2. LOAD [DIMCUSTOMER]
3. Create the corresponding
mappings:

Note again that we are

saving the primary key
of the operation
database into
CustomerAlternateKey,
and not in to
CustomerId of the DW.
3. LOAD [DIMTIME]

In the “Load into DimTime” data flow:

1. Create a OLE DB Source: 2. Edit the OLE DB Source. Choose Northwind connection
manager and the following SQL Command:

Here we extract the day, month, year from

[Orders].[OrderDate] to be ready to put
into [DimTime]
3. LOAD [DIMTIME]
3. Create a Lookup
Transformation:
A Lookup Transformation is used when we need to
search the destination table for data that already
exists inside it.

It can then take action according to whether it has

found existing data or not.
It has two outputs – Match Output (if existing found)
and No match Output (if existing not found)

In this case, for each set of [Day], [Month], [Year]

from our source, we tell the lookup to search for a
record in [DimTime] that has the same values.

If there is already a record in [DimTime] we do not

need to load that record again.

If there isn’t we load it.

3. LOAD [DIMTIME]
4. Edit the Lookoup Transformation. Choose “Redirect rows to no match
output”

If no match found according to our criteria, take action

according to what we wire to the “No Match Output”
(we will see this later)
3. LOAD [DIMTIME]
5. Use NorthwindDW connection manager and choose the
[DimTime] table:
3. LOAD [DIMTIME]
6. Hook up the mappings:

Here we are matching the

[Day], [Month], [Year]
from the source (on the
left) and try to match it to
any rows in [DimTime] of
the DW (on the left).

If there are any rows that

match the Match Output
is executed.

If there aren’t any, the No

Match Output is
executed.

We will see this next.

3. LOAD [DIMTIME]
7. Connect the Lookup 8. Edit the OLE DB Destination. Choose NorthwindDW
to a OLE DB Destination. connection manager and choose the [DimTime] table:
Use “Lookup No Match
Output”:
3. LOAD [DIMTIME]
9. Create the corresponding
mappings:
4. LOAD [FACTSALES]
Now that we have loaded all our dimension tables we can then update/load the
Fact table.

When the dimension tables are loaded into the DW, new primary keys (the primary
key specific to the DW, not the AlternateKeys) were created which can then be
referenced from the fact table.
1. In the “Load into FactSales”
dataflow, create a OLE DB Source:
4. LOAD [FACTSALES]
2. Edit the OLE DB Source. Choose Northwind connection
manager and the following SQL Command:

Here we query all the data from Northwind

that we need to populate the fact table
4. LOAD [FACTSALES]
3. Create a Lookup 4. Edit the Lookup. Choose NorthwindDW connection and
Transformation for Product: [DimProduct] as the table
4. LOAD [FACTSALES]
5. In the columns menu, connect up ProductId to
ProductAlternateKey.
Then tick the ProductId checkbox on the right. Also change
the “Output Alias” to “DWProductId”:
Here we are matching the
[ProductId] from Northwind to
the [ProductAlternateKey] in
[DimProduct] table.

Remember that we have already

loaded data from [Northwind].
[Product] into [NorthwindDW].
[DimProduct], so we are trying
to find the record that we have
just loaded.

If we find a match we want to

add the [ProductId] from
[DimProduct] table into our
result set.

We name this new column as

DWProductId to not get
confused with the original
4. LOAD [FACTSALES]

6. Add Lookup Transformations to find CustomerId and

TimeId:

Try it!

(instructions on the next slides if you get stuck)

4. LOAD [FACTSALES]
7. Create a Lookup 8. Edit the Lookup. Choose NorthwindDW connection and
Transformation for CustomerId: [DimCustomer] as the table
4. LOAD [FACTSALES]
9. In the columns menu, connect up CustomerId to CustomerAlternateKey.
Then tick the CustomerId checkbox on the right. Also change the “Output Alias” to
“DWCustomerId”:
4. LOAD [FACTSALES]
10. Create a Lookup 11. Edit the Lookup. Choose NorthwindDW connection
Transformation for TimeId: and [DimTime] as the table
4. LOAD [FACTSALES]
12. In the columns menu, connect up OrderDay, OrderMonth, OrderYear to Day, Month, Year
respectively.
Then tick the TimeId checkbox on the right. Also change the “Output Alias” to “DWTimeId”:
4. LOAD [FACTSALES]
13. Create a OLE DB Destination: 14. Edit the OLE DB Destination. Choose NorthwindDW
connection and [FactSales] as the table
4. LOAD [FACTSALES]
15. Define mappings to the destination:

Notice that the primary keys

from the NorthwindDW
dimension tables that we have
found using the lookup
transformations were added as
new columns on the left.

We will use these values to

populate our FactSales table.
5. EXECUTE THE PACKAGE!