DSXchange

Where would you use a factless fact table

Factless fact tables come in two (2) varieties. -

• Record/track an event
  or
  Coverage tables

Most of the time a factless fact table does not contain any data other than key fields. No numeric additive data. One variety of a factless fact might record or track an event. Another factless fact table is the coverage fact table and you would use this when the main fact table in the middle of your dimensional model contains sparse or non existant data.

This is pretty much right out of many of Kimball's books and website. You can do more research by either surfing the web or reading the books.

Regards,

I have seen factless facts used mostly to report on all salesmen regardless if they have sales or some simliar concept. This way you can see the salesman has no sales for a given time period and the reports will reflect that. Otherwise the salesman does not show up at all on the report.

Kim gives a great example and one that certainly typifies what a factless fact table is used for. It really is nothing more than the counting of an event.

pump307,
What exactly did/do you mean?

pump307 wrote:OMG u'r such a n00b. go sit in the corner and be ashamed of yourself.

pump307 appears to be a robot spamming content on random postings. Better boot that user off.

kduke wrote:I have seen factless facts used mostly to report on all salesmen regardless if they have sales or some simliar concept. This way you can see the salesman has no sales for a given time period and the reports will reflect that. Otherwise the salesman does not show up at all on the report.

Kim or Mike (or someone else),

could you give some more detail or more examples. I think I understand factless facts, but with the example of sales I'm envisioning a fact with a numeric value of 0, which should show up with a good reporting tool and I don't this think qualifies as a factless fact, so perhaps another example would provide more clarity.

That is correct. All measures are 0. You can do the same with an outer join to the salesman dimension but sometimes this messes up OLAP tools. The purpose is to show all salesmen even if they had no sales. No sales maybe more important than who sold something. You may want to eliminate poor performers.

From The Data Warehouse Lifecycle Toolkit by Kimball et al (pages 212-216 in the 1998 edition):

Factless fact tables are the preferred method for recording events in a data warehouse where there is no natural numeric measurement associated with an event. Factless fact tables also are used to guarantee coverage.
They give examples of attendance by students in classes (an event-tracking fact table), in which students simply do or don't attend a particular class run by a particular teacher in a particular facility at a particular time, and coverage of products by sales promotions (a coverage fact table), in which a product is or is not covered by a particular sales promotion at a particular time in a particular store.

If a fact record has a 0 value it doesn't mean that this is a factless fact - it's possible that in this particular case f.e. the daily sales was 0 but normally you're able to measure the fact (and that's why this isn't a factless fact).
The best definition is the one in Ray's post and as far as I remember there's some more on that topic in Kimball's books and on his website.