Factless Fact Tables
Posted by decipherinfosys on April 7, 2008
There is a term that is used in dimensional modeling which is called Factless Fact Tables. Sounds like an oxymoron since what use is a fact table if it is not recording any facts? Factless fact tables are those that fact tables that consist of only keys i.e. they do not have any measures in them but only keys pointing to the different dimensions. These tables essentially capture the M:N (many to many) relationship between the different dimensions and do not contain any numeric or text facts in them. We have seen two practical scenarios for the usage of factless fact tables:
1) A common use of such tables is to track events. Taking the example of the medical industry (the same one that we did in our previous example of dimensional modeling), when tracking insurance related events, we had to create a factless fact table in order to track the different events which covered the M:N relationships between the different dimensions in the system.
2) The second scenario is called a coverage table. Let’s consider a retail store for this example. If we are tracking the sales of different products in the store, we will more than likely have these dimensions:
Date: To track the dates for the sale.
Store: To track which store and location the sale was made at.
Product: To track which product the sale was for.
Promotion: Any promotions that are put into place to enhance the sales.
And then in the middle of it, we will have our SALES_FACT table which will record the dollar amount, the units sold etc. in addition to the keys to our dimension tables. So, for each sale that is made, all this information will be recorded and will be made available for analysis. But what about the products that were put on a promotion but did not sell?
There are two ways of handling this: a) create records in the SALES_FACT table with 0 as the sales for those products under that time duration for a store during that promotion. Though this is logically valid, this will bloat the table data my many folds since we will be creating entries into the fact table for all the products for that day in a given store during the promotion scheme. A coverage factless fact table comes to the rescue under such a situation. One can create such a fact table with only the 4 keys that point to the dimensions mentioned above. Having such a table allows us to record each product in each store that is on a promotion in a given promotion time frame. This complex M:N relationship is captured by making use of the factless fact table. Now, going back to the question: “But what about the products that were put on a promotion but did not sell?“, we can get that answer by querying the coverage factless fact table to get the products that were on promotion on a given day in that store and then remove the products that were recorded in the SALES_FACT table for that store for that day since it recorded the products that did sell.
Sorry, the comment form is closed at this time.