6 Replies Latest reply on Feb 17, 2017 12:53 PM by David Li

    Call the contents of a script into a calculated field (dimension) tableau

    Michael Damico

      I have several workbooks that use a calcaulted field and Tableau's ISMEMBEROF function.

       

      A while back I read that the ISMEMBEROF function doesn't work with active directory groups, so for security we ended up creating all of the groups in Tableau server, and then a very lengthy script to handle security.

       

      As users leave/join the company, I have to update the script each time, which is a pain. Is it possible (until we get to building a better way of doing security) to call a script or reference the contents of a file in a calculated field so that I just have to update the script and not crack open each workbook and update each data source?

        • 1. Re: Call the contents of a script into a calculated field (dimension) tableau
          David Li

          Hi Michael, could you shed some more light on why you need to update the script each time a user leaves or joins the company?

          • 2. Re: Call the contents of a script into a calculated field (dimension) tableau
            Michael Damico

            Sure, and maybe I've been doing this wrong / unnecessarily the whole time.

             

            We have an employee dimension which allows us row level security to a project table. In most cases, our users only need to see their own project data. However, we have different tiers of employees that should be able to see lower level tier data. Example below

             

            Users:

            A

            B

            C

            D

             

            Groups

            A

            B

            C

            D

             

            Cases:

            User A can see user B,C,D's data, so I have them part of the groups B,C,D.

            User B can only see user B and C's data, so I have them a member of Groups B,C (not A or D).

            User C can see only their own data, so they are only a part of Group C (not A, B, or D)

             

            The script I have is along the lines of this (ignore any missing ('s, the script works fine, quickly built an example):

             

            IIF(

            (

            ISMEMBEROF("A")

            ) and [ID]

            = "A"

            OR

            (

            ISMEMBEROF("B")

            ) and [ID]

            = "B"

            OR

            (

            ISMEMBEROF("C")

            ) and [ID]

            = "C"

            )

            OR

            (

            ISMEMBEROF("D")

            ) and [ID]

            = "D"

            )

            ,True,False

            )

             

            In the above, replace A,B,C,D with employee names, and that's how security is setup. As employees leave the company, in our source finance system we reassign all of their projects to whoever is taking over their position. We don't have generic groups built (Northeast, Southwest, etc.), simply because the business doesn't' operate that way. The groups are built off of employee names, which is why I have to end up opening up our user filter script and update it to a new person.

             

            If there is a better way to do this let me know!

            • 3. Re: Call the contents of a script into a calculated field (dimension) tableau
              Jeff Strauss

              I have a similar need to be able to call a script (perhaps as a datasource filter) based on the logged in user info.  With 10.1, there is an option of referencing a Python script (with a fair bit of integration work), but then I consulted a Tableau engineer and was told this isn't so much the way to go because it comes back as a table across calc and this will throw off upstream calcs.

               

              In any case, if you make any headway, let me know...

              • 4. Re: Call the contents of a script into a calculated field (dimension) tableau
                David Li

                Hmm, this is an interesting problem. Here's a thought on how you could redesign the system, though I haven't tested this myself.

                 

                Instead of managing user permission inheritance in Server groups, you would create a separate data source that has a record for each user. That data source has at least three fields:

                1. A [User ID] field for joining this data source to the project table.
                2. The Server [Username] of each user.
                3. Some kind of a [Viewing Users] field that holds Server usernames for all higher-tier users that can see this particular user's data. The [Viewing Users] field would be delimited, maybe by pipes ( | ).

                 

                In Tableau, you would join this data source to your project table, matching on the [User ID]. Then, to control permissions, you would use the USERNAME() function to check if the current user's Server username is in the [Viewing Users] field. Something like this:

                CONTAINS([Viewing Users], "|" + USERNAME() + "|")

                Here, you'd want to make sure that every username in [Viewing Users] is surrounded by pipes, meaning that you should have leading and trailing ones. You'd also want to make sure that nobody's username contains pipes.

                 

                With this method, you would only have to update the user permission data source when employees leave or join.

                • 5. Re: Call the contents of a script into a calculated field (dimension) tableau
                  Michael Damico

                  Interesting approach David Li, this is something I'll have to try.