Archive for Tableau

Refreshing Tableau Public Data


Tableau Public is a free tool to share your visualizations with the world.  One question we often here is how do you refresh data for a Tableau Public visualization.  Tableau Public will do an automatic, nightly refresh data of Google Sheets data, no extra work on your side.   However, there is a bit of additional work required in order to automatically refresh your Google Sheets data.  In our example we are pulling data from a AWS Redshift database using Python version 3.6
Google Sheets Set-Up
This set-up will create the credentials necessary to refresh your Google Sheets data.

  • Create a project in Google Developers Console https://console.developers.google.com/flows/enableapi?apiid=drive. Create an account if you have not already done so.
  • Click on Library and search for the Google Drive API, and verify the API is enabled.
  • Next, click on Credentials. Then click on create credentials -> OAuth Client ID, choose “Other” as the application type and enter a name for your credentials.  You will need these credentials to connect to Google Sheets.
  • Now, download the client ID you just created. Move this file to your working directory and rename it client_secret.json.  For example, C:\xxxx\xxxx\.credentials
  • Go into your Google Drive account and create a blank google sheet and copy the File ID from the url. https://docs.google.com/spreadsheets/d/1kadJ8y1_VZnKGqPx6ohA3F-OdHtablmyzr2ZF6nuH18/edit#gid=0.  You will need this ID for your Python script.

Python Scripts

  • For this script you will need to install both psycopg2 and google-api-python-client libraries.
  • Now create your Python script which begins with getting your credentials. You can find this at the Google Sheets developers guide on Step 3.
  • Once you have your get_credentials function in place, you will need to connect to your Redshift environment and insert your SQL query. You can connect to Redshift using the psycopg2 package.
  • We then use csv_writer to write the database query to a CSV file.
  • Now overwrite your Google Sheets file with the new data. This requires the Google Drive API.
  • When you run your script, a browser window will open and ask you to enable authentication. This will ensure your code has rights to write to Google Sheets.  This will only happen the first time this is run.
  • Once you have this set-up, you can schedule the Google Sheets refresh to happen prior to the Tableau Public refresh, which occurs around 6 am ET.

I hope this helps you get started with utilizing Tableau Public’s built in data refresh so you can share your visualizations with the world.
 

Power BI vs Tableau

I am often asked, “Which tool is better: Power BI or Tableau?”
Microsoft has done a great job with Power BI over the last two years. The features in Power BI have evolved so much that we’re now recommending it first to our clients. Microsoft identified the most important and useful features for a data visualization tool set and embedded them within Office 365. Power BI now has the distinct advantage of being familiar and easily accessible to non-technical users, which improves its adoption rate.  Microsoft's Power BI vs Tableau
In addition, Gartner’s 2017 Magic Quadrant ranks Power BI higher on “Completeness of Vision” than Tableau. However, to be fair, both Power BI and Tableau are matched for “Ability to Execute.” Tableau was first to market and has deeper capabilities, however, in our experience, the advanced functionality offered by Tableau happens to be the functionality that associations use the least.
If you are a power user, you may still want to consider Tableau. But, if you’re responsible for rolling out a new data analytics initiative within your association, Power BI is an affordable option with the added benefit of being “front and center” when staff log in to Office 365.
This list of pros and cons will continue to evolve, so check with us if you want to know what’s new with either solution.

 Power BI

 Tableau

 
Pros

  • Affordable
  • Integrated with O365
  • New features weekly
  • Familiar to Excel users
  • Self-serve data prep
  • Office store apps and custom visuals available
  • Extensive data connectivity
 
Pros

  • Excellent user interface
  • Fast and easy to create visuals
  • Good customer support
  • Story-telling feature
 
Cons

  • Data Analysis Expressions (DAX) knowledge required for advanced calculations
 
Cons

  • Cost
  • Self-serve data prep

Creating a Dual Axis Chart in Tableau

Dual Axes Definition

Dual axes are two independent axes that are layered on top of each other. According to Tableau, dual axes allow you to compare multiple measures. Dual axes are useful when you have two measures that have different scales.

Using Dual Axes in Tableau

How do you create a dual axis chart in Tableau? It’s easy to do once you know a few tricks.
Any continuous data field can be used in a dual axis chart. This means you can use measures such as Sales, Profit, and date fields in your chart. I will walk you through how to set up a dual axis chart using both a number and date field. For both my examples, I will be using the Sample – Superstore data set.

