1

I'm taking a course on Business Intelligence and I have to deal with a Star Schema for the first time in about 3 years so I wanted your feedback on what I've done if possible and see if there is anything I can do to improve my current design.

So the example says that we want to track performance of some clinics to understand their operations with some metrics like patient waiting time, cost, revenues, etc. The company has a well defined relational database that goes like this: Database Description

My start schema is as follows:

Star Schema

nvogel
  • 24,981
  • 1
  • 44
  • 82
Javier Ruiz Velez
  • 35
  • 1
  • 1
  • 5
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--you may find mistakes that make your question unnecessary & we don't know exactly what algorithm you are following & we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Sep 20 '19 at 03:33
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Sep 20 '19 at 03:33
  • Thanks for the question, I don't see anything wrong with the way this is asked other than an image for the tables. But in this case, it seems clear to me. What I cant see is how you can answer questions about waiting time with this fact table. You only have a single event start and end, how are you planning on recording the multiple dates from your visits table? – TomC Sep 20 '19 at 07:23
  • @TomC Hey Tom would you say its appropriate to scratch the time dimension table since Im going to have to capture the data in the fact table regardless? The assignment says we want to evaluate patient waiting time so it makes no sense I would think to have a time dimension only to capture that data in other attributes in the fact table. – Javier Ruiz Velez Sep 20 '19 at 16:55
  • You have to think in terms of the queries you are going to run. For example your date dimension correctly has information on holidays, workdays, etc. Is it important to do the same with time (workhours/afterhours/day/night etc)? I cant answer that. But also remember that if you want everything to be summable across your dimensions, then you might want to actually record the wait time in the fact table, so you can simply add it up without having to include logic or date-calcs in every query. – TomC Sep 22 '19 at 23:43
  • design should be based on access pattern. However, you can include another dimension of VISITS if you didn't exclude that purposefully. – Channa Jun 11 '20 at 16:36

0 Answers0