Introduction

This is test 2 in a series of examples where Tableau capabilities are compared to those of Power BI (PBI). This test extends the work presented in test 1, which was a topographic mapping exercise. If you want to read about those results or my other previous work completed with PBI, click here.

 

---

Technical Background

I could write a long treatise on the computational framework that allowed this test to be completed, but I’ll pass on that for now. I think I’ll save those details for another upcoming article I have planned, and I also promised to keep these test cases as short as possible.

However, I am compelled to show you a little bit about the science behind this study because I think it is necessary to explain how the data set was created and the assumptions used. If you want to know about my partner in this task, to examine the background of my mathematical and computational wizard of a friend named Dudley.

In writing this blog, I have intentionally avoided blending my past career of numerical simulation work with Tableau. I like to pride myself on being a “working man version of a Tableau user”, rather than a person that is trying to push Tableau into new frontiers for the sake of showing it can be done. I know I could write a wiz-bang blog filled with stuff that nobody would ever use, but I decided against that a long time ago because I want this blog to be useful and to help people learn how to solve business and science problems.

I have encroached upon my past career a time or two or three in previous articles, but in most cases of my work, I simply depend upon the brilliance of Tableau to showcase the data. I believe that it is not necessary for me to invent new ways of displaying data in Tableau when it is already one of the best platforms in the world for doing what it does so effectively. Quite simply, I really enjoy using Tableau in the ways it was designed to be used.

---

Basic Science

All the knowledge you need to understand this article can be summarized in one three-word sentence: Water flows downhill.

Since I don’t have unlimited time to do every experiment I want to do, I sometimes have to combine projects. By building upon the work I did in part 1 for the Path.org malaria reduction/elimination project, I was able to make progress on that project and in the process, I created myself another Tableau/Power BI test case.

When I began work on that project, I was asked to think of a way to determine where water ponding might occur in the regions of the Livingstone district where the malaria project is being conducted.

When the question was first posed to me, I thought of a simple way to visualize where water stagnation might be a problem. The reason we want to find these locations is that areas of stagnant and/or ponded water can be mosquito habitat.

Upon doing some research, I couldn’t find any software tools (i.e., numerical models) to do what I wanted to do. This meant that I had to find a way to fulfill my vision.

---

Determining Potential Mosquito Habitat

Whenever this occurs, I call my buddy Dudley. Dudley had previously built a tool we called PTRAX (a groundwater particle tracking code) that we used for many years. I asked him if it would be possible to use PTRAX to perform particle tracking of rainfall that falls on the ground and flows over the land.

Specifically I asked him if it would be possible to transport rain particles along the ground surface to see where they go over time. Since we know that rain flows downhill, I thought this would be a great use of PTRAX because it could use the numerically optimized topographic surface he created in Part 1 of this series. Dudley told me that he thought it could be done.

Some time later, after a series of emails to collaborate and with Dudley taking a vacation “to the sheep dog trials in the back hills of Idaho”, I received an email with a working PTRAX code. Once I got it, I did a little experiment to visualize the flow of water along the ground surface. As is usual with Dudley, the code worked perfectly and I got some awesome results which were exactly what I was hoping for.

The following paragraph is a description of the experiment.

---

The Flowing Water Experiment

Imagine that you are in Livingstone and it begins to rain. Once the ground becomes saturated and the rainfall rate exceeds the capacity of the soils to absorb any more water, the surface water flow will begin. The water will move downhill via the path of least resistance. Eventually the water will flow into streams and rivers, which is what we expect.

On planet earth, however, there are topographically low regions (i.e., natural depressions) where temporary ponds can form. In addition to identifying wet-weather streams, I wanted to determine any ponding areas in Livingstone because these are potential mosquito habitat areas.

---

Using Particle Tracking To Calculate Water Flow Paths

I was given a study area as shown in Figure 1. We “rained” on the area by randomly placing 1000 particles over the study area, as shown in the lower left corner of Figure 1. Each particle simply represents a raindrop and is used to as a way for us to visualize the flow of water over the land surface.

In this example, no complexities have been added into the problem domain. In future work, building outlines will be introduced in the populated areas as flow obstructions to see if other ponded areas can develop.

---

Once the model boundary was defined, PTRAX was run with the 1000 randomly placed particles as shown in Figure 1. The optimized topographic surface shown in Figure 5 of the Part 1 article was used to calculate the topographic gradients that were needed for all 390,000+ topographic triangles that formed the study area.

