How do you design a factless fact table for event tracking?

2 Why use a factless fact table?

A factless fact table can help you answer questions about the frequency, pattern, or sequence of events or activities, such as how many customers visited a website in a month, what are the most common products added to a cart, or what are the steps involved in a service process. A factless fact table can also enable you to perform complex analysis, such as market basket analysis, cohort analysis, or funnel analysis, by joining it with other dimension tables or fact tables.

3 How to design a factless fact table?

To design a factless fact table, you need to identify the events or activities that you want to track, and the dimensions that describe them. For example, if you want to track the website visits of your customers, you need to define the dimensions of date, time, customer, session, page, and device. Then, you need to create a table that has a foreign key column for each dimension, and optionally, a surrogate key column that uniquely identifies each row. For example, your factless fact table for website visits could look like this:

visit_id | date_id | time_id | customer_id | session_id | page_id | device_id
---------------------------------------------------------------------------
1        | 20210101 | 800     | 1001        | 1          | 101     | 1
2        | 20210101 | 801     | 1001        | 1          | 102     | 1
3        | 20210101 | 802     | 1002        | 2          | 103     | 2
4        | 20210101 | 803     | 1002        | 2          | 104     | 2
...        

4 What are the benefits of a factless fact table?

A factless fact table can be a great asset for event tracking, as it is flexible, scalable, simple, and intuitive. Furthermore, it is efficient and fast due to its small size and ability to be joined with other tables quickly using indexes. These characteristics make it a great choice for tracking events.

5 What are the challenges of a factless fact table?

Factless fact tables can present certain challenges for event tracking, such as having a large number of rows due to recording each occurrence of an event or activity, which can impact data warehouse performance and storage. Additionally, they may have a low level of granularity, as they do not capture any numerical or qualitative information about the events or activities. Furthermore, they do not provide direct measures or facts that can be used for analysis or reporting, thus requiring additional logic or transformation.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?