Dimensional Modelling, Facts and Measurements

Dim=Dimension tale, Fact= Fact table

Dimensional modeling

Dimensional modeling always uses the concepts of facts, and dimensions. Facts are typically numeric values that can be aggregated, and dimensions are groups of hierarchies and descriptors that define the facts. For example, sales amount is a fact; timestamp, product, register#, store#, etc. are elements of dimensions. Dimensional models are built by business process area, e.g. store sales, inventory, claims, etc.

More at Wikipedia

What is fact table?

A fact table is a table that contains the measures of interest. For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.

What is dimensional table?

Dimensional table maintains information that represents attributes of the business, contains relatively static data. These tables are connected to fact table through foreign key. These are smaller than fact tables.

• A fact is a measurement.
• Facts are based on dimensions.
• Facts are usually additive but may not be true all the time.
• Dimension table contains business elements.
• These tables supply the measurements to the fact tables.
•  Multiple fact tables may reference the dimension tables.

As shown in the diagram the fact table contains measurement fields along with foreign keys pointing to the dimensions.