Dimensional modeling: Fact tables and entities

A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit.

Fact tables and entities aggregate measures, or the numerical data of a business. To measure data in a fact table or entity, all of the measures in a fact table or entity must be of the same grain.

To obtain the most useful data in a fact table or entity, you should use measures that are both numeric and additive. Using these measures guarantees that data can be retrieved and aggregated, so that the business can make use of the wealth of business data in the database.

Fact tables and entities also contain foreign keys to the dimension tables. These foreign keys relate each row of data in the fact table to its corresponding dimensions and levels.

Fact tables and entities use primary keys that are composite keys. A composite key is made up of a subset of other keys. If a table or entity in a dimensional model uses a composite key, then that table is a fact table or entity. The use of composite keys causes the table or entity to have a many-to-many relationship with other tables and entities in the dimensional model.

Fact table and entity types

There are three types of fact tables and entities:

Transaction: A transaction fact table or transaction fact entity records one row per transaction.
Periodic: A periodic fact table or periodic fact entity stores one row for a group of transactions that happen over a period of time.
Accumulating: An accumulating fact table or accumulating fact entity stores one row for the entire lifetime of an event. An example of an accumulating fact table or entity records the lifetime of a credit card application from the time it is sent to the time it is accepted.

Note: You cannot explicitly specify the type of fact table or entity by using the workbench. To document the types of fact tables that you are using, you can add the information to the documentation.

The following table compares the different types of fact tables and entities. The table emphasizes that each has a different type of grain and that there are differences in how insert and update operations occur in each. For example, in transaction and periodic fact tables and entities, only insert operations occur. However, in an accumulating fact table or entity, the row is first inserted, and as a milestone is achieved and additional measures are made available, the table or entity is subsequently updated.

Table 1. Comparison of fact table types
Feature	Transaction	Periodic	Accumulating
Grain	One row per transaction	One row per time period	One row for the entire lifetime of an event
Dimension	Date dimension at the lowest level of granularity	Date dimension at the end-of-period granularity	Multiple date dimensions
Number of dimensions	More than periodic fact type	Fewer than transaction fact type	Highest number of dimensions when compared to other fact table types
Conformed dimensions	Uses shared conformed dimensions	Uses shared conformed dimensions	Uses shared conformed dimensions
Measures	Related to transaction activities	Related to periodic activities	Related to activities which have a definite lifetime
Database size	Largest size. At the most detailed grain level, tends to grow very fast.	Smaller than the transaction fact table because the grain of the date and time dimension is significantly higher	Smallest in size when compared to the transaction and periodic fact tables
Performance	Performs well and can be improved by choosing a grain above the most detailed	Performs better than other fact table types because data is stored at a less detailed grain	Performs well
Insert	Yes	Yes	Yes
Update	No	No	Yes, when a milestone is reached for a particular activity
Delete	No	No	No
Fact table growth	Very fast	Slow in comparison to transaction-based fact table	Slow in comparison to the transaction and periodic fact table
Need for aggregate tables	High, primarily because the data is stored at a very detailed level	No or very low, primarily because the data is already stored at a high aggregated level	Medium, because the data is primarily stored at the day level. However, the data in accumulating fact tables is lower than the transaction level.

Sample fact table

The following table tracks the sales of the products that are sold by the fictional Great Outdoors company:

GOSALESDW.SLS_SALES_FACT
ORDER_DAY_KEY
ORGANIZATION_KEY
EMPLOYEE_KEY
RETAILER_KEY
RETAILER_SITE_KEY
PRODUCT_KEY
PROMOTION_KEY
ORDER_METHOD_KEY
SALES_ORDER_KEY
SHIP_DAY_KEY
CLOSE_DAY_KEY
QUANTITY
UNIT_COST
UNIT_PRICE
UNIT_SALE_PRICE
GROSS_MARGIN
SALE_TOTAL
GROSS_PROFIT