How to join related SharePoint lists that use multi-valued fields when using the SharePoint Lists JDBC connector for Tableau

Summary

Tableau has the ability to connect to SharePoint Online lists via a JDBC connector. Although setting up the data source is trivial, it can be challenging to join two related tables when that relation is done through multi-valued or multi-select fields. This is because the JDBC connector - unlike the Power Query connector used by Power BI and Microsoft Access - brings back multi-valued fields as XML code embedded inside a single column value. This article discusses an approach to solve the multi-valued field join problem in SharePoint JDBC using Custom SQL with UNION clauses.

Steps

Overall Process

1. Connect Tableau to SharePoint Lists and drag the parent table into the data source
2. Create a bridge table that will provide a 1-to-many relationship for the primary table, by copying the primary table, linking to itself and converting to custom SQL
3. Edit the custom SQL to extract the 1st XML element from the multi-valued list, then copy & paste with a UNION line in between, changing the array value to the 2nd element. Then copy & paste again for the 3rd element, and so on.
4. Drag the child table into the data source and join the bridge table (custom SQL query) to the child table

Connect Tableau to the SharePoint Lists

1. Follow the directions in Tableau's documentation for installing the SharePoint Lists JDBC connector. This is needed on the computer used for development with Tableau Desktop, as well as the Tableau Server (if using one).
2. In Tableau Desktop, click Data -> New Data Source and select SharePoint Lists (JDBC).
3. Enter the base URL for the SharePoint site containing the lists. For example, the Depression Center's TrackMaster site would be entered as the address that includes /sites/DepressionCenter-TrackMaster with no leading slash, as shown below.
    
4. Once authenticated, all the available lists will be displayed on the left under Tables.

Create a Bridge Table to Provide a One-to-Many Relationship Using Custom SQL

1. The primary table contains a column with XML values in it, which must be parsed and joined via UNION queries to create a 1-to-many relationship.
2. For example, in TrackMaster, there is a People list with a multi-value lookup field, Group Membership, pointing to a Groups list. Tableau will show a People table with a "Group Membership Id" field, which implies it stores the IDs of the Group list that are selected in the multi-select lookup field. Adding this field to a sheet will display the XML code, which is needed to write the custom SQL query later on:
   
    
3. The XML code in the multi-value lookup is structured as <internal_field_name><element>ID#</element></internal_field_name>. In the case of the Group Membership Id column in TrackMaster, it would look as follows:
   <Group_x0020_MembershipId type=""Collection(Edm.Int32)"">
  <element>3</element>
  <element>2</element>
</Group_x0020_MembershipId>
   Each element contains the ID of the secondary table (Groups List)
    
4. Drag the parent table and drop it into the data source.
5. Drag the parent table and drop it a second time into the data source to create a duplicate.
6. Form a connection between the ID field of both tables
7. Right-click on the copy of the table, and select Convert to Custom SQL

8. The SQL query is structured as follows:
   select
    primary table ID,
    extracted 1st element from multi-value field
from
    primary table

UNION

select
    primary table ID,
    extracted 2nd element from multi-value field
from
    primary table

UNION

select
    primary table ID,
    extracted 3rd element from multi-value field
from
    primary table

... up to the 10th field, or more if needed.

9. To extract the elements from the multi-value field (XML shown in previous step), use the XML_EXTRACT function as follows:
   XML_EXTRACT(table_alias.field_name, '/internal_field_name/element[n]')
   Where n is the position in th array, starting with 1.
10. Outside the XML_EXTRACT() function, apply TRIM() to remove white space characters, as well as two REPLACE() functions to remove new line / carriage return characters (ASCII codes 13 and 10) which can sometimes be part of the field. Then, wrap all of these in a CAST() statement to convert to an integer, since SharePoint lists use an internal ID field which is numeric.
11. Add a WHERE clause that uses the same extraction code and compares to not equal an empty string. This ensures the query does not break when an element does not exist.
12. After writing the code to extract the 1st element, add a UNION clause, then copy and paste the same code but change n to 2 for the second element. Repeat all the way to 9 elements (or more if needed).
13. The final query should look like this:
    

    SELECT 
    "PeopleGroupMembershipLInk"."ID" AS "PeopleID (PeopleGroupMembershipLInk)",
    CAST( REPLACE(REPLACE( TRIM(XML_EXTRACT("PeopleGroupMembershipLInk"."GroupMembershipId", '/Group_x0020_MembershipId/element[1]')) , CHAR(13) , ''), CHAR(10), '') AS INTEGER) AS "GroupId (PeopleGroupMembershipLInk)"
FROM "rest"."People" "PeopleGroupMembershipLInk"
WHERE REPLACE(REPLACE( TRIM(XML_EXTRACT("PeopleGroupMembershipLInk"."GroupMembershipId", '/Group_x0020_MembershipId/element[1]')) , CHAR(13) , ''), CHAR(10), '') <> ''

    UNION

    SELECT 
    "PeopleGroupMembershipLInk"."ID" AS "PeopleID (PeopleGroupMembershipLInk)",
    CAST( REPLACE(REPLACE( TRIM(XML_EXTRACT("PeopleGroupMembershipLInk"."GroupMembershipId", '/Group_x0020_MembershipId/element[2]')) , CHAR(13) , ''), CHAR(10), '') AS INTEGER) AS "GroupId (PeopleGroupMembershipLInk)"
FROM "rest"."People" "PeopleGroupMembershipLInk"
WHERE REPLACE(REPLACE( TRIM(XML_EXTRACT("PeopleGroupMembershipLInk"."GroupMembershipId", '/Group_x0020_MembershipId/element[2]')) , CHAR(13) , ''), CHAR(10), '') <> ''

UNION
...

Connect the Bridge Table

1. Ensure the bridge table (custom SQL query) is connected to the parent table by using the same ID field. In the example above, that means: People.ID = PeopleGroupMembershipLink.PeopleID
    
2. Drag the child table into the data source. In the TrackMaster example, the child table is "Group List."
3. Link the bridge table (custom SQL query) to the child table via the child table's ID field (in this case, Group ID).
    
4. In a new Sheet, drag some fields from the primary and child tables to test the relationship. There should now be a row for each combination of parent + child.
    

Notes

• This same process can be used for listing individual values from a multi-valued choice or managed metadata (term) field, with a few differences:
  • For multiple choice fields, there is no child table. The bridge table is also a child table, and the element will contain a value instead of an ID.
  • For multi-valued metadata (term) fields, the bridge table also becomes the child table, but the elements contain both IDs and values. To get the label without having to link to the term store table, simply change /element[n] to /element/Label[n].
• When publishing the data source to Tableau Server, the option to publish it maybe disabled in the data source screen of Tableau Desktop. However, you can still publish the data source by going to any sheet, right-clicking the data source name, and selecting Publish as the option is not disabled there.

Resources

• CData JDBC Driver - String Functions: XML_EXTRACT()
• Tableau Driver Download - SharePoint Lists JDBC Driver
• Tableau Desktop - SharePoint Lists JDBC documentation

About the Author

Gabriel Mongefranco is a Mobile Data Architect at the University of Michigan Eisenberg Family Depression Center. Gabriel has over a decade of experience in data analytics, dashboard design, automation, back end software development, database design, middleware and API architecture, and technical writing.   |   |