Home > Uncategorized > MS BI Workshop #14 – Fact table modeling tips to improve performance and scalability

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.
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: