We know that fact table is a
collection of many facts and measures having multiple keys joined with one or
more dimesion 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 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.
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."
Note:
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 .
Factless fact table is crucial in
many complex business processes. By applying you can design a dimensional model that has no
clear facts to produce more meaningful information for your business processes.Factless
fact table itself can be used to generate the useful reports.
Please Check out our Latest Post: How Nested Tables can be
stored and retrieved in a Normal table-ORACLE PL/SQL
Very nice explaining
ReplyDeleteNice explanation with diagram....:)
ReplyDeletevery useful
ReplyDeleteVery Good Explanation. Thanks a lot
ReplyDeleteThanks ALL for the supporting comments
ReplyDeleteGreat article!
ReplyDeleteThank u .very informative!!!
ReplyDeleteThanks a lot ....very good explanation
ReplyDeleteThanks for the support
Deletethx man...
ReplyDeleteThank you. Nice article.
ReplyDeleteKeep up the good Work
ReplyDeletePerfect explanation !!!! Great job..:)
ReplyDeletethanks for giving valuable and accurate contents and keep going
ReplyDeleteLovely explanation!
ReplyDeleteexplained very well wid example
ReplyDeleteGreat explanation with valid scenarios!
ReplyDeleteNice ! thanks
ReplyDeleteGreat post !!!
ReplyDeleteThanks....Good Post
ReplyDeleteSuperb Article ..Thanks :)
ReplyDeleteThank you all ......please check out our latest post on Unix @ http://dwhlaureate.blogspot.in/2013/12/how-to-do-sorting-in-unix.html
ReplyDeleteVery clear explanation
ReplyDeleteahha ..
ReplyDeleteLife Saver!!
Good info..
ReplyDeleteThanks for information, I am new to DWH and want to know why we are calling it factless fact table, why it cant be just dimention table?
ReplyDeleteVery good explantions. Keep it up..!!!
ReplyDeletev.good information
ReplyDeleteNice article
ReplyDeleteHi All,
ReplyDeleteJust trying to give answer for above question "why we are calling it factless fact table, why it cant be just dimention table?"
Dimension table - A table having detailed information of an object. Here object can be a product, store, customer, etc. And will have one primary key which will be used in Fact table
Fact table - A table having facts, which are used to measure the performance of the business using the primary key of the dimension table.
So, there could be one fact table to capture number of one particular product sold from a Store one a given period. If that particular product is not sold from that store in that given period, then in that table there will be no fact only primary keys of each dimension table will available.
Since, Factless fact table will have multiple primary keys which are coming from different Dimension table whereas Dimension table will have only one primary key. Hence, Factless fact table can't be called dimension table.
very good explanation..
ReplyDeleteHi
ReplyDeleteI have a quick question. How do we load coverage table with the information that a product did not sell in a perticular period?
I guess etl process has to execute appropriate sql query each time it loads the table ?
For event tracking its understandable that an entry gets created to track it, and its simple.
how does a factless fact work with a type 2 dimension? lets say i have a factless fact which associates customers and customer groups (customer can belong to many groups). customer is also type 2, when the customer record gets updated, a new record is inserted with a new surrogate key. at this point, the new customer record has lost the relationship to the customer group via the factless fact. thanks for any help.
ReplyDeleteVery good article. Thanks..
ReplyDeleteCan you please explain why to go for fact less fact table for product promotions event .can't we do it with normal fact table (with cumulative or snapshot)
ReplyDeleteGreat information.. thanks for sharing
ReplyDeleteHi.....I have a DW dimensional model design query as a part of DW Design.....I have many transaction tables in source system i.e. 41 event tables.
ReplyDeleteout of these all tables, Application table is the key where we receive the single application from each applicant/student. As part of the application validation, verification of submitted data with relevant physical documents, re-request of necessary documents, approval of the application, issuing of approval letter etc each applicant may have at least 20 events based on applicant circumstances....it means every applicant may not go through 40 events but some application may have 40 events…. based on the data applicant have provided it will be decide whether applicant need to go through how many events out of 40.
Also each applicant will have ‘n’ no of rows for each event along with event logged datetime and other relevant information which will marked as dim tables in DW but each event table have 10-30 columns depends on the event.
It means Application table is master table and remaining 40 tables are childs and every child table have its own number of columns and unknown number of child row for each applicant.. A application may have one child table or 40 child tables or any number between 1-40 and unknown number of child row in each child table. Every table mandatory have row logged time of each event table activity.
Now I need to create the DW using dimensional model and my model must provide every atomic value of every event table so we can’t accumulate the values.
So I dont understand how can we create the single fact table using these 41 tables as relationship is one – many and unknown row/columns existed in each child table. So its not possible create single fact table as per my knowledge. I thought to create the 40 fact tables by linking those all using a Factless Fact bridge table so that each event table and its dimensions will be considered as a data mart out of entire data warehouse….but as per my knowledge creating 40 fact tables is design flaw.
Most of these table don't have any measure except recording when the event happen along with relevant supportive information...its the similar example of Factless fact tables for Events. It means my data warhouse may be defined with Factless fact tables for Events with dimension but those dimensions will be snowflake. Each event is dimension table with second level dimensions table.
Please suggest how can I proceed with this problem…..You suggestion greatly helps me to deadlock my problem.
your blog is helpful, it shares detailed information with good example.
ReplyDeleteIn your example, Fact_leave and Fact_Promo, I didn't see following being true:
ReplyDeleteA factless fact table captures the many-to-many relationships between dimensions
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download Now
ReplyDelete>>>>> Download Full
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download LINK
>>>>> Download Now
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download Full
>>>>> Download LINK
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download Now
ReplyDelete>>>>> Download Full
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download LINK
>>>>> Download Now
What Is A Factless Fact Table?Where We Use Factless Fact >>>>> Download Full
>>>>> Download LINK f1