Tableau Automation with Python: A Comprehensive Guide 101

Visual Analytics is the union of Data Analytics and Visualisations. This problem-solving approach is concerned with effectively facilitating high-level, complex activities such as reasoning and data-driven decision-making by integrating interactive visual representations with underlying analytical processes. Tableau is a Visual Analytics Engine that simplifies the creation of interactive visual analytics through dashboards. These dashboards facilitate data conversion into intelligible, interactive visualizations for non-technical analysts and end-users.

In this article, you will gain information about Tableau Automation with Python. You will also gain a holistic understanding of Tableau Python, and their key features, adding Python Scripts in Tableau using Python & TabPy & automating Tableau with Python & Tabcmd. Read along to find out in-depth information on how you can automate Tableau dashboard with Python

Adding Python Scripts in Tableau: Python & TabPy

To incorporate Python scripts into your flow, you must first establish a connection between Tableau and a TabPy server. Then, using a pandas data frame, you can use Python scripts to apply supported functions to data from your flow.

The steps to be carried out are as follows:

• Tableau Automation: Configure the Tableau Python (TabPy) server for Tableau Server
• Tableau Automation: Create your Python script
• Tableau Automation: Connect to your Tableau Python (TabPy) server
• Tableau Automation: Add a script to your flow

1) Configure the Tableau Python (TabPy) server for Tableau Server

To publish, create, edit, and run flows with script steps in Tableau Server, you’ll need to connect your TabPy server to Tableau Server.

• Step 1: Navigate to the TSM command line/shell.
• Step 2: To set the host address, port values, and connect timeout, you can use the following commands:
• tsm security maestro-tabpy-ssl enable --connection-type {maestro-tabpy-secure/maestro-tabpy} --tabpy-host <TabPy IP address or host name> --tabpy-port <TabPy port> --tabpy-username <TabPy username> --tabpy-password <TabPy password> --tabpy-connect-timeout-ms <TabPy connect timeout>
  • Choose {maestro-tabpy-secure} for a secure connection or {maestro-tabpy} for an unsecured connection.
  • If you choose {maestro-tabpy-secure}, specify the certificate file in the command line with -cf<certificate file path>.
  • Set the –tabpy-connect-timeout-ms <TabPy connect timeout> in milliseconds. For example –tabpy-connect-timeout-ms 800000.
• Step 3: Enter the command tsm security maestro-tabpy-ssl disable to disable the TabPy connection.

While configuring a connection between your TabPy server and Tableau Server, remember:

• Version 2019.3 and later: Run published flows, including Tableau Server script steps.
• Version 2020.4.1 and later: Create, edit, and run flows along with script steps in Tableau Server.
• Tableau Cloud: Currently, this does not support creating or running flows with script steps. 

The following data types are supported:

2) Create your Python script

When writing your script, include a function that takes pandas, pd.DataFrame as an argument. This will fetch data from Tableau Prep Builder. You must also return the results in pandas, pd.DataFrame with supported data types.

To add encoding to a set of fields in a flow, for example, you could write the following script:

def encode(input):     
  le = preprocessing.LabelEncoder()
  Return pd.DataFrame({
    'Opportunity Number' : input['Opportunity Number'],
    'Supplies Subgroup Encoded' : le.fit_transform(input['Supplies Subgroup']),
    'Region Encoded' : le.fit_transform(input['Region']),
    'Route To Market Encoded' : le.fit_transform(input['Route To Market']),
    'Opportunity Result Encoded' : le.fit_transform(input['Opportunity Result']),
    'Competitor Type Encoded' : le.fit_transform(input['Competitor Type']),
    'Supplies Group Encoded' : le.fit_transform(input['Supplies Group']),
})

Note: Date and DateTime must always be returned as a valid string.

If you want to return a different set of fields than what you entered, you must include a get_output_schema function in your script that defines the output and data types. Otherwise, the fields from the input data will be used in the output, which is taken from the step prior to the script step in the flow.

The get_output_schema function was added to the field encoding python script in the following example:

def get_output_schema():       
  return pd.DataFrame({
    'Opportunity Number' : prep_int(),
    'Supplies Subgroup Encoded' : prep_int(),
    'Region Encoded' : prep_int(),
    'Route To Market Encoded' : prep_int (),
    'Opportunity Result Encoded' : prep_int (),
    'Competitor Type Encoded' : prep_int()
    'Supplies Group Encoded' : prep_int()
})

3) Connect to your Tableau Python (TabPy) server

The steps to be carried out to connect Tableau with TabPy Server are as follows:

• Click on the “Help” option. Then, select the “Settings and Performance” option. Now, select the “Manage Analytics Extension Connection” option.
• Select “Tableau Python (TabPy) Server” from the “Select an Analytics Extension” drop-down list.

