MS BI Workshop #14 – Fact table modeling tips to improve performance and scalability
This is my second notes on fact table modeling. They are tips to improve performance and scalability.
Fact tables are often designed to be index light, meaning that indexes should be placed only to support reporting and cube processing.
Fact tables are often much larger in row count and data volume than your dimensions.
- Implementing table partitioning: table partitioning is very effective for this type of large fact tables. Sliding-window partition scheme, where you roll off the old partition and roll on new ones periodically, can drive the IO and query times down. Data loading and cube processing will also be faster since the new version of SQL Server 2008 and 2008R2 allow for increased parallelization of queries across partitions.
- Configuring and implementing data compression in SQL Server 2008 and 2008R2
- Using physical FK constraints to enforce relationship. There will be minimum amount of overhead in data loading and heavy-duty reporting. But for this minimum overhead, Analysis Services can optimize its processing based on the fact that it can assume these keys are keeping the data valid and disable some internal checks built into the processing algorithm. This will allow Analysis Services to consume the data from the fact and dimension tables much faster than if it didn’t have those keys.