Dual Axis Chart with Measure

For our first chart we are going to look at the Sales and Profit against Sub-Category.

  • First, pull sub-category onto Columns.
  • Drag Sales to Rows and then drag Profit to Rows next to Sales. You will then have SUM(Sales) next to SUM(Profit).
  • Right click on SUM(Profit) and choose Dual Axis.dual axis
  • You will notice that now that you have the dual axis, the two axes have different scales. At this point, you need to decide whether or not the two axes should be syncronized.  To do this, right click on the Profit axis and select Synchronize Axis.synchronize
  • Once you have a dual axis chart, you will be able to use different marks each measure. On the Marks Card, you will now have the option to format the two measures differently.
    marks
  • If we want to have a bar chart for Sales, click on the SUM(Sales) section on the Marks card and choose Bar from the drop down menu.marks bar

 

Dual Axis Chart with Date

The steps for creating a dual axis date chart are very similar to what we did above.  For this chart, we will be looking at profit against ship date and order date.

  • Drag Profit to Rows.
  • Next, right click and drag Order Date to Columns. When the Drop Field pop-up appears, select continuous Month.drop field
  • Now, right click and drag Ship Date to Columns. Now, like above, select continuous Month.
  • Right click on Month(Ship Date) and click Dual Axis.
  • Now, right click on the Ship Date Axis, and choose synchronize axis.
    date dual axis
  • Now, go to the Marks Card and select All. We will need to remove “Multiple Fields” from Color.
    marks color
  • Then on the Marks Card, click on Month(Order Date) and manual change the color, so you have a different color for the Ship and Order Date Profit lines.

I hope that this helps you to create your own Dual Axis Chart in Tableau.  It can be a very powerful chart once you are familiar with the ins and outs of creating it.

How to Add Tableau Information Icons

Neuroscientists from MIT discovered that the human brain can process entire images seen in as little as 13 milliseconds. Data visualizations can instantly communicate complex information and ideas, but that doesn’t mean dashboards don’t require some explanation.

The Role of Explanations in Dashboard Design

When a data analyst creates a dashboard, they are trying to convey vast amounts of information quickly and effectively. Every element on your dashboard takes up cognitive load on the part of your audience. As as dashboard creator, you want to eliminate the elements that unnecessarily use up brain power and space.
Usability is key, which is why it can be helpful to include an explanation in your dashboard about how to use it or what business questions it seeks to answer. While you should strive to make that as intuitive as possible through other dashboard elements, sometimes infrequent or first-time visitors need more context. Lengthy text explanations can clutter a dashboard and distract from important data and results.

How to Add Tableau Information Icons

information icon
You can see the information icon in this visualization that shows Top 5 Customers by Sales. The icon appears as a lowercase i in a circle in the upper right-hand corner.
One way to balance the need for textual explanation with the need to have the focus be on the data is to create a simple information icon. An information icon allows you to provide information about your dashboard or view without taking up valuable real estate.
In about minute, follow along as I show you how to add a Tableau information icon using a custom shape and tooltip.

Workbook Performance Recording – Tableau Server

The first and most important rule about making workbooks more efficient is to understand that if it loads slowly in Desktop on your computer, then it will be slow on the server too once it is published. Desktop and server each have their own way to enable, record, and analyze performance. The focus here is on performance recording for workbooks published to Tableau Server.

Enabling Tracking

  1. Administrators must enable the feature. This is located under settings, for each site.
  2. Check the box and save for Workbook Performance Metrics.
  3. It is a good idea to leave this disabled when you are not using it since recording metrics can also impact performance.

tableau_performance_enable

Create the Recording

      1. Navigate to a view on the server.
      2. Remove the iid=xx from the URL.
      3. Enter in its place record_performance=yes. Your full URL should now look something like this: https://data.associationanalytics.com/#/site/AA/views/AAEmailActivity/MessageStatisticsSummary?:record_performance=yes
      4. After the page reloads, you’ll notice the ID is added automatically back to the URL and that a performance button appears within the View’s toolbar. Don’t click on the performance button yet.
      5. Do some filtering and some clicking within the workbook such as applying filters, selecting marks/rows, and clicks that cause actions to other elements of the visualization.
      6. Then click the performance button.
      7. Now you’re ready to click on the Performance button which will launch a new window with the collected statistics (see next image).
      8. The analysis and follow up actions are a whole other topic, but to quickly mention that you want to make sure your timeline slider is all the way to the left and then you’ll be able to see the different events and which takes the longest: executing query, sorting data, building view, connecting to the data source, geocoding, or computing layout.
      9. The provided workbook is not directly sharable, but the capability to download the resulting workbook is provided. Further, it is possible to use the download to publish it to another location.
      10. Don’t forget to disable the performance recording in the admin settings when you are finished.

