Introducing SQL Support for Tableau CRM
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.
Why SQL?
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
Having
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
Subquery
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
Timestamp
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
Null Semantics
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
Conclusion
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.
Written by
Is it possible to join different tables in the same query? that would really be amazing. Doing so in SAQL is really a pain
Not yet. We are working on supporting different types of joins in the next few releases
cross-apply joins please!
Great article and great examples. Supporting SQL especially allow me, to recruit additional data analyst, who are not expert in T-CRM but can contribute to our progress – Great addition !
This is exactly our goal. Leverage the vast SQL knowledge that data analysts already have to make it easy to onboard to TCRM
This is a great addition to TCRM.
Very cool stuff… looking forward to flexing my SQL muscles in Tableau!
How can we write or involve binding expression/formula in sql
There are new data serializations functions to support this.
This is a great document.
Do you know what I should do if I want to put together the year, month, and day as follows?
I am getting an error when I try to execute the SQL in the documentation.
https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_sql.meta/bi_dev_guide_sql/bi_sql_date_project.htm
—
SELECT CloseDate
FROM “OpportunityFiscalEMTimezoned”
LIMIT 1;
—
Error message
“Date field ‘CloseDate’ missing canonical dimension. Enable CF field indexing to support this query in SQL.”
You ran into a bug. The query you have should work. We will resolve it on our end soon
Looks like it’s an option that can be enabled in Analytics settings in setup called. “Index Date fields in SQL” . Regenerate the dataset to take effect.
Take Note, Enabling this setting can slow dataflow and recipe performance.
Thanks a lot for your work and help @Rikke, SQL support will really help with plenty of limitations when trying to add values from different date ranges in the same query (comparing periods). Current SAQL language is enough for simple things, but when trying to do more elaborated analysis, query development is really a major challenge
I would love to do this in a TCRM query: sum(case when “YEAR” = 2022 then “Sales” else 0 end) as “Sales”, …
Thanks a lot for your work and help @Rikke, SQL support will really help with plenty of limitations when trying to add values from different date ranges in the same query (comparing periods). Current SAQL language is enough for simple things, but when trying to do more elaborated analysis, query development is really a major challenge
I would love to do this in a TCRM query: sum(case when “YEAR” = 2022 then “Sales” else 0 end) as “Sales”, …
will you support sum within a case?
I can’t find a way to run SQL like this:
SELECT column_a AS “A”,
SUM(case when column_b = ‘x’ then 1 else 0 end) AS “B”
FROM a_table
GROUP BY a_column
Tableau CRM doesn’t support traditional SQL directly, but it does support SAQL for querying its internal datasets, and you can use SQL for data prep in external data sources. If you’re familiar with SQL, learning SAQL will be a key part of working with Tableau CRM effectively, especially for creating custom reports and performing advanced data analysis.
Suppose you are more focused on integrating external SQL-based data. In that case, you’d use Dataflows and Salesforce Connect to bring data into Tableau CRM and then analyze it with SAQL or visualizations.