3 of 3 people found this helpful
Is there a reason you're using Custom SQL as opposed to connecting to the table itself. It seems like here if you connected directly to the table/view vETSIYoutubeKPIS with tableau's SQL Connector you can apply a data source filter on the testid. This will then only bring into tableau the testid's you are looking for. Further the grouping bit is not needed in tableau unless you're aggregating something before bringing it in. In this case it does not seem you are. Tableau works fantastically with the underlying data without the need for pre-aggregation (in most circumstances). What's your goal for the group by here?
1) Connect to the table/view directly
2) Apply the datasource filter in question on testid
3) If for some reason you only want address field (I'm assuming there is more) then you can select all of the extra fields and choose to hide them.
Thanks for your reply. Good questions! :-)
I was using a Custom SQL because I was part way through my own testing / investigating on the poor performance. To start from the beginning, here are the flow of events...
1. I created a data source pointing directly at this view called 'vETSIYoutubeKPIS'. I found performance issues i.e. doing anything would take ~2minutes to return data.
2. I then inspected the view's SQL statement and saw there were a lot of complex joins etc so I suspected perhaps it was just a slow view to work. So I proceeded to tune the SQL statement.
3. Meanwhile, I traced the vendor application that was issuing SQL statements to achieve the same result & saw that it was simply executing "(@P1 bigint)Select * from vETSIYouTubeKPIs where TestId = @P1" where @P1 is just a variable parsed in, so I executed this ("SELECT * FROM vETSIYouTubeKPIs WHERE TESTID=12345") directly on the DB and it ran within 1s (wow!!)
4. After seeing it run so quickly, I tried to mirror this in Tableau. Using my data source created above (pointing directly at the view), in the worksheet I dragged TESTID to the filter card & put in 12345. All good so far. They I dragged a dimension called "E2EUserExp" in to the worksheet & it took 125s to execute. Below the SQL Tableau issued to the database...
SELECT [vETSIYouTubeKPIs].[E2EUserExp] AS [E2EUserExp]
FROM [dbo].[vETSIYouTubeKPIs] [vETSIYouTubeKPIs]
WHERE ([vETSIYouTubeKPIs].[TestId] = 12345)
GROUP BY [vETSIYouTubeKPIs].[E2EUserExp]
Obviously Tableau generates it's own SQL and is slightly (just!) more complex then the simple SELECT * before. Now each time I run this it takes 125s to execute. Interestingly, it always takes exactly 125s to execute no matter
Upon further testing, it'd appear that that the simple SELECT * FROM vETSIYouTubeKPIs WHERE TESTID=12345 statement executed directly against the DB sometimes produces fast results but most of the time is actually very slow... so at this point, I'm suspecting more of a database tuning issue rather than Tableau related problems.