tableau_performance_viz
Now that you know you can record and view the results of a workbook published Tableau Server, you can start to analyze the results so they load faster. In separate posts we’ll cover performance recording in Desktop and how to interpret the provided visualization.

Tableau’s R Integration

Ever wondered how data scientists and data analysts use Tableau for predictive analytics? The ability to integrate R into Tableau is powerful functionality. For those familiar with using R, it can be tricky to get started. Here’s how to get started with the R Integration.

Step 1. Set Up R on Your Computer

First, you will need to have a user interface for R on your computer. We recommend R Studio Desktop.

Step 2. Install RServe Package

Next, you will need to install the RServe package. To do this, click on Packages -> Install. Then, type in RServe and it will find the package for you to install.
reserve

Step 3. Set Up Rserve Connection

Now you will need to run the following code to start up the Rserve connection:
library(Rserve)
Rserve()

Step 4. Set Up the External Connection in Tableau

There is one more thing you will need to do prior to writing in R in Tableau, but to do this you will need to switch over to Tableau. Tableau needs to have the external connection set-up in order to run R.  Go to the Help -> Settings and Performance -> Manage External Connections.
R Serve
 
In the pop-up, type in localhost for the Server name. Click on Test Connection to verify it is now connected.

Step 5. Start Using R Integration

At this point, we can now start taking advantage of the R integration.  The integration uses calculated fields to pass R code. There are four different types of calculations used in the R integration:

  1. SCRIPT_BOOL
  2. SCRIPT_INT
  3. SCRIPT_REAL
  4. SCRIPT_STR

Which one you use depends on what type of value you expect to get as a result of your R Code.  SCRIPT_BOOL would be used if you expected a TRUE/FALSE value returned.  SCRIPT_INT would be used if you expected to have an integer returned.  SCRIPT_REAL would be used if you expected a numeric value returned.  SCRIPT_STR would be used if you expected a string value to be returned.
The basic set-up of any R calculated field is as follows:
SCRIPT_REAL (
“R code”,
Tableau fields being passed in
)
The R code would be encased by quote marks and the parenthesis would encase both the R code and any Tableau measures/dimensions that will be used inside the R code. You can pass in multiple Tableau fields, you will just need to separate the field names using a comma.
Two important items to know is that inside the R code, you do not use the Tableau field name. You will use .arg and you cannot mix aggregate and non-aggregate arguments.  Here is an example below.
script_bool
Within my R code, I would need to refer to sum([Profit]) as .arg1 and ATTR([Department]) as .arg2.  Also, I made Department an Attribute in order to use both it and Profit.

Example of R and Tableau in Action

Now that you have the basics of the calculated field, here’s a real life example using the Superstore dataset. We’ll be looking at the correlation between Profit and Discount.  The returned value will be a numeric value, so I will be using SCRIPT_REAL.
script_real
Now, use that field to visualize the correlation coefficient between Customer Segment and Supplier. A value close to -1 indicates a negative linear relationship between the variables. A value to close +1 indicates a positive linear relationship between the variables.
matrix
This is just a starter in using the R integration. Hopefully, this will help you get started using this at your own association. If you need help developing predictive models or using R, contact us.

Top Posts of 2016

We looked at our Google Analytics data to bring you our most popular blog posts from 2016 based on total hits. If you missed these popular posts, here’s another chance to read them.

Tableau 9.3: Smarter Version Control and Better Use of Color

Back in March 2016, Jasmin Ritchie published this summary of her favorite new features in Tableau Desktop 9.3. New features included the ability to have workbook versions stored on the server when a new one is published so that it can be reverted if necessary. Here are her other three favorite new features:

  1. Newly designed dialog for data source publishing
  2. Excluding grand totals from shading so the eye isn’t drawn directly to those obviously higher numbers
  3. Applying colors to sheets. This is very helpful for organization and communication. We’ve started using color on the sheet name to organize and identify which worksheets are not ready to be published.

