On to problem 3 in my series on customizing the Tableau server experience...
Problem #3 - Lack of Access Transparency 
Another problem I've encountered is lack of transparency into who has access to reports on Tableau Server. At Piedmont, we manage rights based on user groups. User groups are assigned to project folders. When a workbook is published to a project, it inherits the rights of the project. But end users cannot see the users that exist in these groups. In version 8.1, Tableau added the ability to expand a user group to see the users when publishing a workbook to the server, but report consumers still do not have the ability to see user rights on Server.

I'm hoping at some point this is solved natively in Tableau Server. But one of the great things about Tableau is it's flexibility. That usually translates into the ability to do pretty much anything you want if you understand how Tableau works. The secret sauce to the solution for this problem relies on Tableau features that are available through tabcmd and URL filtering, and sprinkles in a little scripting code. I used Ruby, but you could use Python or any other language you are familiar with. This is the end result:





Step 1 - Extract the members of your Tableau user groups into XML files

Start by opening notepad and creating a batch file. The key commands are: "tabcmd login", "tabcmd get", and "tabcmd logout". For help on each of these commands, see: http://onlinehelp.tableausoftware.com/current/server/en-us/tabcmd_cmd.htm

You will create a "tabcmd get" line for each user group in your server instance. There are some important parts to note when constructing the strings. Each get commands will be proceeded by "/users.xml?fe_group=". If the group is local, you will then add "local%%5C" followed by the group name. If the group is from Active Directory, you won't need to add this. Next enter the group name. If you have spaces in your group name, replace each space with "%%20". The "%%XX" parts are URL encodings. "%%5C" converts to a "\" and "%%20" converts to a space. If you are typing these into the command line, you don't need the encoded values, but for some reason you do when using batch files. The next segment of your string is "--filename" followed by the path and file name where you want to extract the group members. The format is XML. Finally, if you are running SSL, you will need to add "--no-certcheck" to the end of each command. Here is an example batch file for extracting the local group, "HR Analytics Users":

tabcmd login [your site credentials] --no-certcheck
tabcmd get "/users.xml?fe_group=local%%5CHR%%20Analytics%%20Users" --filename "\\phcms01\Share_Data\Tableau Data\user_audit_HR Analytics Users.xml" --no-certcheck
tabcmd get [additional group 2]
tabcmd logout --no-certcheck

Once you have your file with all your groups, save the it with the .bat extension. Next you will use Windows Task Scheduler to execute the .bat file every day. Do this on your primary server (unless you have enabled remote command administration).




Step 2 - Consolidate the results of your XML files into a single CSV file


This part requires a little scripting. Please note that I'm not an expert with Ruby, but I have just enough of a programming background to figure this stuff out. So I'm not the person to ask for help troubleshooting scripting code. Also, explaining the Ruby code is beyond the scope of this post. With that said, this is the code I use in Ruby to parse the XML files and insert into a single CSV file:

require 'net/http'
require 'rexml/document'
require 'csv'

groups = [ '0PHC Corp Directors',
'0PHC Corp Execs',
'0PHC Corp Managers',
'2PAH Directors',
'2PAH Execs',
'2PAH Managers',
'3PFH Directors',
'3PFH Execs',
'3PFH Managers',
'4PMH Directors',
'4PMH Execs',
'4PMH Managers',
'5PNH Directors',
'5PNH Execs',
'5PNH Managers',
'6PHI Directors',
'6PHI Execs',
'6PHI Managers',
'6PHI Physicians',
'7PMCC Directors',
'7PMCC Execs',
'7PMCC Managers',
'7PMCC Physicians',
'Corporate Directors',
'Corporate Finance',
'Corporate PMO',
'CSS Directors',
'CV Services Administration',
'DCIA - Desktop Clinical Intelligence Analysts',
'Epic Report Writers',
'Executive Group',
'Financial Analysts',
'Financial Planning & Analysis',
'HR Analytics Content Admins',
'HR Analytics Users',
'Labor and Productivity',
'March Madness Players',
'Marketing',
'Medical Records',
'Patient Financial Services',
'Patient Financial Services - Content Admins',
'Physician Outreach',
'Piedmont Atlanta Administration',
'Piedmont Atlanta Content Admins',
'Piedmont Fayette Administration',
'Piedmont Fayette Content Admins',
'Piedmont Heart Administration',
'Piedmont Heart Content Admins',
'Piedmont Henry Administration',
'Piedmont Henry Content Admins',
'Piedmont Mountainside Administration',
'Piedmont Mountainside Content Admins',
'Piedmont Newnan Administration',
'Piedmont Newnan Content Admins',
'PMCC Administration',
'PMCC Content Admin',
'Revenue Cycle Administration Viewers',
'Revenue Cycle Content Admins',
'Revenue Cycle Management',
'SCIA - Server Clinical Intelligence Analysts',
'Piedmont Information Systems',
'0PHC Systemwide Employees',
'Tableau_All'
      ]

