My first Publication Agile-Data-Warehouse-Design-eBook | Page 278

258
Chapter 8
How Many
Derived Fact Table Patterns
Problem/Requirement
Missing evolving
events cause BI
pain and suffering
The unhappy user’s comparison problems, in Figure 8-19, are not so much drill-
across limitations, as poor or missing design. Orders and shipments are not dis-
crete events that can be fully analyzed in isolation using transaction fact tables.
They are evolving event milestones that constantly need to be compared to each
other and to deliveries, returns and payments to provide key measures of process
performance. Ad-hoc queries shouldn’t have to try to join these events together
every time, especially if there are complex M:M relationships between them.
Solution
Figure 8-20 shows what the user really needs: an orders accumulating snapshot
that can be queried using simple single-pass SQL. Following the agile approach to
Developing Accumulating Snapshots , outlined earlier in this chapter, this snapshot is
delivered as a derived fact table (DF), by merging the two existing order and
shipments transaction fact tables.
Figure 8-20
Happy BI user:
derived fact table
to the rescue
Derived fact tables
solve difficult BI with
simple ETL rather
than complex SQL
A sliced fact table
contains a subset of
a base fact table
A pivoted fact table
transposes base fact
table rows into fact
columns
Derived fact tables are built from existing fact tables to simplify queries. They use
additional ETL processing and DBMS storage, rather than more complex BI and
SQL, to answer difficult analytical questions. In addition to aggregates, there are
three other types of derived fact table: sliced, pivoted, and merged.
Sliced fact tables contain subsets of base fact tables; for example, UK sales
derived from a global sales fact table. Sliced fact tables can support restricted
row-level access and data distribution needs as well as enhanced performance
for users who only need a subset of the data. They are often used in conjunc-
tion with swappable dimensions (SD) that contain matching subsets of dimen-
sional values.
Pivoted fact tables transpose row values in a base fact table into columns; for
example, a fact table with nine facts derived from a base transaction fact table
with a single fact that records nine transaction types. Pivoted fact tables make
fact comparisons and calculations simpler. The same rows-to-columns ap-
proach can also be used to create bitmap dimensions (see Chapter 9) that sup-
port combination constraint queries.