<< Back

Exploring the Tableau Server database

It’s great to see more businesses investing not only in Tableau Desktop but in Tableau server as well these days, and with this we’re getting asked a lot of questions by enterprise IT folks about how to monitor and get ‘under the hood’ of the Tableau Server. Well, most Tableau Server admins can tell you that there are a set of administrative dashboards in the admin panel that provide information on server activity, user activity, background processes and more. The data source for these dashboards is, of course, the database that underlies the Tableau Server, otherwise known as the repository.

And you can in fact gain unfettered access to it to build your own administrative views. Readers who have been following our blog will remember that we had a guest blogger write about this very topic some time ago, but I think we’re due an update as some things have changed since then.

Gaining access

The repository is essentially a Postgres database, and there are two default users that we can use to gain access initially, ‘tableau’ (standard user with limited permissions) and ‘tblwgadmin’ (super user). For now, let’s ignore the super user, we don’t want to go there just yet.

Tableau actually provides great instructions on how to enable the user ‘tableau’ here. By default, ‘tableau’ does not have a password set for it, so access is effectively disabled. To fix this, open an administrator command prompt on your Tableau Server, navigate to your Tableau Server bin directory and issue the tabadmin dbpass command, specifying your chosen password. Then restart your server to let the change take effect. It should go a little something like this…


tabadmindbpass

 

 

Connecting to the database

Now that you have set a password for the ‘tableau’ user and restarted the server, you can connect to the database using your favourite SQL editor (I like RazorSQL), or even Tableau Desktop. To connect using Tableau Desktop from your local machine, you’ll need to have the Postgres database drivers installed – you can find them here.

If you are connecting to the database from Tableau Desktop installed on the machine which hosts Tableau Server itself, then you can skip this step since Tableau Server already comes bundled with the Postgres drivers.

To connect, we use the following details:

Server: (your tableau server hostname, e.g. tableau.intranet.mycompany.com)

Port: 8060 (this is the default port)

Database: workgroup

User: tableau

Password: (the one you just set in the above step)

Exploring the database

Now as I said before, the standard ‘tableau’ user has limited permissions in that it can only access a set of historical tables (prefixed by ‘hist_’) and a set of views (prefixed by an underscore ‘_’) in the database. The views contain information on the structure of your server, e.g. lists of users, groups, sites, projects, dashboards and views, and information relating to those entities.

The historical tables contain information on events occurring on your server, and to get the most out of these you’ll need to join a few together. The two key tables here are historical events (historical_events) – this logs all the events happening on the server – and historical event types (historical_event_types), which describe what these events are. Try using an inner join to join these two tables together to see a list of events on the server.

historicaleventsinnerjoin

 

In addition, you’ll then likely want to bring in user information from the hist_users table so you can tie these events to specific users. In the historical events tables there are actually two IDs you can join the hist_users table to: hist_actor_user_id and hist_target_user_id. The first one, the ‘Actor’ ID, as the name suggests, relates to the user that was responsible for creating, or ‘actioning’ the event. For the most part you’ll only ever want to know the ‘Actor’ user if you’re analysing things like extract refreshes, workbook publishing etc. The ‘Target’ ID only comes into play when the event relates to a user administration activity, for example when an administrator adds a new user, updates a user password, or adds a user to a group. In these types of events, there is both an Actor ID relating to the user who actioned the event, and a Target ID relating to the user that the event impacted.  You could simply join the hist_users table twice, using the Actor ID as the key in one join, and the Target ID in the other join. This would give you more User information for both fields.

histevents&histusers

 

You could also join on hist_projects, hist_groups, hist_workbooks or more, to get more details on events related to these specific entities.

histevents&datasources

 

Remember to LEFT join these tables as not every event will be related to a workbook, a project, or even a user – activities performed by the backgrounder server automatically, like an extract refresh, wouldn’t have an Actor user ID associated with it.

At this point you should be able to build your own set of basic administrative views that show all the various activities taking placed on your Tableau server. For some more reading on the history tables, take a look at Russell Christopher’s excellent blog post on this topic, which includes a diagram of the schema.

Going deeper

While the views and the history tables are a great starting point, you might also notice there are a bunch of other tables in the database that you can’t get access to, and there are some interesting bits in there for helping to monitor and analyse your server set up. For example you may want to get data on permissions, user creation dates (great to see the uptake in server usage over time), or more detailed information on the different data connections in use on the server.

Before we go further, I should highlight: here be dragons. Everything you do from now on is at your own risk, and it’s very likely Tableau will not support you if you mess up the database. I strongly encourage you to test these methods out on a throwaway/development Tableau Server instance and not your production server!

You’ll recall earlier I mentioned there was a super user account on the database, ‘tblwgadmin’. This user has administrator rights on the whole database, and given its power, you can only use it to connect to the database from the Tableau Server machine itself, i.e. localhost. This user doesn’t have a password.

We could just use this super user account to access those additional tables, but what I recommend doing is to use this account to simply grant read only privileges on all tables to the standard Tableau user account. This will give you the ability to read these tables, from your local machine, and will also help prevent you from fat-fingering a drop table or something equally horrendous. To do this, we need to go back to the command prompt (remember to run it as Administrator) on the Tableau Server. Navigate to the bin Postgres folder inside your Tableau Server working directory, e.g.

D:Program FilesTableauTableau Server8.2psqlbin

