SQL vs SOQL vs SAQL

With Spring '21 releasing this week (Feb 2021), the Tableau CRM team is releasing SQL that can now be used for Direct Queries in dashboard widgets in addition to SOQL and for datasets in Tableau CRM SQL can be used in addition to SAQL.

Well, what does any of these terms means and why do we care?

SQL? Hmm… What's that?

In the world of Data Science and Data Analytics, SQL happens to be one of the most commonly used programming language for querying the database or table in Relational Databases. SQL (pronounced "ess-que-el" or Sequel) is short for Structured Query Language. Most programmers and others working with data in the world of relational database use SQL to query, organize, update, re-create, modify their databases or tables as the case maybe. Of course we can do these in Excel. But, Excel can handle only so many rows. Imagine a massive sized dataset that runs into millions and Excel cannot open them or a file that is so large with the amount of columns that it is nearly impossible for Excel to open them. SQL comes to our rescue. :)

So, why is Tableau CRM adding SQL now? Well, as explained earlier, SQL is obviously one of the most commonly used language for database querying! So, why not add the language that most people who work with databases understand and make it easier for them instead of asking everyone to learn a new albeit similar language (SOQL) while SAQL is a totally different ball game that we will try understanding soon.

SOQL!

SOQL is Salesforce Object Query Language. This is very specifically used for querying the Salesforce database. This is very similar to SQL with some subtle and some obvious differences — You cannot modify the Salesforce database like we just learnt you can in SQL. The reason being that there are no insert or update statements. Having said that, we can use Workbench or dataloader.io or Salesforce integrated data import wizard or even Apex Dataloader to insert/update/upsert/delete the Salesforce records based on the query we run. All we need to do in that case is download the data as Bulk csv — This tool is part of Workbench. In short, in SOQL, the Salesforce Objects are represented as SQL tables.

Back to SOQL and some other differences with SQL: Another important distinction is that in SQL we can do SELECT * FROM — This means that we can select everything from the specified table by just using *. In SOQL, we have to select all columns individually to achieve the same — this will no longer be the case come Spring '21. Going forward we can use the following:

FIELDS(ALL), FIELDS(STANDARD), or FIELDS(CUSTOM) within our SELECT statements.

FIELDS(ALL), FIELDS(STANDARD), or FIELDS(CUSTOM) within our SELECT statements.

Caveat: The number of fields is however limited to 200.

Another extremely important distinction is JOIN (this is a SQL keyword) but in SOQL, we use a simple dot notation to form the Parent-Child Relationship. An example of this was presented in the previous Live Dashboards post where Taxi__r.Taxi_Affiliation__r.Name is a Parent-Child Relationship to find the Name of the Taxi Affiliation by traversing from the Taxi Object to the Taxi Affiliation object!

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) day_Start_Time__c, SUM(Total_Reported__c), SUM(Miles__c) FROM Trips__c WHERE Taxi__r.Taxi_Affiliation__r.Name = '{{cell(Affiliations_1.selection, 0, \"Name\").asString()}}' GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) day_Start_Time__c, SUM(Total_Reported__c), SUM(Miles__c) FROM Trips__c WHERE Taxi__r.Taxi_Affiliation__r.Name = '{{cell(Affiliations_1.selection, 0, \"Name\").asString()}}' GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

More on the difference between SQL and SOQL can be found here.

SAQL

Last but not the least, SAQL — After all, that is the reason for the blogs here! SAQL, is short for Salesforce Analytics Query Language. SAQL is influenced by Apache Pig Latin (PigQL) — though their implementations differ, and are not compatible. SAQL is what we use all across Tableau CRM from dataflows to dashboards and lenses.

A little about PigQL that will help us understand SAQL better.

() — We use these to enclose one or more items. Similar to SAQL group statement by multiple fields.

[] — We use these for optional items or filters. They can also form a tuple. Say,

q = filter q by Name in ["ABC", "DEF"] and Start_Date__c in ["current year".."1 year ahead"]

q = filter q by Name in ["ABC", "DEF"] and Start_Date__c in ["current year".."1 year ahead"]

{} — In PigQL it encloses one or two items. In SAQL, we use these for bindings/interactions as {{}}. Example:

{{cell(step_result.result, 0, 'value').asString()}}
{{column(step_select.selection, ['value']).asObject}}

{{cell(step_result.result, 0, 'value').asString()}}
{{column(step_select.selection, ['value']).asObject}}

SAQL loads the query dataset, then performs some operations on it like generate statements to group, aggregate, order, limit etc. and then output the result. Every component in Tableau CRM is a SAQL statement behind the scene.

Unlike SQL or SOQL, SAQL needs a minimum of 2 statements:

• Load
• Generate

The above example is for value table. For compare table/pivot table/charts, here is an easy way to remember what comes next-LiFeGuARD:

• L — Load
• F — Filter
• G — Group
• A — Aggregate
• R — Order
• D — Limit/Display number of rows

More on SAQL can be found on Rikke Hovgaard's blog.

So, what happens now?

Tableau CRM from Spring '21 allows SQL, SOQL and SAQL in lenses and dashboard widgets. Now, that we know what the difference between them are, we have to choose wisely and based on our comfort level to find the optimal solutions for our queries — all while maintaining that whatever our methodology, it will work across all use cases. A dashboard that is a combination of SQL, SOQL and SAQL can be a great idea when developed in the right way.

At the end, SQL maybe new to some, but since it can be used cross-platform, it is a great programming language to learn! On a similar note, if we are working with SOQL queries, SQL is easy to get a grasp of and vice-versa. SAQL, works differently as we just saw. But, it remains a way we can do much more than what SOQL lets us do and with minimal effort. In addition, since datasets stored in Tableau CRM are flat datasets — meaning the dataset has all the joins you need (simple case), it makes the dashboards run faster. End of the day, the goal is for the dashboards to open in a jiffy and not tell users to wait for 3 min for the dashboard to load.

References:

• Automatic Champions blog on SOQL
• Rikke Hovgaard's blog on SAQL