Deeper into the Tableau Repository Part 6: Cache how you want to

WARNING 

This is out of the box, non-standard, and not supported. Proceed at your own risk. Make sure you have a backup. Don't try this at home (more likely work). 

UPDATE 11/12/2020: I added some more details and comments about the code

Tableau server only allows you to set the cache for everything server wide. I have our server set at 12 hours. This is a pretty good compromise for things that update monthly, weekly, or daily. Newer versions of Tableau will dump the cache if an extract refreshes or the workbook is republished but a dashboard with a live database connection also gets that 12 hour cache. Mark Wu has a great article about how to manage this. I chatted with Mark last week at TC19 and got some really good insights. His solution is to add 1 millisecond to the last_published_at value for the workbook. His blog doesn't include code so below is what I came up with. Since the workbook thinks it has been updated it will dump the cache. 

There are comments in the code, but there are some things I am checking for:

1. Who tagged it? Unless the tagger was the owner, site admin, or server admin I will ignore the tag.
2. Does the workbook have an extract? This is intended for live database connections where the data is constantly changing. Extracts refreshes trigger a cache reload automatically so we want to use the cache. 

I run it every 5 minutes, but there is no reason you couldn't extend the idea to using multiple tags and having a more customizable cache. Something like a '1h-cache' tag to update it hourly and '15m-cache' that runs every 15 minutes. 

/*
The idea here is to refresh the cache ala http://enterprisetableau.com/cache3/ 
How it works:
This update statement will increment the published date for a workbook view tagged 'no-cache' by 1 millisecond
This forces the cache to be ignored and requery the database. 
*/

update workbooks w
set last_published_at = last_published_at + interval '1 millisecond'
FROM views v
,taggings ts
,tags t
,users u
,system_users su
where 
v.workbook_id = w.id
AND ts.user_id=u.id
AND su.id=u.system_user_id
AND v.id=ts.taggable_id
AND ts.tag_id=t.id
AND t.name='no-cache' --the tag we are looking for
AND (w.owner_id=ts.user_id --owner tagged the workbook
    OR su.admin_level=10 --OR a server admin tagged it
    OR u.site_role_id=0  --OR a site admin tagged it
    ) 
-- we are going to ignore it if it the workbook has an extract
AND (w.refreshable_extracts=false
    OR 
    w.incrementable_extracts=false);
 

For some reason the if I tag a workbook it shows as the view being tagged in the repository. I also set it to only do this for tags that were created by an admin (server or site) or the workbook owner.

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.

Deeper into the Tableau Repository Part 1: The Basics

Over the years I have leveraged the Tableau Repository to do all sorts of things. I have found some articles here and there but thought I would share some that I haven't seen before. Since there will be a few (hopefully lots) I am going to do a series that will get deeper into the repository than you should probably go, but we are doing this for informational purposes and fun only.

Some of the topics I have that you should see in the next few months will include:

• Part 2: Sharing repository data with users
• Part 3: Importing AD groups the easier way.
• Part 4: Let users know what is happening with their content. 
• Writing to the repository to change things from the backend.

Any topics people really want to see?

Into the breach. The Tableau Repository  is well documented and I am not going to re-write the manual. Basically run a command to set the password for the readonly user (or the tableau user if you want only access to basic info).

tsm data-access repository-access enable --repository-username readonly --repository-password <PASSWORD>

After the server restarts you can query it with Tableau Desktop or any SQL tool that works with PostgreSQL. Note the port is 8060 not the standard PostgreSQL port. 

Things to know:

Views
Back in the old days we only had the tableau account for the repository and it could only access views (they start with an underscore). Some of these can still be useful. For example _users contains details that you would normally have to pull from users and system_users. 

Permissions

The NextGen permissions tables (nextgen_permissions, capabilities, roles, etc.) are fairly new but really powerful. It used to be really hard to figure out who had access to what. Not it is easier, but still not easy. 

