Facts Vs. Dimensions Tables in a Database

Facts and dimensions form the core of any business intelligence effort. These tables contain the basic data used to conduct detailed analyses and derive business value. This article takes a look at the development and use of facts and dimensions in a database.

What Are Facts and Fact Tables?

Fact tables contain the data corresponding to a particular business process. Each row represents a single event associated with a process and contains the measurement data associated with that event.

For example, a retail organization might have fact tables related to customer purchases, customer service telephone calls, and product returns. The customer purchases table would likely contain information about the amount of the purchase, any discounts applied, and the sales tax paid.

The information contained within a fact table is typically numeric data, and it is often data that can be easily manipulated, particularly by summing together many thousands of rows. For example, the retailer described above may want to pull a profit report for a particular store, product line, or customer segment. The retailer can do this by retrieving information from the fact table that relates to those transactions, meeting the specific criteria, and then adding those rows together.

What Is a Fact Table Grain?

When designing a fact table, developers must pay careful attention to the grain of the table, which is the level of detail contained within the table.

The developer designing the purchase fact table for the retail organization described above would need to decide whether the grain of the table is a customer transaction or an individual item purchase. In the case of an individual item purchase grain, each customer transaction would generate multiple fact table entries corresponding to each item purchased.

The choice of grain is a fundamental decision made during the design process that can ​significantly affect the business intelligence effort down the road.

What Are Dimensions and Dimension Tables?

Dimensions describe the objects involved in a business intelligence effort. While facts correspond to events, dimensions correspond to people, items, or other objects.

In the retail scenario used in the example, we discussed that purchases, returns, and calls are facts. On the other hand, customers, employees, items, and stores are dimensions and should be contained in dimension tables.

Dimension tables contain details about each instance of an object. For example, the items dimension table would contain a record for each item sold in the store. It might include information such as the cost of the item, the supplier, color, sizes, and similar data.

Relationship Between Fact and Dimension Tables

Fact tables and dimension tables form a database relationship. Returning to the retail model, the fact table for a customer transaction would likely contain a foreign key reference to the item dimension table, where the entry corresponds to a primary key in that table for a record describing the item purchased.