Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


dimensional model for a factless fact

dimensional model for a factless fact

dimensional model for a factless fact

I am exploring the design options for the dimensional model of Health Care System. In this design we don't have any financial data or any sort of concrete measurements. The business process works as follows:

The patient enters into the Hospital or Health Services Center and answers some questions on an assessment form. And by the answered questions, it is determined what kind of services are needed by the patient. This entire process is termed as "in-take assessment" and focuses solely on assessment.

Mostly the reports will be measuring the count of assessments based on various dimensions. In this model we have Patient, Assessing Organization, Diagnosis (multivalued), Time, User (one who assesses), Assessment Type, Care Product, etc. as Dimensions and the Actual Assessments as the Factless Fact. The assessment process form consists of objective questions(defined values/radio button choices - mostly int or string) and open-ended questions (free text). There are approximately 100 questions in the form. The reporting criteria(where clause) can be one of the above mentioned dimensions or may be one of the answers given by the patient in the form. If I create one dimension per Q/A it will be a centipede design with too many dimensions.

Now my question is

*whether the assessment Fact should contain all answers in one row in different question columns (in which case, it will be a very big table - approximately 120 columns in one row with 30,000 Assessments each month) or

*should the questions be in a dimension, in which case the dimension will be as large as the fact, in terms of rows. It will act more like a fact table. It is like having a Primary Key in the Fact table Assessment and Foreign Key in the Assessment Q/A table or

*should I try to come up with various combinations of possible answers for these questions and then put them in a dimension, in which case the dimension again will be very large or

*should I distribute the questions in combinations of 20 each and then apply possible answer combinations in 5 different dimensions or

*should I create 1 dimension per questions/answer (centipede design)?

Any thoughts/suggestions/guidance will be really appreciated.

RE: dimensional model for a factless fact

I would start off with something like this initially:

Assessment dimension:  Assessment ID, Time, User, etc.

Question dimension:  Question ID

Diagnosis dimension:  Diagnosis ID

Answer dimension:  Answer ID

Question fact: Assessment ID, Question ID, Answer ID, Answer Text (using a 0 Answer ID for free text)

Diagnosis fact: Assessment ID, Diagnosis ID

RE: dimensional model for a factless fact

The assessment looks like a survey. Typical survey type data structures should suffice. Examples of tables in survey data structures.


The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: dimensional model for a factless fact

Thanks for answering my question. So are u both suggesting to go with 1 row per assement question. In this case the fact will end up with 100 rows per assessment. And each patient is assessed multiple times, lets assume avg 5. So I will end up with 500 rows per patient. And they normally assess 30 thousand people per month. So this means 500*30,000 = 15000,000 rows per month and 15000,000 * 12 = 180000000 per year. This design is good in terms of only two joins. But in terms of no. of rows, performance might still be a problem. Any thoughts?

RE: dimensional model for a factless fact

You could consider keeping the most recent assessment(s) in one table, and the earlier ones in another; available "joined" via a UNION view. Alot depends on what kinds of queries you are expecting.

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: dimensional model for a factless fact

If you're worried about the performance, a fact table is a good candidate to randomly populate 180,000,000 rows - since you're mostly concerned about a few numerical values as opposed to string data.   Populate a test table, and try out the performance.  Depending on your DBMS, you may also have other alternatives such as partioned tables.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close