Deeper into the Tableau Repository Part 3: So many AD Groups

This is Part 3 of a series where we dig into the Tableau repository.

In my previous lives as a sysadmin I learned that there are very few things you actually have to do once. From that experience, I learned that I should script EVERYTHING!!! As our Tableau Server use grew people were confused why a user or group wasn't on the Tableau Server. I had to explain that users and groups have to be imported and import them. I took the next step of importing a group with all our employees. But what about groups?

On our dev server I wrote a script to find every group and import them. It worked fine except it took about 8 hours for the Tableau Server to sync them. Turns out in our organization we have about 30,000 users and 35,000 groups. Since that wouldn't work it was back to the drawing board. Since every group was out we needed to import groups people actually wanted without them bothering me.

I came up with a solution that used a Google Form. Users could enter the group name and minimum site role for that group. I used Pentaho to make a job that:

1. checks the Google Sheet with the results
2. checks the Tableau Repository for groups that are already imported
3. joins the two together and if any groups need to be imported import them with tabcmd
4. send the user who requested the group a confirmation email with the results
5. log the results to another tab in the Google Sheet. 

This job has been running 3 times a day for a couple years now and really cuts down on emails from the 300+ people who are publishing content. I found early on that users would sometimes include the @domain.com instead of just the group name so I ended up doing some error checking in the Google Form to check for an '@' character. Originally I allowed the requestor to choose the site role, but since all our employees got a 'Publisher' role when I altered it for the new roles 'Creator Publisher' is the only option.

Deeper into the Tableau Repository Part 2: Opening it up with published datasources

This is Part 2 of a series where we dig into the Tableau repository.

There are lots of good bits of info in the Tableau Repository but Tableau only gives you one (two really but the readonly user is the one with good access) account with one password to access it. Site and Server Admins can see the built-in status dashboards to get information but there are a couple problems with them:

1. Only admins? come on Tableau it can't be that hard to add a row-level filter to your dashboards and let anyone see what they did or own.
2. Why can't I subscribe? They have embedded their own dashboards into Tableau Server they turned off the ability to subscribe. Wouldn't it be nice to get a subscription with how many extracts are failing on your server?

A few years back Matt Coles shared his sharable datasources at TC16 and I loved the idea. I don't like having to be a bottleneck or doing the same thing twice so these were a really great solution. I applied a user filter (more on that later) and then piloted the Background Tasks, Events, and a custom-built Comments DS with a small group of power-users. The beta was a great success and now they have been rolled out to all users.

Row-Level headache

First off who gets to see what? I kicked around some ideas and came up with that you should be able to see the row if:

• if you were the actor (you did the thing)
• if you own the object acted upon (Workbook, View, or Datasource)
• if you own a Datasource and the Workbook acted up connects to it
• if you own the project the Datasource or Workbook lives in
• if you are a project lead for the project the Datasource or Workbook lives in
• if you are a site admin or server admin

That list can be confusing, but I took it in chunks. Owners are easy, but it can be harder when there are multiple site/server admins and project leads. This was back before multi-table extracts and I wanted to be able to extract this datasource. To prevent exploding the extract I ended up combining all the Project Leads into one comma-separated field and doing a contains() in Tableau

select
    pr.id as project_id
    ,pr.site_id
    , ',' || string_agg(COALESCE(_users.name,gmemebers.name) , ',') || ',' AS username_PLead
from projects pr
left outer join next_gen_permissions ng on (pr.id = ng.authorizable_id AND ng.authorizable_type = 'Project' AND (ng.permission=1 or ng.permission=3))
inner join capabilities cap on (ng.capability_id = cap.id AND cap.name = 'project_leader')
left outer join _users on (ng.grantee_id = _users.id AND ng.grantee_type = 'User')
left outer join _groups on (ng.grantee_id = _groups.id AND ng.grantee_type = 'Group')
left outer join group_users on (_groups.id=group_users.group_id)
left outer join _users gmemebers on ( group_users.user_id = gmemebers.id)
Group by 1

I joined the above CustomSQL to the project_id once each for the DataSource, Workbook, and View. I did something similar for site and server admins. Then my row-level filter had a big boolean statement that looked something like:

USERNAME() = [item_owner_username]
OR USERNAME() = [actor_username]
OR CONTAINS([Current DataSource Project Leads],','+USERNAME()+',')
OR CONTAINS([Current View Project Leads],','+USERNAME()+',')
OR  CONTAINS([Current WB Project Leads],','+USERNAME()+',')
OR  CONTAINS([Admin Username],','+USERNAME()+',')

 I now do this with a live query, so there is no extract to explode. Sometimes I get off the wall requests that I have to manually create, but the published datasources make it only a handful each year.