A customer of ours who has Netsuite, the Netsuite Mitto Connector and Tableau Online provides Company specific reports to a range of different Companies.
It is a security requirement that these customers can only see their own data. The Tableau Online Workbook was initially secured using a calculated field that tests the User’s Group against the Company’s name in the datasource:
When the client adds a customer to Netsuite and wishes to allow them access to Tableau Online reports the following process is performed.
- Add a Company Name to Netsuite (“Company 1”):
- Add the company user to the group created
- Add a group corresponding to the Company Name in Tableau Online:
- Edit the USER FILTER TEST security function above to add in the logic pertaining to Company 1
While the process to add the company to Netsuite and the group/user to Tableau Online will always be manual*, having to manually edit the workbook each time a Company is added is not scalable.
Using the Tableau Server REST API (https://github.com/tableau/server-client-python) a list of the following from Tableau Online was obtained in a format that could be read by Mitto and then output to database tables:
- Group to User
The resultant table looks like this (Company and Usernames hidden).
As some groups have more than one user a lookup table is created using the string_agg function to concatenate each user for each group into 1 row (this avoids blowout of the dataset - 1 company has one row):
This table looks like this. Each group_name has a single line and the username field is comma separated for each username in that group:
So now the USER FILTER TEST equation can be changed to use this table.
In our Tableau data source we join this table to our company table, so each row in the data has the Company Name and the usernames pertaining to that company. The join with the comma separated fields ensures that we only join 1:1 with company data and doesn't create duplicated rows in the data set.
We then change our USER FILTER TEST calculation to test whether the username exists in the row:
The dashboards are now secured based upon this method. When a new company is added to Netsuite, and users/groups added to Tableau Online, Mitto is able to recognize this and update the security table automatically.
* The Adding of groups could be automated with Mitto and the REST API as well, but that’s another blog post