Weather Dashboard in Selected Cities Part 2 - Tableau Dashboard

In the previous part, I’ve created a source data table on Google BigQuery cloud. It’s time to build a dashboard with Tableau based on the data table. My source data on Google BigQuery looks like this:

No matter how often you execute our ETL R code in Part 1, the total number of rows will always be 2688. This is because our set of code pulls 24 hours of weather data for the consecutive 7 days for 16 cities (24*7*16 = 2688).

The goal of the dashboard is to provide my wife with three pieces of weather information: 

• the current weather of all cities (temperature, precipitation), 

• the weather for the next 24 hours, 

• and the weather for 7 days (starting with the day of data extraction)

A map visualization would be a perfect method for displaying the current weather information for all cities. My wife can check the current weather in the city that interests her.

For the other two, I want to display each of them on a table visualization. Also, I want them on the same dashboard so you can filter for one city and check the weather of the city for the next 24 hours and 7 days at the same time.

So, there will be two dashboards:

• Dashboard 1: Current weather in all cities

• Dashboard 2: Weather for the next 24 hours and the 7 days for a selected city

I won’t go into too much detail because you’ll understand how I built it when you see the positions of dimensions and measure pills.

The only thing I want to show you is how I filtered by the current time for each city. It is simple because by default, the Open-Meteo API provides weather information based on UTC for a certain time range. So you need to know what time it is now based on UTC and apply a filter for that “now” time.

However, you can’t show the UTC time alongside each city’s name. You need local time, which I created with another calculated field, “Local_Time.” 

I had made the “Dst Offset” measure in the data source table in the previous part. We can create a local time for each region by applying it to a uniform UTC timestamp. 

We’re moving on to the second dashboard. Remember, we’ll need two separate sheets because we’ll use different approaches to create weather for 24 hours and 7 days, respectively.

Let's first take care of the weather for the next 24 hours of a selected city.

The first column is temperature, and the second is precipitation, which is all the weather information I extracted from the Open-Meteo API. I color-coded rows with precipitation above 0 in blue with the "Rain_Indicator" calculated field to inform my wife about possible rain or snow in a given city.

Also, I created another calculated field called “Is_Next_24_Hours” to retrieve every hour from now, but only for the next 24 hours. This will allow my wife to anticipate the weather at specific times of the day and possibly the next day.

Next, the selected city's weather for 7 days. This would help my wife decide which clothes to pack, especially if she needs to move to another town in a few days.

I’ve included weather indicator icons on the second column to make it more intuitive. In fact, I used the same "Rain_Indicator" dimension to color-code the numbers in the first visualization. This time, I dragged the dimension to the “Shape” marks card.

If you look at the Columns shelf, you’ll see two measure pills instead of a single "Measure Names" field that you saw in the previous visualization. They’re both dummy measures so I can include two different types of marks (Text and Shape).

One more thing: I didn’t need anything on the "Filters" shelf for 7 days because our data source's default range is 7 days. All I need to do is refresh my data source every day so that my wife can see today’s weather in the top row and the following six days.

So, if I bring two visualizations into a single dashboard, it looks like this: 

Now, I can present my wife with a weather dashboard for 16 cities, which she'll want to check for the weather throughout her tour. I've embedded the dashboard below so you can play with it yourself. You can also click here to see it on Tableau Public. 

There's one last thing I'd like to mention about this self-project: the level of automation.

Unfortunately, I later found that I needed to refresh Google Sheets manually to retrieve up-to-date weather data from my Google BigQuery table. Moreover, even if I found a way to avoid manual refreshing in Google Sheets, I still need to refresh my source data manually in Tableau because I'm using Tableau Public.

To cut to the chase, in such a scenario, I didn't need to load to Google BigQuery; I could load to an Excel or CSV file (which I'm currently utilizing) after the transformation because my Google Sheets aren't automatically updated after loading to BigQuery. 

So, I'm currently using a CSV file as my source data because, again, there's no point of using Google Sheets at this point. Then, I manually connect the CSV file to Tableau Public every day to give my wife the up-to-date weather data refreshed daily. Maybe if I were using a higher version of Tableau, I wouldn't have to connect to the source data manually.