The Story of the Second Punic War

I love history, but I find it difficult to remember all the details. That’s mostly the way it’s taught and presented. Lectures, lots of reading, lists of names and dates, facts and figures.
But all of that can be told visually as a data story! And that makes it easier to remember and understand.

So, here’s my contribution for Storytelling month at Tableau Public. I’ve always been fascinated by how close Hannibal came to conquering the Roman Republic. I’ve thought repeatedly about crating a dashboard to explore the battle of Cannae (ever since reading Cannae by Adrian Goldsworthy.) With Tableau Stories, telling the story just got a lot easier.

By the way, did you know that Charles Minard plotted Hannibal’s invasion of Italy?

carte

Of course you did.  But I didn’t, until after I had created the dashboard.  Of course, I was inspired by his more famous visualization of Napoleon’s March.  And his representations are much more precise than mine.  I went for accuracy, but was a little loose with precision (especially latitudes and longitudes — in other words don’t use my numbers for your doctoral dissertation).

And here it is:

(I’ll come back to Tic Tac Toe, I promise!)

Creating Tic Tac Tableau Part 2: 5 Million Records – Really?

If you missed them, see the original Tic Tac Toe Dashboard and Part 1 first.

Chris Love asked if it was really necessary to have 5 million records. After all, there are only 19,683 possible variations of boards – even less when you consider what would be a valid board.

(By the way, check out Chris’ post Generating Tic Tac Toe Data the Alteryx Way which has caused me to bump “Learn Alteryx” up on my to-do list. Visually working with the data beats coding any day. That’s why I love Tableau! And I’m really hoping to see an Alteryx solution that incorporates the complexity below.)

At first, Chris’ question caused some self-doubt. Maybe I had over-complicated things and really should have had 19,683 records of data instead of 5 million. That would have derailed the posts I had planned to explain how I turned 5 million records into less than a million!

But then I remembered why I needed such a large data set. What makes the Tic Tac Toe dashboard work is not having every possible board. It’s having every possible move that leads to every possible board. You can see that here:

boards - parent, children, grand-children

You’ll notice Board 1 at the top. That’s the starting point. X can play in any empty square, which represents 9 possible children boards for Board 1 (only 3 are shown). For any one of those boards, O has 8 possible moves. Those represent the grand-children of board 1. So there are 9 children and 9 X 8 = 72 grand-children of Board 1. So, just 2 plays into the game, there are 72 possible outcomes, but 72 + 9 combinations of moves. When you work your way through to every possible outcome, there are (9 X 8 X 7 X 6 X 5 X 4 X 3 X 2 X 1) + (9 X 8 X 7 X 6 X 5 X 4 X 3 X 2) + (9 X 8 X 7 X 6 X 5 X 4 X 3) + (9 X 8 X 7 X 6 X 5 X 4) + (9 X 8 X 7 X 6 X 5) + (9 X 8 X 7 X 6) + (9 X 8 X 7) + (9 X 8 X 7) + (9 X 8) + 9 possible moves. Except that some moves result in wins before every space is used. Then, multiply the result by 9 (one record for every space) and the end result is nearly 5,000,000.

That’s the number of records required for the full data set required by the Tic Tac Toe dashboard. Additionally, to allow Tableau to understand how to play, each record must have these elements:

  • Board ID: the ID of the current board
  • Child ID: the ID of the board resulting from the move. This will allow the action filter to work
  • Parent ID: the ID of the board that preceded this one. The child ID alone would be enough except that the dashboard resulting from a click will always skip a generation. In the example above, Board 1 will be shown but no board from the second level will ever display. Upon clicking, Tableau will select a board from the next generation. The third level represents the resulting board after Tableau has taken its turn. Thus, the grand-children are the set of boards from which Tableau must pick the “best”
  • Game Status: In Progress, X Win, O Win, or Draw. Tableau should select O Win, if possible
  • Leads to Inevitable Loss: the board will result in a loss for Tableau (so, Tableau needs to not select it). This was a late addition to the data set (so it’s not in the code). I added it after the fact, once I realized it was necessary.
  • # X Wins: the number of X wins possible from this board (Tableau needs to minimize this)
  • # O Wins: the number of O wins possible from this board (Tableau needs to maximize this)

Now, out of the 5 million records that result from every combination of every possible move, I can eliminate some records and use some tricks to have Tableau supply missing data I remove from the source. I’ll show how in future posts…

In the meantime, here’s a view of the first 30 possible boards (with 9 spaces for each board)

boards - better view

Creating Tic Tac Tableau Part 1: Generating the Data

Once I had the idea of creating a dashboard to play Tic Tac Toe against Tableau, I had to come up with a way. I knew I wanted to use actions in a dashboard – and that meant I’d need dimensions to pass as filters. That meant I’d need to have data at the level of a space on the board. There are nine spaces. But I’d also need to have every possible move so that clicking on a square could send an action filter specific to a given board. So I’d need every possible board. This is more than just the possible outcomes of the game – in fact, even more important, I’d need every possible board of every possible game at every possible state: Win, Draw or In Progress.

I’ve never seen such a data set. So, I set out to create one. I wrote an application in C# to generate the data as flat files. I subsequently imported the data into SQL Server so I could manipulate it as needed. I won’t go into the code (you can download it here), but here’s an illustration of the recursive process it used to play through every possible game:

First 18 boards

Notice that every board has 9 records of data, one for each space – even empty spaces. In fact, especially empty spaces as those are the ones that will eventually trigger the action filters they must be in the data.

Starting with an empty board, the application placed an X in the first available spot (board 2), then an O in the next (board 3), then an X in the next (board 4), and so on. At every point, the application checked to see if the game was a final outcome (X Win, O Win, or Draw) and if so would stop, go back to the previous board and try the next possible play. Notice that board 8 is an X win. Board 9 then is a continuation of board 7 with the next possible X move.

When I first wrote the application, I had no idea how many iterations it would take (I’m sure I could have calculated it), how long it would run, whether it would consume all my memory and blow up, or how many records it would create. It actually worked very well, going through every possibility and writing the data out in a matter of seconds. I was excited…

…but then I opened the file and did a record count. My heart sank…

Although I only had 549,946 possible boards – each of those boards had 9 spaces or squares. That meant I had nearly 5 million records of data. That’s not too much for Tableau at all. I work with many times that amount of data every day. In fact, you can download the full data here as an extract in a packaged workbook.

But it is over the 1,000,000 record limit for Tableau Public! And I wanted to share Tic Tac Toe with the world.

So…

…I got creative. I’ll show you how in Part 2.

Tableau Tips and Tricks ● Story Telling ● Beautiful Data Visualizations