Once the model was set-up, the simulation of overland flow was completed in less than 1 minute as shown in Video 1.

---

The output of PTRAX was a 210 megabtye particle track file that contained particle positions over time. For each particle simulated, the lat/long coordinates were stored along the flowpaths. Since this level of detail was more than I needed to visualize the stream network and animate the results, I wrote an Alteryx workflow to extract every Nth particle position along each flow path. I experimented and found that every 50th position worked good to visualize the water flow. This reduced the file from 3.85M records to 76,527 records.

I should point out that this post processing to reduce the size of the data wasn’t needed for Tableau, but I suspected it might be needed for Power BI. As it will be shown below, this suspicion is likely to be true for PBI.

---

Using Alteryx to Process The Water Flow Paths

Figure 2 shows the Alteryx workflow that collapsed the 210 Mb file down to 4 Mb for rendering in PBI and Tableau.

---

---

Using Tableau to Visualize Water Flow

The video shown below animates these particles over time to simulate the flow of water. By watching the video, you can see the development of ponded water zones, which are potential mosquito breeding grounds.

---

---

Using Power BI to Visualize Water Flow

The same 4Mb input file used to create the Tableau animation shown in the previous section was sent to PBI as shown in Figure 3.

 

---

 

Since I am a PBI novice, I did some research to determine how to animate the particle positions. The only reference I could find regarding animations in PBI specified that I should use an add-in package called SandDance. This add-in can be downloaded using this link and then the file is imported into your PBI workbook.

Those steps only took me about 5 minutes to complete, including the research, which I thought was very good. So far, so good.

Even the initial picture wasn’t too bad as show in Figure 4, although there is no automatic geospatial recognition with the usage of latitude and longitude, like you get with Tableau. The rendering of this image almost seems 3D.

---

 

Once that was complete, I had to determine how to animate the particle positions.  Upon reading the article referenced above, this is what I saw:

The focus of SandDance is on tasks where data is not pre-aggregated (that is, many individual records), and can be rearranged in groups on the screen to create meaning.

Since my data fit this description (76,527 records), my natural inclination was to group the particles by Record ID. This is the technique I used to animate the particle movement in Tableau.

For each Record ID, the particle groupings should be able to be turned on in sequence to animate the positions (i.e., 50 = initial positions, 100 = 1st positions, 150 = 2nd positions… end). This is how all animations programs I have ever used work, and this is exactly how the Tableau page shelf worked to create the animation shown above. By marching through the record ID’s (which represent new positions over time), the animation gives the illusion of moving water particles.

Well, this is where the problems began for me. Even after spending about 1 hour to examine the controls in PBI for creating animations in SandDance, I was not able to determine how to reproduce the animation I created in Tableau. Certainly, my inexperience in PBI may be a part of the problem.

I welcome additional instruction from someone familiar with SandDance to help me configure this workbook to produce an animation like I showed above. However, since I have over 30 years of experience in animating data, I think I should have been able to make some progress in 1 hour. However, that was not the case. What I was able to accomplish is shown in the video below, along with my commentary as I tried to learn what was happening.

If someone does help me with this work, I promise to update the animation shown below.

---

 

---

Final Thoughts

PBI is a bit mysterious to me with some of the things it does. For example, I do not understand how moving the particles across the stationary lat/long axes represents anything useful. Also, the title indicated 30,000 data points, although there were over 76,500 data points in the data set. I don’t know if this is another type of PBI data set size limitation in SandDance or not. The documentation doesn’t indicate if this is the case.

According to the article:

SandDance is the Microsoft Research data visualization project, spearheaded by Steven M. Drucker and Roland Fernandez, that experiments with new genres of visualization and represents an innovation in user interactions. With SandDance, every data element is always represented on the screen, and animated transitions are used between views to help people explore, understand, and communicate insights in their data.

At this time, I do not know how to make the appropriate transitions between views. For the animation type I am trying to create,  I do not want all the data elements on the screen at the same time, which might just be the problem. I want to animate through the data and with this being the case, I cannot determine if this can be currently completed in PBI. It is also possible that another type of add-in package could be used to do this work, but I have not been able to identify one.

Animated GIFs of Livingstone Surface Water Hydrology

---