We use use Tableau and host our data locally on a SQL Server database. We have 3 years worth of data and 55,000 enrollments. I've run queries that have taken hours to run, which typically occur when I'm joining many tables together or using the requests table. Some solutions I've landed on as I've become more proficient with Tableau and familiar with the circumstances that initiate a long query:
- Use SQL if you know it. By using a custom SQL query you can reduce the amount of data being processed by only bringing in the fields that you need.
- Chunk your data into multiple data sources. At one point we were trying to do a data analysis on our districts use of assignments, quizzes, discussions, announcements, and modules. It was simply too much data to run in an efficient time span. On top of that, after the query ran, I'd find an error in a calculation and have to rerun the query again and wait to see if the new calculation was correct. What I've come to do instead is create a separate worksheet for each of the Canvas items we wanted to analyze, in each case connecting the worksheet to a new data source. It's all still connected to or SQL Server, but I can run a separate query for each worksheet that only queries the data for one feature at a time. I then build out a Dashboard with the visualizations from each of my worksheets so that everything resides on one page. When I load the dashboard, multiple queries are being run simultaneously, but the full dashboard loads in under a minute, as opposed to an hour plus when I tried to place everything on the same worksheet using one query.
- The request table is huge. If your using this in your query, it's going to take a long time. In Tableau, on the Data Source screen, I add a filter and to restrict the range of the timestamp field. If I'm testing something, I usually pare this range down to a day so that I can work with the logic. Once I feel my calculations make sense and that I have the data I want, I expand that time frame and just let that query run while I do other things.
