Hi there Tableau Community,
My team and I have spent months trying to fine tune our Tableau Server to optimize it's ability to store, access and extract data for our BI users. We've tried every possible approach we can think of and discussed solutions with Tableau, our DBAs and a Tableau/ Vertica consultant. No consistent answers from anyone so I'm turning to this community. Here are the following solutions we've tried to get our Tableau - Vertica environment running smoothly:
NOTE: We're working with massive amounts of data. 46+ billion rows in Vertica views is a standard table we use for our business. As per Tableau's advice, we created multiple, extensive ETL processes to aggregate these tables into more digestible tables. This detracts from the "on-demand" data that we would like our users to have access to and has added 50+ new tables to our catalog. We cannot realistically create ETL processes to create tables for use in each distinct Tableau tool because this would muddy up our database environment further.
General Development Process:
- Create development workbook and build data source.
- Push data source to server
- Connect to server level data source in production workbook.
- Push production workbook to server when ready.
Different Development Approaches:
- LIVE Connetions
- APPROACH: Tableau server level data source w/ live connection to Vertica; Calculated fields in server level data source
- APPROACH: Tableau server level data source w/ live connection to Vertica; Calculated fields in workbook connected to server level data source
- EXTRACT Connections
- APPROACH: Tableau server level data source w/ extracted connection to Vertica on refresh schedule; Calculated fields in server level data source
- APPROACH: Tableau server level data source w/ extracted connection to Vertica on refresh schedule; Calculated fields in workbook connected to server level data source
In all of these approaches we are seeing varying levels of issues on our Vertica side. Our Vertica environment has 80+ direct users. Our BI team is the access point for anyone else in the company so we are really trying to balance that load and make sure our Vertica system is not over-loaded by the queries to Vertica from Tableau. Tableau sends off a dozen queries for any one click in the LIVE workbooks. In the EXTRACT workbooks, Tableau still seems to send off queries to Vertica for the calculated fields and hold them in temp tables on the Tableau Server.
If anyone has any light to shed on the best practices for creating server level data sources, please share! Thanks!