Tableau Server - Compendium of Links

Learn About Tableau

Happy Holidays 2013 Tableau Edition

Happy Holidays in 2013!! I was trying to solve a pretty simple problem and got a little carried away. The problem was to calculate the military format for "durations" - e.g. convertings seconds into DD:HH:MM:SS format. F5/Refresh this page to update the viz. I could have also added a forced javascript refresh, or better yet, a full blown image retrieval and slideshow example... but that would have been sort of spammy to the Tableau Public infrastructure. :) Enjoy!

Learn About Tableau

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 "Add to Context" Filters

I like to review ancient Tableau features from time to time, just to make sure things haven't changed. A Tableau "Add to context" filter is one of these.

The Feature called "Add to Context" is a great way to look at a subset of data inside of Tableau. It works by attempting to create a TEMP table in the database (which may or may not always succeed, depending on the database and your permissions - we are assuming that TEMP tables succeed for this blog post). As such, this feature can also be considered "dangerous" if it is not used correctly. Two areas where it is used incorrectly are:

1. Returning all the records from the original join or data connection.

Consider an imaginary table of data which is 100 million rows. The proper use of "add to context" is to filter down to 800k rows (for example), perhaps for one segment, customer, line of service, or whatever dimension you want. The improper use of "add to context" is to select everything in the filter. This has the effect of creating a new, second 100 million row table. If the originating data model was a multiple table join, the resulting context table is a single, denormalized, fully indexed Tableau TEMP table. A nice denormalizing effect, but largely useless if you are simply recreating the original 100 million rows. Now imagine you have Tableau Server thrown into the mix: each time an end user interacts with this sheet or dashboard, a new 100 million row TEMP table will be created. Not good... the solution is do not do this, please!Don't select all the field values in a "add to context" filter. It defeats the purpose of using this feature in the first place.

2. Returning a very wide table result when you only need some of the columns.

Let's continue to use the 100 million row example. But this time, we will stipulate that it contains 160 dimensions and 65 measurements. 225 columns in total. The feature "add to context" will dutifully create a TEMP table with all of these columns, and indexes on each column. This could take a long time to generate, before you even get to the analysis itself! If you do not need them, you can hide fields prior to using "add to context". Once hidden, they will not be included in the TEMP table output. This can have a huge performance impact on your use of "add to context".

In an actual test against a 40 million row table, we ran a context filter on a very wide and a more narrow Tableau result set. The wide data connection took 47.57 seconds to A) create the TEMP table, B) index the columns and C) query the "number of records". You can see all of this in the Tableau desktop logs. By the way, it's worth pointing out how amazing this fact is, that Tableau generates all of the correct SQL code for this. However, the result is huge and wide. This data connection had 50 dimensions and 32 measurements.

We ran the same exact "add to context" test against a second data connection which only had 11 dimensions and 4 measurements. This time the query took 13.98 seconds. This was a 340% reduction in query time!

Be careful when using this feature. It can be a huge time saver - as well as a huge pain...

Tableau as a Web Application

This is a draft of a work in progress, designed to showcase how to build web applications using Tableau Server, Tableau Online, or Tableau Public. To get started, take note that there are hyperlinks on the front door. On each subsequent tab, there are help icons (with useless help) as well as a home icone to take you back to the main screen. Most of these dashboards are also highly interactive. Tableau can be used to build applications, not just provide analytics.

Learn About Tableau

Get Your Brasil On

Two years ago I wrote two posts about Brazilian geocoding and mapping here and here. Since that time, the Brazilian government website IBGE.gov.br seems to have removed their city level shapefiles, or at least, I cannot find them. LUCKILY, I kept a copy of the polygon data set that I created.

Shown below is the entire country. Click a state to drill down. There are no metrics in this data set. The assumption here is that you will use data blending for your analysis. I also make no warrants or guarantees about the accuracy of the polygon data or about how up to date the boundaries are.

Learn About Tableau

Tableau Menu Navigation using Parameters

I didn't see a good example of using Tableau "Parameters" to create navigation systems. I could have sworn there were some good ones out there; so if you have any, post them in the comments section.

The basic trick is to

• place number of records and a parameter onto a sheet called "navigator".
• On various dashboards, create a Tableau Action, source is this navigator sheet, target is the destination dashboard, filters are set to "leave filter", fields are set to "all", and the action is of type "menu".
• You will need to create lots and lots of these. Each menu to each dashboard from each source...

Download the workbook shown below if you want to reverse engineer it

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

Revisiting Sparklines in Tableau

In the Seattle Times print edition (various days), there is a interesting micro chart in the business section which, for a 52 week stock price range, shows a circle on a range of "low to high". (I cannot seem to find an online example of this chart)... It's really a 0 to 100 percentile. The circle is the last price of the stock, and the range is the lowest to highest for the date range. At a glance you can get a sense of where the stock currently is along the date spectrum. I have recreated that concept below. Also thrown in is a sparkline. These viz make use of Tableau Table Calculations, of course.

