1 Reply Latest reply on Dec 2, 2014 11:12 AM by Shawn Wallwork

    Using Custom SQL to convert daily database to weekly?




      I am using Custom SQL to create a dashboard. It is a daily dataset from which I create a Tableau data extract. I created a calculated field that compares the values of a score from a user-chosen start date to a user-chosen end date. I then use a bubble chart to compare changes for the various individuals. However, w/ 30 million + rows the calculation time is very slow when the start and end dates are far apart.


      To get around this speed issue I am trying to reduce the number of rows I am importing by converting the frequency of the data from daily to weekly. Is it possible to adjust the custom SQL query so that I only import the maximum day of the week corresponding to the most recent date? For example, say the maximum date in my dataset is Friday, Nov. 28. I would like to then only import previous dates that fall on a Friday. Can this be done through custom SQL?


      Wondering if anyone has dealt with similar issues and would love any feedback/advice. Thanks in advance!

        • 1. Re: Using Custom SQL to convert daily database to weekly?
          Shawn Wallwork

          The best advice I can give you is to do the calculation in the underlying database. When you use Tableau's Custom SQL queries, Tableau wraps your query in it's own query, in effect turning your query into a subquery. This is one reason it is so slow. You're better off doing a CREATE VIEW in the database using a GROUP BY to aggregate your date field, and then connecting to this view.


          The other option you should consider is to make a straight live connection, build your viz and then do an extract using the aggregation option. (That doesn't have a week option, so that might not work.)


          Whatever you do, doing an extract will definitely speed things up.