How to analyse the Tableau Server database

The following post is a guest post by Mark Peck – Database Administrator and Tableau Jedi from HiFX.  Mark has kindly taken the time to explain his recent journey taken to understand the database behind Tableau server.

Many thanks Mark.

With Tableau being so good at providing mechanisms for users to analyse, report, extract and present data, we have quickly built a very large repository of workbooks and views.  Over time, we have refined many of the earlier reports and visualisations to make them better as our experience and knowledge have grown.  This has presented us with a challenge of how to manage all those workbooks and views – especially when the business has undertaken a period of rapid change and users have moved departments and roles with increasing frequency for a period of time.

So, users began to log calls like “I’d like to access the reports that David has.”  And, we as admins, would like to remove older and no-longer accessed workbooks, etc.

Whilst the out-of-the-box management views are great, they are limited in functionality and didn’t provide the exact information we required in a format that suited us.

So, the first thing we learnt courtesy of Tom was that Tableau Server has its own database and we could connect to the Tableau Server database and potentially get the information we required!  It’s essentially a centralised catalogue of the workbooks, views, users, projects and a myriad of other settings for Tableau Server held in a Postgres database.  By default, you cannot connect Tableau Desktop to the Tableau Server database  without first enabling it for external connections.  Tableau provide the documentation on how to do this.

What follows is what I have found as I worked my way through the documentation and then sought to extend the ability of what could be done.  As a DBA of some years experience I have tried to compile this for a wide audience of differing levels of ability rather than assume it’ll only be read by an “expert”.  So, to the more experienced DBA or technical specialist I apologise if some of the information is easy and all too familiar.  And, to the database novice, I hope it’s clear and concise.

Enabling the Tableau Server Database for External Connections

You can find out how to enable the database for external connections by looking at page 137 of the server admin guide or, by searching for “Creating Your own Administrative Views”.  You must do this first and I strongly advise working through each step carefully and getting the basic set-up working first before attempting to do anything more advanced.

Download the Admin guide here:

http://downloads.tableausoftware.com/quickstart/server-guides/en-us/server_admin7.0.pdf

Download the Postgres Database Drivers from here:

http://www.tableausoftware.com/support/drivers

The Postgres ODBC Driver and its Set-up

Here are a few tips not mentioned in the manual for setting-up the Tableau Server DSN (Data Source Name) using the Postgres ODBC (Open Database Connectivity) Driver available from the Tableau website.   The first is that 2 drivers will be installed.  One is the “PostgresSQL ANSI” driver.  The other is the “PostgresSQL Unicode” Driver.  I opted to use the “Postgres ANSI” driver without any problems.

64-Bit Windows Note:

The driver installation will work on 64-Windows.  If you are installing onto a Windows 64-bit installation then you will not see the drivers listed in the normal ODBC dialog accessible from the windows menus/control panel once the installation is complete.  This is important if you intend to extract the data using a 64-Windows installation of products like SSIS (SQL Server Integration Services)  and will need to set-up the Tableau Server connection.  In order to see and set-up an ODBC connection on a 64-Bit Windows machine, you will need to run (Start -> Run):

C:WINDOWSSysWOW64odbcad32.exe 

This will provide you with the 32-bit version of the ODBC interface to set-up the driver as with a normal 32-bit version of Windows.  Please note that this will mean you have to run your SSIS package(s) in 32-bit mode or they will not work properly, esp. if you intend to schedule data extraction via SQL Server Agent, etc.

ODBC Dialog showing the Postgres ODBC Drivers once they have been installed.

The following graphics show some specific settings I have found useful when configuring the Postgres driver.  Applies to both 32-bit and 64-bit installations.  A “System” DSN as that means the connection is available to users on the windows desktop/server where you are installing it.  A “User” DSN then its only available to the currently logged-on user account.  Which you chose will depend on what level of security you would like around your DSN and how you intend it to be used.  Opt to create the DSN of your choice using the “ANSI” driver and you will be presented with a simple dialog.

Showing the basic settings for the Postgres ODBC driver.

Please note that you should use your Tableau Server computer name in the “Server” field.  The other values are detailed in the Admin manual and are dependant on how your system is set-up.

Click the “Datasource” button and you will be presented with an additional dialog with many more options as follows.

Showing “Page 2” and “Read Only” tick-box option.

Click the “Page 2” button and tick the option “Read Only”.

Once you have set the “Read Only” option click the OK button to return to first dialog.

You can then click on the “Test” button.  If the test reports that the connection is successful you should be able to click OK to clear  the message and then click the “Save” button to save the DSN (Data Source Name).  If the test fails then you should check your connection settings and confirm the Tableau Server is running and the steps taken to enable the database for external connections have been performed correctly.

Once your DSN passes the test, it can be saved and you are now set-up.

Extending Your Ability

Having set-up our DSN we quickly connected to the database and started working with the “_views” which the manual details we can connect to.  However, whilst useful it didn’t provide everything I would have liked to have accessed.  I searched the Tableau Support and Knowledge base to find that Tableau invite ideas for further development of the administrative views, but realised they would not come quickly.  One discussion highlighted that the Tableau Server database is just a Postgres database and any DBA could, in theory, create their own views in it.  Great I thought.  I am a DBA, but don’t know much about Postgres.  How do I do it?

