The facts about factless fact tables

In a BI project, there is a real thing as a factless fact table. In most business intelligence environments, fact tables will contain measures, or numeric values that are pulled from a process like Sales or Orders.

A factless fact table is a table with no facts. The table contains information about whether a transaction or event occurred or not.  In BI, a factless fact tables contains only foreign keys. So what does all this mean in business terms?

The best way to explain this is to take an example.

Suppose that I want to track the success or failure of customer logins to an e-commerce site.  An audit table may contain extra details of a login event, such as date and time or user ID. Each entry in such a table will have its own unique identifier, or unique key.

My factless fact table may contain only those foreign key values where failed transactions (failed logins) occurred.  These foreign keys refer to the unique key found in the login audit table whose event was a failed login attempt.

While this is an oversimplification I hope it illustrates the concept of factless fact tables.

I welcome your thought and experience with factless fact tables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s