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

8.4 Can we model splitting of flows without gateways? Are there cases
wherethisisnotpossible?
8.5 Give examples of the use of the several kinds of BPMN events.
8.6 What is a default flow? When should we use it?
8.7 Discuss why should we or should we not need a conceptual design phase
for ETL processes.
8.8 Why is BPMN appropriate for modeling ETL processes? Do you think
that there are situations where this is not the case?
8.9 Explain the rationale of the methodology studied in the chapter. What
are control tasks? What are data tasks?
8.10 What is the main difference between the diagrams for ETL design
proposed in this chapter and the typical BPMN diagram?
8.11 Discuss the advantages and disadvantages of representing, in Integra-
tion Services, a sequence of tasks as a single SQL query (see an example
in Sect. 8.5 ).
8.12 Compare Integration Services and Pentaho Kettle. What are the main
differences between them? For each element of the former (e.g., a flow),
describe the analogous one on the latter.
8.13 Discuss why it is not possible to perform a sequence of lookups in
Kettle, as it is done in Integration Services.
8.10 Exercises
8.1 Design the conceptual ETL schema for loading the Product dimension
of the Northwind data warehouse.
8.2 In the Northwind data warehouse, suppose that surrogate keys Catego-
ryKey and ProductKey are added, respectively, to the Category and the
Product tables, while the operational keys are kept in attributes Cate-
goryID and ProductID . Modify the conceptual ETL schema obtained in
Ex. 8.1 to take into account this situation.
8.3 Modify the conceptual ETL schema obtained in Ex. 8.2 to take into
account a refresh scenario in which products obtained from the
operational database may be already in the Product dimension in the
data warehouse. Use a type 1 solution for the slowly changing dimension
by which the attribute values obtained from the operational database
are updated in the data warehouse.
8.4 Modify the conceptual ETL schema obtained in Ex. 8.3 byusingatype
2 solution for the slowly changing dimension Product by which for the
products that have changed the value of the To attribute for the current
record is set to the current date and a new record is inserted in the
dimension with a null value in that attribute.
8.5 Design the conceptual ETL schema for loading the Supplier dimension
of the Northwind data warehouse.