Optimizing Your Association’s Dashboard Performance - Part 1
I recently had a conversation with my colleagues about the best practice for creating calculated fields based on source data. More specifically, the conversation revolved around whether the Extract Transform Load process (ETL) for a data mart should perform the calculations or if they should be done in the data visualization tool, such as Tableau. Even though calculations can be done in Tableau, is it optimal to manage this important business logic within the visualization software and is it efficient? The short answer is no. These are our conclusions about how your association can implement best practices for calculations in the data mart.
When a Tableau dashboard loads a visualization, the calculations run at the time of the request. Not every calculation, but ones that can change, such as those based on a parameter or an aggregate function (sum/average). Tableau has a robust and complex extraction engine and memory usage architecture to emphasize speed, but the bottom line is that the calculation is subject to re-calculation many times during an individual's interaction with the dashboard.
That potential issue convinced us that we needed to explore other options, but just to make sure we queried one more expert. We asked our good friend Google if Tableau calculated fields are a best practice. We found hundreds of articles and even videos on how to create them, yet not one noted that this was an optimal practice.
There are a couple of distinct advantages to performing calculations in the ETL vs. Tableau. First, this ensures that the calculations only happen one time. Secondly, your association can leverage technologies that are designed to handle massive amounts of data in memory, like SSIS, because doing it in memory is faster. The ETL process allows you to Extract, Transform and Load your data. The data is extracted from your data source and loaded into the data mart. The transform step is where we translate values, sort, transpose and aggregate. This is the step where we should perform the calculations.
An example of a common calculation is the member type someone had when they attended an event. This is a common one we see from our clients. Tableau could handle that calculation, but every time someone went to view their data visualizations, the calculation would be performed and it would slow the response time of the visualization. Having this calculation performed during the Transform step will improve the performance of the data visualization and the user experience
In the end, we recommend using Tableau calculated fields sparingly. For example, there might be a situation such as blending of disparate data sources where the only time the data can come together is in Tableau, which makes it the only place a calculated field can reside. Otherwise, it is all about the ETL for data mart calculations!