If They Only Had Tableau
I’m a car nut! Specifically, I’m in love with old classic cars from the 60’s. When I was nothing more than a twinkle in my Mother’s eye, my Dad was racing stock cars on dirt tracks in central MN in the late 60’s. My dad’s car was a late model Studebaker Hawk and he had a lot of fun with the hoppy. I sometimes wish we could see new chrome encrusted Studebakers coming out of South Bend IN again!
Which brings me to my next thought, “What if Studebaker and others had BI?” More specifically, what if they had Tableau? Put aside the fact they would have needed a computer the size of my old high school to run Tableau in 1963 – but just imagine the possibilities! With that kid fantasy in mind, I put Studebaker and a few others back in business to present day. In the visualization, we have a dashboard of some classic bankrupt auto companies showing rate of return, dealer participation and product recalls (Sorry John Delorean – even in my fantasy world your cool stainless steel cars are crap!)
As of this writing, Tableau doesn’t yet have a built in ROR function. However, you can roll your own with a little table function magic, which is exactly what I did here. So my data set had rate of return for each calendar year but didn’t have a compound rate for many years and you can’t just sum the aggregate of yearly RORs. You need a product of all the year members filtered by the end user. The ROR formula works like this: (Period1 +1)*(Period2+1)…* (Period last +1)-1. In Tableau, we can reproduce this function by using the PREVIOUS_VALUE function in a calculation like this: PREVIOUS_VALUE(1)*(1+SUM([ROR])) and then the cumulative function used in the visualization would be this: [ROR Product] -1.
So why should anyone care? First of all, you simply can’t put this logic in your data warehouse and calculate it ahead of time. You don’t know which time period(s) the analyst is going to start/end with. In a data warehouse, if you were going to pre-populate the cumulative ROR, it would have to be since inception date and that would make for some really un-usable charts and graphs. Care to see cumulative ROR since 1902 anyone? I thought not. So the cumulative ROR as well as a host of other financial calculations that are dependent on chosen time periods can take advantage of these table calculations in Tableau. Another benefit to keeping analytic calculations in Tableau is speed of delivery. We don’t have to go back to the data warehouse and refactor, we don’t have to re-extract the dataset, we can prototype, experiment, test and retest until we have it right – all from the Tableau client. In my sample, I used Tableau Desktop pro, but I could just as easily have used the free version of Tableau Public to do the same exact visualization. Tableau is an amazing piece of technology. I just wish Studebaker had it when they needed it most!
To learn more about BI and all of the exciting new features SWC has for the business intelligence community; please join us for our next informative Business Intelligence event.
If you enjoyed this post from Chad, please check out a few of our past posts on Tableau and business intelligence:
Ask SWC: What’s So Great About Tableau?
My Search For The Business Intelligence Chupacabra
OAuth 2.0 – Google API Business Intelligence Implementation
An Agile Approach to Business Intelligence
How to Fast Track Business Intelligence
Can’t afford BI? Try the BI Analytics Tools in Everyday Software
How to Break Business Intelligence Users’ Excel Addiction
Ask SWC: What Is A New Technology That You Find Interesting?
Agile BI Software Solution
SWC’s Virtual Database Administration (VDBA) Solution