I am doing a small exercise, I need to create a small dimensional design that deals with the tsunamis that have occurred in different countries over the years. I have created a "Country" dimension and a "Location" dimension. In each record of the provided table comes (or may not come) the longitude and latitude in which the place is located. My question is where should I put such attributes, whether in the fact table or in the location dimension. My understanding is that in the fact table it should only contain metrics and the foreign keys of the dimensions. However, I don't know how correct it can be to add the longitude and latitude to the location dimension, since by having the values a very wide range, many records are being created in the "Location" dimensional table. Would it be more appropriate to put those attributes in the fact table?

Thanks.

CodePudding user response：

On the fact table. This is exactly like a fact table that has a time attribute, with sub-second resolution. It's not necessary to create a dimension table containing every possible point in time, or every possible lat/long.

CodePudding user response：

You should merge Location and Country into a single Location dimension (country is an attribute of location) and hold lat and long in the dimension