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

Table 8.1 (continued)
Update Column: replaces column values from the flow
Columns List of Col=Expr computing the new value of column Col from
the input flow from expression Expr
[Condition] Boolean condition that the records in the input flow must satisfy
in order to be updated. If not specified all records are updated
Update Column: replaces column values from the flow
Columns List of column names from the input flow whose values are
changed in the output flow
Database Name of the database
Query SQL query
[Condition] Boolean condition that the records in the input flow must satisfy
in order to be updated. If not specified all records are updated
Update Data: update tuples of a database corresponding to records in the flow
Database Name of the database

Table Name of the table

Columns List of Attr=Expr computing the new value of attribute Attr in

the table from expression Expr

Where List of column names from the input flow

Matches List of attribute names from the table

is used. The file contains three fields separated by tabs and begins as shown in
Fig. 8.17 a, where the first line contains field names. In the case of cities located
in countries that do not have states, as it is the case of Singapore, a null value
is given for the second field. The above file is also used to identify to which
state the city in the attribute TerritoryDescription of the table Territories in the
Northwind database corresponds. A temporary table in the data warehouse,
denoted TempCities , will be used for storing the contents of this file. The
structure of the table is given in Fig. 8.17 b.
It is worth noting that the keys of the operational database are reused in
the data warehouse as surrogate keys for all dimensions except for dimension
Customer . In this dimension, the key of the operational database is kept in
the attribute CustomerID , while a new surrogate key is generated during the
ETL process.
In addition, for the Sales table in the Northwind data warehouse, the
following transformations are needed:

￿ The attribute OrderLineNo must be generated in ascending order of
ProductID (in the operational database, there is no order line number).
￿ The attribute SalesAmount must be calculated taking into account the unit
price, the discount, and the quantity.
￿ The attribute Freight , which in the operational database is related to the
whole order, must be evenly distributed among the lines of the order.