This viz is set to 400x150 - nice and micro! - the concept could be quite useful for online media outlets. Download the workbook to study the solution. Enjoy!

 

Learn About Tableau

Cells Gone Bad...

Part of me is almost embarrassed to post this. But part of me is like "yeah, why not - take that silly rabbit!"...

No part of me is going to post any type of description of the solution, because it's not best practice; it's not very pretty; and it's just not that cool... luckily you can download the workbook and play around.

hint: use the scrollbars...

Learn About Tableau

250K

Alan Smithee Presents hit a quarter million page visits this week. He is enthralled and also loves it when we talk about him in the third person. Let's take a moment to remember all those forgotten features, websites, memes, viz types, and other assorted socio politics -visual errata from years gone by... ok now please stop doing that.

Thanks for all the great clickings on the 'puters!! We loves our alter ego. It's pppprrrrrecious to us!

Team Geiger Rides Again...

Over two years ago I gave a shout out to Team Geiger in this blog post. Well, this team of superstars has updated their awesome workbook to version 8. Lots of new viz types, both functional and business.

Check it out...

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

Chess Data in Tableau

Tableau has a public visual on The Best Chess Openings - a great viz by Ben Jones.

This made me curious as to what it might take to generate chess data for plotting in Tableau. It's non-trivial. I am still working on that script. In the meantime, here's a short game from the 1600's. Enjoy!

Learn About Tableau

And here is Joe Mako's improved version, from his comment below:

Learn About Tableau

Filtering Measure Names via URL

I love learning something new. We stumbled across the fact that you can filter the special "measure names" dimension via URL. I am sure this is not supported by Tableau, but it appears to work great. A few things to worry about:

1. It starts with &[:Measure%20Names] - take note of the colon in there!
2. You need the internal name of the data source, not the display name - see example below

Here is an example URL:

http://public.tableausoftware.com/views/MeasureNamesURLFiltering/TestDashboard?:embed=y&:display_count=no&[:Measure%20Names]=[excel.41431.369623564817].[sum:Cost:qk]

"excel.41431.369623564817" is the name of my datasource. "Cost" is one of the measures. Enjoy!

Select Measure Names Combinations
Cost
Quantity
Cost and Quantity
Demand Met
Cost and Demand
Cost, Quantity and Demand

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

IPEDS School Data

The U.S. Department of Education is nice enough to collect a vast trove of demographic information on over 7,000 schools in the US. I've created this little mini-app to help you explore the metrics involved. These are just a sliver of all of the available metrics. You can build your own output data and then run it through Tableau. Warning: it's a very rough ride to format this data to something which is beautiful!

Enjoy!

Learn About Tableau

Fedscope Data

This data set comes from the US federal government "Office of Personnel Management" - the data is the latest reported as of march 2012. The direct link is here.

All the panes in the dashboard below are "hot". Start clicking around to see where our federal salary dollars are being spent. Click on any whitespace to clear the selection for that pane. Enjoy!

 

Learn About Tableau

Tableau Server Integration with Salesforce

Update 03/31/2014: Tableau has released a Salesforce canvas solution, which consists of excellent documentation and example workbooks. Check out their blog post:
http://www.tableausoftware.com/about/blog/2014/3/now-available-salesforce-canvas-documentation-embedding-tableau-29499

Or download the zip file here

---

original post:

I wrote this up recently and I didn't want anyone else to have to slog through it... so enjoy!

Primer on Integration of the Tableau Platform with Salesforce

This document is broken out by “task”. Refer to each type of desired solution on the remaining pages.

Tasks:

1. Get access to and analyze Salesforce data with offline/on-premise hardware using Tableau Desktop or Tableau Server.
2. Get access to and analyze Salesforce data online/in the cloud using Tableau Desktop or Tableau Server.
3. Provide row-level security for Salesforce data when using Tableau Desktop or Tableau Server (example: sales reps seeing only their relevant information)
4. Provide URL links from a Tableau dashboard to relevant Salesforce information.
5. Embed a Tableau dashboard into the Salesforce web interface. (example: when looking at an account in Salesforce, there is a Tableau dashboard embedded into the account view showing details for that account)
6. Integrate Salesforce authentication with Tableau Server authentication when all users are behind a firewall using Active Directory. Desired behavior is that end users only have to log into a web application once.
7. Integrate Salesforce authentication with Tableau Server authentication when users are outside a firewall and not using Active Directory. Desired behavior is that end users only have to log into a web application once.
8. Encryption technologies. Ensure that Tableau Server is running with SSL enabled.

