Perform SAQL query from file against Org and display results

[jplacht]

Is your feature request related to a problem? Please describe.
We're doing Analytics implementations for enterprise clients that mostly involve us creating huge calculations (>3.000 lines of SAQL is the standard) to return KPIs. Our only option is to write those queries in the Salesforce Analytics UI which in our mind is not suited for such complex queries and make it very hard to extend and maintain them.

Describe the solution you'd like
The REST API allows to perform queries against datasets and have the response contain the result table. A prefered way of extending the Extension would be the ability to write SAQL code in VSCode, define a query.json that contains readable names for datasets to be queried against, have the ability to send the query and have the result displayed as a simple table.

Components:

• define a JSON definition for datasets to be used / loaded, that can be referenced in the file containing SAQL code

"datasets":
	[
		{
			"name": "Test1",
			"containerId": "xyz",
			"versionId": "123"
		},
		{
			"name": "Test2",
			"containerId": "446",
			"versionId": "aer"
		},

	]

• Used in SAQL code as $Test1 in load statements (or similar ways of referencing)
• Button / command to use JSON + written SAQL and have it performed against the connected Salesforce Org, therefor using dataset definitions before submitting API call
• Display of JSON response transformed to table (or error message on errors)

Describe alternatives you've considered

• writing SAQL and then manually creating the API payload + call in Postman / Workbench
• Chrome Extension EADashboardHelper

I would love to hear your feedback on this feature, why it is not available or ways to achieve this. Especially for projects like ours at enterprise clients with complex and huge calculations the current way of writing this is not performing well or limits our abilities to work on Analytics in a proper development way.

Thank you!

[smithgp]

Hi @jplacht, thanks for the feedback. This is something we've discussed internally before, and having this feedback helps us prioritize. I've added it to the backlog in the github Project.

Here how I was envisioning implementing it (no promises, just ideas for now), which I think lines up with what you're suggesting:

1. Write an sfdx plugin (e.g. analytics:query:saql), that could also automatically do the dataset name->id conversion and/or support passing that in as you describe. This would work like the others for auth, e.g. support -u to specify a org username, defaulting to using the current project's default org auth.
2. In VSCode:
   • Add a command on *.saql files to run the sfdx command (i.e. command palette with a file picker and/or right-click).
   • Do syntax highlighting on *.saql files in vscode.
   • See about doing in-ilne validation, code-completion, hovertext, etc. for *.saql files.
   • Maybe long term, look into doing a visual builder, similar to the SOQL builder extension.

We'll also want to look at supporting SQL as it comes out of beta. It's interesting that SQL supports dataset names in the queries and doesn't need the name->id replacement, although I'm not sure how it works if you want to use an older versionId.

cc: @skipsauls

[jplacht]

Hej @smithgp, thank you! Really glad to hear that this was already discussed internally & also reading your initial ideas on this. Completely agree on 1 and 2, sounds promising!

I think displaying the result then could just be a simple table view of the response somewhere as a tab etc.

Definitely not for the first step, but down the road one could also think about using the UI to add additional filters (outside of SAQL, like one would use global filters on dashboards) to mimic dashboard filter behavior on the query.

If you need more feedback, ideas or a group of people who would love to give more insights on what we're doing and how such an extension would improve our project work - happy to find some time to talk! We got a group of people around Europe doing such things for enterprise projects. As mentioned, right now it is very hard to work on long and complex queries / calculations in the UI. Using VSCode to do proper development would be an fantastic opportunity and help a lot.

[smithgp]

We just released v0.30.0 of the @salesforce/analytics sfdx plugin to the public. That has an analytics:query command that can execute saql or sql queries, including sql against live datasets. Also, for saql, it will do the dataset name -> id/versionId mapping automatically.

You can upgrade to that via sfdx plugins:install @salesforce/analytics@latest. See sfdx analytics:query --help for details.

[jplacht]

Hi @smithgp & team, thank you!! We've started testing this out and it looks really good. We did run some smaller queries this morning with success - fantastic, to do query development in VSCode and not the UI.

While a smaller part of a query did run perfectly, we also had the following error after we did run the whole query.

ERROR running analytics:query: Invalid empty containerId/versionId in 'xyz'

I'm wondering, as both queries have this dataset (only once, exact same 2nd line of code), but the bigger query produces the error. Should we open a bug ticket or is there a way we can trace this better? Other loglevels don't show more information.

Edit: Replacing all dataset loads with id/versionid works like a charm! So probably only something related to the automatic mapping?

Edit 2: Is it important that the file extension is .saql for the mapping to work? This looks like the cause of missing mapping in our test.

[smithgp]

Hi @jplacht, you can add --dev-debug to the command to see more output. If it's doing the name mapping, you should see a line like:

sfdx:connection DEBUG <request> method=GET, url=https://orgname.my.salesforce.com/services/data/v51.0/wave/datasets/datasetname 

The command does look at the file extension to see if it's .sql (unless you specify --sql), otherwise it assumes the query is saql and should do the mappinng (unless you do --nomapnames). Not sure why that didn't work with a different filename extension. What was the filename extension?
Does sfdx analytics:dataset:display -n xyz show the dataset details?
You can also add --dryrun to the analytics:query command and it will just show the query json it will send up w/o running it, to see what it generated.
Also, the dataset name mapping is basically some regex and string-sub on the saql, so it's possible the command is not correctly matching your saql to do the replacement.

[jplacht]

Hi @smithgp, thank you for providing more insights on this. Indeed, our files were named .sql and it therefor did not apply the mapping. Renaming them to .saql (or anything else) works.

Again: Thank you for releasing this feature, it's such a huge help in maintaining and developing our Analytics implementation and it just works like a charm. Many thanks. Should I close this request then or how do you prefer to proceed?

[smithgp]

Excellent @jplacht, I'm glad it worked for you.
I would like to keep this enhancement issue open for the vscode parts for the editor experience on .saql files and for a right-click on .saql or .sql files to run them from.