Many asked us to introduce SQL support in Tableau CRM (TCRM), and we heard you. We’re very happy to announce that TCRM now supports SQL in dashboards and lenses. Now you have the option to create powerful visualizations using SAQL(Salesforce Analytics Query Language) and/or SQL in TCRM.
You might be wondering why SQL when we already have SAQL in place. Or when to use SAQL, when to use SQL. Or even how to use SQL in TCRM dashboards and lenses. Are there any limitations around using SQL in the dashboard? This blog is an attempt to answer these questions and show you some examples of SQL in action.
SQL is a standardized, user-friendly language with well-defined standards and semantics. Most dashboard developers are very familiar with writing SQL. There is also a lot of community support available to look at standard examples to achieve any use case. Supporting SQL also makes it easier to build integrations as it’s a standard language. The preferred method for building analytics still remains point and click. However, when point and click are not enough for our advanced power users, instead of learning SAQL semantics, we thought onboarding to TCRM would be easy with a familiar language. The performance characteristics remain the same both across SQL & SAQL.
SQL for TCRM follows the ANSI SQL standards and semantics. The feature set in the ANSI SQL standard is very rich and extensive, with many capabilities. We do not have the full ANSI features at the moment, but we are adding more and more standard features to it every release. The feature set is rich enough to rewrite most SAQL queries as SQL already.
Apart from the standard features, we also support extensions that are Salesforce specific and seamlessly support native CRM features. SQL queries will respect the security settings defined by sharing inheritance. Another example of a native CRM feature is the support for custom fiscals. SQL queries will support the fiscal calendar defined in Salesforce and provide capabilities to filter and group by fiscal periods.
How to use SQL with TCRM
A developer can use SQL as a query language to build both lenses and dashboards in Tableau CRM Analytics Studio. In addition to that, SQL can also be used to build applications and integrations by using Tableau CRM REST API & Apex support. To use SQL in those use cases, just specify the query language as SQL in the request and function parameters
So what about data prep? SAQL is supported in dataflow to build formulas within the computeExpression and computeRelative Nodes. These nodes will not be enhanced to support SQL. However, the formula node in Recipe already supports SQL.
Tableau CRM Analytics Studio
To start using SQL in Tableau CRM Analytics Studio, begin by exploring a dataset. Once you switch to query mode on the explorer view, you will notice a default SAQL count query. We can change this to SQL, which displays the SQL equivalent count query as shown in the image below.
Once we have written the SQL Query, we can switch back to chart mode and save the dashboard. The dashboard is now saved with the SQL query. We can then again open the query mode to edit more SQL queries.
Now let’s try out some SQL queries on an opportunity dataset
Select, Group By With Aggregates
Let’s find the sum of your open opportunity amount for the product family accessories in various stages.
Here is the query for that along with sample results.
SELECT "Product.Product.Name" AS "Product Name", "StageName" AS "Stage Name", SUM(Amount) AS "sumAmount". /* Groups and Aggregates to display */ FROM "opportunity85"/* Dataset API Name */ WHERE "Product.Product.Family" = 'Accessories' AND "IsClosed" = 'false' /* Filter for product family and isclosed fields */ GROUP BY "Product.Product.Name" ,"StageName" /* Grouping columns*/ LIMIT 2000
As another example, If we now want to look at products with over 500M total opportunity amount, we can add a having clause to the above query.
Here is the query for that, along with sample results.
SELECT "Product.Product.Name" AS "Product Name", "StageName" AS "Stage Name",SUM(Amount) AS "sumAmount" FROM "opportunity85" /* Dataset API Name */ WHERE "Product.Product.Family" = 'Accessories' AND "IsClosed" = 'false' GROUP BY "Product.Product.Name" ,"StageName" HAVING SUM(Amount) > 500000000 /* Having clause that will filter aggregate */ LIMIT 2000
Now, if we want to further find the opportunity amount per stage, we can use subqueries to make an additional grouping as follows.
SELECT "Stage Name", SUM(sumAmount) as sumPerStage /* Group and aggregate on subquery result*/ FROM ( /* Sub Query */ SELECT "Product.Product.Name" AS "Product Name", "StageName" AS "Stage Name",SUM(Amount) AS "sumAmount" FROM "opportunity85" WHERE "Product.Product.Family" = 'Accessories' AND "IsClosed" = 'false' GROUP BY "Product.Product.Name" ,"StageName" HAVING SUM(Amount) > 500000000 ) GROUP BY "Stage Name" /* Group on subquery result */ ORDER BY sumPerStage DESC /* order on subquery result. The field name should match the alias in select clause*/ LIMIT 2000
We also support the standard SQL type Timestamp semantics for TCRM data type Date & DateTime. We use the EXTRACT functions as defined in the ANSI standard to access date parts of the Timestamp field.
For example, we can find the sum of opportunity amount per Year, Month with the following query.
SELECT EXTRACT(YEAR FROM CloseDate) AS CloseDateYear, /*Extract function for year of date*/ EXTRACT(MONTH FROM CloseDate) AS CloseDateMonth,/*Extract function for month of date*/ sum(Amount) AS "sumAmout" FROM "opportunity85" GROUP BY EXTRACT(YEAR FROM CloseDate), EXTRACT(MONTH FROM CloseDate) ORDER BY CloseDateYear,CloseDateMonth LIMIT 2000
Standard and Custom Fiscal Support
We expanded the ANSI standard extract functions to build SQL Extensions for both standard and custom fiscal calendars.
For example, if we want to find the sum of opportunity amount per fiscal Month in the fiscal year 2021, here is its query.
SELECT EXTRACT(FISCAL_YEAR FROM CloseDate) AS CloseDateFiscalYear, /* Extract function for fiscal year*/ EXTRACT(FISCAL_MONTH FROM CloseDate) AS CloseDateFiscalMonth, /* Extract function for fiscal month*/ sum(Amount) AS "sumAmout" FROM "OpportunityFiscal" WHERE EXTRACT(FISCAL_YEAR FROM CloseDate) =2021 GROUP BY EXTRACT(FISCAL_YEAR FROM CloseDate), EXTRACT(FISCAL_MONTH FROM CloseDate) ORDER BY CloseDateFiscalYear,CloseDateFiscalMonth LIMIT 2000
Another aspect of the SQL support here is standard null semantics for dimensions and measures. Most importantly, we fixed the SAQL limitation of dropping null groupings with this. You see the null grouping for Account source with its associated opportunity sum in the example below.
SELECT "Account.AccountSource",sum(Amount) AS "sumAmout" FROM "opportunity85" GROUP BY "Account.AccountSource" /* Includes null grouping */ ORDER BY "Account.AccountSource" ASC NULLS FIRST /* Nulls ordered either first or last*/ LIMIT 2000
I hope this blog gives you ideas on how you can leverage your existing SQL knowledge to build TCRM dashboards for your upcoming projects. The SQL features discussed here are limited, and the full list can be found in our product documentation. Comment below if you have some cool use cases or thoughts on SQL functions to add to Tableau CRM in the future.