Database Schema Variants for Mixed OLTP and OLAP - Benchmarking Transaction and Analytical Processing Systems - page 95

Tab le 5.1 gives an overview of the changes in the SQL statements of the queries.
Only the changed parts of the SQL statements are presented and unmodified parts
are abbreviated with “ [...] ”. Queries not mentioned in the table are not changed
at all. This applies to all accesses to master data.

5.2.2 Snowflake Schema

The next schema variant further increases the level of denormalization by com-
pletely merging the sales and delivery entities on one hand and the billing and
accounting entities on the other hand. Thus, joins between transactional data tables
can be completely avoided in the OLAP queries making up the workload of CBTR.
This schema variant resembles a snowflake schema with shared dimension tables. It
represents the maximum level of denormalization that is appropriate for the set of
OLAP queries given in CBTR. No changes are applied to the master data tables to
create a star schema, because the CBTR OLAP queries would not benefit from joins
between dimension tables that represent the master data.
Figure 5.4 illustrates the snowflake schema variant. As can be seen two fact tables
have been created. The two fact tables share the product and customer dimension
tables. The sales-shipping facts table is produced by a left outer equijoin of the
sales facts table to the shipping facts table introduced in Sect. 5.2.1 . The join is
computed on the order ID reference and order item ID reference foreign keys, which
are removed in the pre-joining step. The left outer join is used, because shipping
data may not be present for some sales orders, but a sales order always exists for
shipments. However, a shipping may cover several sales orders, or a sales order
may be split over several shipments. By the left outer join, this relation is retained.
Similarly, the billing-accounting facts table is created by a left outer equijoin of the
billing facts table to the accounting facts table using the billing ID reference and
billing item ID reference foreign keys of the accounting table.
Tab le 5.2 gives an overview of the changes of the CBTR queries for the snowflake
schema variant. Only the differences to Table 5.1 are given as the snowflake schema
variant builds upon the document-oriented schema variant. The change column lists
all types of changes according to the above classification that have to be applied
when starting from the original benchmark schema in first normal form. In the daily
flash query only the accessed table is exchanged with the new pre-joined table, but
no other changes are included as the selection refers to the former sales header
and the original tuples are filtered by the distinct selection on order ID, which is
already included in the document-oriented schema variant. A similar behavior can
be observed for the orders by period and open items transactions.
For the write-access OLTP queries, another type of change has to be introduced
in the case of the snowflake schema. The fact tables now consist of transac-
tion tables where new tuples are added in different phases of the order-to-cash
process. For example, the sales-shipping fact table combines data that is created
during sales order processing and shipment processing. Shipment data may not be