October 30, 2003

No Detail Too Small

Although there's no substitute for atomic details, look into complementary consolidations

by Margy Ross & Ralph Kimball

Atomic fact tables are the core foundation of any analytic environment. Business analysts thrive on atomic details because they can be easily rolled up "any which way" by grouping on one or more dimension attributes. The robust dimensionality of atomic data is extremely powerful as it supports a nearly endless combination of inquiries. However, business analysts can't always live happily ever after on atomic details alone.

We've allocated significant space in this column to stress the importance of designing atomic fact tables with the following characteristics, as captured in our four-step dimensional design technique:

1. Business process. Focused on a single business process or event. In most organizations, a single operational source system collects or generates each set of process- or event-centric metrics.
2. Granularity. Atomic. In other words, the lowest level of detail captured during the business process.
3. Dimensions. Foreign keys to conformed dimension tables densely populated with descriptive attributes.
4. Facts. Performance metrics, typically numeric, consistent with the stated grain.

Accumulating the Atoms

In addition to atomic fact tables, you'll probably also build aggregated dimensional models. Aggregations and indexes are the most common tools for improving query performance. Summary aggregations may be structured as an OLAP cube or another relational star schema. Because the granularity is no longer atomic, you'll need a different fact table for the aggregated data, which typically exhibits the following characteristics:

1. Business process. Focused on a single business process or event, just like the atomic fact tables.
2. Granularity. Specified as a roll-up of the atomic facts.
3. Dimensions. Detail and/or summary conformed dimension foreign keys corresponding to the level of detail. When atomic data is aggregated, some dimensions may be completely eliminated. In other cases, the relevant dimension tables are shrunken, subset versions of the more detailed dimension tables.
4. Facts. Aggregated performance metrics consistent with the stated granularity.

Consolidating Across Processes

In addition to aggregated fact tables that roll-up facts from a single atomic fact table, we sometimes construct fact tables that combine data from multiple atomic fact tables. These cross-process or cross-event tables are referred to as second-level or consolidated fact tables. The consolidated fact tables are identified as enterprise data warehouse bus matrix rows, but are typically listed beneath the single-process or first-level matrix rows they are dependent upon. Consolidated fact tables exhibit slightly different characteristic patterns:

1. Business process. As the name implies, these fact tables look across discrete business processes or events.
2. Granularity. Represents the lowest level of detail common to all the processes. In the case of consolidated accumulating snapshots, the grain is typically one row per "object" moving through a pipeline of discrete processes.
3. Dimensions. Often multiple dates are corresponding to the major events or milestones, plus "least common denominator" conformed dimensions and degenerate dimensions from each underlying process.
4. Facts. Key metrics from each of the individual business process fact tables. Calculated metrics such as differences or lag metrics are also common.

• Privacy
• Your California Privacy Rights
• Copyright © 2008 United Business Media LLC