• Enter the following credentials:
  • The default port for TabPy is 9004.
  • Enter a username and password if the server requires them.
  • Select the Require SSL check box, then click the Custom configuration file. Link to specify a certificate for the connection if the server uses SSL encryption.

4) Add a script to your flow

Start your TabPy server then complete the following steps:

• Step 1: Open the Tableau Prep Builder. Now, click the “Add connection“ button.
• Step 2: In web authoring, from the Home page, click the “Create” option. Then select the “Flow” option. Or, from the Explore page, click the “New” option. Then, select the “Flow” option. Then click on the “Connect to Data” option.
• Step 3: Choose the file type or server that hosts your data from the list of connectors. Enter the information required to sign in and access your data if prompted.
• Step 4: Select “Add Script” from the context menu after clicking the plus icon.

• Step 5: Select “Tableau Python (TabPy) Server” in the Connection type section of the Script pane.

• Step 6: To select your script file, click the “Browse” option in the File Name section.
• Step 7: To run your script, enter the “Function Name” and press the Enter key.

Automating Analytics in Tableau: Python & Tabcmd

For automating Analytics in Tableau, the steps to be carried out are as follows:

• Tableau Automation: Authentication
• Tableau Automation: Fetch & Download a Workbook
• Tableau Automation: Apply a Filter to a View & Download as a File
• Tableau Automation: Data Sources & Connections

1) Authentication

> tabcmd login -s http://172.16.22.2/ -t my_website -u abcxyz@gmail.com -p 123

Each credential parameter is passed as a flag to the shell.

The output would be the same as given below:

===== redirecting to http://172.16.22.2/auth
===== Signed out
===== Creating new session
=====     Server:   http://172.16.22.2/
=====     Username: abcxyz@gmail.com
=====     Site:     my_website
===== Connecting to the server...
===== Signing in...
===== Succeeded

Note: In the event of a failed authentication in either route, you can consult with a Tableau Server administrator about your user privileges/user authorization.

2) Fetch & Download a Workbook

Note: The workbook ‘Superstore‘ is available as a sample in all Tableau distributions. It will be the default workbook in the following code snippet demonstrations.

You can find and download the workbook as a .twbx file using the following code in tabcmd.

tabcmd get “/workbooks/Superstore.twbx”

3) Apply a Filter to a View & Download as a File

Within the workbook, you can query a specific view and apply a filter to it.

In the process, you make a view request with filters as parameters. The API processes the request against the view and returns a snapshot to the requesting client.

Before Filter (with all regions):

After Filter for (Region = West):

The export command accepts the URL of the specified view as well as optional download parameters.

tabcmd export -t my_site "Superstore/Overview?refresh=yes&Region=West" --pdf --pagesize a4 --pagelayout landscape -f "Overview.pdf"

The view can be exported as a PNG or CSV too.

The URL on the browser would be for the same command as given below:

http://172.16.22.2/#/site/my_website/views/Superstore/Overview?Region=West

4) Data Sources & Connections

The data source object, like the workbook or view objects, can be filtered or paginated.

tabcmd refreshextracts --datasource sales_ds

Limitations of Tableau Automation with Python using Manual Method

• Requires programming expertise: Utilizing Python effectively for Tableau automation necessitates knowledge of Python libraries like tabpy or custom scripting. This can be a barrier for non-technical users.
• Limited functionality: Basic data transformations can be carried out in Tableau but advanced manipulations might require additional tools or manual intervention.

Use Cases of Tableau Automation with Python:

• Real-Time Data Analysis: By Tableau automation with Python, you can send commands to an external service through Tableau Prep Builder. This enables you to perform actions like adding row numbers, ranking fields, filling down fields, and performing other cleaning operations that you might otherwise do using calculated fields.
• Business Intelligence: You can automate Tableau dashboard using Python to prepare interactive dashboards and visualizations to help you in business decision-making. 
• Advanced Data Transformations: Using Python in Tableau lets you perform complex calculations, create custom aggregations, and manipulate data beyond Tableau’s built-in functions using Python libraries like NumPy and Pandas.
• Machine Learning: Tableau Python examples enable you to create custom predictive models, score new data with existing models, and Feature engineering for improved model performance. 

Before wrapping up, let us take a look at the basics of Tableau and Python.

What is Tableau?

Tableau is a modern Data Analytics and Business Intelligence platform. It is an easy-to-use tool, hence, it offers a smooth experience to its users. Some of Its amazing features include real-time analytics, quick responsiveness, and interactive dashboards. 

It also offers simple yet appealing graphics/visualizations that you can use to present your data pictorially. It comes with all the features needed for data extraction, data processing, and generating reports and dashboards. 

It also offers a drag-and-drop functionality that makes it faster than other BI tools. It is also a very scalable tool, which gives it the capability to adapt to both individual and enterprise needs. You can also connect it to multiple data sources without the need to purchase a license. It is mobile compatible and it comes with an online version. 

