Complex Modeling Question (Hospital Management System)

[Infrecon]

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.

[v-shex-msft · Accepted solution]

Hi @Infrecon,

For the power bi data model, it should be suitable to build relationships with the star schema. (fact table link with dimension tables and use them as bridges to link other tables)

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng