Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Factless fact table

A factless fact table is a fact table that does not have any measures.  It is essentially an intersection of dimensions (it contains nothing but dimensional keys).

There are two types of factless tables:  One is for capturing an event, and one is for describing conditions.

An event establishes the relationship among the dimension members from various dimensions, but there is no measured value.  The existence of the relationship itself is the fact.

This type of fact table itself can be used to generate useful reports.  You can count the number of occurrences with various criteria.  For example, you can have a factless fact table to capture student attendance, creating a row each time a student attends a class.  The following questions can be answered:

  • Which class has the least attendance?
  • Which teachers taught the most students?
  • What is the average number of attendance of a given course?

All the queries are based on the COUNT() with the GROUP BY queries.  The interesting metrics are the nested GROUP BY so you can first count and then apply other aggregate functions such as AVERAGE, MAX, MIX.

The other type of factless table is for describing conditions and is called a “coverage table” by Ralph Kimball.  It is used to support negative analysis reports.  For example, a store that did not sell a product for a given period.  To produce such a report, you need to have a factless fact table to capture all the possible combinations of products that were on sale.  You can then figure out what is missing by comparing the factless table with the sales table for the list of products that did sell.

As another example, say an investment bank assigns a broker to each customer.  Each row in this factless fact table represents a bounded time period during which a broker was assigned to a particular customer.  This factless fact table can be compared to one that tracks investment transactions to find brokers who are not interacting with their customers, brokers who conducted transactions with accounts that belong to a different broker, etc.

More info:

Factless Fact Tables by Ralph Kimball

Factless Fact Tables by Chris Adamson

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...