Top 5 New Features in Tableau 10

Tableau 10 was released in the August 2016. Tamsen Haught provided a review of her favorite features in Tableau 10, including:

  • Revision history improvements and previews
  • Device specific dashboards which enable a single dashboard to target multiple device sizes automatically
  • Workbook formatting which is a glorious time saver that lets you apply a style to an entire workbook
  • Cross data source filtering which makes filtering across data sources much easier with an option called “all related data sources”
  • Clustering, which is essentially a simplified way to use segmented groups like exercise and diet and find which locations are similar to one another.

Also worth mentioning, that when you upgrade to Tableau 10, you get the new features from 9.3 as well as any other earlier version you’re missing.

Using Propensity Modeling to Drive Revenue and Increase Engagement

Propensity modeling lets you “look at past behaviors in order to make predictions about your customers.” Associations can use propensity modeling to drive revenue and increase engagement. Models are able to predict the likelihood that someone will buy, churn or lapse, or unsubscribe. There’s a lot of potential with this type of advanced analytics. We preict more associations will invest in advanced analytics like propensity modeling in the year to come.

Tableau Conference 2016 Highlights

I had the opportunity to attend the 2016 Tableau Conference in Austin, TX. Every year at the conference, Tableau announces and demoes new functionality that will be released in upcoming builds. Here are some of the new, exciting features coming down the pipeline.

New Features with Tableau Server/Online/Mobile

  • Creating Story points on web
  • Data-driven alerting for data falling outside of a certain threshold
  • Ability to use full screen mode on Tableau Server/Online which will make it easier to use Tableau when giving presentations
  • View what fields are being used in a data source
  • Tableau Mobile will have offline capability so you can interact with your visualizations anywhere
  • Certified data sources which will allow IT to certify data sources that have been tested and verified as an accurate and trustworthy data sources

certified
 

New Features with Dashboard Formatting

  • Distribute evenly within a container automatically
  • Add margins around your visualizations that are in dashboards
  • More text formatting options in Dashboard text boxes

Improved Interaction with Visualizations

  • Natural language generation – through the Tableau API you can add the power of natural language searches using a NLG provider
  • Hyper is a new in-memory data engine which will improve performance

Project Maestro

  • Project Maestro provides a new visual way of data preparation
  • Fix your data on the fly

maestro

Visualization Updates

  • Map scales will now appear on maps
  • Ability to align time series data
  • Ability to automatically change the level of detail when you zoom in on maps. If you are looking at the United States and the detail is at the state level, then you zoom in to a particular state, the detail level will change to be city level.
  • Improved map layering which allows you to layer different sets of data on a single map.

map-layering
Hopefully this provides a nice teaser to what is to come for Tableau and gets you excited about how these new improvements can help you provide better data insight for your organization.

Everything You Need to Know About Tableau 10

This week, Tableau released Tableau 10 – the latest version of their business intelligence software. Here’s everything you need to know about Tableau 10.

Web Authoring in Tableau 10Our Favorite New Features

There are a lot of new features with Tableau 10, but here are a few of our favorites. Read our blog post on our top 5 favorite features.

  • Workbook Formatting – you can now set this on the workbook level. Now each new worksheet and dashboard will have the styling you want.
  • New Color Palettes – More modern colors that are more friendly for those with color vision deficiencies.
  • Clustering – Automatically groups together similar data points
  • Cross-database joins – Join data from different data sources, like SQL Server and Oracle. Publish the integrated data source to Tableau Online or Tableau Server to collaborate with others.
  • Mobile – Tableau 10 is mobile friendly and it’s easy to set default layouts for different devices
  • Revision History – The new revision history feature allows you to restore or download previous versions of workbooks.
  • Web Authoring – Tableau 10 includes a new feature that lets you ask additional questions of your data right on the web

Tableau Upgrade Tips

Upgrade Tableau Server

If you are using Tableau Server, here are a few tips to make sure your upgrade to Tableau 10 goes smoothly.

  1. Always do a backup first
  2. Always check the software product key first. It should not be expired. The key is not required to re-install. Verify license expiration in Dynamics CRM.
  3. Start downloading the file(s) while you are running the clean up and back up. Server is over 1GB and takes a while.
  4. It is required to uninstall the prior version. Configuration settings and content are preserved.
  5. Plan 2-3 hours for a major update.
  6. Using a maintenance script to do a clean and backup in advance will make the process go faster. It is advised to do an additional tsbak before starting.
  7. When upgrading an Active Directory integrated server, the service account password is not required.
  8. Update shortcuts and scripts after the upgrade to point to the new location of tabadmin.

