Wednesday, January 13, 2016

A Better Way to Manage JSON and SAQL in Analytics Cloud

labs_headerIt’s really easy to get started with Analytics Cloud.  Load an internal or external dataset, create a couple of lens, clip to a dashboard and relish in the insights.  Now to get the most out of Wave, you have to dive under the covers and work with JSON and SAQL (Salesforce Analytics Query Language).

Previously, this meant spinning up your favorite editor, digging through the documentation and a lot of copy and paste.  Thanks to the innovation coming from Salesforce Labs, there is a new tool that enables inline editing of Metadata/JSON as well as SAQL.  Let’s take a look and see how much time you can save.



Wave Tools

Wave Tools is a site hosted on Heroku and uses the Wave APIs to make it significantly easier to manipulate JSON and SAQL in your Analytics Cloud instance.  After you have logged into your Wave instance, launch the tools by browsing to https://wave-labs.herokuapp.com in a new tab.  Next, select either a Production or Sandbox instance (Dev orgs fall under production).  The first time you access Wave Tools from an org, you will be prompted to authorize OAuth access.  Now, we are ready to dive into the details. Remember, this is a prototype tool out of an innovation lab and not a generally available product.

Wave Tools

Metadata/JSON Editor

The first available tool is a nifty enhancement to the lens.aspx page.  You can edit both dataset XMD as well lens/dashboard JSON.  After clicking on this option, the main editor screen loads.  The left side provides multiple search options to find your dataset, lens or dashboard.  You can filter by an App, search by name, or filter the results by type.  The left side panel can be hidden, allowing for a full screen edit mode.  Let’s start at looking at the XMD options for a dataset. Selecting a dataset on the left loads the XMD, as set of Helpers, and command options.

Wave Tools 2

There is a lot of awesomeness going in in this editor, so let’s take a look from top to bottom.  First in the editor menu bar, you have expand and collapse all options, undo and redo, a mode selector to toggle between a tree view and code view that includes inline editing, and a search that will highlight all instances of the supplied text in the file.

Next, for any block in the tree, use the drag selector (two column of three dots) to reorder the section.  An action menu provides context sensitive options for adjusting the XMD. For example, you can add a type or sort.  You also have options to Append, Insert, Duplicate and Remove.  For example, head to the labels section in the dimensions node.  You can insert a new String and will be prompted for the label/value in the JSON.

Wave Tools 3

The Dataset XMD Helpers is where the timesaving aspects come into play.  Need to add a measure format or colors to your dimension.  No longer do you have to go looking for documentation on the syntax.  Click a helpers and a template is added to the file.  Helpers are provided for
  • Adding Types (Date Definition)
  • Measure Formats
  • Dimension Colors
  • Labels
    • Dimension Alias
    • Measure Alias
    • Measure Filters
    • Adding Keys (dimension value mapping)
  • Dimension Link Template
  • Hiding Columns for Dimensions or Measures
  • Adding a values table definition
Just fill in your specifics and Save.  No more copying from one tool into another.  If you make a mistake along the way, use the Refresh command to pull back the last saved value.  Download will pull down a copy of the JSON.

Selecting a Lens or Dashboard provides similar options, which are also context sensitive.

Wave Tools 4

When working with a dashboard, there is also the option to select from a list of steps to configure.  This will open up the step in the editor (much handier than having to scroll through looking for it) and provide additional helpers.  You can also see which widgets use the step, which helps keep track of things.  These helper options include
  • Add Dimension
  • Add Measure
  • Add Group
  • Add Filter on a Dimension
  • Add Filter on a Measure
  • Add Limit
  • Add Order
Wave Tools 5

SAQL Editor

Similar to the JSON Editor, the SAQL Editor lets you interact with your current datasets, lens and dashboards (or start from new).  Helpers accelerate your productivity by adding code templates with a single quick.  Let’s take a look at building a new SAQL statement from a dataset.

First click on your dataset to bring up the helpers.  I am using Sales_Data from a prior post.  Clicking the LOAD helper to add the load statement.  Next, FILTER will add the filter statement.  You can click on a Dimension to add it in the placeholder.

In addition, auto-complete drop downs display as you type.  Anyone who has spent way too much time debugging a typo in a dashboard will love this feature.  Continue building out your statement with GROUP and FOREACH statements.  Best of all, no more waiting until your dashboard bombs to know you have  SAQL issue.  Use the Run command to execute and see your results.  You can even download the results or add the statement to your command History for easy retrieval.

Wave Tools 6

If building SAQL from scratch is not your style, start from a lens.  Simply click on a saved lens built in Wave and the statement is automatically loaded.  You can then adjust it to your needs.

Wave Tools 8

Behavior with a dashboard is similar, including the ability to convert from compact form to SAQL as well as replacing the dataset id on the fly.  While you can modify the dashboard SAQL, bindings are not available for editing.

Conclusion

These new tools are a significant improvement over prior options, as less copy/paste and manual steps helps improve efficiency.  With the appearance of these tools as a stand alone solution, it likely means it’s only a matter of time before improvements are embedded directly in the Analytics Cloud.  Happy data surfing.

6 comments:

  1. Hi Carl,
    thanks for the guide to Wave Tools! However when I open the Metadata/JSON Editor in my browser, I am only given the datasets as a choice option, but not Lenses or Dashboards (I have both types - standard and Flex Dashboards in my App).
    Any suggestions?
    Thanks,
    Martynas

    ReplyDelete
    Replies
    1. Martynas, with the Spring 16 release the Wave API went GA. Wave Tools does not yet support the API. I added a post with additional details - http://blog.canntechnology.com/2016/02/wave-api-and-wave-tools.html

      Delete
  2. Hi Carl,

    It seems like with Winter 17 release, these editor tools are deprecated and we need to use tools bundled with Dashboard builder. If you get a chance to explore on those, can you please help me understand how the queries of the lenses in the dashboards (steps --> queries) can be edited? where can i get SAQL for these lenses embedded in the dashboards?

    Thanks & Regards,
    Prasanna

    ReplyDelete
  3. Prasanna,

    The quick answer is to use CTRL-E on a Windows machine and Command-E on a Mac to see the JSON for a dashboard. Then search for the step name. If the query was added with SAQL, there will be a pigql step with the details. I will work on a more detailed posting.

    Carl

    ReplyDelete