Skip to content

atlas-bi/Tableau-Metadata-Exporter

Repository files navigation

Tableau Metadata Exporter

Atlas BI Library ETL | Atlas Supplementary Tableau ETL

WebsiteDemoDocumentationChat

maintainability security discord chat latest release

A Python script pulls and parses XML data from PSQL readonly Tableau database. This data, along with data pulled from Tableau's database, is inserted into a SQL database and is able to be run daily and inserted into Atlas.

🔧 How Does it Work?

  1. Logs in to the Tableau server via SSH and connects to the Tableau PSQL Admin account in the workgroup database
  2. The query will create .twbx and .twb files containing SQL queries related to the workbooks stored in Tableau
  3. The files retrieved are placed in the TwbxFiles folder and any zipped folders are unzipped
  4. All XML files are parsed and the server, database name, and SQL queries are pulled from each along with the the respective workbook
  5. Connection to SQL database is established and all tables are truncated so as not to duplicate data
  6. All PSQL queries are run and data pulled is stored in SQl tables

🏃 Getting Started

  • Get the code git clone git@github.com:atlas-bi/Tableau-Metadata-Exporter.git
  • Create the Atlas Staging database tables. Run the create_tables.sql script in your atlas_staging database
  • Install the project depenedencies poetry install, or pip install the dependencies listed in the pyproject.toml file
  • Set parameters (see below)
  • Run poetry run python main.py, or python main.py

⚙️Parameters

The python script accepts environment variables. This is the recommended way to run the script. You can also create a .env file to hold the parameters.

# Tableau's psql admin account. Documentation on Tabeau's website for getting the admin password.
PSQLADMIN=postgres://tblwgadmin:password@localhost:8060/workgroup
# Tableau's psql ro account
PSQLRO=postgres://readonly:password@hostname:8060/workgroup
# Atlas Staging database connection
SQLSERVER=DRIVER={ODBC Driver 17 for SQL Server};SERVER=sqlServer;DATABASE=atlas_staging;UID=joe;PWD=12345
# SSH Connection to Tableau Server. Old servers will need to install OpenSSH
SSHHOST=tableauServer
SSHUSERNAME=mr_cool
SSHPASSWORD=12345
# Url to Tableau
TABLEAUURL=https://tableau.example.com

Here's a few steps to get the Tableau PSQL admin and readonly passwords.

Navigate to the Tableau server and open command prompt.

cd C:\Program Files\Tableau\Tableau Server\packages\pgsql.<version>\bin

#to retrieve the Tableau admin password
tsm configuration get -k pgsql.adminpassword

#to retrieve the readonly password
tsm configuration get -k pgsql.readonly_password

📝 Notes

Some of the PSQL queries that are run look at user and group data. To access these tables, try this guide. The user data is mostly found in the ts_users data source.

For a list of nearly all Tableau tables along with columns and descriptions, try this guide.