Run the following command to login to the PostgreSQL database from the command line using the super user:

psql -p8060 -Utblwgadmin workgroup

You should then be presented with the PostgreSQL command line, where you can then enter the following command to grant the permissions:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO tableau;

Then type q and press enter to quit.

psqlgrantselectonall

 

You should now have read-only access from the tableau user to the full list of tables and views in the Tableau Server database. Enjoy, and stay tuned for more posts on what’s in these tables and how best to leverage it!

 

Edit: No need to run all these PSQL commands any more to access all the tables, since 9.0 there is a new built-in user, ‘readonly’, which you can set a password for using tabadmin dbpass and that will do the same thing!

Jonathan MacDonald

London, UK

18 thoughts on “Exploring the Tableau Server database

  1. Hi Jonathon,

    Great article! At Data14 in Seattle I attended a class by Ashley Howard on admin dashboards, you should check out the recording it was very helpful for us. Also the is a Readonly user available with access to all tables in version 8.2.3 mentioned in the session with a viz that talks about the Postgres Schema and joins.

    If you don’t have access to the video session shoot me your email I can send you the files and specifics.

    Best if luck,
    ~Tim

    1. Thanks for your comments, Tim! Glad you enojoyed the post. I have just checked out that recording you mentioned, it looks very interesting indeed. Re the ‘readonly’ user, I see it is only accessible on the server machine itself (localhost) so you need to update the configuration to allow access from a remote machine. Suppose you could approach it either way, but that’s great that Tableau have listened to its users and created a new user with this functionality!

    2. Hi Tim – I am building a set of dashboards using the Tableau PostGRESql Workbook database.I’d be very grateful if you could email me the video on Admin Dashboards. That would be extremely helpful as a reference starting point for me. I’d be very grateful if you could share it on vibhu_passi@cable.comcast.com. I’d love to share the workbook and dashboards I come up with once done. Best, Vic.

  2. Hi Jonathan,

    Is service account user (on the Tableau server) able to get access to the Postgres database?

    Thanks

    Jessie

    1. Hi Jessie, by default the service account user doesn’t have access to the Postgres DB. The three users that can access it are: “tableau” (standard access to history tables and views); “readonly” (read-only access to the entire DB); “tblwgadmin” (full read write access to the DB). That said, you could potentially use the “tblwgadmin” superuser to create an additional user account on the DB with the same username and pw as your Tableau Service run as account, although I’m not sure of the use case here, so I would typically recommend using either the “tableau” or “readonly” user only.

  3. What’s the relationship between the Roles table and System_Users?

    I see there’s an admin_level_integer to denote Admins, but I’m trying to produce a list of users similar to what’s in the Users tab of the Server UI with the intent of providing some filters to narrow down which of our users are in which licensed role and when they’ve logged in last. I’m only seeing Interactor or Unlicensed role names from the users view.. Digging through the tables I’ve found the Roles table which lists all the views, but without spending too much time I wasn’t sure how it relates back to the users table, so I thought maybe you’d know.

    Thanks!

  4. Hi Jonathan
    Congratulations, great article.
    Do you know which tables contain information about performance indicators like memory usage, CPU usage, processor usage?
    Are these database tables enabled to see with the readonly user account?

    Thanks.

    1. Hi Jose, you won’t find this information recorded in the postgres database, but you can get this data using Windows’ in-built performance counters. Both TabMon and TabJolt plug into the windows perfmon apis to collect this data from the tableau server machine. Hope that helps.

  5. Great article Jonathan. Very useful. The one correction I might add is that with Tableau 10.x there is a default password for tblwgadmin. The password in generated randomly at install time and is stored in workbook.yml.

  6. Hi All,
    I tried creating a new user and granted the read only access. I am only able to connect to the db using desktop present in the server but not from the tableau desktop present in my laptop. I am getting the following error :-

    FATAL: no pg_hba.conf entry for host “10.100.141.105”, user “XXX-XXXX”, database “workgroup”, SSL off

    Please help

    1. Hi there, this is quite an old post and you now no longer need to create a new user. There is a user called ‘readonly’ which provides access to all tables. You just need to set a password for it which you can do with a few tabadmin commands.

  7. Yeah, I am already using the ‘readonly’ user for creating admin views. I need to create a new user whose credentials can be given to another team so that they can hit the tableau repository and do their own analytics in another tool.

    1. Hi Aditya,
      Were you able to create a new user other than ‘readonly’ successfully that can be accessed by desktops outside Tableau server?

  8. Thanks for the article. I am using the admin dashboards and created a few of my own. I wanted to take it a little deeper, by capturing which filters the user is using and the values of they are selecting. This would help the developers understand the behavior of the users, so that we may remove certain filters and limit the data we present in the dashboard. We can always ask but to get a understanding of how all users are accessing the information will allow us great insight and provide a better user experience. Which tables would this be captured in. I am on tableau server 10. Any direction or third party tools would be very appreciated.

    1. Great question Sylvia, and it’s one that I am asked about quite often. The short answer is that unfortunately this information does not exist in the Postgres database. It does however, exist in some form in the VizQLServer logs, but requires a fair bit of parsing to be of any use, and I’m not aware of anyone having cracked this yet. The logs you’ll want to look at are all the ‘vizqlserver’ prefixed logs located in the folder \Program Files\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs. I hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *