I have the following database structure:
Servers - A table with a list of Server Names
Server / State - A Join table resolving a many to many relationship among servers and states. One server could be linked to one or many States.
State - A table containing a list of States
Region- A table containing a list of Regions in the country, e.g. North, South, East, and West.
Country - A table containing a list of countries.
My Goal
I am trying to measure and report on server up time and view their metrics by State, Region, and Country using filters to display the desired dimension.
The Challenge
The problem that I am having is that since an individual servers could be linked or support many States within a Region or Regions, when looking at their performance metrics from a Region perspective, I get the aggregate value of that one server times the number of States. For example, if that server was up only 10% of the time and it supported 5 States within a Region, my dashboard shows the up time by that server for that region as 50% when in reality it should only show 10%.
The Solution
I am not sure if this could be solved in Tableau or this is an issue with the way I have my relationships setup in my database. Could you please help!
Thanks!
