Factless Fact Table

A factless fact table is a fact table this does not has any measures. It is essentially an intersection of dimensions. On the surface, a factless fact table does do sense, since a fact table was, after all, about facts. However, there is situations where has this kind of relationship makes sense in data warehousing.

For example, think on a record of student attendance in classes. In this case, the fact table would consist of 3 dimensions:the student dimension, the time dimension, and the class Dimen Sion. This factless fact table would look like the following:

The only measure so can possibly attach to each combination are "1" to show the presence of that particular Combinati On. However, adding a fact that shows 1 are redundant because we can simply use the COUNT function in SQL to answer the Same questions.

factless fact tables offer the most flexibility in data warehouse design. For example, one can easily answer the following questions with this factless fact table:

• How many students attended a particular class on a particular day?

• How many classes on average does a student attend on a given day?

Without using a factless fact table, we'll need and separate fact tables to answer the above. With the above factless fact table, it is becomes the only fact table and the needed.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------

The following is transferred from http://www.cnblogs.com/lijun4017/archive/2010/08/04/1792293.html

Analysis of non-factual fact table

In the Data warehouse in dimension modeling, there is a fact table called factless fact table, which is generally translated into "non-fact-based facts tables". In the fact table, it is common to save 10 or so dimension foreign keys and a number of metric facts, which are the key to the fact table. In non-fact-based fact tables, there are no such metric facts, only multiple dimension foreign keys. A non-factual fact table is usually used to track some events or to describe the scope of certain activities. The following examples illustrate.

The first type of non-factual fact table is the fact table used to track events. For example: Student registration events, schools need to track students by semester. The dimension table includes semester dimension, course dimension, Department dimension, student dimension, registered professional dimension and acquisition credit dimension, while fact table consists of the primary key of these dimensions, the fact is only registered number, and the constant is 1. Such a fact table can answer a large number of questions about the registration of university classes, mainly to answer the number of registrations in various cases.

The second type of non-factual fact table is a fact table used to illustrate certain areas of activity. Example: the Promotion scope fact table. Usually the Sales Fact table can answer the sales of the promotional items, but there is no answer to the promotional items that are not sold. At this time, by establishing the promotion scope fact table, the store needs to promote the product to establish a separate fact table to save. Then, this promotional scope fact sheet and the Sales Fact table can be drawn out which promotional items are not sold. The fact table for such promotions is only used to illustrate the scope of the promotion, without any measure of fact.

The following is transferred from http://www.cnblogs.com/lijun4017/archive/2010/08/04/1792440.html

We use a non-factual fact table to record many-to-many relationships between dimensions, but there are no numbers or text facts on the relationship. A non-factual fact table is typically used to record events or scope information. Common examples are: identifying the scope of a product's promotion, recording student attendance or registering events, etc.

In this tip we use a non-fact-based fact table to supplement the slow-change-dimension processing strategy.

For a large-scale business, there will be tens of millions of customers. For this large customer table, we need to keep track of customer changes, and we can respond with the slow change dimension strategy of type 2. For example, for a large customer table, we try to reduce the number of records generated by type 2 and establish four micro dimensions, namely customer credit attributes, customer parameters, market trends, and customer detail addresses. So in the transaction granularity fact table we keep five foreign keys for the customer's information, which are related to the customer's different information.

At the same time, we should also support the customer's overall information access. To facilitate access to the customer's overall information, we can create another cycle snapshot fact table that loads the relationship of the customer dimension and its associated micro dimension every day. However, it is not a good choice to keep a snapshot of tens of thousands of records every day.

At this point, we can consider establishing a non-factual fact table to solve this problem. We can create a fact table in which only the customer dimension and its corresponding micro-dimension's foreign key, we can insert a record in the fact table when the customer has a type 2 change or when a new micro dimension record is generated. In this fact table can also be added to the effective date and expiration date, you can add a column to identify the current customer information records, can be changed cause column

Factless Fact Table