Integrate Tableau and SSRS – A complete tutorial

One of the most difficult part of Business Intelligence is finding the right tool for the right situation. Too often folks will discover Tableau and use it for every single bit of reporting they need, including data that used to be in Excel. The problem is, they will utilize Tableau as if it was a glorified Excel replacement and try to mimic Excels features. Unfortunately if there is one thing Tableau does horrible, it is display large amounts of raw data. Tableau is great at visualizations, which is the wheelhouse it should stay in. However - there is often a legitimate need to not only get at the raw data, but display it in a "prettier" format. Viewing data within Tableau isn't a great experience. Often it's littered with calculations you don't want to see, and users want you to organize the raw data (maybe add some nice headers). None of this is really possible or practical within Tableau. But -...
Read More

Dashboard Use-Case: Mapping NFL Plays

As part of a paper I wrote, I utilized Tableau to generate a visual representation of plays ran by various NFL players. It turns out to be quite a simple thing to do, and appears more complex than it is. The dataset comes from a Kaggle challenge located here, which provides data for 2 years of NFL seasons and provides detailed X,Y coordinates for each of the plays and players. With this information we can map the points on a standard scatter plot over time, and play connect the dots. In an effort to make things quicker, I parsed down the dataset to only include 2 players and the top 100 plays of each. The original dataset is approximately 76 million records (down to ~50k). With a minimum of X,Y and Time, we plot the Sum(X) and Sum(Y) on Cols/Rows (making sure the X is on the columns). At this point you could add time to detail and you would see each...
Read More

Temp Tables within Tableau (Initial SQL)

Did you know you can create/use temp tables within Tableau? It's one of those features that hasn't always existed, is very useful, and most people do not realize exists. Utilizing SQL Server as an example - you can make use of Initial SQL. From here you can type out your SQL to generate temp tables. Then, in your custom SQL you can reference that temp table. (I realize This can then be published on the Tableau server and works as normal. The temp table will drop when the user session ends. This is very beneficial for smaller datasets that are performant in which you don't want to go through the pain of creating a stored procedure. However - the downside is that the Initial SQL code can be hidden from anyone else trying to develop on the dashboard. Any yes - you can in fact utilize variables as you would in stored procedures within Initial SQL, good article here on how...
Read More

Tableau Stacked Bar Chart to 100% (progress bars)

This is one of those "simple" things that is a bit confusing at first. In general I complain when people use stacked bars, because I see them as horizonal/vertical pie charts. And the pie chart is that universal point of contention between data analysts. Some people claim it's the best visual tool for easy comparison, and other people claim it's one of the worst tools. You can count me in the "never use it" camp. I won't attempt to go into why on my soap box, but my general advice is that if you have to use it then never use it with something requiring more than two "slices". The same applies for stacked bars. However - a great use for stacked bars is when you display them as you would a "progress bar". I.E. each bar fills up complete space and is out of 100% (a bit different than a bullet chart if you are familiar). There are two ways...
Read More

Dashboard Use-Case: Correlation Matrix

An interesting way to show correlations on pairs of attributes is a correlation matrix. It's a very simple thing to setup and incredibly easy to understand for the user Full dashboard on Tableau Public (downloadable):https://public.tableau.com/profile/kaledev#!/vizhome/CorrelationMatrixofFREDData/Correlation The dashboard above works as follows. There are a total of 6 attributes. In this case we're viewing various indicators of Federal Reserve Economic Data (I.E. imports/exports). It's not really necessary to understand the data itself. Each square displays a number that indicates how well the data correlates between any two pairs of indicators. The closer the data is to 1.00, the more similar the pairs are. In addition, hovering over each pair in the matrix will display the corresponding scatterplot to the right for a more detailed analysis. The first step is to properly format your data. If I attempt to setup a matrix by simply using my main data (shown below), you'll notice that I'll only get the option to add values down the...
Read More

Dashboard Use-Case: Small Multiples and Hex Maps

This dashboard utilizes fake data, and was simply an exercise in multiple techniques I wanted to try. It gives an overview of Tick Bite Occurrences across the US from 2002 to 2011 per 100k Persons. It's meant to highlight the Periodicity of the bites rather than the total number of bites by year. Periodicity is defined. Full dashboard on Tableau Public (downloadable):https://public.tableau.com/profile/kaledev#!/vizhome/TickBiteIncidentsintheUS/TickBiteMultiples Periodicity is defined as a pattern of Rises and Falls across multiple years. This can be quite subjective depending on the person viewing. Therefore, I focused on the first pattern I came across and defined those years as Heavy, Medium, Slight, or None. If two patterns occur in one state, I took the most likely and threw the other out. Generally you wouldn't want to determine periodicity with the actual metric value, as extremes in values make it difficult to determine a pattern. Since we only really care if something has gone up or down (and not by how much), we...
Read More

When sorting goes wrong in Tableau

One problem I've run into several times has to do with how Tableau sorts data. When you use the built in sorting buttons on a visualizations, it doesn't always sort in the way you may hope or expect. Generally the "problem" comes in when you have multiple dimensions. For example - when you have a simple Sales/Profit by State - clicking the sorting button on Sales seems to do what you would expect (In other words, sorting everything asc or desc by Sales) Now what happens when we add City and try again? Well - it turns out Tableau now uses "Nested Sorting". In reality - it's correct, but what happens if I want to sort everything by Sales? There are a few ways to accomplish this but they each have downsides. What to use sort of depends on how much data you have and desired usability. Commonly I choose to use parameter based sorting. The big downside of this is that you...
Read More

Shape Alignment

Within Tableau - very often we use shapes to represent dimensions to allow for quick understanding, and tack on a metric for clarify. A good example may be a person to represent number of employees, with the number below. The problem I have with this is the alignment. Technically the shape is in the center - but I would prefer the shape and text be vertically aligned as a group. One way to fix this is to generate an axis that can allow you to manually move the objects up and down (or left and right). Create and add to the rows, and then edit the Axis. Now we can fiddle with the start/end to offset Lastly, make sure to hide the zero lines in the formatting menu for the sheet, and hide the axis for the sheet. For the Tableau workbook used you can download here. ...
Read More

Hide ‘View Data’ within Tableau

Here's something you can do as a developer to help "hide" the view data button when clicking and hovering over a viz. Granted - this is a bit superficial as it doesn't disable the Download > Data option within Tableau Server. BUT - it deters, and as a by-product it also hides the options for 'Keep Only', and 'Exclude' which very often can cause confusion for curated dashboards in which we don't want users to do much of their own editing. These are called "command buttons" and Tableau hides the option to turn this off very well. Tableau thought the best place to turn this off would be... the tooltip marks card... Uncheck "Include command buttons". If you do want to disable 'View Data' at the server level... take a look here: https://community.tableau.com/thread/235216 ...
Read More

Advanced Row-Level Filtering in Tableau

One of the biggest problems you’re going to run into while publishing to Tableau Server is how to deal with data security. Implementing Row-level security isn’t quite the most straight-forward thing to do within Tableau, but it is possible. In fact, Tableau has an option called “Create User Filter” to try to make this easier. However – that built in feature is very manual and only works for a small group of people or very specific circumstances. Chances are you are going to need something a bit more advanced. There are several different types of row-level security that you may want based upon the business need. See below for our agenda today. “I want a dashboard that filters for data related directly to the user viewing the dashboard.”“I want a dashboard that filters for data related to a particular user’s group, LOB, etc.”“I only want users within a particular AD group to see data on this dashboard.”Final thoughts: Security Check The Tableau packaged...
Read More