We know
that fact table is a collection of many facts and measures having multiple keys
joined with one or more dimension tables. Facts contain both numeric and
additive fields. But factless fact table are different from all these.
A factless
fact table is fact table that does not contain fact. They contain
only dimensional 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 aggregate table 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.
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.
For eg.
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.
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.”
Subscribe to:
Post Comments (Atom)
Post a Comment