1 – Get access to and analyze Salesforce data with offline/on-premise hardware using Tableau Desktop or Tableau Server.

In this initial example, we want to pull Salesforce data out of the cloud and into an on-premise data warehouse. This can be achieved using a third party tool such as www.dbamp.com:

Pros	Cons
Provides an easy, automated method of aggregating Salesforce data offline for analytics	Requires on-premise hardware and local installation of SQL Server.
Allows integration with other local systems inside the corporate network	Requires configuration and setup and ongoing DB maintenance.
All standard and custom Salesforce objects are supported.

There are other ODBC tools out there as well. The same basic concept applies: using a third part ETL process, you can pull Salesforce objects and data out of Salesforce and into a data warehouse of your choosing. At this point, the data is available to all users inside of a corporate network. Tableau Desktop and Tableau Server connect to this data just like any other supported data source.

2 - Get access to and analyze Salesforce data online/in the cloud using Tableau Desktop or Tableau Server.

In this example, we want to pull Salesforce data out of the Salesforce cloud and put it into Tableau Server directly. For this connection type, we recommend Tableau’s Salesforce connector:

In this mode, Tableau will use the Salesforce API and perform a query and retrieval of data from Salesforce. Tableau Desktop will automatically extract the data using our fast in-memory data engine:

This type of connection will work anywhere, anytime. As long as Tableau Desktop and Tableau Server have access to Salesforce, you can use the built-in live connector to retrieve Salesforce information.

Once extracted, you can perform incremental updates against the Salesforce object. This is a very efficient way of maintaining the latest most up-to-date information inside of Tableau Desktop or Tableau Server:

Furthermore, you can schedule this extraction using Tableau Server. You will need to embed credentials at the time you publish the Tableau data connection:

Tableau Server will then automatically perform incremental refreshes of this data connection:

At this point you have a continuous and full data analytics experience for Salesforce data; the data originates in Salesforce and ends up inside of the Tableau Server “Data Server” and is now available for any new connections, whether in the browser or Tableau Desktop:

3 - Provide row-level security for Salesforce data when using Tableau Desktop or Tableau Server. Example: sales reps seeing only their relevant information.

Regardless of the authentication solution (see later sections), once your users are connected to the Salesforce data, you may want or require them to see only their data. In the previous section (2) we connected to Salesforce using Tableau’s native connector. This connection used the current user at the time of the connection. Tableau does not currently support a truly “live” connection to Salesforce data because it would be quite slow and would also utilize the Salesforce API quota extensively and expensively. Plus, the native Salesforce web user interface already serves this purpose.

In lieu of a live connection which would handle per-user security just as if you were logging into Salesforce directly, Tableau supports row-level security models by identifying the currently logged-in Tableau Server or Tableau Desktop user and then comparing this information against actual data. Remember that in section 1 and 2 we already received this data or information. All we need to do now is compare Tableau users to this information. An example Tableau Server user ID such as “Adam King”…

…would be matched up against the accounts that Adam King owns by creating a Tableau calculated field like so:

In the calculation shown above, the function FULLNAME() returns the full name of the Tableau Server user ID, and [Owner name] is a Salesforce field on the accounts object.

You can then add this filter to your views and dashboards. Each user will only see their data at this point. Further, you can add this filter to the data source directly – it will never be exposed and will always enforce row-level security across your organization:

This type of configuration can be for convenience to simply reduce the “data noise” associated with large sales or services teams – the default for individuals would be to see only their data, but still browse other people’s data. Or, this configuration can be truly locked down and secure – the default for individuals is that they would only see their data with no exceptions. The Tableau platform supports either of these modes.

4 - Provide URL links from a Tableau dashboard to relevant Salesforce information.

A common requirement for Tableau users is to link out from a Tableau dashboard back to the Salesforce web user interface. This is easily achieved using Tableau “Actions”. The action would key off of the Salesforce object ID. The destination URL is the standard Salesforce URL https://na4.salesforce.com/

In Tableau Desktop, you can design a URL action on any sheet or dashboard. For example, to create an action which links out to the Salesforce “Lead” object, your action would look like this:

Once created, this action would look like the following on a Tableau dashboard:

5 - Embed a Tableau dashboard into the Salesforce web interface. Example: when looking at an account in Salesforce, there is a Tableau dashboard embedded into the account view showing details for that account.

In contrast to the previous example, Tableau customers also want to embed a Tableau dashboard directly into their Salesforce portal. This is also achieved through the use of URL manipulation. The URL control is in the opposite direction from the previous example: Salesforce is controlling a Tableau dashboard.

Two design choices are required for this to work. These include: A) Both Salesforce and Tableau Server need to be accessible to the end users on a network and transport level, and B) Authentication choices need to be made (see next two sections).

