Results 1 to 2 of 2

Thread: Dimensions vs. Factless Fact Tables

  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Question Dimensions vs. Factless Fact Tables

    I am building a health care application that marries transaction-level data (health care services provided) with person-level characteristics that have a time-dimension. The person-level characteristics are diseases that the person has (these disease all have a start and some have an end date). The diseases are stored in a table in which the foreign keys are a person-identifier, a time identifier (month/year) and a surrogate for the disease. Persons can have more than one disease at a time (the diseases are NOT mutually-exclusive). There are no measures in this table. The transaction table has a foreign key for person and time (month/day/year), a procedure code (the type of service rendered) and money (the cost of the services).

    How do I answer the following questions:

    What is the total cost of care (the sum of all service costs) last year for persons with "disease A"?

    What is the total cost of care last year for persons with "disease A" AND "disease B"?

    What is the total cost of care last year for persons with "disease A" OR "disease B"?

    I've tried a factless fact table but can't get it to work. If anyone has the right solution and can communicate to me before I slit my wrists, I would be greatly appreciative!!!

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    While unrelated to your industry, here is an article that deals with an example of a "factless" fact table:

    Pt 1:

    http://www.databasejournal.com/featu...le.php/2237551

    Pt. 2:

    http://www.databasejournal.com/featu...le.php/3067851

    Most of the "facts" in cases like this are derivative: e.g., counts, as the example in the articles will show.

    In your case, I'm not at all sure that you need a "factless" table. Cost is, itelf, a fact. The disease accumulation / logic surrounding aggregation of same seems to me pretty standard dimensional stuff. Am I missing something?

    Hope this helps. Sounds like a schema discussion might be useful ...

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •