Have a more complex modelling question which is not apparent to me yet:
Background:
Have two different data sets:
1. A general one with benchmarks from all hospitals, granularity is at case level, on cost level partly already aggregated.
2. An internal data set from one hospital, where further parameters can be extracted.
Objective:
- On the one hand, I would like to do analyses on the benchmark set, but also combine it with the other internal data if necessary.
Questions:
1. How do I build a data model for the benchmark set that is performant?
The following tables are available:
Case Table (Excerpt) incl. 2 Mio Rows (Unfortunately the patient id is not available):
| Case_ID | Case_Nr | Insurance | Admission_Date | Discharge_Date | DRG | ... |
| AAA_202213123123 | 13123123 | private | 23.01.2022 | 30.1.2022 | A45G | ... |
AAA_202218124123 | 18124123 | general | 1.2.2022 | 4.2.2022 | C65G | ... |
| BBB_202214515354 | 14515354 | general | 31.1.2022 | 4.2.2022 | I75F | ... |
| ... | ... | ... | ... | ... | ... | ... |
Diagnosis Table (Excerpt) incl. 5 Mio Rows (One line per diagnosis, with classification primary or secondary diagnosis):
| Case_ID | Diagnosis | Classification |
| AAA_202213123123 | F51.0 | Primary |
AAA_202213123123 | I71.9 | Secondary |
| AAA_202213123123 | I14.0 | Secondary |
| AAA_202218124123 | A90.8 | Primary |
| ... | ... | ... |
Procedures Table (Excerpt) incl. 8 Mio Rows (One line per procedure):
| Case_ID | Procedure | Date_Procedure | ... |
| AAA_202213123123 | Z812.00 | 24.01.2022 | ... |
AAA_202213123123 | Z814.00 | 25.01.2022 | ... |
| AAA_202213123123 | G103.00 | 28.01.2022 | ... |
| AAA_202218124123 | Z931.00 | 1.2.2022 | ... |
| ... | ... | ... | ... |
Cost Table (Excerpt) incl. 10 Mio Rows (One line per cost element):
| Case_ID | Cost Element | Value | ... |
| AAA_202213123123 | Doctors | 4124.10 | ... |
AAA_202213123123 | Nurses | 232.90 | ... |
| AAA_202213123123 | OR | 293.20 | ... |
| AAA_202218124123 | Material | 294.10 | ... |
| ... | ... | ... | ... |
In fact, I have several fact tables with different granularities. Case table at case level, the others at sublevels.
One possibility would be to join the tables, but then I would have a huge table.
