The Client’s Challenges
Our team was developing a set of interactive, real-time dashboards for a well known facilities services client. One of the dashboards was taking 40+ minutes to process and retrieve the results using a live Tableau data model connection. This dashboard was intended for our client’s internal users, who have access to essentially all the data available in the system. This level of access also included our client’s customer users, who only access data related to their specific businesses.
This requirement called for the enforcement of multi-tenancy into the dashboard. This entailed that each time a user logged in, the dashboard had to filter out only the data he/she had been authorized to access. Each time a user logged in, a link of accessible locations and elaborate trail of selected drop-down options were making the underlying SQL query run for an exorbitant time. This was significantly affecting the dashboard’s performance, and rendered a very slow response time. We were faced with two primary challenges with respect to these dashboards:
- Resolve the Tableau security requirement efficiently
- Improve the dashboard response time to less than 5 seconds
The Approach and Solution
Our team came up with several prospective solutions to handle the Tableau user security and performance optimization requirements. We tried and tested the below options before integrating the final solution:
- Use a security view to establish user access at the customer and location levels, and also create an extract of the data.
- Separate the internal dashboards and customer-facing dashboards.
- Use two separate tables in the process. One to identify the user as ‘internal’ or ‘customer’, and the other to identify the locations for customer users.
- Using the join condition functionality added in Tableau 10.2, conditionally join the security view based on user access.
- Toggle between data sources using a parameter that dynamically populates based off of a user’s global access indicator.
After carefully weighing the pros and cons, we decided to go with option 3. Additionally, we built a customized SQL query (with string concatenation & union) specifically intended for our dashboard requirements. We also paired it with data source filters, such as restricting the amount of data users could see in a given time period. We then created an extract of this custom SQL.
How to Increase Tableau Dashboard Performance – The Step by Step Process
Use two separate tables to resolve the security and data replication problem, discussed in option 3. Below is a user ID mapping table which determines if a user level is customer or global. For global users only, we set the “user_IDE” field to “internal”. This table is filtered by actual user name (User_IDF) in Tableau. The customer user IDs are shown as is.
The second table is a security table (Tbl_Rel_Security). All global users are entered as “internal”, while customer user IDs are still shown as is. This table is joined to the main fact table on ‘customer_ID’ and ‘location_ID’.
Write and deploy a custom SQL Query (with string concatenation & union) to handle the specific security requirements for each dashboard.
When published on our test servers, the solution brought down our dashboard response time to under 3 seconds. By detecting this problem in the early stages and tailoring the solution to the situation, we were able to deliver best experience of faster dashboard performance to our client.