Assuming both A and B are satisfied, setting up an embedded Tableau visual inside of Salesforce has two main steps. The first is creating a Salesforce APEX frame – this is usually done by a Salesforce administrator. The frame code looks like the following:

<apex:page standardController="Account"><apex:iframe src="http://tableauserver/views/SFDC_viz/dashboard?:embed=yes&:tabs=no&AccountID={!Account.Id}" height="340px" width="910px" scrolling="false"/></apex:page>

The Salesforce administrator will take the following basic steps to create and use this code:

Create a visualforce page with an iframe to display the viz.

The standardController is the name of the object that is related to the ID you will be passing.

The {!Account.Id} merge field will not work unless the standardController is set properly on the page.

See the online visualforce documentation (http://www.salesforce.com/us/developer/docs/pages/index.htm) for details about the standard component.

The reason this works is that all published Tableau Server visualizations can “receive” filters on the URL directly. In the above example, we want to provide an account overview using a Tableau Visual. We create this dashboard in Tableau Desktop, and Publish to Tableau Server. This dashboard contains the field “AccountID” somewhere on the level of detail for the visual. The APEX code invokes the URL and hands in the current Account ID at run time. This is notated with Salesforce syntax: {!Account.Id} 

This is very straight-forward to configure. You can of course design your embedded dashboards to look however you want them to.

6 - Integrate Salesforce authentication with Tableau Server authentication when all users are behind a firewall using Active Directory. Desired behavior is that end users only have to log into a web application once.

This section assumes you also have section 5 in mind: the ability to embed Tableau visuals directly in Salesforce. In section 6 and 7, we introduce the two main methods of authentication most commonly seen by our customers. Both of these methods have as their main desired behavior the following statement:

“End users should not have to log in twice, once to the Salesforce system and a second time to Tableau Server.”

When all users will be accessing Salesforce from behind a corporate network and firewall, this is fairly straightforward. Tableau Server should be installed/deployed using “Active Directory” mode.

End users will still need to log into Salesforce, but any embedded Tableau visuals or dashboards will pick up the current identity of the user using the web browsers’ existing credentials. This is a built-in feature of Tableau Server and achieved with a Microsoft security mechanism known as “SSPI“)

In section 3 we discussed row-level security requirements. These would still apply and some considerations of the data model need to be taken into account:

a) If we have end users logging into Salesforce with their email address, we will need some type of data-driven mapping which associates their email address with their Active Directory Account.

b) Alternatively, we can retrieve the end users’ “Full Name” which will often match across multiple systems. The “Full Name” can be retrieved in a Tableau calculated field with the FULLNAME() function.

c) Lastly, the Salesforce user ID itself makes an excellent join candidate when creating robust data-driven security solutions, as this alphanumeric string never changes over time.

7 - Integrate Salesforce authentication with Tableau Server authentication when users are outside a firewall and not using Active Directory. Desired behavior is that end users only have to log into a web application once.

If active directory is not an option due to network and remote office considerations, and a single sign-on experience is still a requirement, then the remaining alternative for Tableau Server is to enable “Trusted Tickets Authentication”. This is discussed in the online documentation here.

Tableau’s “trusted tickets” authentication works by having Tableau Server 100% trust an existing external web application – in this case Salesforce – this is achieved on an IP address basis.

Because Salesforce cannot present itself to an external application by IP address, in order to integrate the authentication, Tableau customers stand up a machine which acts as a proxy between Tableau Server and Salesforce.

Tableau Server only has to trust this one single proxy machine. This machine in turn simply acts as a wrapper for the Salesforce apex frame.

8 – Encryption and SSL.

Because Salesforce has SSL enabled by default, you will want to enable SSL support for Tableau Server. Otherwise, your end users may get a warning in their browser about mixed networks – undesirable from an IT point of view. To enable SSL support, review the online help here.

Good luck and happing integrating!!

Excel Color Palette

Pasted below the viz is the xml to add to your tableau preferences file to get the "excel color palette". Why you ask? No particular reason. I have also garishly formatted this viz to make the excel users more comfortable. Enjoy!

Powered by Tableau

Here is the xml:

<color-palette name="excel" type="regular">

<color>#000000</color>
<color>#FFFFFF</color>
<color>#FF0000</color>
<color>#00FF00</color>
<color>#0000FF</color>
<color>#FFFF00</color>
<color>#FF00FF</color>
<color>#00FFFF</color>
<color>#800000</color>
<color>#008000</color>
<color>#000080</color>
<color>#808000</color>
<color>#800080</color>
<color>#008080</color>
<color>#C0C0C0</color>
<color>#808080</color>
<color>#9999FF</color>
<color>#993366</color>
<color>#FFFFCC</color>
<color>#CCFFFF</color>

</color-palette>

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!