Home > Uncategorized > MS BI Workshop #13 – Fact table modeling

MS BI Workshop #13 – Fact table modeling

Finally here is my notes about fact table modeling.

  • A fact table is the center of the star schema
  • It contains two primary types of data: Key columns and Data columns.
  • Fact table holds the keys to relate the needed dimension tables
  • Many OLAP tools, such as Analysis Services, look for star schema model and are optimized to work with it. So the fact table is a critical piece of the puzzle.

Process of designing fact table:

  • Decide on the data to analyze
  • Pick the level of granularity. Actually your choice of dimension tables has largely determined the granularity of your fact table.
  • Decide how your will load the fact table. A transactional table will load transactions at intervals from OLTP system. A snapshot fact table will load all the rows of inventory/snapshot-style data from the day, and allow users to see the current status and information based on the date the information was loaded.
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: