IPEDS Revisited

File under "how to break every Tableau rule in the book and thus create a poorly performing dashboard". Armed with a 70,000 row dataset from nces.ed.gov (specifically, the IPEDS longitudinal data set) I used parameters to allow the end user to select their desired metrics for correlation. I also have way too many filters on the filter shelf. The basic problem is that NCES has hundreds of metrics, all poorly named, all arcane, all requiring an excel file to look up their data definition. Many of them are ragged, meaning not all schools reported in all years. A brief scan of the metrics also shows me that definitions of metrics themselves change over the years.

I'm curious as to how other might solve this problem. I considered reshaping the 70k rows but with the number of metrics, it would end up in the tens of millions. Further, I would have a data model that is "too reshaped" or "too tall and narrow" for correlations.

If you are familiar with the common data set around IPEDS, you will be comfy using this dashboard. If you are not familiar with it... good luck! There's also a second tab with "additional filters" to refine your IPEDS criteria.

Learn About Tableau

Individual Query Auditing with Tableau

Tableau Server has great built-in audit tables for who has looked at what content and when. We discussed this briefly in a previous post related to performance monitoring. What is NOT built-in to Tableau Server, however, is the ability to audit detailed field-level usage, or put in plain terms: who filtered what? (in other words, what was the resulting WHERE clause and WHO ran it?)

It turns out that if you are clever you can get this to work. Here's the brief overview of steps required:

• In your database, enable tracing - this is not covered in this blog post!
Be prepared to report on this tracing. This could be with Tableau Desktop, or some other tool. In the example below, we used SQL Server and Tableau Desktop and only those two tools.
• On all of your visuals, create a calculated field that tracks the current Tableau Server "Username". You also need to add a unique string with which to grep the answers later on.
• Optionally, you can add this calculated field to Tableau data source filters - read more about that feature here and here. This is super awesome because it means that you will have enabled detailed auditing not just for Tableau Server web users, but also for any Tableau Desktop users as well!
• In the example shown below, because we have a calc which results in "SPECIALTRACKINGKEY"+Username, we can then filter out all of the noise in our tracing data, and focus only on the Tableau query usage. Nifty.

In order to publish this workbook to Tableau Public, we first had to delete the magic calculated "tracking field" (Tableau Public does not support user filtering, and nor should it). Here is the definition of that field:

"SPECIALTRACKINGKEY" + Username()

You will need to add this back into your example in order for any of this to work. This subject is generally not for the faint of heart, so take your time and best of luck. Enjoy!

Learn About Tableau

Individual database information for enabling SQL tracing (I'll try to add to this list as time permits):

• Postgres: http://chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/
• MS SQL Server: http://technet.microsoft.com/en-us/library/ms175047.aspx

Tableau Stepped Values

Filed under "stupid pet tricks" (should that read "stupid tableau tricks" ?), this handy calc form can provide all kinds of rounding tricks:

(int([tablecalc]*[Number of Steps])) / [Number of Steps]

Where "tablecalc" is a Tableau Table Calculation and "Number of Steps" is a Tableau Parameter. See example below. Enjoy!

Learn About Tableau

Tableau Measurement Conversions

There are lots and lots of measurement converter utilities out there - in javascript, perl, html, asp, java, whatever - but I haven't seen one in Tableau yet!

So here you go :) ...enjoy!

Learn About Tableau

Modulo Function

I'm trying to make sense of a) the world we live in, and b) the modulo function in Tableau - all in one fell swoop. The viz below is the best I could come up with. Print out your very own geopolitical bingo card. Chances are good that you will get five in a row in any given news week...

Learn About Tableau

Tableau and Multiple Trend Models

Following on some previous work where we calculated the basic linear regression model in Tableau manually using calculations and Table Calculations, we can extend this a bit to allow multiple regression models. In the example below, we start with the manual calculations for various average metrics from the IPEDS higher education data set, as compared to a primary metric “Total Gross Revenue”. From here we can enable color. And from there we can have Tableau automatically calculate a new regression model based upon the color. This means we end up with one primary linear regression model, and two sub-models for elements above and below the primary model. Hope you find this useful in some manner…

