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
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