Data Retention
The historical tables purge events after 183 days by default. If you want to look back farther or have to keep the records for compliance make sure you change it.

tsm configuration set -k wgserver.audit_history_expiration_days -v <number of days>

Better Timezone and DST calculations

Almost 4 years ago I posted a piece on converting UNIX timestamps to dates and then to local time where I did all the calculations in Tableau. You can also convert timezones in SQL and it is much easier. As an added bonus it also takes care of half hour timezones!

I have a bunch of dashboards that use the Tableau Workgroup internal repository and they are all kept in UTC. I was looking at a post on The Information Lab on building a better traffic to views dashboard and I noticed that there was no adjusting the time from UTC so my heatmap of peak times was off.

RAWSQL_DATETIME("%1 at time zone 'utc' at time zone %2 " ,[Created At],[Timezone Parameter])

The parameter works fine with a live connection but you might want to just hard code it if you are running extracts. Something like this would work better in extracts:

RAWSQL_DATETIME("%1 at time zone 'utc' at time zone 'America/New_York' " ,[Created At])

To generate the timezone parameter I just ran the following against the repository and put it into a string parameter.

select
name as value,
name || ' (' || abbrev || ')' as display
from pg_timezone_names;

This creates a nice list to display as well as the values to pass in the RAWSQL function.

Overall this should be more performant. I make sure to note in the field name and in the dashboards what timezone the date is in so I might end up with [Created At (GMT)] and [Created At (EDT)] columns.

To roud this back to UNIX timestamps you can combine them to something like:

RAWSQL_DATETIME("(to_date('1970-01-01', 'YYYY-MM-DD') + (%1/ 86400000)) at time zone 'utc' at time zone %2 " ,[Timestamp],[Timezone])

My Ski Data

Last year I inadvertently tracked my skiing for a week at ABasin using the Moves App. It wasn't super accurate and lacked elevation (a big deal when skiing). This past season I used an app called Ski Tracks that is designed for skiing. It is also smart enough to split runs and chairlift rides. It started snowing out west this weekend so I figured I should get this finished up.

Here are the results of my 21 ski days for the 2016-2017 season.

Details:

The app I used will export as KML or GPX, but I found the .SKIZ files it uses are actually .ZIP files. They contain an XML with day stats and CSV files with run segments, nodes, battery life, and any photos I tagged along the way.

I created a pentaho job that would expand the SKIZ files, and then process them into 3 files.

1. Day Level Stats - one record per file/day that has max speed, total decent, number of runs, min and max altitude, etc. 
2. Ski Paths - a combination of the runs and nodes data. Along the way I also add some calculations for delta altitude and distance so I don't have to do table calcs in Tableau. 
3. Battery life - it tracks battery life during use and will stop tracking if the battery gets low, but I am never reached that point or have done anything with this data yes. 

Once the data is processed I take it away with Tableau! For my detailed speeds, I ended up using a moving average. I have found that with GPS data at small distances can be...less than accurate. Some line segments had me over 150mph. I am good, but not that good. A moving average helps smooth the speed line out. 

Tableau Push instead of Subscribe notifications

Being able to subscribe to views or workbooks was a huge added feature when it was added in Tableau v8.0. Users could get the info in their email on a schedule instead of having to go to the web page. Then came the questions....

"Can I get a notification when an extract updates?" No.

"Can I subscribe a group?" No.

"Can I sign other people?" No.

We do our best to let people know which schedule to choose. 

I have seen people who get daily messages for a dashboard that updates monthly. Not only does the server have unnecessary load, but they get 29 extra emails a month.

I use Pentaho Data Integration to manage other Tableau Server tasks and I was working on notifying owners of when extracts failed and realized that the opposite would be to notify them when it is successfully updated.

I boiled what was subscribe-able down to 3 things:

1. Successful Workbook Extract Refresh
2. Successful Datasource Extract Refresh
3. Workbook Publish