directorplus = [ '0PHC Corp Directors',
'0PHC Corp Execs',
'2PAH Directors',
'2PAH Execs',
'3PFH Directors',
'3PFH Execs',
'4PMH Directors',
'4PMH Execs',
'5PNH Directors',
'5PNH Execs',
'6PHI Directors',
'6PHI Execs',
'7PMCC Directors',
'7PMCC Execs',
'Executive Group'
]

managerplus = [ '0PHC Corp Directors',
'0PHC Corp Execs',
'2PAH Directors',
'2PAH Execs',
'3PFH Directors',
'3PFH Execs',
'4PMH Directors',
'4PMH Execs',
'5PNH Directors',
'5PNH Execs',
'6PHI Directors',
'6PHI Execs',
'7PMCC Directors',
'7PMCC Execs',
'Executive Group',
'2PAH Managers',
'3PFH Managers',
'4PMH Managers',
'5PNH Managers',
'6PHI Managers',
'7PMCC Managers'
]

pahmanagerplus = [ '2PAH Directors',
'2PAH Execs',
'2PAH Managers'
]

file = '//phcms01/Share_Data/Tableau Data/user_audit.csv'
fileExists = File.exist?(file)

if fileExists
   File.delete(file)
end

CSV.open(file,"a") do |csv|

   csv << %w{Group User_ID User_Name Email}

   groups.each do |group|

# get the XML data as a string
xml_data = File.new("//phcms01/Share_Data/Tableau Data/user_audit_#{group}.xml")

# extract event information
doc = REXML::Document.new(xml_data)

doc.elements.each('users/user') do |ele|

  print "#{group} | #{ele.elements['name'].text} | #{ele.elements['friendly_name'].text} | #{ele.elements['email'].text}\n" 
  csv << [
"#{group}",
"#{ele.elements['name'].text}",
"#{ele.elements['friendly_name'].text}",
"#{ele.elements['email'].text}"
 ]
end
   end

   directorplus.each do |group|

# get the XML data as a string
xml_data = File.new("//phcms01/Share_Data/Tableau Data/user_audit_#{group}.xml")

# extract event information
doc = REXML::Document.new(xml_data)

doc.elements.each('users/user') do |ele|

  print "DirectorPlus | #{ele.elements['name'].text} | #{ele.elements['friendly_name'].text} | #{ele.elements['email'].text}\n" 
  csv << [
"DirectorPlus",
"#{ele.elements['name'].text}",
"#{ele.elements['friendly_name'].text}",
"#{ele.elements['email'].text}"
 ]
end
   end

   managerplus.each do |group|

# get the XML data as a string
xml_data = File.new("//phcms01/Share_Data/Tableau Data/user_audit_#{group}.xml")

# extract event information
doc = REXML::Document.new(xml_data)

doc.elements.each('users/user') do |ele|

  print "ManagerPlus | #{ele.elements['name'].text} | #{ele.elements['friendly_name'].text} | #{ele.elements['email'].text}\n" 
  csv << [
"ManagerPlus",
"#{ele.elements['name'].text}",
"#{ele.elements['friendly_name'].text}",
"#{ele.elements['email'].text}"
 ]
end
   end

   pahmanagerplus.each do |group|

# get the XML data as a string
xml_data = File.new("//phcms01/Share_Data/Tableau Data/user_audit_#{group}.xml")

# extract event information
doc = REXML::Document.new(xml_data)

doc.elements.each('users/user') do |ele|

  print "PAHManagerPlus | #{ele.elements['name'].text} | #{ele.elements['friendly_name'].text} | #{ele.elements['email'].text}\n" 
  csv << [
"PAHManagerPlus",
"#{ele.elements['name'].text}",
"#{ele.elements['friendly_name'].text}",
"#{ele.elements['email'].text}"
 ]
end
   end
end 


Step 3 - Build a view in Tableau that leverages the CSV file created in step 2


