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:
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.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access