SQL is nice way to get your data in shape, but when using HAVING, ORDER BY and GROUP BY in Tableau doesn't handle it very well. There are built-in Tableau features for sorting data so please use them if you don't want to have performance issues
Thanks Hrvoje for your response.
We are using bigdata as a datasource, want to use LIVE connection and restrict the data in custom SQL due to huge amount of data (approx. 5 million for 1 month after aggregation).
Dear Santosh - Try the Latest Version of Tableau 9.3 released on 23rd March 2016. In that you can pass Parameter in the data extract SQL.
A combination of union all , along with a Flag on page View, or Visit can be an answer to your question
This could be your custom sql
SELECT top 10 user_id as "Top 10 Visitors", SUM(page_views), SUM(Visits) ,' PV' as Page_view_Visit Flag ,
GROUP BY user_id,'PV '
ORDER BY sum(page_views ) DESC
SELECT top 10 user_id as "Top 10 Visitors", SUM(page_views), SUM(Visits) , 'V' as Page_view_Visit Flag
GROUP BY user_id, 'V'
ORDER BY sum(visits ) DESC
And in your worksheet you can have a parameter on page_view or visit flag that if selected will eliminate the 'other' data set and the remaining one will be your answer.
Hope it helps.
Thanks for your response.
Currently we have 21 metrics selection and it may grow up. Using UNION ALL for 21 metrics will impact the performance. We are using Hadoop Hive database with huge amount of data.
Manideep, I will check the features in 9.3. Thank you!
2 of 2 people found this helpful
I can't think of a change in 9.3 that will help, but upgrading is always a good idea.
Why don't you try something like:
ORDER BY CASE WHEN <parameter>='pageviews' THEN SUM(pageviews) ELSE SUM(Visits) END
I don't know the exact syntax for your database or whether it will allow an expression in the ORDER BY clause, but it should work on many databases. It might still be very slow.
You can do the same thing using a calculation directly in Tableau and not use custom SQL
IF <parameter>='pageviews' THEN [pageviews]) ELSE [Visits] END
Then sort your user_id by the SUM of Calculation1.
Some recent improvements (in 9.2, I believe) will let Tableau remove the IF/THEN/ELSE from the query and likely make it run quite a bit faster.
That's a great logic. I will try both the options you suggested. Seems first logic should meet my requirement.