5 Replies Latest reply on Jan 26, 2016 4:32 PM by John Markley

    row level user filter with multiple datasources

    Andy Harris

      I have a workbook that connects to 4 different SQL tables. The data from each table is presented in  a series of analysis over 4 dashboard tabs.

       

      I need to provide the end user with data relating to the area for which they are responsible. Row Level security would achieve this but my data sources do not contain the required information that allow me to apply this automatically so i am trying to find the most efficient  (low maintenance) approach to managing this via Tableau.

       

      My first thought was to setup an excel file containing the required usernames and the organisations that they can access and then link this to my datasources. However when I tried to create this link  I could only use the Relationships function and this doesnt seem to support the application of user filters.

       

      I have now setup manual user filters which has given me the desired effect but because I have 4 SQL extracts I fear I am creating ongoing effort if each time an update is required I have to amend each connection.

       

      In the absence of being able to get the row level permissions stored into an SQL view, can anyone recommend an effective and efficient way to implement this via desktop?

       

      thanks

        • 1. Re: row level user filter with multiple datasources
          Antonio Willybiro

          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.

           

          Best

          Antonio

          • 2. Re: row level user filter with multiple datasources
            Robert Morton

            Hi Antonio,

             

            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,

            Robert

            • 3. Re: row level user filter with multiple datasources
              Antonio Willybiro

              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.

               

              Thanks

              Antonio

              • 4. Re: row level user filter with multiple datasources
                Robert Morton

                Hi Antonio,

                 

                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?

                Robert

                • 5. Re: row level user filter with multiple datasources
                  John Markley

                  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.