Any inputs on the above please ?
I have a few thoughts and suggestions that might help you.
- 1/2 million rows is actually quite small in the scheme of things. Consider taking an extract of all years. I just pulled in 11.2 million rows of financial data and Tableau can parse that data easily. That said, I am using an extract for my data, and Tableau will never be able to run faster than your underlying data source. Extracts tend to be fast, sometimes live connections to databases can be slow.
- Consider what it means for your organization to be live. You can run extracts on a 15 minute refresh schedule; your data would never be more than 15 minutes old and you could leverage the power of an extract.
- If you do absolutely need your data source to be a live connection, consider moving away from custom SQL. Custom SQL significantly slows the performance capabilities of Tableau. Either use Tableau's built-in data prep features OR build a materialized view in the underlying database. Doing so will significantly improve performance.
- Finally, instead of using a date parameter, use a date filter.
For more information about performance and keeping your workbooks running smoothly: 6 Tips to Increase Dashboard Performance
Hope this helps!
Hi @Nicholas Hara,
Thank you for you pointer, I am certainly with you on #1.
#1 & #2 . Extracts are best, but in our case cannot use them as the requirmenet is that if a user makes changes to a value then all the calculations using it should be updated instantly. Basically they are trying to build a web application but just trying to fit in tableau to fit in is my feel, I also gave them n option of refreshing n extract every 15 to 30 mins n that tableau is not a live streaming tool so this is a way around it. But live is wat they want so have to restrict the query by year n to do that I have to use a paramter which is then a static list as it wont update once a new year data is added.
#3. We have a materiliazed view built in but the way data is going to be stored it wud need custom sql for the joins and subqueries. we are able to get a response time of about 10 secs while changing filters with live connection with a year worth of data. Not so great as these reports are going to be embedded in web application and so they wont run at the speed a web app data table cud responded.
#4. I have to use paramter as otherwise how do I limit to show only one year ( year selected data at the first load) as otherwise if I do not add
WHERE year = <Parameter>.<Year> in by custom sql then initially it tries to get all the years about 5 million rows and gives the error that out of temp space.AS the view also have all this data in it.
Any way to limit the query to show only one year data on the initial load without using parameter?
1 of 1 people found this helpful
Dipti -- When you say "initial load", you're talking about how the sheet's table loads, right? Not your data extract.
If so, I think putting the YEAR filter in-context will limit what gets read into the sheet up front.
Well that's the only way I can think of doing it without parameters.
Sure thanks so much... it did help though!