Databases Reference
In-Depth Information
SQL, shown in Figure 5.2, which will extract data from the Market Basket
Table.
Because the Market Basket Table is a staging table, never to be used by
anything other than ETL applications, the data can be cycled through
the Market Basket Table in manageable batches. The use of manageable
batches of Market Basket data means the data volumes moving from the
data warehouse to the Market Basket Table can be managed at a low enough
volume to mitigate adverse effects on the data warehouse; furthermore, the
same managed data volumes will help optimize the recursive SQL used to
extract data from the Market Basket Table. Rather than recursively query
all transactions for the entire enterprise, the ETL application that manages
the data flow through the Market Basket Table can move data in and out
of the Market Basket Table in managed subsets. Remember, the recursive
SQL will recursively join all the rows in each Itemset individually, not all
the rows throughout the enterprise. Therefore, the only minimum require-
ment for the recursive SQL to succeed is that each Itemset in the Market
Basket Table be complete and lack no granular transaction rows. So, as
long as the Itemsets in the Market Basket Table are complete, the recursive
SQL can successfully deliver data into the Market Basket BI Table with one
Itemset, one hundred Itemsets, or one million Itemsets.
Market Basket BI Table
The Market Basket BI Table is a class of tables that share the construction
of a Market Basket BI Table. The general concept of a Market Basket BI
Table is shown in Table 5.3. The SQL statement used to query a Market
Basket BI Table, shown in Figure 5.7, uses a SUM and GROUP BY method.
This query method simplifies the optimization of the Market Basket BI
Tables. The intention of this Market Basket solution design is to isolate the
recursive join operation from the aἀ nity calculation operation. In that
way, the Market Basket Analysis BI View is intended to perform one oper-
ation in one way, which presents the opportunity to optimize the Market
Basket BI Table and the Market Basket Analysis BI View for each other.
A Market Basket BI Table can be retained for as long as an analyst needs
to refer back to that table. The iterative and repetitive nature of Market
Basket Analysis creates the need to compare observations, refine query
conditions, and then compare again. For that reason an analyst may com-
pare query results from multiple tables, viewing the result sets in a side-by-
side fashion. This also invites the “junk drawer” syndrome. If the retention