Learn About Tableau

Purchasing History Using Table Calcs

This subject is nothing new to Tableau, but one I revisit all the time. A client asked me "I want to total up the customers but only those that have purchased something within thirty dates of their first purchase ever." Table calcs to the rescue using a special form of which I am a big fan. See the intro tab below. Enjoy!

 

Learn About Tableau

Multivariate Analysis

Over the years I have heard people throw around the phrases 'multivariate analysis' and 'regression analysis' in situations where I don't think they know what they're talking about. It's just a checkbox on a list for them. Guess what? I also don't really profess to understand all the functional math behind the many different subjects related to these phrases. (Well, maybe just a little...) But what I can say is that I'm starting to suspect that all of the traditional modeling is becoming less and less of a requirement and more and more of a nice to have. It's also boring as hell. And it's also totally doable in Tableau if you are willing to change the way you look at information.
For example, wikipedia has a nice sentence in their writeup on multivariate analysis: "Often, studies that wish to use multivariate analysis are stalled by the dimensionality of the problem." What? They are? Sad... this is not a problem in Tableau! Data can be measures, dimensions, discrete, continuous, dependent, independent, quantifiable, and categorical… all at once. We just don't even think about these kinds of challenges at all.   To the right, we are using one of the oldest books in the Tableau bag of witches. I don't want to beat a broken horse, but seriously? Tableau "measure names" and "measure values" are da bomb! We have also added Tableau parameters to allow selection of the primary variable; logarithmic scale to show widely different value ranges; and the use of color encoding to help identify variables. Tight, amarite? No, this paragraph was not run through a bro-speak filter. It's just your browser.	Learn About Tableau
Learn About Tableau	They also state: "regression analysis can be used to understand which among the independent variables are related to the dependent variable…" - Hello? I think this is called a Tableau Bin or a Tableau Group the last time I checked? It's been around forever. Oh, and you can create ridiculously complicated Tableau Sets and calculations to drive various cohort and dimensional slicing analyses if you need that type of thing.   The interesting item on the viz to the left is the use of type-in filters to remove calculated outliers. Not originating data, but calculations of that data. The calculations in this case are rather simple: it's the difference of the per-student values (GPA or Hours) from the mean values of the entire data set. They could be as complex as you need them, I suppose.. Where this gets into multivariate territory is the use of small multiples if needed - even picking the dimension for the small multiple itself. Play with the choices to see what's going on.
One type of multivariate analysis is "bivariate analysis" which is the simplest form you can get away with in order to pass your entry level college stats class. You gotta have two variables to call it "multi" after all! In conversation, "bivariate" will get you into trouble because it's just not as cool-sounding as "multivariate". Although it does has an above-average coolness factor compared to most buzz words.   On the viz to the right we are allowing bivariate analysis to explode into greater and greater detail. Why? At lower, blockier resolutions you could lasso an entire circle; view underlying data; and then do something with that data. Look for this icon after you lasso some data: Or, simply increase the resolution ("Bin Size") to see the more-detailed pattern. Like the above examples, another way to say the word multivariate is to simply place an independent variable - in this case "low income flag" - onto the row or column shelf in Tableau.	Learn About Tableau
I feel like a lot of this discussion comes down to syntax and dialect. When I speak with people who have a rigid way of looking at information from a didactic and terminology point of view, I want to shake them around a little bit and ask "have you even tried to look at your information in any form whatsoever, before asking about this advanced crap that no one really understands that well to begin with?" At least, that is what I am thinking. Perhaps I am just staring at you vaguely. A scenario I see all the time is when someone wants a particular analysis - say, multivariate - but when I ask them why, they don't know. They were just told to do it. Or they read about it somewhere. Or a colleague insisted on it. Or any number of other inane reasons. I advise these people to take a moment to ask themselves "what am I trying to do here." Even worse is when someone truly knows what they want, but they don't want to spin up any cycles trying to use a tool to do it. If you are going to use Tableau or any piece of analytic software, take a moment to understand how it works. There's rarely a magic "multivariate" button. And if there is a claim of one, caveat emptor!

