What is it?
Custom SQL is the SQL editor within the data model that allows you to write the query needed to produce the data for Tableau instead of using the Tableau drag-and-drop data model interface.
...
- Power, flexibility and control: Firstly, it places the output in your hands, to build just what you need using the power and flexibility of SQL which, when combined with the correct drivers, allows you to use the full potential of the SQL subset of your database
- Highly optimised queries: You can write, test and optimise the query needed to obtain the data, this is in contrast to the data-model where you can neither see nor alter / influence the query that Tableau generates
- Parameterised queries: Unique to Custom SQL, you can add further control elements to the query that operate ahead of filters to make changes from filters to query control in order to achieve greater flexibility
- Additive calculations: Increase overall performance by pre-materialising additive data in the result-set.
- Improved OLAP experience (SQL Server Only)
And now, the long-winded bit...
So lets look at these points in a little more detail:
...
Yet you would only be interested in dim_Product and dim_SalesDivision; like an SQL statement, Tableau will also need to process all the joins before it can reach the data you require.
So, what is the benefit to me in writing SQL rather than using the data model, especially if both are processed at the same time?
As far as I can tell, they both achieve the same result yet the Custom SQL method takes longer to build.
...
- Using parameters as filters to control the query based on the parameter selection
- Create new performant control's such as case statements
- Dynamically select data based on how users want to cut the report without exposing too much information
- And to a lessor degree, accessing functions such as bitwise operations
4. Additive ( and semi-additive (Row-level) Calculations
Building on the Paremetrised parameterised querying above, writing SQL enable's analysts to create additional data from calculations. This will be discussed in further detail in the calculations article however, additive calculations such as case or "if..then..else" logic calculations or, are not ratio calculations will normally be built as a calculation to be executed at run-time which will add to processing time and unnecessary network load, however, by including them in your query, you can materialise the data which can add further benefits in regarding aggregated calculations.
...