ER diagram to Star model

[dinek]

Hello everyone!

I want to make datawarehouse for F1 races. Main idea is to trace all results from races for all drivers and constructors. On below picture is Entity Relationship Diagram for database from which I took data.

My idea:

fact table - results

dimensions - driver, constructor, race (with cirucit) and status

Is it smart to hold date and year of race in dimensional table race or extract date and year in individual dimensional table?

(I tried to make model with dimensional date table but then I can't join dimDate with factResults because there are duplicates - except I use one middle table to join those two.)

Also, what bothers me, is it good to hold circuitId in race dimension?

[shebr]

Hi @dinek

1. Firstly regarding the date, you should have a date dimension yes, use this video to create a date dimension in seconds! Set the parameters to the earliest date and set the end date to a year in the future (you can amend this later also). You can then link your race date from your fact to the date dimension.

https://youtu.be/vJ7xNUK8sQE

2. Regarding the circuit, surely a race has a single circuit only? There should be a circuit ID in the fact table and you should connect the circuit dimension to the fact table also. This will make your model truly star schemed.

Hope that helps, let me know how you get on.

Thanks

shebr

[vanessafvg · Accepted solution]

@dinek what was the problem with a date dimension, as long as your date dimension has unique values its fine, i would put the race date in the results table not the race dimension, and then link that race date to a separate date dimension that is a date table.  Does that make sense.  

what is the relevance of the circuitid?  is it an attribue of a race?  if so it should be fine, if it needs its own dimension it should only be in one dimension. and the fact table, not 2 dimensions, everything links via the fact, essentially the easiest way to do all this in power bi could be just to denormalise it all into one table, depending on how many rows you have, except for the date table which is required for continiious date functions- but thats just me, sometimes all the joining creating more complexities and performance issues.  Power bi doesn't require you use a star schema, 3nf is fine