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

Design Patterns for High Performance Fact Tables and Flexible Measures
Merged fact tables combine facts and dimensions from two or more base fact
tables, summarized to a common granularity; for example, a fact table that
combines targets with summarized actual sales, or an accumulating snapshot
derived from milestone transaction fact tables. Merged fact tables simplify
cross-process analysis by replacing complex drill-across queries and expensive
joins with single star queries.
259
A merged fact table
combines multiple
base fact tables,
summarized to a
common granularity
DF: used as a table code to identify a derived fact table constructed from one or
more existing fact tables. Used as a column code to identify derived facts that can
be calculated (possibly in a view) from other facts.
Data warehouse designs routinely fail to take full advantage of derived fact tables.
Often there is a false impression that once the fact tables on the matrix have been
loaded: “That’s the data stored now, the major ETL development is done, and
everything from here on out is BI”. This can leave BI users and developers strug-
gling to answer increasingly complex business questions. In all forms of agile
development, project teams hold end of sprint meetings, known as retrospectives,
to discuss what was successful and what could be improved. For DW/BI, retrospec-
tives should include BI developers sharing their most common reporting com-
plexities with the team to see whether these queries can be simplified by derived
fact tables and other ETL enhancements. DW/BI
Merged fact tables are often referred to as consolidated data marts when they are
used to combine and summarize facts from several different business processes on
a periodic basis. These “one-stop shop” data marts are incredibly popular with
stakeholders because they provide high performance fact access in a format suit-
able for simpler BI tools. Common consolidated data marts include: Consolidated data
retrospectives
should re-examine
the design
periodically, to see if
additional ETL or
derived fact tables
can simplify difficult
queries
marts are the
periodic equivalent
of accumulating
snapshots
Customer relationship management data marts that provide a so-called “360°
customer view” by summarizing measures from all the individual fact tables
that relate to “customer touch points”.
Profitability data marts that combine revenue with all the elements of cost, to
support product or service profitability analysis.
Consequences
There is often pressure from business stakeholders to dispense with the details and
build highly summarized consolidated data marts directly from operational
sources to provide “quick win” key performance indicator (KPI) dashboards.
Unfortunately, data marts that summarize many different business processes and
consolidate multiple operational sources are literally the last thing you should
build. Apart from the ETL risks, the lack of detail rapidly undermines confidence
in the KPIs when users cannot drill-down deep enough to explain the figures and
view actionable information. Instead, consolidated data marts should be developed
incrementally as derived fact tables: derived from atomic-level fact tables.
Don’t attempt to
build consolidated
data marts before
you have loaded
atomic detailed star
schemas