When to avoid Tableau REST API: single vs multithread vs database

 In Clojure, FOR BI PROS, Tableau

Tableau Server REST API is a relatively new feature in Tableau Server. It has must-have functions for publishing data sources and workbooks in addition to the usual CRUD functionality like getting users, groups and projects. However, like every new thing it has its own limitations: it’s terribly slow when you need to obtain information for individual objects like users or groups. In these cases it’s better to fall back to direct repository connection – which isn’t the best option for multi-tenant installation for obvious security reasons. In the following post I will show you where the API needs to be improved, how it can be used and what alternate ways are available to workaround these issues.

First of all let’s start from a real life use case.

The use case

Tableau 9.0 has a new web UI. The old was ugly and too 90’s but at it least it was ergonomic. The new looks are cool but from and administrative perspective it’s an ergonomic nightmare.

On a larger environment we usually have 5-600 groups and if I want to check my group memberships then it takes forever.

I need to scroll 30-40 pages and memorize my groups. Baaaaaad.

I need to scroll 30-40 pages and memorize my groups. Baaaaaad.

On this panel I cannot sort by “groups where I have access”, frankly sayin’ I have no way to sort things at all. I need to scroll down hundreds of groups while memorizing them. Bad, bad Tableau 9 admin UI.

No issues, let’s write a small app which takes users as parameters and prints their groups.

Version 1.0 with clj-tableau

I made a small library (clj-tableau) which partially implements the Tableau Server REST API. You can find it on github or on clojars. The first issue with our program is

The REST API does not have any function which returns a user’s group memberships.

This will lead to some sad consequences, discussed later. As an alternate solution we can:

  • Query all groups
  • List all users in these groups
  • Filter for our target users

Based on that this is the initial version of our “get the groups for the users” app:

(ns tableau-group-inspect.core
  (:require [clj-tableau.restapi :refer :all]))

(defn filter-searched-users
  [searched-users users]
  (filter #(some #{(val %)} searched-users) users))


(defn search-for-users-in-group
  [sess users-to-search group]
  (->>
    (get-users-from-group sess (key group))
    (filter-searched-users users-to-search)
    (vals)))


(defn build-map-with-user-groups
  [users users-in-groups]
  (into {}
        (map (fn [user]
               {user
                (doall
                  (map second (filter #(contains? (set (first %)) user) users-in-groups)))}
               ) users)))

(defn get-filtered-users-in-groups
  [sess users groups]
  (map
    #(vector (search-for-users-in-group sess users %) (val %))
    groups))

(defn -main
  [& args]
(with-tableau-rest-api [tableau_session ["http://tableau-server.com" "TestSite" "JohnDoe" "secret"]]
                         (let [users ["123456789" "234567890"]
                               groups (get-groups-on-site tableau_session)]
                             (->>
                               (get-filtered-users-in-groups tableau_session users groups)
                               (build-map-with-user-groups users)
                               (doall)
                               (time)))))

Just for the record:

  • with-tableau-rest-api  logs in to the server
  • get-groups-on-site  gets the group ids and names for the tableau server groups
  • get-filtered-users-in-group  iterates on all groups and filters our users
  • build-map-with-user-group  prints the output

When I execute it shows:

(-main)
"Elapsed time: 345134.711925 msecs"
=> {"123456789" ("Group 1" "Group 2" "Group 3" "Group 4" "Group 5" "Group 6" "Group 7" "All Users" "Group 8" "Group 9" "Group 10" "Group 11"), "234567890" ("Group 3" "Group 12" "All Users" "Group 8" "Group 9" "Group 11")}

What? 345 seconds? Like six minutes? For getting two user’s group information? Well, this is a big NO for version 1.0.

Version 2.0 – Multi-threading

We are living in 2015, thus, we do not use single threaded programs anymore. Period. If you are not using all of your resources (CPU cores) you are doing it wrong. Fortunately with modern programming languages you do not have to care about locking, threading or semaphoring. Everything is done by the language. Clojure is not an exception, thus, we need to change only one character to execute our application on multiple threads.

Please note that we changed map  to pmap  in line 28. Since in clojure you do not have variables and everything is immutable you don’t have to care about multi-threading issues. Everything will be okay.

(ns tableau-group-inspect.core
  (:require [clj-tableau.restapi :refer :all]))

(defn filter-searched-users
  [searched-users users]
  (filter #(some #{(val %)} searched-users) users))


(defn search-for-users-in-group
  [sess users-to-search group]
  (->>
    (get-users-from-group sess (key group))
    (filter-searched-users users-to-search)
    (vals)))


(defn build-map-with-user-groups
  [users users-in-groups]
  (into {}
        (map (fn [user]
               {user
                (doall
                  (map second (filter #(contains? (set (first %)) user) users-in-groups)))}
               ) users)))

(defn get-filtered-users-in-groups
  [sess users groups]
  (pmap
    #(vector (search-for-users-in-group sess users %) (val %))
    groups))

(defn -main
  [& args]
(with-tableau-rest-api [tableau_session ["http://tableau-server.com" "TestSite" "JohnDoe" "secret"]]
                         (let [users ["123456789" "234567890"]
                               groups (get-groups-on-site tableau_session)]
                             (->>
                               (get-filtered-users-in-groups tableau_session users groups)
                               (build-map-with-user-groups users)
                               (doall)
                               (time)))))

After execution it shows:

(-main)
"Elapsed time: 146054.729824 msecs"
=> {"123456789" ("Group 1" "Group 2" "Group 3" "Group 4" "Group 5" "Group 6" "Group 7" "All Users" "Group 8" "Group 9" "Group 10" "Group 11"), "234567890" ("Group 3" "Group 12" "All Users" "Group 8" "Group 9" "Group 11")}

The execute time went done from ~6 minutes to ~2 minutes. This is way better (thank you multi-threading and clojure) but still slow.

Version 3.0 – Forget REST API, use repository

Moment of truth: when I execute the query to get users and their groups :

workgroup=# \o /dev/null
workgroup=# \timing
Timing is on.
workgroup=# SELECT
workgroup-#   sites.name,
workgroup-#   groups.name,
workgroup-#   system_users.name
workgroup-# FROM
workgroup-#   public.group_users,
workgroup-#   public.groups,
workgroup-#   public.users,
workgroup-#   public.sites,
workgroup-#   public.system_users
workgroup-# WHERE
workgroup-#   group_users.group_id = groups.id AND
workgroup-#   group_users.user_id = users.id AND
workgroup-#   groups.site_id = sites.id AND
workgroup-#   users.system_user_id = system_users.id AND
workgroup-#   sites.name ='Default' AND
workgroup-#   system_users.name IN ('12345678','87654321');
Time: 183.743 ms

Less than a second. Fast. Or maybe super-fast.

Summary

As we see there are some use cases where Tableau Server REST API is just not fast enough. For publishing and managing sites it’s okay but if you need to query user, group or project information for a few objects it’s always faster to use Tableau Server direct repository connection.

On the other hand direct connection allows you to access the complete data set for all sites – which is probably a no-go for multi-tenant deployments.

If you are looking for a good way to interact with Tableau Server REST API you can use my clj-tableau clojure library. Since it follows the functional programming paradigm you can make parallel multi-threaded programs with almost zero efforts.

And yes, Tableau, please please please fix the group selection UI to make the administration as easy as it was in 8.x.

Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Not readable? Change text. captcha txt