Hi, I'm after some advice!
I am wanting to create a world map where each country in the world displays a value between 0-100. Then users can click on a country and be able to drill down into the underlying data that determines that value (perhaps as a separate table in a dashboard). The country's value is calculated from values (0-100) assigned at the very end of a tree structure and then averaging those values up a series of hierarchies to get to a final value.
For example, each Country has a Category A with three branches (Weather, Outdoors, Food). Each Category A branch has its own Category B branches, etc... Values are only ever assigned at the very end to B.
But then B values are weighted and averaged to arrive at an A branch value. Then A branch values are weighted and averaged to arrive at a Country value. So the U.S. would get a value of 45.4.
Users would see a map with the U.S. showing 45.4 and be able click on it to see the A branch values and B branch values in a table or chart if they wish - with some sort of filtering options available.
So! I have questions about the feasibility of: this way of approaching structuring the data, visualizing this in Tableau, and whether a spreadsheet or database would be better to house the data. Once built, we'll want to grant access to individuals, as well as be able to embed/display it on outside websites as well. The current thinking is that we'll purchase Tableau Online.
In the example above there are 3 countries with 7 different values each. Ultimately, the entire data set will be for about 200 countries with about 50 values each, and multiple interim branches - perhaps out to F. So, connection latency as a potential issue is coming to mind.
The idea of all of this in a single page Excel file seems overwhelming. Especially as the structure could ultimately add or subtract some branches, categories, values, or countries. Some thoughts I'm having:
- Should the data be broken up across multiple Excel sheets and/or workbooks somehow?
- Would the calculations in the orange area be better handled by Tableau than Excel?
- Should the data be organized into a database like a cloud-based service instead of Excel?
- If using a database, would the calculations in the orange area be better handled by SQL?
I'm fairly new to Tableau and databases, so have lots of different ideas floating around in my head. I'm reading about hierarchies and calculated fields and data extracts in Tableau. I'm reading about MySQL and considering learning how to do some querying/calcs. I'm reading that in Tableau I could join multiple Excel spreadsheets/pages. I also read the multidimensional hierarchies only work in Windows (http://onlinehelp.tableau.com/current/pro/desktop/en-us/buildmanual_multidimensional.html)? All this leaves me a bit overwhelmed.
At the very least, I'd love any opinions that could help me eliminate which routes are obviously bad ideas - because I don't know!
Thanks for entertaining such a nebulous request. I'd be very grateful for any pointers in the right direction.