Databases Reference
In-Depth Information
Hierarchy_Key. The SQL in Figure 14.5 juxtaposes the Driver Instance_
Key with the Correlation Entity_Key.
The output shown in Table 14.7 is likewise analogous to the output in
Table 7.8. The lower hierarchical level is the Driver Object while the higher
hierarchical level is the Correlation Object.
entity Driver object and Instance correlation object
The next step in the Time Variant Market Basket ETL is to juxtapose the
Itemsets by using the Entity_Key as the Driver Object and the Instance_
Key as the Correlation Object. The SQL in Figure 14.6 uses the data already
juxtaposed in the Market Basket BI Table in another controlled batch of
Market Basket ETL.
The inequality statement (DRIVER_ENTITY_KEY <> CORR_
ENTITY_KEY) causes an Entity_Key to never juxtapose with any of its
hierarchically subordinate Instance_Keys. Unlike the previous Market
Basket ETL step, which could change the inequality to DRIVER_
INSTANCE_KEY<>DRIVER_INSTANCE_KEY, this Market Basket
ETL step cannot change the inequality to DRIVER_INSTANCE_
KEY<>DRIVER_INSTANCE_KEY. To do so would cause the juxtaposi-
tion of Driver Entity_Key to Correlation Instance_Key to only apply to the
Driver Entity_Key, rather than the Entity_Key of each row of the Market
Basket BI Table, which would generate bogus results.
The SQL in Figure 14.6 is analogous to the SQL in Figure 7.6. The output
in Table 14.8 is analogous to the output in Table 7.9. The “1 AS DRIVER_
COUNT” statement resets the count of Itemsets back to one because the
count of Itemsets is not additive. In the transformation of the grain of the
Driver Object from Instance_Key to Entity_Key, the count of Itemsets
cannot be summed. Instead, in this ETL step, the count of Itemsets can
only be reset to one. The Quantity and Dollars metrics, however, are addi-
tive and can be summed in the transformation from Instance_Key to
Entity_Key.
The SQL in Figure 14.7 is analogous to the SQL in Figure 7.7. In both SQL
statements the parent hierarchy is the Driver Object and the child hierar-
chy is the Correlation Object because the Driver and Correlation subque-
ries join on their Driver_Entity_Keys (DRIVER.DRIVER_ENTITY_KEY
= CORR.DRIVER_ENTITY_KEY). The output in Table 14.9 is analogous
to the output in Table 14.10.