Next, create a Tableau workbook that attaches to the resulting CSV file. It could just be a simple cross-tab with the User Name field on the row shelf. Publish the resulting view to the server. We will send the group filter in the URL.


Step 4 - Create project descriptions with links to the view created in step 3

The final step is to customize the project descriptions and provide the end user with links to see who is in each group that has rights to the project folder. The setup looks like this:

The end result looks like this:

The link syntax is given at the bottom of the screen where you edit the project description. The URL portion should be the address of your workbook you created and published in step 3. The key is adding the right filter to the end of the URL. I shortened my group field name to "G" to reduce the number of characters required because there is a character limit on the description field. So the syntax is "?G=[group to filter]". I also added "&:embed=y" so that the Tableau navigation doesn't appear at the top. The last part I added was " - [Role]", which added some text after the link describing what that group could do. Note that you will need to URL encode any spaces and special characters in your group names.


Conclusion

This wraps up my series on customizing the Tableau Server experience. I hope what you've learned is that Tableau is a very flexible product that you can customize to meet your needs. In my experience, you can typically overcome a lot of the limitations you encounter if you are creative and understand how a lot of the features of Tableau work. In many ways it is a simple, but also very deep product. If you missed any of the previous posts, you can find them here:


2021 Wk 7

Challenge: https://preppindata.blogspot.com/2021/02/2021-week-7-vegan-shopping-list.html

The 2021 Week 7 #PreppinData challenge introduces the use of the GROUP_CONCAT() and INSTR() functions in Exasol and expands on the use of scaffolding techniques to unpivot data contained in a single field with delimited values. I also used CTEs, regular expressions, and built a view to avoid code repetition for this challenge. The full SQL solution is posted below.

Lines 1-23 create our table structures and load the data into Exasol. Refer to the first post in this series for more about these DDL / DML statements. The bulk of the work is saved in a view that starts on line 25 so that it can be reference to generate the two outputs which are just pulling from the view with different filters. This is a good practice so that you don't repeat code multiple times for different uses and later have version control issues when minor changes fork from the original code.

The view contains a couple of common table expressions (CTEs) that manipulate the keyword dataset. The challenge with this one is that the list of keywords exist in two discrete columns as a comma separate list (shown below). The first column has ingredients and the second column has E numbers used to identify food additives. These two lists are not related, so they ultimately need to be concatenated. In retrospect I probably could have eliminated the second CTE by concatenating the two fields in the first, but I'll just explain the steps as I did them. 


The first CTE on lines 26-33 named "keyword_tall_pass1" converts the two comma separated lists into one record per value as shown below. This is accomplished by cross joining to a statement that uses the CONNECT BY hierarchical query functionality that generates 30 records for us on line 32. Thirty is just a number I chose that is large enough to capture every value in the two lists. On line 33 I drop the excess records that didn't need because there were only 16 items max between the two lists. The magic here is with the REGEXP_SUBSTR() functions. I used pattern matching to capture just alphabetic characters for the first list (line 29) or numeric characters for the second list (line 30) and kept the nth matching instance where the nth value is the RecordID value I generated on line 32. The result of this CTE is shown below. So you can see "Milk" was the first word followed by "Whey", "Honey", etc. from the screenshot above. Likewise for the second list of E numbers.


The second CTE named "nonvegan_keywords" on lines 35-38 just takes the Ingredient and ENumber columns shown above and stacks them on top of each other with UNION ALL. The ALL qualifier tells the query compiler not to bother checking for duplicate values among the two expressions. I also needed to append the letter "E" to the beginning of each number. You can concatenate string values with a double pipe operator "||". It turned out that the E numbers weren't found in the shopping list data, so none of that data was used anyway.

The final SELECT statement for the view appears on lines 41-50. This query uses the shopping list as the base table (line 48) and cross joins to the "nonvegan_keywords" CTE (line 49) so that each shopping list product's ingredients can be compared to every keyword in the list individually. I do this with the CASE statement you find on lines 46-47. Exasol is case sensitive, so I forced both the ingredients and the keywords to be lowercase and used the INSTR() function to see if an individual keyword is found in the list of ingredients. INSTR() returns the character location of the found text in a string, so if it is greater than zero I return the matched keyword. Any non-matched keywords are ignored and return NULL. 

