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

D.UnitPrice, Quantity, Discount,
D.UnitPrice * (1-Discount) * Quantity AS SalesAmount,
O.Freight/COUNT(*) OVER (PARTITION BY D.OrderID) AS Freight
FROM Orders O, OrderDetails D, Products P
WHERE O.OrderID = D.OrderID AND D.ProductID = P.ProductID
A sequence of lookups follows, which obtains the missing foreign keys for the
dimension tables. Finally, the fact table is loaded with the data retrieved.
8.4 Integration Services and Kettle
To be able to understand how the conceptual ETL design for the Northwind
data warehouse can be implemented using existing tools, in this section we
give a brief description of Microsoft Integration Services and Pentaho Data
Integration (also known as Kettle, the name we use from here on).
8.4.1 Overview of Integration Services
Integration Services is a component of SQL Server that can be used to
perform data migration tasks and in particular to implement and execute
ETL processes.
In Integration Services, a package is basically a workflow containing a
collection of tasks executed in an orderly fashion. A package consists of a
control flow and, optionally, one or more data flows . Integration Services
provides three different types of control flow elements:
￿ Tasks , which are individual units of work that provide functionality to a
package.
￿ Containers , which group tasks logically into units of work and are also used
to define variables and events. Examples of containers are the Sequence
Container and the For Loop Container .
￿ Precedence constraints , which connect tasks, containers, and executables in
order to define the order in which these are executed within the workflow
of a package.
A control flow orchestrates the order of execution of package components
according to the precedence constraints defined. Among the many different
kinds of tasks supported by Integration Services, there are data flow tasks
(which run data flows to extract data, apply column-level transformations,
and load data), data preparation tasks (which copy files and directories,
download files and data, profile data for cleansing, and so on), Analysis
Services tasks (which create, modify, delete, and process Analysis Services