Hi - did anyone ever manage to do this in an efficient and effective manner?
I have the same requirements right now i.e. multiple data sources, need to set users filters at country level and two other verticals (region and group company).
and I have many countries. doing that manually works but is an administration nightmare.
If your organization uses Tableau Server, you can use it to host a Data Server data source that users can connect to from Tableau Desktop. The data source on Tableau Server can be crafted to contain User Filters that Tableau Server will enforce for any queries issued by a Tableau Desktop user. In fact, the Tableau Desktop user will not even know the filters are being applied, and will not be able to see or modify the filters.
So how does this work? First, create a direct connection to your data source using Tableau Desktop. Then create User Filters for the fields you wish to filter based on individual users or groups defined on Tableau Server. Instead of placing those user filters directly in the Filter shelf for a given visualization, you can embed the filters directly in the data source. From the Data menu, within your data source, click 'Edit data source filters...' and add your User Filters. Then publish this data source to Tableau Server. Configure the permissions for the data source such that it supports connect/query, but not download or edit. This allows other Desktop users to log on to Server within Desktop and then connect to any Data Server data source listed under the 'Tableau Server' connection option.
I hope this helps,
Thank you Robert.
It's actually user filters for tableau viewers/interactors rather than people developing dashboards on Tableau desktop.
I am not sure if what you are suggesting would also work there then?
if it could then I'll ask our admin to look into that.
After you create a Data Server data source, you can use it to author visualizations & dashboards that you wish to publish to Tableau Server. Many different published workbooks can all point to the same Data Server data source, thus sharing the same rules for data security. At a later time if you must alter any aspect of the data model (add calcs, join additional tables, etc.) you can alter the single published data source shared by multiple workbooks; when you republish just the data source and overwrite the original, all existing workbooks on Tableau Server will begin to use the updated Data Server data source (cached results notwithstanding).
Does this help?
You should be able to use an Excel file just fine as a data source for row level authority. The only issue is ongoing maintenance of that Excel file, but if it's a small group it probably isn't too bad.
- Publish the Excel file as a data source to Tableau Server. If there's multiple areas they should have access to each area gets a row in this file and the username repeats
- Connect to the authority table that was an Excel file now hosted in Tableau Desktop
- Add a calculated field in Tableau Desktop in your main query dimensions called USERNAME (the calc is simply username()). This presumes the username they log into Tableau server to view the reports is the same as on your Excel file. You will use this to join over to authority.
- Join your main query with the authority table on username (query) = username (auth table) AND area (query) = area (auth table)
- You need to enable this link on each worksheet (i.e. click the link symbol from grey to orange, it isnt automatic)
- Now that the links are done create a calculated field in the dimensions panel for your authority datasource. This is a bit tough to explain but the calc is simply USERNAME() = username. Looks weird to write it like. the USERNAME() in this calculated field should not be referencing the username calculated field you made over on the query panel, just type USERNAME() and let it sit. The second username in this calculated field DOES reference the username field of the authority data set you got from Tableau Server.
- Once this T/F filter is created go back to the authority data source and add the filter to exclude null.
Quite possibly an easier more elegant way to do it, but it's how I do it and have verified it works throughout my organization. If you need to edit the auth table just edit the Excel file, update it in Tableau, and push it back up to the server in the same place.