To aggregate or not to aggregate. Multiple fact table or not ?? 2

[Speediro]

Sketch of the problem.

Medical background.

Payment of treatment is based on certain 'nomenclature' (don't mind the terminology) numbers.
The source system aggregates these numbers into 'Exams'.
Exams are requested. Multiple exams can be requested on one request. One patient can have multiple requests.

I have to be able to report how many nomenclatures, exams , requests and patients are performed.

How does one model this. It's definitely a grain problem.

First idea:

Nomenclature fact table that links to exam dimension and request dimension and patient dimension.

Second idea:

One source table (nomenclature) and two aggregate tables (exam and request) with overlapped and/or shrunken dimensions (e.g. patient).

Any ideas? Especially regarding query response times en integrety of the aggregations.

Thanks in advance
Speediro

 

[dwspecialist]

If I understand correctly, your entities are:
PATIENT->can have ONE OR MORE->REQUEST(S)
REQUEST->can have ONE OR MORE->EXAM(S)
PAYMENT->is based on ONE OR MORE EXAM(s) performed on the PATIENT.

When I model this scenario, I would not think about AGGREGATES first. I would first model the data at its lowest grain which would be:
DATE - Dimension
PATIENT- Dimension
EXAM-DIMENSION
REQUEST- DIMENSION
PAYMENT_FACT - FACT

This would give you answers like:
1) Give me all the requests for a particular patient
2) Payment data daily/monthly/YTD etc
3) # of particular exams conducted (if exams have codes)

When you see the data growth is enormous and you have a specific dashboard (pre-defined queries), you can think of aggregating the data on "Exam-Patient-Payment-Date" based on your requirement (Daily or monthly aggregates) and ofcourse use shrunken patient dimension.

Hope this helps,
dws.

 

[Speediro]

Thx for the reply dwspecialist,

A little update on the entity list.

You are very right in assuming that these are the valuable entities to our management.
However things are structured (nation-wide) a little differently.

Entities:

PATIENT->can have ONE OR MORE->REQUEST(S)
REQUEST->can have ONE OR MORE->EXAM(S)
EXAM -> can have ONE OR MORE -> NOMNUMBERS (defined by a higher state agency)
PAYMENT->is based on ONE OR MORE NOMNUMBERS performed on the PATIENT.

A little added complication is that even though one of multiple exams is executed or done it is possible we cannot ask payment for it since it is performed concurrently with other exams.

Simple example: examination of hand, wrist and fore-arm
This yields three exams HAND, WRIST and FORE-ARM
but, in come the nation's laws...
adjacent bodyparts cannot be billed.
Thus, wrist is excluded from the billing list
So :
number of exams Performed = 3.
number of exams Billed = 2.

Billing is done based on associated NOMNUMBER for EXAMS:
HAND
FORE-ARM

Reporting should be possible both for executed procedures and for billed procedures. The first giving an idea of the workload, the second of expected revenue.

Second consideration:
I think it would be best to model the NOMNUMBER as a slowly changing dimension since the nation tends to change the revenue associated with each nomnumber occasionally.

This might give you an idea why I struggle with the idea of one or multiple fact tables.

Any further ideas are most welcome.

thx,
Speediro

 

[johnherman]

As someone with a medical DW background, let me provide you with a little start.

Base Fact Table at granular level is Service.

Service table contains foreign keys to Patient, Provider, Procedure, Service Date, Request, Exam

Statement table contains monies billed to a Patient on a date.

ServiceStatement is a bridge table containing services which were billed on a statement.

Payment table has money received for a Patient on a Date.

ServicePayment is a bridge table containing infor regarding which service(s) were paid for with a payment.

When you have digested this, send next set of questions.

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

 

[Speediro]

Thanks johnherman,

This really clarifies stuff.

This setup indeed offers the most flexibility.
Apparently it isn't really a grain problem but more of a definition kind of problem.
This setup opens up the possibility to widen the scope of the DW effortlessly.

Thanks,
If I have other questions I'll get back to you.

 

[avjoshi]

John man, you have solved so many of our problems that if you had charged 100 bucks for every solution you would be millionaire by now. here's a purple star from me.

Anand

 

[johnherman]

Thanks for your kind words.

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