It can be used by all kinds of users and no specific skill or knowledge is needed to work with the tool. Users from any department in your company can use it for Data Analysis and Data Visualization. 

Key Features of Tableau

Tableau is a powerful tool and is widely used by a lot of industries. To understand Tableau better let’s look at some of its key features:

• Supports Multiple Data Sources
• Houses a Wide Range of Visualizations
• Allows Data Filtering
• Dynamic and Real-time Dashboards
• Powerful Collaboration

1) Supports Multiple Data Sources

Since every task is performed on data in Tableau, it allows you to integrate your data from a large variety of data sources like:

• Microsoft Excel
• CSV files
• MS SQL Server
• Oracle
• IBM DB2
• Google BigQuery
• Windows Azure
• ODBC/JDBC, etc

You can make use of these integrations to stream your data to Tableau and analyze it seamlessly.

2) Houses a Wide Range of Visualizations

Tableau also provides a large number of simple tools for its users (both for technical and non-technical people) and empowers them to create different types of visualizations using their data. You can use these tools and create simple or complex visualizations using Tableau. Its key visualizations include:

• Scatter plot
• Line plot
• Pie Chart 
• Bar Chart 
• Bullet Chart
• Highlight Tables
• Gantt Chart
• Boxplot, etc. 

Tableau’s Map features allow you to visualize your data on a geographical map. It is very useful if your data needs to be categorized region-wise or across various countries to help you analyze the performance of each region.

3) Allows Data Filtering

With the help of Tableau, you can filter data from a single source or multiple sources. But the only condition that needs to be satisfied for filtering data from multiple Data Sources is that the data must have the same dimensions. Once this is satisfied, Tableau automatically updates the required changes to all your worksheets using the same Data Sources and the same filters that you set previously.

4) Dynamic and Real-time Dashboards

You can build dynamic and interactive Dashboards using Tableau. Building Reports and Dashboards is made very simple by Tableau. You can make them more informative by adding colorful charts and diagrams. Using these real-time Dashboards, you can monitor everything in absolute depth for your organization. Tableau also houses a feature that allows you to share your Dashboards and Reports with other employees in the organization.

5) Powerful Collaboration

Every person can work a lot more efficiently if they understand their data and make informed decisions which are critical to success in any organization. Tableau was initially built to enable collaboration among employees. Using Tableau, all the members in a team can share their work, make follow-up queries with fellow peers, and share visualizations with any employee in the organization, allowing them to gain valuable insights easily.

Tableau gives its users the ability to work and understand the data they need from web editing and authoring to Data Source recommendations. You can easily publish your Dashboard to Tableau Server or Tableau Online within seconds and as a result, everyone in your organization can see your insights, ask various questions, and make the right decisions.

What is Python?

Python is a versatile general-purpose Programming Language. Its small learning curve coupled with its robustness has made it one of the most popular Programming Languages ​​today. It is the go-to choice of developers for Website and Software Development, Automation, Data Analysis, Data Visualization, and much more. Moreover, its straightforward syntax allows Accountants, Scientists to utilize it for daily tasks. The Python Programming Language serves as the key integral tool in the field of Data Science for performing complex Statistical Calculations, creating Machine Learning Algorithms, etc. 

Python Programming Language is also renowned for its ability to generate a variety of Data Visualizations like Bar Charts, Column Charts, Pie Charts,  and 3D Charts. Furthermore, it offers a rich set of libraries that facilitates advanced Machine Learning programs in a faster and simpler manner. 

Key Features of Python

The following features are responsible for Python Programming Language’s popularity today:

• Beginner Friendly: The Python Programming Language offers a hassle-free environment for developers. Its straightforward workflow is suitable for everyone and entry-level coders are drawn to it. Moreover, you can use and distribute its open-source codes for commercial purposes free of cost.
• Robust Applications: Its simple syntax operates on natural human-readable language making it the go-to choice of projects on Python Programming Language, which is faster as compared to other Programming Languages. Furthermore, its versatile nature makes it the ideal choice for Web Development and Machine Learning projects.
• Large Communities: Due to Python’s immense popularity, a huge active community of programmers is available online that contributes to this language’s modules and libraries. Moreover, this vast support community is ready to help in case you or any other coder gets stuck in a programming issue. You can easily get suggestions and solutions by posting your issue on these community pages.

Conclusion

In this article, you have learned about Tableau Automation with Python. This article also provided information on Tableau, Python, their key features, adding Python Scripts in Tableau using Python & TabPy & Tableau Automation using Python & Tabcmd. It also gives information about effective use cases of how to automate reports in tableau. 

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding Tableau Automation with Python in the comment section below! We would love to hear your thoughts.