How to explore Tableau Server Connections with Python

In this article, we'll explore a Python script that utilizes the tableauserverclient library to sign in to Tableau Server/Tableau Online using a Personal Access Token (PAT) and retrieve information about workbook and datasource connections.

Python Code Screenshot

Exploring Tableau Server/Tableau Online connections can be useful for:

• Security Auditing
• Content Maintenance
• Dependency Analysis
• Resource Management

LLM and programmation

LLM serves as a valuable aid for leveraging Tableau programmatically. While it offers a solid starting point, I frequently find myself delving into the Tableau documentation and modifying code to ensure it functions as intended. In contrast, the script I’m sharing is ready for use without requiring any adjustments.

I am confident LLMs will be there someday soon (or already are for some more advanced models). Meanwhile, I am please to offer additional training content!

Setting Up the Environment

Feel free to write your Python code in the notebook environment of your choice. Personally, I prefer using Google Colab for its simplicity.

https://colab.research.google.com/

Before delving into the script, it’s crucial to have the necessary libraries installed. You can install them using the following commands:

pip install tableauserverclient pandas

Understanding the Script

Libraries

The script starts by importing the necessary libraries, tableauserverclient as TSC, and pandas as pd.

import tableauserverclient as TSC
import pandas as pd

Sign in

The sign_in function we create takes three parameters – token_name, token_value, and tableau_site – to authenticate and sign in to Tableau Server using a Personal Access Token. We return a server object to play with if necessary.

def sign_in(token_name: str, token_value: str, tableau_site: str) -> None:
    tableau_auth = TSC.PersonalAccessTokenAuth(token_name, token_value, tableau_site)
    server = TSC.Server('https://dub01.online.tableau.com')
    server.auth.sign_in(tableau_auth)

    return server

You can create and manage your Personal Access Token in the “My Account Settings” section on Tableau Server/Tableau Online.

Get Workbook Connections

The get_workbooks_connections function we create retrieves information about workbook connections. It uses the provided server connection and optional parameters project_name and workbook_name to filter the list of workbooks.

The script extracts relevant details such as project name, workbook name, datasource name, connection type, and connection username from the workbooks and their connections. I do not need more for my use case.

The extracted information is organized into a Pandas DataFrame, making it convenient for further analysis.

def get_workbooks_connections(server, project_name=None, workbook_name=None) -> pd.DataFrame:
    # Retrieve all workbooks from the server
    all_workbooks = list(TSC.Pager(server.workbooks))
    
    # Filter the list of workbooks based on project_name and workbook_name
    workbooks = list(filter(lambda workbook: (
        (workbook.project_name == project_name or not project_name) and 
        (workbook.name == workbook_name or not workbook_name)
    ), all_workbooks))

    # Extract workbook connection details
    list_of_workbooks = [
        [workbook.project_name, workbook.name, connection.datasource_name, connection.connection_type, connection.username]
        for workbook in workbooks
        for connection in (server.workbooks.populate_connections(workbook) or workbook.connections)
    ]

    # Create a Pandas DataFrame
    data = pd.DataFrame(list_of_workbooks, columns=['project_name', 'workbook_name', 'datasource_name', 'connection_type', 'connection_username'])

    return data

The script concludes by signing in to Tableau Server and calling the get_workbooks_connections function with a specific project name (optional).

server = sign_in('token_name','token_value','site')
get_workbooks_connections(server,project_name='default')

Script and variant

Here is the full python script:

import tableauserverclient as TSC
import pandas as pd

# Function to sign in to Tableau Server using PAT (Personal Access Token)
def sign_in(token_name: str, token_value: str, tableau_site: str) -> None:
    tableau_auth = TSC.PersonalAccessTokenAuth(token_name, token_value, tableau_site)
    server = TSC.Server('https://dub01.online.tableau.com')
    server.auth.sign_in(tableau_auth)

    return server

# Function to get workbook connections information
def get_workbooks_connections(server, project_name=None, workbook_name=None) -> pd.DataFrame:
    # Retrieve all workbooks from the server
    all_workbooks = list(TSC.Pager(server.workbooks))
    
    # Filter the list of workbooks based on project_name and workbook_name
    workbooks = list(filter(lambda workbook: (
        (workbook.project_name == project_name or not project_name) and 
        (workbook.name == workbook_name or not workbook_name)
    ), all_workbooks))

    # Extract workbook connection details
    list_of_workbooks = [
        [workbook.project_name, workbook.name, connection.datasource_name, connection.connection_type, connection.username]
        for workbook in workbooks
        for connection in (server.workbooks.populate_connections(workbook) or workbook.connections)
    ]

    # Create a Pandas DataFrame
    data = pd.DataFrame(list_of_workbooks, columns=['project_name', 'workbook_name', 'datasource_name', 'connection_type', 'connection_username'])

    return data


server = sign_in('token_name','token_value','site')
get_workbooks_connections(server,project_name='default')

Here’s an alternative version of the get_workbooks_connections function tailored for standalone datasources:

def get_datasources_connections(server, project_name=None, datasource_name=None) -> pd.DataFrame:
    # Retrieve all datasources from the server
    all_datasources = list(TSC.Pager(server.datasources))

    # Filter the list of datasources based on project_name and datasource_name
    datasources = list(filter(lambda datasource: (
        (datasource.project_name == project_name or not project_name) and
        (datasource.name == datasource_name or not datasource_name)
    ), all_datasources))

    # Extract datasource connection details
    list_of_datasources = [
        [datasource.project_name, datasource.name, connection.datasource_name, connection.connection_type, connection.username]
        for datasource in datasources
        for connection in (server.datasources.populate_connections(datasource) or datasource.connections)
    ]
    
    # Create a Pandas DataFrame
    data = pd.DataFrame(list_of_datasources, columns=['project_name', 'datasource_name', 'datasource_name', 'connection_type', 'connection_username'])

    return data

Going further

In an upcoming article, we’ll explore the process of bulk-changing credentials for both workbook and datasource connections.