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>

Hours of Daylight

This week on the Winter Solstice the Northern Hemisphere will have the shortest day of the year. A while ago I read an article where it was clear the author had no idea the effect geography had on daylight. The idea for this viz has been kicking around in the back of my mind since then and on my way home this week I managed to catch some sunlight and decided to make it happen for the Solstice.

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])

#Data17 Guide

Just a few weeks to go before data nerds converge in Las Vegas for the 10th Tableau conference. This will be my 7th and I still get excited about it.

1. Have fun - There will be parties, mingle time, relax time, keynotes (even those are fun). Welcome Reception on Monday and Empire of the Sun and Robert DeLong will be playing at Data Night Out on Wednesday. 
2. Meet people - in the elevator, in sessions, at the parties, in the hall. Go to meetups and meet people from the same industry or in similar roles. 
3. Comfy shoes - you are going to walk. 
4. Travel light - I hated carrying a bag with my laptop and all the trimmings. It kept getting in the way during sessions and I had to keep track of it. I tried slimming down to an iPad, but now I just go with sunglasses, a phone, and a power pack. Everything fits in my pocket so I can. I feel bad for those folks with computer bags at Data Night Out. 
5. Be flexible - You might go to a session, but it isn't your cup of tea. Don't be afraid to bail and check out our second choice, or go do something else. 
6. Tweet - The main hashtag is #data17, but there are others, like #rundata17, #fitdata17, #DataNightOut, #data17wish and more. 
7. Eat and drink - We will be in the desert, so keep hydrated during the day so you can party in at night. Pro tips:
1.  here is the Bar Chart often starts serving beer and wine around 3
2. Often people line up funny at the food tables. It isn't a buffet and the same food ready to grab is all over the table, but people form a line at one end and just take food from one side. Mob the table people!
3. Drinks lines can be crazy, so go to the back of the party where they are shorter. 
8. Pick the right sessions - I usually star everything I am interested in and then remove anything I think will be as good or better in the video replay (you do know they record the sessions?). Hopefully, I have two or three for each timeslot to give me a backup if something is full or boring. Some people like the hands-on sessions, but I am not one of them. They are longer and in my experience, I don't get as much out of them.
9. Dress how you want - Anything from a suit to jeans and t-shirt is acceptable. Average Highs are between 82-84 and lows around 60. Record temps are high 90's and mid-30's. Check the weather before you go to pack right. 

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. 

Ticks and Trips: Tick Danger along the MI North Country Trail

We were planning the most recent UMich Tableau Users group and I heard that I was supposed to do a Ticks and Trips. Apparently, they actually said "Tips and Tricks" but my mind was already working. In no time I was combining Lyme Disease data from the CDC and journal articles with demographics data. In order to span much of the state I used the North Country Trail as my "trip" and I was off. I built this on the fly, incorporating many tips and tricks. One person in the audience raised her hand and told us that she got Lyme Disease hiking in the Western UP, exactly where my viz showed the highest probability.