Multiple Measures on a Timeseries Chart - Salesforce Analytics Cloud - SAQL

A question I get asked a lot is how we can place multiple measures on a single time series graph in Analytics cloud. This can be achieved really quickly in SAQL / PIGQL.

The idea here is to project each measure for each line on the graph then add an extra column to the projection essentialy for grouping the graph. Once we have both of our measures projected we then union these projection sets to combine them.

Read More

The Ultimate Transitioning to Bindings 2.0 from Bindings 1.0 Guide.

This post inspired by and a large nod to [Robert Houston][1] whose nickname in our ecosystem is The Wolf. Most likely the best technical person on our platform. The Wolf recently wrote a piece I saw comparing Bindings 1.0 and Bindings 2.0 - this post is a view of this for the community. The idea of this post is to show all steps needed to build a full dashboard in Bindings 2.0 then show a comparison of Bindings 1.0 and Bindings 2.0 in a Copy/Paste scenario for reference. 

Read More

Moving Salesforce Analytics dashboards between Salesforce environments.

One of the issues with Analytics cloud is that there is no process to take Analytics cloud assets from a dev or test environment and push them into production. Basically what you need to do is rebuild all of the items again in production. Here are the steps just for the dashboard component, all of the other peices of a dataset/dasboard are the same including:

  1. Uploading the datasets into new environment / pulling from Salesforce
  2. Copying and pasting XMD from one environment to the other
  3. Downloading images and re add images to the new environment

How to move dashboards JSON between environments:

I want to move this simple dashboard from one of my pre sales environments to another pre sales environment. The dashboard looks like this:

Next, I want to show the sourcecode so you can see the id's of the dataset, dashboard and the environment.

Looking at this dashboard we can see that it is very simple and that we have used in our steps the "OnTime" dataset. This is the dataset that we will need to use in the new environment. Looking at the code below we can see there are specific environment variables.

  "_url": "/insights/internal_api/v1.0/esObject/lens/0FKB000000003sPOAQ/json",
  "_uid": "0FKB000000003sPOAQ",
  "edgemarts": {
    "emName": {
      "_type": "edgemart",
      "_uid": "0FbB000000002slKAA"

First thing we want to do is move to our new environment and make sure the dataset has been repulled form Salesforce or uploaded via CSV/API or middleware. Once this has been created, open the dataset and snap the lense to a dashboard.

Next step is to save the new dashboard to the same name as the dashboard we are moving across between environments in this case it is "Carrier Performance".

Once we have saved the dashboard, goto the onboard lens tool and access the dashboard JSON. The lens viewer can be found using the following instructions:

In your browser’s address bar, type the URL of the Create Lens page. For example, if your Salesforce instance is na3.salesforce.com, type
https://na3.salesforce.com/insights/web/lens.apexp in your browser’s address bar.

Copy the JSON from the lens viewer and paste into a text editor

This is the JSON of our new dashboard, the next step is pretty simple. All we need to do is to take the "State" section from the dashboard JSON from the old environment and paste it into the "State" section in this json. The new dashboard JSON currently looks like the following:

We will now add the new "State" section to this dashboard JSON. It now looks like the below:

We can now add this to the new dashboard in the new environment via the lens viewer. Once this is completed your dashboard will look something like this:

This is a quick overview on the basics of moving the content of a dashboard from one environment to another. There are a number of things in addition to this mentioned at the start of the artice. These need to be applied manually to complete the dashboard.

Simple calculations on Salesforce Analytics Cloud

When looking at the most searched items on andrewprice.me I am finding that people are trying to find out how to do calculations on Analytics Cloud. For those vistors to the site there are a couple of ways to do calculations on the platform:

  1. Using the compare table, and adding a calculation as a column
  2. Use SAQL / PIGQL and create a calculation.

Compare Table Calulations

To create a compare table calculation it is very simple. Add a column that is a measure.

In this case I have chosen to calculate the sum of all flights.

Once the measure has been added to a column int the compare table , we then click the drop down arrow on the cell and choose add column.

Once the column has been added, we can then press the drop down again and scroll down and select "edit formula and format".

Once selected we have the option to add in a formula in the "Edit Columns" box. Each column is represented like a Excel Spreadsheet using the alphabet to detail the column names in the formula edit box. In this example I have created the below calculation.

Column A x 2 + Column A

Once the calculation has been added in the Formula Editor, press OK and this will be added to the compare table in a seperate column.

Using SAQL / PIGQL to create calculations:

SAQL is the Salesforce Analytics Cloud Query language. We use this query language in conjuntion with the UI framwork what we call the compact form. The SAQL query is used within a step in a dashboard and returns a result to the step , in which the compact form interprets and renders in eclair / the Salesforce Dashboard UI.

The calculation we are going to create is the same calculation we created in the compare table above however we will represent the outcome in a number widget.

  1. Use the cervello Salesforce Analytics Cloud SAQL Tester - (available here: )
  2. Get the Dataset ID using the details here:

Once we have the Dataset ID we can start to build out a query using the SAQL tester. To Start with we build out a query to get the sum of all flights within the dataset. This is a simple query and looks like the below:

Now we have the simple calculation of sum of flights, we now just need to adjust this to take the calculation in the above raw table and add it into the query.

We now add this to a step and then add that step to the number widget within the dashboard JSON. Below you can see this, it is important to make sure that the measures and groupings added / created in the JSON need to also be replicated in the compact form within the step.

Below is the dashboard functioning with the new calculation in SAQL rendering the number widget.