Optimizing data models for Tableau dashboard performance
Learn tips on analytics methodology, data structure, and aggregation from the 2024 Tableau Conference session.
By Laurie Rugemer and James “Jay” Young
The announcements at the Tableau Conference this year centered around Tableau Pulse and AI, both of which require a strong data foundation and an understanding of how to use data to optimize business outcomes. In our conference session called “Optimizing Data Model for Tableau Dashboards,” we discussed steps that practitioners can take to increase dashboard performance that start long before development begins.
User adoption of Tableau dashboards depends on many things, but a lot can ride on how quickly the dashboards load and their overall performance. We’ll talk through tips to create performant dashboards by starting with the dashboard story and overall analytics methodology, then follow through to the data model and development choices.
Implementing a robust analytics methodology
Implementing a robust analytics methodology helps create efficient datasets for optimal dashboard performance. You may be thinking, “This isn’t a technical tip,” but we would argue that using an Align/Design/Develop methodology for Tableau dashboards starts to streamline what is needed and creates a robust plan that improves performance. An inefficient strategy with analytics is to attempt to build one dataset that answers all your business problems and can be used to build any dashboard. Building to purpose and having a clear idea of who you’re building for (personas) and why they need this (What questions do they want to answer? What actions will they take?) allows the developer to cut down on what needs to go in the dashboard dataset.
Once alignment is reached on the “why”behind the dashboard, then it’s valuable to spend time on the Design phase, creating wireframes that allow you to get stakeholder sign-off before beginning development, thus having a clear idea of what data is needed by working through the visuals on the wireframe. Possible tools to create these include Lucidchart, Paper, Miro, and Figma. Once the wireframe is approved, it is important to create a data mapping document and data model diagram that lay out the level of granularity needed, the data sources and fields needed, joins, and calculations. Having a plan before development allows us to build dataset purpose built and optimized for our specific dashboard.
Data structure
Dimensional modeling
Using dimensional modeling can significantly increase the performance of your Tableau dashboards, especially when utilizing live data connections instead of data extracts. Dimensional modeling reduces the number of columns in your primary fact table, focusing on primary and foreign keys to relate dimension tables with more granular details and string columns that can affect performance. The fact tables contain measurable data, such as quantities and prices. The dimension tables contain categorical data often used for filtering.
For example, in a retail scenario a dimensional model may be built on a sales fact table that contains a unique primary key, an order ID, product ID, customer ID, date, quantity ordered, and price. This table would then be joined to dimension tables that contain order details, product details, customer details, and (when using a best practices environment) a date table to limit the amount of date calculations needed to aggregate data.
Using a dimensional model increases Tableau dashboard performance by allowing Tableau to limit the amount of information queried each time a visualization is loaded or updated. When a filter is applied, instead of querying a large, wide dataset with many columns of heavily granular data, Tableau can instead query the fact table and then query only relevant details from the related dimension tables connected to it.
Tableau’s logical and physical layers
Tableau’s data model is built on two distinct layers: the logical layer and the physical layer. Each consists of underlying tables, but the distinct difference is in how they are related. The physical layer, which is made up of physical tables, is related using joins, much like a typical SQL model. The logical layer (made up of logical tables) is related using Tableau’s relationship model.
Why does this matter? Couldn’t I just connect all my tables in the physical layer, or the relational layer? Yes — you certainly could. But that may not always be the most efficient or performant method of creating a data model in Tableau.
Physical tables (accessed by dragging a single table into the Tableau data model and then double-clicking it) are best used to join data into a single, flat table with a specific level of granularity needed for your data model. Continuing our retail store example, you might use a physical table to join relevant data about sales into a single logical table.
Logical tables can then be connected using relationships. While relationships function like joins, they offer more flexibility compared to joins in the physical layer. Once you have created your sales table and a revenue table, you could join these two logical tables using a relationship to create a comprehensive picture of your retail store’s profits.
Aggregation
Using row-level datasets for many dashboards can decrease performance when our visualizations are aggregated views. When considering the level of granularity and aggregation needed in a dashboard, it can be wise to build a dataset that is already aggregated to that level. For example, if I’m building an executive summary view of monthly revenue and margin, I can aggregate the data to the monthly level and by business segment and create my sum of revenue and monthly change calculation within the dataset for this summary dashboard, thus cutting down on the aggregation and calculations that need to happen every time the dashboard loads. Think about time cadence, segments, and calculations — can we build our dataset to match those?
Filtering
Similarly, dashboard performance can be improved by filtering to just the data we need. If my executives only need to see the last 18 months of revenue data, why give them the last 10 years? I can filter before bringing the data into the dashboard, thus cutting down on filters that need to run within the dashboard. Also, ask if a timestamp is needed to drill down to specific points in time. If not, get rid of timestamp! Also, ask if there are any columns in the dataset that aren’t being used in the dashboard. If so, get rid of them!
Table calcs and level of detail (LODs) calculations
Calculations processed within the dashboard can slow down performance. It is way more performant to materialize calculations in the ETL (extract, transform, and load) process when possible rather than use dashboard queries for calculations. For example, in Tableau Prep it is now possible to push multi-row calculations to compute table calculations in the physical table (like difference from, percent from, moving calculations). It is also possible to create Fixed LODs, Rank and Tile calculations within the dataset instead of the dashboard (like top/bottom N, distributing rows into a bucket field).
Extract refresh strategy
Planning an effective and efficient refresh strategy is the icing on the cake that is your newly optimized data model. After all, spending so much time and effort building a performant dataset would be meaningless were it not kept up to date with fresh and relevant data.
The first question you should ask: Is there a business need for real-time data for decision-making? If so, then use a live connection to your data source(s) so that your consumers always have access to the most recent data. Otherwise, Tableau dashboards are most performant using data extracts.
But with data extracts comes another question: What refresh schedule should I use to ensure my data is fresh and relevant? And should I use full refreshes or incremental refreshes?
The answer: It depends. If you’re working with a relatively small dataset, then setting a cadence of full extract refreshes will be fine. However, if you’re visualizing a massive amount of data, you should instead schedule frequent incremental extract refreshes paired with a regular full refresh to maintain data integrity. For example, set an incremental refresh daily (or hourly if you need near-real-time data) and a weekly full refresh.
Even when using incremental refreshes, running full extract refreshes is crucial. Incremental refreshes will pull in new data added since the last extract refresh, but they will not update existing data. If any existing data was modified, it will not be updated in the Tableau extract without a full extract refresh.
Optimizing for Tableau Pulse
All the tips mentioned in this blog become even more important when making the transition to Tableau Cloud and Pulse. The power of Tableau Pulse lies in giving bite-size, captivating, and meaningful metrics to our stakeholders so that they can ask insightful questions of the data and take action easily.
Because Pulse development requires immediate choices related to measures, time dimensions, prior period comparisons, and filters, having the necessary dimensions and date fields — as well as understanding the essential metric calculations needed — becomes especially important in the creation of a dataset used within Pulse.
Starting with a strong methodology that guides developers to create purposeful dashboard datasets will help streamline the development process and improve performance. There’s nothing worse than creating a dashboard with all the bells and whistles that never loads and no one uses. By following these tips, you can create a better experience for your end users.
Slalom is a next-generation professional services company creating value at the intersection of business, technology, and humanity. Learn more about our partnership with Tableau and contact us to discuss your migration journey.
