1 Reply Latest reply on Apr 28, 2018 8:55 AM by Okechukwu Ossai

    KPI Regional Aggregation

    Manuel Munoz Jr

      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!

        • 1. Re: KPI Regional Aggregation
          Okechukwu Ossai

          The first thing is to get your data join correct. Is your data join in Tableau or did you create a database view? Whichever way, you need to ensure the join correctly reflects the relationship between the tables. I can't comment on that since you did not specify what the primary and foreign keys are for each table and how they have been joined.

           

          Assuming the tables are joined correctly, you can use simple table calculations or LODs depending on how your viz is set up.

           

          If you are using LODs, calculations can be performed at any level of detail you wish.

           

          For example you can count servers at;

          Database (Overall Total Count) level using;

          {FIXED: COUNTD([Server Name])}

          This calculation cannot be filtered by Country, Region or State except the filters are added to context.

           

          Country level using;

          {FIXED [Country]: COUNTD([Server Name])}

          This calculation cannot be filtered by Region or State except the filters are added to context.

           

          Country/Region level

          {FIXED [Country], [Region]: COUNTD([Server Name])}

          This calculation cannot be filtered by State except the filter is added to context.

           

          Country/Region/State level

          {FIXED [Country], [Region], [State]: COUNTD([Server Name])}

           

          Finest level of detail

          {FIXED [Country], [Region], [State], [Server Name]: COUNTD([Server Name])}

          This will probably be the finest level of detail depending on your data structure. If yes, then you can roll this up to any level of detail you wish.

           

          The result will be correct if use the right combination of the above calculations to compute your percentage. Simple table calcs will just be fine also. The major determinant is how your data is structured/joined.

           

          Hope this helps. Try attaching a dummy workbook, if you need further help.

           

          Ossai