The case statement is wrapped in a GROUP_CONCAT() function, which is an aggregate function for string data. By default it comma delimits the string data with the group, but you could choose a different delimiter. I then grouped by the Product, Description, and Ingredient fields on line 50 to get the dataset back to one line per product on the shopping list. The results are saved in a view (line 25) so I can call all this code again for my output queries.

The two output queries on lines 53-63 are very simple and largely the same. One just filters for products with a NULL value for the "Contains" field and the other for non-NULL values. This means one list is vegan and the other list is non-vegan.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CREATE OR REPLACE TABLE DEV_MAJ."PD_2021_Wk7_ShoppingList" (
    "Product"     VARCHAR(255),
    "Description" VARCHAR(1000),
    "Ingredients" VARCHAR(2000)
);
CREATE OR REPLACE TABLE DEV_MAJ."PD_2021_Wk7_Keywords" (
    "AnimalIngredients" VARCHAR(1000),
    "ENumbers"          VARCHAR(1000)
);
IMPORT INTO DEV_MAJ."PD_2021_Wk7_ShoppingList" FROM LOCAL CSV
    FILE 'C:\Mark\Preppin Data\PD 2021 Wk 7 Shopping List.csv'
    SKIP = 1
    ROW SEPARATOR  = 'CRLF'
    COLUMN SEPARATOR = ','
    COLUMN DELIMITER = '"'
;
IMPORT INTO DEV_MAJ."PD_2021_Wk7_Keywords" FROM LOCAL CSV
    FILE 'C:\Mark\Preppin Data\PD 2021 Wk 7 Ingredients.csv'
    SKIP = 1
    ROW SEPARATOR  = 'CRLF'
    COLUMN SEPARATOR = ','
    COLUMN DELIMITER = '"'
;

CREATE OR REPLACE VIEW DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" AS
    WITH keyword_tall_pass1 AS ( --generate unique rows for ingredients / e-numbers
        SELECT
            i."RecordID"
            ,REGEXP_SUBSTR(k."AnimalIngredients",'(?i)([a-z]+)',1,i."RecordID") AS "Ingredient"
            ,REGEXP_SUBSTR(k."ENumbers",'(?i)([0-9]+)',1,i."RecordID") AS "ENumber"
        FROM DEV_MAJ."PD_2021_Wk7_Keywords" k
            CROSS JOIN (SELECT level AS "RecordID" FROM DUAL CONNECT BY level < 30) i --30 is arbitrary
        WHERE local."Ingredient" IS NOT NULL OR local."ENumber" IS NOT NULL --drop null records

    ), nonvegan_keywords AS ( --stack ingredients / e-numbers
        SELECT "Ingredient" AS "Keyword" FROM keyword_tall_pass1 k WHERE k."Ingredient" IS NOT NULL
        UNION ALL
        SELECT 'E' || "ENumber" AS "Keyword" FROM keyword_tall_pass1 k WHERE k."ENumber" IS NOT NULL
    )

    SELECT --return products w/ delimited list of matching ingredients
        sl."Product"
        ,sl."Description"
        ,sl."Ingredients"
        ,GROUP_CONCAT(
            CASE WHEN INSTR(LOWER(sl."Ingredients"),LOWER(nvk."Keyword"))>0  --when ingredients contain keyword
            THEN nvk."Keyword" END) AS "Contains"
    FROM DEV_MAJ."PD_2021_Wk7_ShoppingList" sl
        CROSS JOIN nonvegan_keywords nvk
    GROUP BY 1,2,3
;

--OUTPUT 1: Vegan Shopping List
SELECT slk."Product", slk."Description"
FROM DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" slk
WHERE slk."Contains" IS NULL
ORDER BY 1;

--OUTPUT 2: Non-Vegan Shopping List
SELECT slk."Product", slk."Description", slk."Contains"
FROM DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" slk
WHERE slk."Contains" IS NOT NULL
ORDER BY 1;

I hope you found this exercise informative. If so, share with your friends and colleagues on your favorite social platforms.  If there is a particular #PreppinData challenge you'd like me to re-create in SQL, let me know on Twitter @ugamarkj.

If you want to follow along, Exasol has a free Community Edition. It is pretty easy to stand up as a virtual machine with the free Oracle VirtualBox platform. I use DataGrip as my favorite database IDE, which is paid software, though you can use the free DBeaver platform if you prefer.
Popular Posts
Popular Posts
About Me
About Me
Blog Archive
Labels
Labels
Other Links
Subscribe
Subscribe
Total Pageviews
Total Pageviews
1385868
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.