A Fact table consists of metrics, measurements, or facts of any business process. Facts and Dimensions are at the root of data warehouse. Facts are the metrics that senior management or board members of an organization use for making business decisions. Dimensional modeling brings together the relational model and realities of text and numerical facts. Any real life process is made of numbers and context describing the numbers.
All facts are always measured in numerals.
The context describing the facts is in verbose. For example sales facts are always best described with product, period, and store or region where it was sold and to whom it was sold. Facts measured can be additive, semi additive, or non-additive.
A Fact Table is most of the time defined by a grain. A grain is the most atomic form of data in the table. Example: The grain in a SALES table may be defined as “Sales of a Day by Product in a Store”.
The types are
• Transactional – The grain of a transactional table is normally mentioned as “1 row per entry in a transaction” example every entry in an invoice. Classically a transactional table holds data to a detailed level, creating dimensions with varied specifications associated to facts.
• Periodic Snapshot – Representation of facts in a given moment. A moment is a given period of time with in which the facts have been repeatedly.
• Accumulating Snapshot – Represents the life cycle of the key entity in a process. Most of the time it shows the transactions from beginning to end of a given process. For example fulfillment process of a telephone or mobile connection.
Understanding the fact table and its types may now indicate what clearly what is required to create one. Some key steps are to know the process, identifying dimensions of various facts such as location, time, and product or service types, listing the facts, and determine the atomic grains.
Identifying the business process should not be confused with the functions of an organization like sales or marketing but order fulfillment is a process that can be triggered by sales / marketing team.
Dimensions give structures to facts which best explain changes in them. Example: sales across the year and sales across regions in a country.
The grain identified for a fact table should be at the most atomic level possible to materialize on best utilization of the dimensions.
Facts have a many-one relationship with dimensions as we saw in the example for dimensions. Facts are to be defined after knowing the granularity.
Relationships with dimensions
Dimensional modeling is a familiar design discipline used to make these tables. The smaller the facts the greater are the dimensions. Facts can be related to multiple dimensions in different types of schemas such as star, snowflakes, and matrix.
All facts in a table may not be associated to the dimension but some are foreign keys. Foreign keys in a fact table associated with dimension table using the schemas should be surrogate keys.