Upgrade Tableau Desktop

  1. Do not uninstall the prior version first. A minor revision (9 to 9.1) will replace a major revision (8 to 9) will install side by side.
  2. Plan 30 minutes for the upgrade.

Demystify Tableau Permissions and Roles

Ever get stuck trying to grant a Tableau user or group permissions to a project, workbook or data source and find yourself going in circles?  In this post, we’ll cover definitions, how different permissions interact with each other, and best practices, so you can configure your environment optimally and set permissions like a champ. If you want to take a step back and access users/groups, check here.

Key Elements

Users = Each individual accessing the system must be setup as a user. This is similar to a user account for logging into your computer.
Groups = A group is a way to organize similar users and grant them identical permissions.
Site Role = This is a system definition that represents the maximum amount of permissions for a user. That means a user defined with a site role of Interactor can never publish content. A highly detailed walk through of each role is available here. In short, in decreasing order of power are: Server Administrator, Site Administrator, Publisher, Interactor, Viewer, Viewer (can publish), Unlicensed (can publish), Unlicensed.
Permissions Rules = These out of the box configurations are available for selection when assigning permissions to content.  Examples are Project Lead and Interactor.  When selected, a default set of capabilities are provided.
Permission Capabilities = This is the most granular level of control over permissions assigned to content. For example, if the Publisher permission rule is applied, you grant a user project access to view and save. However, you can override any of them (although in this case you likely wouldn’t want to do that).
In this image, the user “Harry” is assigned the Publisher permission rule for the project and has the capabilities to view and save the project. The third icon is for a project leader.
permissions rule
Recapping the above definitions, a user is assigned a site role, and a user or group is assigned permission rules to THE CONTENT with each permission rule being customizable to the permission capability. Remembering that distinction of assigning permission rules to the content (not users) will help wrap your head around implementation of permissions.

Helpful Tips for Configuring Tableau Environments

Following are some helpful tips and best practices when configuring your association’s Tableau environment.

  • Always assign a user to a group. Always set permissions to projects, views, and data sources using a group.
  • Never assign a user directly to a project, view or data source. Assign a group instead.
  • Think about applying permissions from the perspective of allowing instead of denying. In fact, use None instead of deny everywhere possible. Keep in mind an administrator site role will overrule any deny permission, however it will otherwise overrule any accept or unspecified rule.
  • Using the Project Leader rule on a project supersedes other rules applied to that user/group for that project because it grants administrator type permissions for the project and the content it contains.
  • If you want to use the Project Leader rule, then make sure the group to receive it has the Interactor or Publisher site role. For completeness, the admin site roles work too, but there isn’t an advantage to doing that.
  • Using the Site Administrator role yields that user access to everything within the site which means not other sites or a few system settings that only a Server Administrator can modify.
  • It will be less burdensome to organize your groups by Role (maximum permissions) and not strictly organizational based. That means put all the publishers in a group and don’t worry so much about dividing events and membership. However, if you do need to keep departments separate by their data access, then create multiple groups like Event – Interactors and Membership – Interactors to combine organizational and functional into groups.

Most importantly, don’t publish anything to the Default project. You can’t delete it, but you can leverage its baseline functionality of how it acts as a template. When a new project is created, it copies the permissions from the Default project. So set the All Users group to None on the default as well as configuring all your other standard groups.  Some training is required for your publishers to instruct them not to publish to it.  Lastly, notice when changing to None the save button turns to the phrase Delete, but you’re really just deleting previous permissions and when you click it the result will be a save action.
default_project_permissions
Once you get everything setup for your users, groups, and content, you can set each project permission to be locked. This means that publishers can’t override the original permissions of the project which is otherwise possible when publishing workbooks from Tableau Desktop. It is strongly recommended to use the project locking feature to reduce the administrative burden and provide additional simplicity to your publishers to avoid having unique permissions all over the environment.
Want to more about Tableau? Check out our posts about “Organizing Your Tableau Server” or “Putting Tableau Content in its Place.”