Tableau PostgreSQL Repository: Viewing all groups, users and most recent login

We are seeing more and more interest from our customers to analyse and get additional information from Tableau's Server Repository, the database where Tableau Server stores data about all user interactions, extract refreshes, workbooks, sites, projects, users, groups and more. If you haven't heard about this database and want to know a bit more about it or you want to gain access to it, I'll recommend you to visit the post from my friend Jonathan MacDonald that he wrote a few years ago.Today I will show you how to create a data source that gives you the complete list of sites, groups, users and the last time each person logged in Tableau Server. A very useful way of managing group members and tracking users that might not have access to the server in a while, what can be interesting if you have a big number in a waiting list to get a Tableau Server license and you want to allocate them licenses from users that haven't access the server in X amount of months for instance.

Things to have in mind

First of all, we have to have in mind a few things before querying the Tableau Server PostgreSQL Repository.

1. Some tables of the Repository can be really big and querying intensely the database can have an impact on the Server performance. For this reason, we recommend to use Extracts once you create your data source instead of querying the database live.
2. There are a lot of tables in the database and each table has a lot of fields, and some of them will not give you the data you might expect based on the field name. Have always opened the data dictionary and use it as a reference as frequent as you need. It's a must that you query the correct fields and get the insights you are looking for correctly. Here's a link to the data dictionary.
3. Difference between the 'hist_' tables and 'normal' tables. You will see the database has for example a 'hist_groups' table and a 'groups' table. The 'hist' tables shows you data that was relevant at a concrete timestamp. So it might show you groups that don't exist in the Server anymore but they did weeks ago and were deleted. If you aren't interested in historical data and you just want to the current situation of the server, then I'll recommend to forget about the tables with a 'hist_' prefix.
4. For this particular use case, remember that groups and users (and projects, workbooks, etc.) are site based. so we will need to use first the Site tables to get a correlation between Sites, Groups and Users.
5. Each table has a significant amount of fields. Probably you don't need all of them so to simplify the number of fields you have, especially after several joins, I recommend to hide at the end all the fields you don't need.

Step 1: Matching Sites and Groups

As we just mentioned, Tableau Server content is managed per site. So if you have more than one Site then you will need to use first the Site table to then get all the groups for each site. We will connect to the Sites table and join it to the Groups table based on sites.id = groups.site_id as shown in the image below.This one we have our first step ready: Get all the Sites of our Server and all the groups available in each Site.Following my previous advice. I'm going to hide all the fields from this first join except Id and Name from the Sites table and Id (Groups), Name (Groups) and Site Id from the groups table.

Step 2: Matching groups and members of each group

We have the groups but we need the users that are part of each group. We might tend to think that we could just use the Users table for this but we will not get want we want and this is when the data dictionary becomes very useful. We have to remember that a group can contain more than one user and a user can be part of several groups, so there is a potential many to many relationship between groups and users. The PostgreSQL repository has a table with this information, the group_users table and join it to the groups table based on groups.id = group_users.group_id as in the next image.Now we can use the User Id field from the group_users table to know what users are members of each group... but this table only gives us the User Id, so not very useful still. Let's make it more useful in the next step.

Step 3: Matching Group_Users and Users table

We now have the User Id from each user but it's not very useful to identify users based on their Id. The table that will give us the information of the username is the System Users table. Unfortunately, the Id from this table doesn't correspond to the User Id from the Group Users table. This is why we need this step: to match each User Id to the System User Id. To do so we need to use the Users table and join it to the Group Users table based on group_users.user_id = users.id and also to the Site table (remember that users are also Site based) based on site.id = users.site_id as in the next image.What useful information we get from this step? not much that we will use in our dashboard, because the objective of this step is to get from the Users table the System User Id. That will allow us to join Users with the System Users table and get a meaningful identifier for each user as we will see in the fourth step. Again, we can hide a lot of fields from the Users table to just keep the fields Id (Users), Login At, Site Id (Users) and System User Id.

Step 4: Getting meaningful User identifiers

Last step to get all the data we need. We have now the System User Id from the Users table that will allow us to get the real name or username from the System Users table. So we will create now the last join between Users and System Users based on users.system_users_id = system_users.id. Then we can keep just from the System Users Table the Friendly Name - the real name of the user on Tableau Server, Name (System Users) - the username of each user and the Id (System Users). The join should look like in the image below.We have all the data we need! Time to start checking it's working as expected and for example, create a view to get for each site, the number of groups and number of users.

Step 5: Build a dashboard to keep track of your users and groups

Time to answer the questions you need to. For instance, you might want to see easily all the groups, the number of total members of each group, who is a member of them and also the number of days since each user login, highlighting in red the users that login more than 100 days ago.There's probably unlimited analysis you can create with Tableau Server postgreSQL repository. Give it a try!