Star schema...suggestion?

[alexandernatale]

Hi everyone,

I work in a large healthcare company (hospitals) and I need to create a single app on QS in order to be in control of some aspects of the production activity.

My idea is to create a star schema as shown in the figure.

The fact table (star center) would be transformed into the list of departments, while the dimensions tables would be the various "types of production" that take place within the company (for example: hospitalizations, outpatient activities, etc.). I'd like to tie it all up with a master calendar, so the pattern I'm thinking about would become the following.

What do you think about it? Could I have some data model issues? Alternative suggestions?

[danielrozental]

That won't work as it will create a circula reference.

You should look into concatenating the fact tables or using a link table.

You can find a very good articule about it by Ometis here.

[alexandernatale]

thanks @danielrozental for the reply!

I tried to create a simply model on QS.
This is the result:

the cdc table is the department.
QS created 1 syntetic key...I think that is not a bad idea...

[danielrozental]

That will probably work but ambulatori and ricoveri should be concatenated into a single table to remove the synthetic key.

[alexandernatale]

Thanks @danielrozental.

Searching in the forum I found this very interesting discussion which shows the following diagram...

i think that is a good solution for more fact tables...

[danielrozental]

Hi Alexander, you're welcome to try anything you'd like.

My recommendation still stands, you should concatenate both fact tables in order to avoid a circular reference.