How do you use degenerate dimensions to simplify fact tables?

1 Transactional fact tables

Transactional fact tables are the most granular and detailed type of fact tables. They capture each individual transaction or event that occurs in a business process, such as a sale, an order, or a shipment. Transactional fact tables may have many dimensions, each representing a different attribute of the transaction, such as customer, product, date, location, etc. They also have one or more numeric measures, such as quantity, price, or revenue, that reflect the performance or outcome of the transaction. (This section has been?updated?by LinkedIn editors based on member feedback.)

2 Periodic fact tables

Periodic fact tables are less granular and more aggregated than transactional fact tables. They capture the summary or snapshot of a business process over a fixed period of time, such as a day, a week, or a month. Periodic fact tables have fewer dimensions than transactional fact tables, as some dimensions are not relevant or meaningful for the chosen time period. For example, a periodic fact table for monthly sales may not include the time of day or the payment method dimensions. They also have one or more numeric measures, such as total sales, average price, or profit margin, that reflect the performance or outcome of the business process over the time period.

3 Accumulating fact tables

Accumulating fact tables are different from transactional and periodic fact tables in that they capture the changes or milestones of a long-running business process, such as a project, a contract, or a warranty. Accumulating fact tables have a fixed set of dimensions, such as project, customer, or product, that identify the business process. They also have multiple numeric measures, each representing a different stage or status of the business process, such as start date, end date, budget, cost, or revenue. Accumulating fact tables are updated as the business process progresses and reaches new milestones.

4 Degenerate dimensions

Degenerate dimensions are dimensions that have no attributes or descriptive information, but only a single identifier or key. They are used to simplify fact tables by eliminating the need for a separate dimension table. Degenerate dimensions are typically derived from the source data and are often codes or numbers that uniquely identify a transaction or an event. For example, a degenerate dimension for a sales fact table could be the invoice number or the order number.