The first thing was to find and download a management interface for the database.  I found this one and it’s been easy to set-up and use.  Please note that being an “Open-Source” system that I not specifically endorsing any particular product, rather this is what I found and have successfully worked with.  It’s called:  pgpAdmin III.  You can download it for free from here:

http://www.pgadmin.org/download/windows.php

Its a simple installation with minimal options.  Once installed it has to be configured to connect to the database.  This means installing it onto a client that has been enabled for connection to the Tableau Server database – if you install onto a different computer then you’ll need to update the server connection information to include this new client as per instructions in the Admin guide.

DISCLAIMER

If you opt to use the “tblwgadmin” user account as detailed below then I would heartily recommend that you backup your Tableau Server FIRST.  With this user account you can break the database and, therefore, your Tableau installation.  Tableau Support will not be able to help you very much, if at all, if you do this.  If you are not confident working with databases and are not a DBA then ask your DBA for assistance.  Consider carefully whether you actually want to connect to the raw data tables using Tableau Desktop.  And, esp. when using products like SSIS that have a potential to change a database.  Personally, I opted to create views to which I granted the SELECT permissions to the “tableau” user.  This extended the “tableau” user account ability without risk of breaking the database for Tableau Server.  It’s a bit more work, but as this is not a supported way of achieving our goal of extending the administration views, we have to be careful.

It’s also probably wise to consider, from a security aspect, whether its worth enabling just one server to perform regular extracts of the data into a data warehouse and then have the Tableau users connect to the warehouse as normal.  Or, even a specific database that is not the Tableau Server database.  Please bear in mind there are details of various settings in the database tables that you might not to be accessible to general users or made too public.

Also consider documenting any changes you make to the Tableau Server database e.g. new views you have created or new user accounts, etc.  The more adventurous you are with administering and changing the database, the more this becomes important.  Its quite possible with future upgrades and changes that you may need to remove/change your customisations in order to keep things running smoothly.  If you have not documented the changes properly and how you did them then performing an “undo” becomes that much harder if not impossible.  Consider that you might not be working for the company several years from now and someone else might be left with the task of sorting out a problem.  Without good documentation it becomes much harder; it shouldn’t take long to do and could save a lot of time and money in the future.  After all, we only wanted to extend our ability to read the Tableau Server DB until such time as Tableau can extend the admin views themselves.  Our intention should not be to try and break the database or the system that depends on it.

For this reason my strongest advice is to keep any changes to an absolute minimum.  The less you change the less likely you are to run into problems in the future, the less you have to document and the less you are likely to have to “unwind” anything in the event of a problem.

If you find certain views and changes really useful then let the Tableau Support team and community know; its by giving feedback to them that they have a chance to extend the product and make it better for us, the users and admins alike, so we can reduce our reliance on workarounds like this.

Once you start pgpAdmin  you will need to add a sever/connection.  You will be provided a dialog like the one below.

The Connection Properties Dialog

Note that you need to provide the IP Address of the Tableau Server – if you’re not sure what that is then ask your network administrator or DBA.  Also note that we’re connecting to the “workgroup” database as detailed in the Admin guide.  If you use the “tableau” user as shown in this picture then you’re connection will be limited in what it can do.

You can also opt to use the “tblwgadmin” user – it doesn’t have a password (so no need to store or enter a password) and has full access rights on the database.  This is the account to use if you want to create new views and/or be able to access the tables which you cannot normally access.  However, please take heed of the disclaimer above!

Also note that on the SSL tab I opted the “disable” option and nothing to set on the Advanced tab.

Once you have connected to your database, you can expand it and under the “public” schema you can then access the tables, views, etc.  Note that the interface works very much like Windows Explorer when dealing with files on your computer.  If in doubt, “right click” – pop-up menus will appear in context of the context of the object or container that you right-clicked on.

If you’re not familiar with writing SQL then you can actually script views for the tables you want very easily as follows:

1) Expand the Tables list

2) Right click on a table you wish to create a view for and get a pop-up menu

3) Select the Scripts -> SELECT Script option – a new window will open

4) Highlight and (CTRL + C or right-click and Copy) copy the SQL in the SQL Editor pane

5) Close the newly opened window – it returns you back to the table list

6) Right click on the Views object and Select New View….

7) Give your view a name – no spaces or special characters except an underscore – keep it simple and obvious e.g. if table “permissions” then call it “_permissions”

8) Set the owner as “tableau” from the list

9) Click “Definition” and paste the query into the window

10) Click “Privileges” and click SELECT option (tick it) with the Role set to “public” and then click “Add/Change” button

11) Click OK and the view will be created – and should be available to the “tableau” user

Hopefully, armed with this information you can then write the queries and extract the information you need from the Tableau Server database.

Last Notes on SSIS

If working with SSIS then I found that using Tableau to select “single table” or the relevant “view” and then click Custom SQL, I could use that SQL successfully in SSIS.  Basically, because it’s a 32-ODBC connection running on a 64-but server I had to use an ADO.NET source connection based on it and it’s a little awkward at first, but do-able.  The SSIS designer could error if I wanted to use some of the GUI-based features/database explorer, but Tableau provides SQL that is fully compatible and works great.

Tom Brown

London, UK