Tableau Server can compare the ID of the Tableau user currently logged in or the user Full Name to a piece of data. Thus, you can achieve cell-level security with only a small calculated field. However, when connecting to Microsoft Analysis Services, you cannot perform this comparison, because the only fields available to Tableau's calculation engine are those in the "Measures" hierarchy (i.e., measurements). A Tableau user ID or user Full Fame, on the other hand, is a string.
However, Tableau supports the ability to write "Calculated Member" MDX directly in Tableau Desktop. You can create a new calculated member of hierarchical type "Measures" with result type "string", at which point you can use a Tableau calculated field to compare the current user to data in the cube.
Do not confuse the phrase "Calculated Member," which is MDX and specific to the cube, with the phrase "Calculated Field," which is a Tableau concept.
The summary steps required to achieve cell level security in a cube are:
- Create the calculated member.
- Create the calculated field.
- Filter on this calculated field.
Refer to the attached Tableau packaged workbook when reviewing the rest of these steps.
Step 1
In Tableau Desktop, in the Dimensions pane title bar, click the menu arrow and select Calculated Members.
In the example, you want to treat each "Customer Name" as a possible security situation. The MDX for this is going to look like the following:
The Hierarchy must be of type "Measures," and the result type must be of type "String."
Step 2
From the Measures pane, drag this new calculated member to the Level of Detail shelf. Any view requiring this security requires that the calculated member be somewhere in the view.
Step 3
You need to create a calculated field to compare the currently logged-in Tableau Server user to the current value of the calculated member. This step requires that you to have a copy of Tableau Server running. In our example, we have customer names such as "Paul Prost" or "Rick Reed." To absolutely follow these instructions, you also need these users added to your system (more realistically, you follow these instructions with your own data against your own cube source).
The image below shows what the Tableau calculated field should look like:
Step 4
In Tableau Desktop, select Server > Log On. This step is required so that you can emulate one of your end users (a dimension called "customer").
Step 5
If you are logged on and you have a Tableau Server user whose "Full Name" is Paul Prost, Rick Reed, or any of the other users in the attached example, drag your new calculated field Is Currently Logged in User? to the Filters shelf. At this point you should see a range of only two numbers: 0 and 1.
In the Filter dialog box, under Range of Values, change the number in the left text box to 1 so that the range is 1 to 1, and then click OK.
At this point, you have probably built a view with Customer Name (or some similar dimension on which you want security) and are ready to publish. When you do so, you have created a per-user experience against your cube. The workbook attached to this article should help you in understanding these concepts. If you have issues opening the attached twbx file, make sure that you have the Analysis Services 2008 drivers installed - the twbx file contains a CUB file built with MSAS 2008.
A final note about performance: Using this method is an efficient way to create cell-level security. However, it generates calculated member MDX on the fly. If you are attempting to perform cell-level security on thousands of discrete member values, this approach will eventually become non-performing. Always test to ensure that the expected behavior is satisfactory.
Below is a finished result published to a Tableau Server. Although there are hundreds of customers in the data set, when Paul Prost logs in, he sees only his relevant data.