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.

Using a Relative Date Static Step with with a Selection Bind in SAQL Pigql and Compact form Graphs

When building out dashboards for business consumption, there will be in most cases a requirement to have relative date filters and facet across various widgets on the dashboard ( A relative date for example is 3 months from today). A simple way to achieve this maybe to use the Date widget which can be easily added onto the dashboard from the widget toolbar, however another way is to provide a set of buttons that have static date ranges. This can result in a easier dashboard navigation experience, fixed date ranges, and makes it easier for end users to select date ranges that they would like / should only see.

To setup a widget such as the pillbox or the selector widget with relative date ranges you need a static step to drive the dates added as values on the widget. The picture below shows the outcome of a static step controlling two widgets, one widget which is being filtered via the static step in compact form , and the other filtered via pigql.

Below is the static step for the above dashboard. This is the step that is:

  1. Feeding the values of the buttons - everything in the "Values" section is a value,
  2. Detailing what should be displayed on the buttons per value under the "display" tag.

As you can see in each value there are two subordinate values with the parameters of:

  • saqlstepvalue
  • compactstepvalue

In each of the parameters there are the relative date ranges in the syntax required to the specific type of widget / query that will need to render their relative date range filter. The static step filter must be placed in the steps: section on a dashobard.

The below JSON is the code and syntax required to filter a compact form widget where there is no pigql passed to the widget. As you can see in the whole dashboard JSON at the bottom of the page, this filter must be added in the "query" section of a step. The filter is a selection bind in which it takes the value from the selected static step and passes it into the filter for the associated compact form step.

This next script is a pigql query with the outcomes of the query the same as the compact query above. Included in this query as well is a selection bind that takes the relative date selected in the static step and adds it into the query and therefore filters the associated widget.

Now that we have added a static step into our dashboard , and added the associated filters to both types of widgets / queries, we can see how they look in a completed dashboard. This dashboard is the JSON from the image above.

The JSON describes:

  1. Pillbox Widget connected to the static step
  2. Static Step with relative date ranges
  3. Compact from bar chart with relative date filtering
  4. Pigql query bar chart with relative date filtering

How to use a Scope Widget - Salesforce Analytics Cloud

Summary:

When creating a dashboard, you may want to set a hard filter on a measure or attribute across a whole dashboard. To do this you can use the Scope Widget. Below are the steps to create and apply a Scope Widget to a Dashboard, using in this specific example the OnTime Dataset Salesforce has used as part of the Salesforce Analytics Brownbelt.

Video:

 

Scope Widget - Analytics Cloud - Andrew Price 

 

Walkthrough

Download OnTime Dataset

Step 1 - Choose Count of Rows on a dataset of your choosing and select the filter button on a lens. I am using the OnTime Dataset.

 
 
 

Step 2 - Choose Arrival delay by clicking on the arrow on the right and enter the figures from: 10 and To: 30. This has filtered in this dataset "OnTime" flights where the arrival delay is between 10 - 30 minutes.

 
 

Step 3 - Apply the Filter to the dataset, and take a Snapshot of this lens adding the lens to a dashboard or creating a new dashboard.

 
 

Step 4 - Choose the Scope Widget and select the filtered step from the left handside to add the step to the Scope Widget.

 
 

Step 5 - Once the step has been added to the Scope Widget, you will see that the filtered field and the specific filter applied to the Scope Widget and Dashboard. In this case there is now a Global filter of flights with an arrival delay between 10 - 30 Minutes on the "OnTime" dataset.

 
 
 

Uploading a Dataset into Salesforce Analytics

One of the first steps when looking to utilise and work with Salesforce Analytics is investigating how to get data into the platform. There are a few ways to upload a dataset into Salesforce Analytics:

  1. ELT Platform - hosted on Salesforce Analytics instance the ELT tool is used to pull data from the Salesforce instance it is connected to
  2. Dataset UI - Via the UI you can goto the upload page for the Dataset and choose a local file to upload
  3. Dataset Utility - A Salesforce built JAVA utility that will use the Salesforce API to upload a dataset into Salesforce Analytics
  4. A Middleware or ETL tool - (Informatica, Mulesoft, Snaplogic) A third party integration tool of which many have connectors that will automatically integrate into Salesforce Analytics and can take current data being loaded and transformed and can push it into Salesforce Analytics.

The easiest way to get data into the Salesforce Analytics platform is to use the UI and upload a CSV through the Dataset Utility. Choose CSV under "Select a Data Source"

 
 

Once you have chosen to upload a CSV , you will be met with the dataset page where you can detail:

  • The CSV file to be uploaded.
  • A custom JSON file
  • Dataset Name
  • App - Where you would like to store the dataset

There is a current limit on the size of the CSV file you can upload via the UI process - 500MB. You can use a variety of other options for example the Dataset Utility , API, or Middleware tools if you require a larger dataset to be imported into Salesforce Analytics.

We will now look at the easiest of all the ways to upload a dataset into Salesforce Analytics, upload a CSV. The first step is to find an appropriate dataset to upload. Any CSV dataset is fine, one that is commonly used for testing and navigating through a variety of measures and attributes on the platform is a dataset called ontime. Ontime is a dataset including measures and attributes from the US airline industry. You can find this demo dataset here:

US Airlines Ontime Dataset

Once you have the dataset downloaded, you can go to the Dataset UI and choose to upload this dataset. Give the dataset a name on the left handside and also a App or place where to store the dataset. Your private app is a good place, this way no one can access the dataset automatically. When you start the uploading process you have the option to keep the data in a .ZIP format or upload as a CSV file. If you would like to have the measures and attributes configured for you , you will need to upload as a CSV file.

Once the file has been uploaded, it may take some time dependent on the size of the dataset. Navigate to where you stored the dataset and open it up. You will be met with your dataset with the results of how many rows of data are in the dataset. To verify that the measures and attributes have been generated by Salesforce Analytics, create a few groupings and measures to see if the data is correct. In the case of the dataset Ontime - Choose the following:

  • Sum of Flights
  • Sum of DepartureDelayMinutes
  • Group by Carrier

Looking at the results, you can see the total amount of minutes that American Airlines was delayed for during the total amount of flights held in the dataset. In this case it looks like the dataset has been uploaded and measures and attributes have been defined successfully.

 

Salesforce Analytics - On time Dataset - Measures and Attributes