Alteryx and Tableau are a match made in heaven, and for today’s blog, I will showcase just one other beautiful pairing of these. Connecting to Tableau metadata (or more broadly the process of connecting to databases). Before we go through the process, however, I want to start by providing some context.
The Tableau PostgreSQL is responsible for storing metadata regarding the processes of the Tableau Server. While this blog is not aimed to teach you how or why to use this database, it is important to note why you would want to import this data into Alteryx, instead of directly connect to this database within Tableau Desktop.
Reasons to Connect:
Reason 1: Limitations of Storing Data
The Tableau Server metadata contains information such as HTTP requests, and for large servers, this could be millions of records every day. Thus, Web Requests as an example are stored by default for 7 days. There may be instances where we want to keep this data for longer periods of time, but we want the data at an aggregated level to save on rows. Another example might come from the Events data source, maybe we would want to know every time a user accessed a tableau server item, but we do not care to store any of the other event types.
Reason 2: Advanced Data Preparation
The Tableau PostgreSQL Data is quite clean, and within Tableau Desktop you can conduct data preparation and transformation, but Alteryx gives you so many more capabilities. For instance, I do like to include densification with some of my visualizations, and this can be conducted very easily using a multi-row tool, but quite difficult within Tableau. As mentioned before, aggregations may also be all you need, and thus this will significantly improve the performance of the Tableau dashboard.
Reason 3: Think Bigger
Predictive Modelling is quite a buzz word concept, but you could do something with the metadata and this application. You may even want to consider doing some time-series analysis. You may even want to join this data with other reports that you have, which would be much more difficult without Alteryx.
Now that I have reminded you of how amazing Alteryx is, and you have started thinking about how you can use your metadata, here are the steps to connect to the PostgreSQL in Alteryx (for Windows).
Process of Connecting to Tableau PostgreSQL:
Step 1: Know the credentials
To access the Tableau Server Metadata, you will need to know the server name, the port number (8060), the database (workgroup unless otherwise changed), and the authentication (usually readonly and password). This will be important both in the input tool and your ODBC connection.
Step 2: Open & Configure ODBC Data Source Administrator
Within the windows search bar, search ODBC, and there should be an app named ODBC Data Source Administrator. In this app, you will need to add a new connection to the PostgreSQL database. To do this, select add within User DSN, select the PostgreSQL Unicode driver, then input and save all of the credentials in the pop-up window.
Step 3: Connect to Database Via Alteryx
After Alteryx has loaded, drag an input tool onto the canvas. In the configuration winder, select the option “data sources” and select PostgreSQL ODBC, You will be prompted by a window that requires you to select the name of the ODBC you saved in step 2, and type the credentials. You should now have set up a connection with the Tableau PostgreSQL database and Alteryx. You can save this connection, or re-use it quickly by going into your recent connections.
Step 4: Choose Table or Specific Query
After step 3, you will be prompted to select which table(s) you want to access from the PostgreSQL database. Usually in this step, I turn to the tab named SQL Editor and write a simple query such as:
SELECT * FROM “public”.”views”.
You may also write more complicated SQL queries if you wish, but they won’t be necessary.
The Tableau Data Dictionary would be the place where you can learn more about this metadata database.
There we have it, 4 simple steps to connect to Tableau Server metadata in Alteryx. If you want to learn more options in connecting to Tableau metadata, I will be continuing this conversation in Part 2, where I will be connecting to the Tableau REST API, and giving you some further examples of how the metadata can be used.