Know Your Data at the Database Level
Understanding the impact that certain factors in the design of your database have on Tableau performance might give you information you can use to work with your database team to optimise data at the database level.
Enable support for referential integrity
Databases that support referential integrity support the Tableau Assume Referential Integrity feature, which improves the performance of inner joins. Joins cost time and resources to process on the database server. When you join multiple tables in a data source, Tableau uses functionality that is generally invisible to the user, called join culling. Join culling queries only the relevant tables instead of all tables defined in your join. Join culling only occurs where referential integrity is defined between tables. For more information, see Assuming Referential Integrity for Joins.
Make sure database permissions support creating temp tables
Does your database grant users permission to create and drop temporary tables, also known as temp tables, and does the environment have sufficient spool space for the queries being run? Tableau creates temp tables to help improve performance and add functionality. The temp tables temporarily hold information specific to a connection. If the creation of temp tables in the database is restricted, the overall performance of workbooks and views isn’t as fast as it could be.
Create indexes for tables
Index the tables in your relational database. To successfully index your data set, identify the fields that you frequently filter on and add them to the index. If you have a field that you use as a context filter often, consider setting it as your primary index. If you are working with Access tables that have more than 200,000 rows of data, consider setting indexes on the tables. You can learn how to do this by searching for “index” in the Access online help. You can officially store 2 GB of data (approximately 1-2 million rows) in an Access database, but it performs poorly well below this limit.
Many database management system (DBMS) environments have management tools that will look at a query and recommend indexes that would help.
Break up your data
You can often improve performance if you partition a large database table into multiple smaller tables. For example, you can create a cluster of Access tables that addresses specific subsets of your data.
Use a database server
If you have a lot of data, you might consider storing it in a database server, such as Oracle, MySQL or Microsoft SQL Server. The Professional Edition of Tableau can connect to these larger database servers.