Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Fact and Dimension Tables

Author  Topic 

zicitron
Starting Member

5 Posts

Posted - 2014-10-13 : 15:25:52
Hi all,

I'm new to SQL Server and I'm having a tough time understanding the differences between a fact table and dimension table.
Any pointers on online tutorials or websites which clearly explain these terms?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:12:27
A dimension table is one that is used as a reference. e.g. Customer, Employee, Supplier. These are not so volatile.
A fact table is one that is transactional, e.g. Order where there is a regular (e.g. daily) addition of new/updated data.

The Fact table serves as a hub with FKs to the Dim tables. Again think of the order analogy. An order item is for a certain customer and comes from some supplier and is handled by some employee. When you map them out, it looks like a starfish (hence the name star schema)
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-11-18 : 10:31:08
Yep, and you can also have a snowflake which would be something like subcategory into catgeory, for example car models in category and car makers in sub category.

Dimensions are used along with the Fact tables to show data depending on what detailed information you have aggregated in your Fact tables, the Dimensions is where you have your actual data where the Fact holds the key aggregations along with FK keys to each Dimension table. You would also create a date and time dimension based on the datatime fields of your records.

You will then use your cube for reporting from the data warehouse source away from production/live source.

We are the creators of our own reality!
Go to Top of Page
   

- Advertisement -