In our last blog 6 Best Practices on Tableau to Optimize Reporting – 8 Second Rule we shared the best practices which needs to be followed at Tableau Platform level to optimize the report generation and achieve the 8 second rule. Today we will talk about the best practices which needs to be followed at the Database level to achieve the desired target.
Top 5 best practices to be followed on Database queries front to achieve 8 seconds rule are:
- Queries are written in native SQL which should execute within 8 seconds and the result is stored in a summary table and this summary table will be used by Tableau to display the data.
- Fact tables should be strictly parsed only and once as Fact tables are the biggest tables. Dimension tables in extreme cases can be parsed more than once. The aim is to reduce multiple parsing of the same table.
- For time dimensions where the same calculations need to be performed across MTD, QTD, YTD, T3M, N3M, PY, CTE’s are used to generate the required dates and the calculations are matched to the dates.
- Analytical functions LEAD/LAG are used in time dimension calculations as this help reduce the no. of lines in the query and hence also improves the overall performance.
- There is always a time frame usually the last 2 years while filtering the results. This helps limit the no. of rows.
You can also check our complete case study Tableau Reports Optimisation to get a full understanding of what was done.
It was great to share this knowledge and experience with you. In our next blog we will deal with the second problem we discussed in our first blog. Top Reasons to Choose TABLEAU as a Reporting Platform and Issues associated with it.
