I would like to know about star schemas with multiple fact tables. What are the advantages of having multiple fact star schemas? What scenarios trigger multiple facts star schemas?


Les Barbusinski’s Answer: There are two basic drivers for having multiple fact tables in a given star schema:

  • Aggregates
  • Temporal Perspective

Aggregate fact tables can significantly improve the performance of BI reports that have no need for detailed transaction-level data. Typically, an aggregate table will eliminate one or more dimensions from its foreign keys, and summarizes the data accordingly. Having an aggregate fact table in a star schema allows a BI tool to provide drill-down capabilities from summary metrics in the aggregate fact table to detail metrics in the "base" fact table.
"Temporal perspective" refers to the version of the dimensions that are connected to a particular fact table, and only comes into play where you have slowly changing dimensions Typically, a fact table can represent its transactional information in an "as was" or an "as is" time perspective. An "as was" perspective is typically applied to snapshot metrics, while an "as is" perspective is typically applied to "cumulative" metrics. For example, an "as was" perspective would show monthly sales totals for Region 7 based on the stores that reported to that Region in each month, while an "as is" perspective would show Year-to-Date sales totals for Region 7 based on the stores that report to that Region today (i.e., stores that report to Region 7 today may have reported to a different region in a prior month). Since the dimensional values for a fact may differ with "temporal perspective" (e.g., Store 112 reported to Region 3 in February vs. Store 112 reports to Region 7 today), you must have two different fact tables to store the same information.

Hope this helps.

Chuck Kelley’s Answer: First, the fact table is the intersection of the dimensions. Therefore, as a general rule, I believe that you would have all the facts in a fact table that is associated with those dimensions. Most of the time, multiple fact tables are due to having a different set of dimensions tied to a fact. Given that understanding, the only scenario that would trigger multiple fact tables is when a fact has different set of dimensions. The advantage is that your data would be correctly identified and stored in your data warehouse/data mart.

Clay Rehm’s Answer: The reason you have multiple fact tables is the fact that either each fact table is storing different data from each other, or at it is the same data but at a different level of granularity. This will happen because over time, data is captured using the business rules that were available at the time. You may have historical data that is not at the same level of detail than your current data.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access