Statistics

Aaaaaaannnd, we're back!

I took so much time off from Tableau stuff that I had to go back and bone up on my table calc-ing. I've put together a collection of "statistics" type calculations. Some of these are Tableau classics, some of them are mundane, some are newish, and some of them are downright stupid and not recommended (which is of course why I created them)

Enjoy!



Select A Statistic
Skewness and Kurtosis
Histogram
Cumulative Distribution
Confidance Intervals
Correlation Modeling
N-Tiles
Mode
Skewness as Transform
Small Multiple
Control Chart
Pareto
Linear Regression
Box-Cox Transform
Z Test
Fitted Curve
Benford's Rule
Forecasting
Parallel Coordinates

MACD Technical Analysis with Tableau

Moving Average Convergence Divergence (MACD)

As a follow up to my previous post on Bollinger Bands, I took a stab at MACD, which derives from similar calculations.

This is a somewhat squirrely subject and I might be opening myself up to attack or a can of worms in this attempted analysis. As a preemptive strike against such attacks, let me start off by saying that as a stock trader, I fall firmly in the "fundamentals" camp, and this type of technical analysis does little for me. Second, allow me to graciously further admit that my math could very well be wrong. Finally, grant me the chance to comment on the fact that we can find on the internet dozens and dozens of permutations on the mathematics of this MACD subject.

Three web links which I found quite helpful in understanding the math behind MACD are these:

The Wikipedia Page on MACD
The Wikipedia Page on Moving Averages and Exponential Moving Averages
which are required components of MACD analysis, and lastly,
A Stock Charts Dot Com web page which has a nice discussion on the subject.

Feedback on the mathematics is welcome.

Powered by Tableau

Bollinger Bands in Tableau

Note: I am updating this post to include the following cross-link to Andy Kriebel's vizwiz.blogspot.com blog. He came up with a superior-looking approach to the banding effect. That post is here.

Just another calc that I have been meaning to pick off the list. The essence of the Bollinger Band is this:

1. Start with a moving average - the industry standard appears to be "20 trailing periods".
2. Create a upper band from this average which is 2*STDEV(same periods) + moving average
3. Create a lower band which is moving average - 2*STDEV(same periods)

However, I have added Tableau Parameters because there are several variations on the Bollinger definition. E.G. instead of using a multiplier of "2" you can vary this, and instead of defining the moving average as "20 periods" can you can vary this. Download the book to review the calcs - it's remarkably simple, thanks to Table Calcs. Enjoy!

Powered by Tableau

Fitted Curve Modeling in Tableau

 

Powered by Tableau

Table Calcs and Parameters

I continue to be a huge fan of Tableau Table Calculations in conjunction with Tableau Parameters. In the example below, someone wanted to have a single visual with multiple possible "percent of total" calculations. But, they didn't want to have multiple measurements in play.

Because each Table Calculation can have its own "scope" or "partition" this turns out to be pretty easy to create. Enjoy!

Powered by Tableau

Table Calcs and Multiple Years

This viz uses several Tableau features to create a streamlined analytic view. See the "explanation" tab for the original challenge as well as the components of the solution. Use of tooltips below is highly recommended.

Powered by Tableau

Thematic Maps - Seattle Crime Statistics

Sometimes I get rather caught up in the various dross and droll of the geocoding and mapping subject (read: thrash) as it relates to analytics, and I forget that a good map does not have to reference actual geography to represent a theme of data... Seems like a "D'uh" moment. I must be getting old.

The data below comes from http://data.seattle.gov and this visual uses a simple police precinct image as the background.

The basic conclusion I can come to with this data is: overall, crime is quite a bit down year over year for 2008-2009. Nifty.

All Crime Data | Crime Decreases | Crime Increases

Forecast Modeling

Author's Note, Dec 2010:This blog post is now obsolete by virtue of the fact that Tableau 6.0 has "parameters" - the entire subject of data explosion or data fanning is no longer a requirement.

original blog post


Quick start cheat notes:
 

• sample data is here 
• make all joins "not equal to" 
• set all filters to single-select (and make them global just for convenience)

