My first data warehousing tip is to store fact data at the leaf level. If you aggregate your fact data as part of your ETL/ELT then you are painting yourself into a corner. Here’s why:
- Leaf level data with an associated business key allows you to map your fact data back to your source data, giving your data traceability and credibility if it’s ever questioned.
- You’ll have flexibility in querying. If you haven’t aggregated then you’ll have every possibly way of slicing and rolling up your data as you need.
- Incrementally loading new and changed data is simplified as you can compare source records to destination records, and avoid clearing and reloading fact tables because aggregations have changed.
I’m not saying that you shouldn’t aggregate your data. Tools like SSAS are designed specifically for this and allow you to aggregate data in a flexible and efficient way, and if you do still want to aggregate your fact data in your data warehouse or data marts you can. Just make sure you have a leaf level fact table and build your aggregate fact table on that.