fact table

Moderators: chulett, rschirm

Post Reply
harithay
Participant
Posts: 106
Joined: Tue Dec 14, 2004 10:51 pm

fact table

Post by harithay »

Where would you use a factless fact table
mhester
Participant
Posts: 622
Joined: Tue Mar 04, 2003 5:26 am
Location: Phoenix, AZ
Contact:

Post by mhester »

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,
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

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.
Mamu Kim
mhester
Participant
Posts: 622
Joined: Tue Mar 04, 2003 5:26 am
Location: Phoenix, AZ
Contact:

Post by mhester »

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.
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Re: n00b

Post by roy »

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. :evil: :x
Roy R.
Time is money but when you don't have money time is all you can afford.

Search before posting:)

Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Image
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
datastage
Participant
Posts: 229
Joined: Wed Oct 23, 2002 10:10 am
Location: Omaha

Post by datastage »

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.
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

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.
Mamu Kim
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
wnogalski
Charter Member
Charter Member
Posts: 54
Joined: Thu Jan 06, 2005 10:49 am
Location: Warsaw

Post by wnogalski »

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.
Regards,
Wojciech Nogalski
Post Reply