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.
Factless Fact Tables by Ralph Kimball
Factless Fact Tables by Chris Adamson
Data Warehouse Design Considerations
About James SerraJames is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
View all posts by James Serra →
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.
A factless fact table is fact table that does not contain fact.They contain only dimesional keys and it captures events that happen only at information level but not included in the calculations level.just an information about an event that happen over a period.
A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of factless fact tables include:
- Identifying product promotion events (to determine promoted products that didn’t sell)
- Tracking student attendance or registration events
- Tracking insurance-related accident events
- Identifying building, facility, and equipment schedules for a hospital or university
Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information.There are two types of factless fact tables: those that describe events, and those that describe conditions. Both may play important roles in your dimensional models.
Factless fact tables for Events
The first type of factless fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless.Sometimes there seem to be no facts associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.
The above fact is used to capture the leave taken by an employee.Whenever an employee takes leave a record is created with the dimensions.Using the fact FACT_LEAVE we can answer many questions like
- Number of leaves taken by an employee
- The type of leave an employee takes
- Details of the employee who took leave
Factless fact tables for Conditions
Factless fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events.It is used to support negative analysis report. For example a Store that did not sell a product for a given period. To produce such report, you need to have a fact table to capture all the possible combinations. You can then figure out what is missing.
For eg, fact_promo gives the information about the products which have promotions but still did not sell
This fact answers the below questions:
- To find out products that have promotions.
- To find out products that have promotion that sell.
- The list of products that have promotion but did not sell.
This kind of factless fact table is used to track conditions, coverage or eligibility. In Kimball terminology, it is called a "coverage table."
We may have the question that why we cannot include these information in the actual fact table .The problem is that if we do so then the fact size will increase enormously .
Please Check out our Latest Post:How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL