Benefits/Drawbacks of using Custom SQL?

Is there any advantage/disadvantage of using Custom SQL? I’ve recently started using the CustomSQL and just find it much easier to work with. Thinking about two things in particular:

  • Calculated Fields using the UI v Doing calculations in the SQL Query
  • Joining tables using the UI v Joining table in the SQL Query

I don’t think I’m familiar enough with databases to know the performance impact of using CASE in the SQL Query vs IFELSE in the calculated field.

hi @Stephen_Vines,

if you find it easier to work with custom SQL, it could be great advantage for QuickSight, as it prevents from doing all the calculations configured in calculation fields on analysis when loading the dashboard. Joining tables on UI vs joining tables in custom query could be the same or similar in general, as QuickSight generates a query behind when configured in UI and execute to the data source.

kind regards,
Wakana

2 Likes

Hi @Stephen_Vines

As @Wakana said - in a sense it comes down to what’s easier for you, your skills, your eco-system and your team.

A few things to consider.

  • When doing custom SQL, SELECT statement is sent to source databases and executed there. You have available at your disposal all functions available natively in the source db to do calculations and transformations. The time it takes to execute depends on the resources in the source DB.

  • When doing transformations/calculations in Calculated fields they will be executed differently depending on if the Dataset is SPICE or DIRECT QUERY.

    *DIRECT QUERY - All calculated fields are pushed down to source DB when analysis/dashboard is being rendered.
    *SPICE - Calculated fields are either computed at time of SPICE refresh or when loading dashboard. If it contains an aggregation or parameter its computed when rendering chart. If not it gets computed at the time of Dataset refresh.

Below blog post comes in handy by providing additional detail while highlighting ways to optimize your dashboard.

Thanks!
Ramon Lopez

1 Like