Our data warehouse is present on vertica columnar database. Normally we segregate our aggregates in to daily, weekly & monthly tables.
Currently we use combination of custom sql and parameters to query daily or weekly or monthly tables based on user selection from parameter. Parameter has options like Daily , Weekly , Monthly. Based on user selection we query the appropriate daily/weekly/monthly tables by replacing table name like below in custom sql :
select * from TABLE_NAME_<PARAMETER_VALUE> i.e DAY / WEEK / MONTH.
Recently we have gone through vertica best practices document for tableau and it is recommended by them to use database views or drag & drop tables for good optimal performance.
Creating database views or dragging & dropping tables will not help in our case since our tables are segregated based on date granularity.
Can anyone help on achieving this by optimal method ?
I'm not sure of the request here because it sounds like you are unable to utilize the best practice recommendation of Vertica. How is the performance of the current designed method with the parameters?