The third is because there are some workbooks that are manually worked/QA'ed monthly and then published from a QA version to a Prod version. You might want to add a fourth if there are datasources you manually publish. 

Watch the video for more details on how the Pentaho Job/Transform works, but the overview is:

1. Run Custom_Subscriptions.kjb Job, which deletes old images and launches the transform
2. Figure out what has happened and is actionable
1. Query Tableau Database to find actionable events
2. Join it to a table/file with a list of who is subscribed to what
3. Load some details for the view URLs
4. Generate the emails
1. Find unique views
2. run tabcmd to generate the images once for each subscribed dashboard
5. Email each person/group the image/content they are subscribed to.

I have two tables/text files containing

• email addresses, subscribed object, site, etc
• Details about the subscribed object (url, path, site, filetype)

We use Active Directory groups to control access, so most dashboards already have a corresponding group that we can email. This prevents having to manually manage individual users.

I have only tested images, but switching the file type from .png to a .pdf or .csv should work. 

Download the Pentaho Files

There is also a #DATA15 session on how they do this at Tableau and I am really excited about seeing this. Maybe they will announce that it will be a new feature in Tableau 10.

Analyzing text with Tableau

This afternoon a colleague and I were discussing text analysis and how there isn't a good way to do it in Tableau. We have seen demos for tools like Oracle Endeca that are all about analyzing unstructured text. An example that was often given was using it against doctors notes. The other is for occurrences of keywords.

I was able to use a "Split Fields to Rows" transform in Pentaho to make a row for each word and generate the viz below. I excluded numbers, and some common words like 'Tableau'. I also used a Stop Word List to exclude common words.

National Parks

During #TravelMonth I managed to get in two new National Parks (Lassen Volcanic and Olympic). My son was quizzing me on which parks I have been to and I had the idea to come up with a viz about the National Park System.

The first tab is a story walking though some general info. There is also a storypoint about the units I have been to and on the next one you can see how many you have. Leave a comment below with your results. Other tabs have a explorer where you can pick from a map and details about the different locations.

I had to pull the data from all over. There are a bunch of reports from the NPS Reporting site but they are by park and I had to ETL them together. The images I pulled down from the Unit web pages with import.io.

Find a NPR Station

I have been traveling and forgot to do anything for #TravelMonth, so I will put a couple out that I have been playing with. This is the first.

I may be bucking the trend of my generation, but I don't listen to music in the car. I always listen to NPR though. The first thing I do when traveling to a new place and getting in the rental car is to figure out the local NPR affiliate and change the station.

I love maps, so tracking down and figuring out how to model all the FCC Data for the stations was fun. They have 360 coordinates to plot the ring of station coverage, so Tableau is having to plot 361 points for each station. This is why I decided to display one (my home state) to load. It is about a quarter million points for all of the US. Originally I was going to do it for all radio stations, but it was north of 6 million rows/points and was horendiously slow. I have that dataset if anyone is looking for it, let me know. The coordinates were in 17,000-ish individual KML files that I was able to ETL using Pentaho Data Integration into one dataset.

Dual axis maps are only doable in Tableau if you have the same lat/long field. In my case the station lat/long was different from the coverage area lat/long so I had to put the station info in the center of the coverage area, not where they actually are. That caused some wonkiness with some stations on a coast or border.

I really wish that Tableau would build in location awareness. There are so many ways this could help. Imaging a salesperson loading up a viz and it automatically filters to the state they are in, or clients in a x mile radius. For this example I would love to be able to have it zoom to where you are and then show stations that you can get. Instead I have to lean on the new map search or people manually filtering by state.

Trees of Ann Arbor

Today is Earth Day so I thought I would get out a dashboard of Trees from the City of Ann Arbor.

Some of the data was tough going until I figured out that the X/Y coordinates were an actual system. I had a background image of a map and was messing with scales until I discoverd the State Plane System. Then things went quickly and I got the data converted to Lat/Long and cleaned up and grouped.

Click the dashboard below to load