SQL

For production development, it is preferred to do most of the ETL and especially aggregation in the database so that data being fed to Tableau is much smaller.

This is a key performance requirement since the database engine is usually more powerful.

Datetime Slicing

#-- Show Only Time at Axis
#-- Set date as constant so that Tableau can group by time only
SELECT to_char(DATE_TIME, '1900-01-01 HH24:MI')::timestamp
FROM table_nm


#-- Time Slice
#-- change 900 to appropriate seconds, e.g., 15 mins == 15 * 60 = 900
#-- convert timestamp to epoch, divide by interval desired in minutes then round to get the desired interval
SELECT to_timestamp(floor((extract('epoch' from starttime) / 900 )) * 900)
FROM tablename

Restrict Datetime Range

#--Filter Date Range
WHERE trunc(DATE_TIME) BETWEEN :strt_1_tme and :end_1_tme;


#--Filter Time Range
WHERE (to_char(DATE_TIME,'hh24:mi') between :stme1 and :etme1))


#--Filter Fixed Time Range
where transactiontimestamp between now()::timestamp - interval '24 hour'
      and now()::timestamp


#--Filter Latest Data by DateTime
where transactiontimestamp = (select max(transactiontimestamp) from joins limit 1)

Gap Fill

Gap Filling is very important to fulfil the aesthetics of a visualisation. Lets say a barchart is supposed to have 1 dimension with 10 attributes to show. But the dataset only contain 5 at this time. The barchart will only show 5 bars.

This is bad for aesthetics as the bar width will resize whenever a new row show a new attribute of that dimension.

Other attributes will not show as none of the rows have those data

To prevent that, we can have a union in sql to add all possible attributes in.

select col1, col2, dimension from table1
union
select null as col1,
        null as col2,
        dimension
from mapping_table

Other attributes will show now.

Another time of gap fill occurs when we have a visualisation with more than 1 dimension. The previous code will not do as it will produce this. A example is a heatmap shown below.

All the nulls will be filled only for 1 of the dimension.

A cross-join for the new table for union in SQL is necessary for this case to fill every single gap for both dimensions. Now it is perfect!

#--generate_series() is exclusive for postgresql

select col1, col2, dimension1, date_dimension2 from table1
where date_dimension2 between '2007-02-01'::timestamp and '2008-04-01'::timestamp

union

select col1, col2, dimension1, date_dimension2 from

#--table1 for cross join
(select null as col1,
        null as col2,
        dimension
from mapping_table) a

cross join

#--table2 for cross join
(select date_trunc('day', dd)::date as date_dimension2
 from generate_series
        ('2007-02-01'::timestamp,
         '2008-04-01'::timestamp,
         '1 day'::interval) dd) b

Parameter with (ALL) option

One of the disadvantages of Parameters to Quick Filters among other things is that it lacks a select ALL option. However, we can manually code for this in SQL.

#-- with (All) option

      #--either define a table with possible and (All) attributes, and define in WHERE clause
with filter_week AS (select 'Weekend' AS weektype, 'Weekend' AS param
                        UNION select 'Weekday' AS weektype, 'Weekday' AS param
                        UNION select 'Weekday' AS weektype, '(All)' AS param
                        UNION select 'Weekend' AS weektype, '(All)' AS param)
SELECT * FROM DBO.table_nm a
WHERE WEEKTYPE IN (SELECT weektype FROM filter_week WHERE param=:week);


      #--or just entirely in the WHERE clause as below
WHERE (((time_belt='Morning' OR time_belt='Day' OR time_belt= 'Evening') AND :timebelt='(All)') OR (time_belt=:timebelt))