Extraction, Transformation, and Loading - Data Warehouse Systems: Design and Implementation - page 303

Then, an update column task is applied to remove the leading spaces (with
operation trim) from the attribute TerritoryDescription . The city key is then
obtained with a lookup over the table City in the data warehouse, which adds
the attribute CityKey to the flow. The data flow continues with a task that
removes duplicates in the assignment of employees to cities. Indeed, in the
Northwind operational database, New York appears twice in the Territories
table with different identifiers, and employee number 5 is assigned to both
of these versions of New York in the EmployeeTerritories table. Finally, after
removing duplicates, we populate the Territories table with an insert data
task, where a mapping matches the attribute EmployeeID in the database
with the attribute EmployeeKey in the data warehouse.
Fig. 8.25 Load of the Sales fact table
Figure 8.25 shows the conceptual ETL process for loading the Sales fact
table. This task is performed once all the other tasks loading the dimension
tables have been done. The process starts with an input data task that obtains
data from the operational database by means of the SQL query below:
SELECT O.CustomerID, EmployeeID AS EmployeeKey, O.OrderDate,
O.RequiredDate AS DueDate, O.ShippedDate,
ShipVia AS ShipperKey, P.ProductID AS ProductKey,
P.SupplierID AS SupplierKey, O.OrderID AS OrderNo,
ROW NUMBER() OVER (PARTITION BY D.OrderID
ORDER BY D.ProductID) AS OrderLineNo,