Read the rest of the article below this dashboard, for the full details... 
Use the sliders to interact

Powered by Tableau

 

 




A discussion about "predictive modeling", or "forecast analysis", or, "what if analysis"

This entry discusses steps required to use Tableau to create what if or hypothetical analysis models. While not currently a core feature of the product suite (with the typical resulting marketing and sales splash as such), it turns out that with a little thought, and by following a few simple rules, you can create forecast models in Tableau. For the purposes of this discussion, I have used Xcelsius as a comparison, a tool that is commonly thought of as excellent for this type of analysis.

Products that provide extensive forecasting and predictive modeling algorithms are great tools for creating "what if" or hypothetical analysis - there's a great deal of heavy lifting going on to make this magic work. Some of the technical challenges involved include (not limited to):

All of the heavy lifting or number crunching needs to occur somewhere or at some point in the overall process

The user experience and software design thereof needs to provide compelling and useful tools for managing the input and output of the modeling.

There are some good software packages out there that provide forecast modeling and predictive analysis. A few worthy mentions include SAS, SPSS, the open source "R" package, and SAP’s Xcelsius (formally owned by Business Objects, who in turn purchased it from Infommersion, Inc, see nice history link here). From anecdotal evidence it appears that Xcelsius attempts to operate on one very small amount of data at a time, and then "explodes" this data out into a much larger data set - most likely in memory. General approaches to this type of modeling scenario include pre-processing possible values in a database engine, or, performing other in-memory processing.

Tableau can achieve this "exploded" data concept by creating table joins on all possible values - essentially a Cartesian join showing all possible combinations of data. There are a few simple tricks to make this work:

1. Create all joins as "table1.fieldX <> table2.fieldY" (i.e. use the "not equal" type). Make sure no values will match, or else this does not work! See the excel file which shows the four sheets to join; as well, you can download the workbook shown above to see the joins.
2. Any time you create calculated fields, make sure that they are non-aggregation calculations. See the workbook for the calculation called "potential value". Basically, you want row-by-row calculations at all times! 
3. And most importantly! Make sure you have single-select filters for all forecast or what-if variables which are controlling the "fact" data.

What does that last item 3 really mean? Let’s take a closer look at the sample excel data:

In the example excel file, we had three categories (K-12 School District, S&P 2000, and Federal Agencies) and 6 possible values (50k through 300k) for a total of 18 possible values.

By joining on a percentage between 1 and 100, the data gets exploded out to 1800. And by adding in a second variable called "blending", which ranges from -.21 percent to .21 percent (43 possible choices including zero), this further explodes the 1800 records out to 1800 times 43 = 77,400 records.

However, because our three Tableau filters for "blending", "percentage" and "variable" are all marked as single-value selections, we will only see 3 records at a time (the original three categories).

Exploring the workbook shown above by downloading it will help convey this point.

You can now start to guess why Xcelsius and possibly other products start to perform slowly when dealing with larger data. One of the reasons is the data explode factor involved. If instead of the above numbers, I wanted to apply a two-variable what if or forecast model on 500 types of values (500 customers showing sales, 500 products showing inventory, etc), and one variable ranged from 1 to 100, the second variable ranges from -30 to +30, then my data explode result would be 500 * 100 * 61 = 3,050,000 records! No matter how you slice and dice this problem - in memory vs. pre-calculated, oracle vs. sql server or some other database, java versus dot net- you will still be faced with this concept of data "explosion"...

Tableau can handle 3 million records, by the way...

Multiple Levels of Aggregation

In the visual below, we needed to A) get all of the averages across all hours, B) find the minimum average of these, and C) compare the rest of the averages to the minimum average. Custom SQL as part of the initial Tableau connection is the best solution here.

In the example below, the client wanted to see - for each utility registrant and each hour of the day - the averages of energy use, but also wanted to see the various averages as compared to the lowest average (I.E. the min of the averages) This solution also allows us to see percentage bands above this minimum. Plus, if you click on a bar segment you can see the exact percentage above the minimum. Nifty!

Powered by Tableau