Report Editor

Overview

The report editor is where analysts write the SQL & R to query the data, as well as configure the visualization of the report via a chart type. You can access the report editor by selecting the New Report button on a dashboard or the dashboard overview page, or by choosing Edit from the drop-down menu of a particular report. The overall UI of the report editor is shown below.

Datasource Selection

A report is written to run against a particular datasource. To select a datasource for the report, use the drop-down menu above the text input where the SQL queries are written.

Almanach

On the left-hand side of the report editor, the datasource almanach gives an overview of the relations (i.e. tables) available for querying with the selected datasource.

By selecting a table in the almanach you can inspect the columns and run some basic, predefined queries.

The datasource metadata shown in the almanach is refreshed nightly, so that up-to-date schema information is shown when you make changes to the remote database tables or columns outside of Cluvio.

SQL Editor

The SQL editor is a text area located right below the datasource selector. It is here that you write the SQL to run against the datasource. In the top-right corner, the SQL editor allows formatting the SQL and enabling R.

Enabling R will result in a split of the editor window in two parts, one for SQL and one for R.

A central concept when authoring SQL for reports in Cluvio is the use of filters via Cluvio filters syntax, a simple to use extension of the SQL syntax and a key to making your reports interactive.

Query Results

When a SQL query is run, the results are shown right below the SQL editor. How the results are displayed depends on the selected chart type. To select a chart type, click on one of the chart names in the horizontal menu.

Cluvio inspects the result metadata (i.e. the number and type of the result columns) and enables only those charts which are likely to offer a useful visualization (and are available according to your Cluvio plan). For more information on the available chart types and their configuration options, see Chart Types.

The Results tab is always available and shows the result data in a simple tabular form, together with chart recommendations on the right.

From the Results tab, you can also inspect the executed SQL, as well as get a query performance analysis via the EXPLAIN Query option, and even download the result data as CSV.

Using R

Available on

All plans

On top of running SQL, Cluvio also allows you to post-process the SQL results via a custom R script, which makes it possible to do process data in ways which are impractical or even impossible to do with only SQL.

The following example shows how easy it is to add a running total column to the SQL results:

SQL:

SELECT {saletime:week}, SUM(pricepaid) as "revenue"
  FROM sales
  GROUP BY 1
  ORDER BY 1

R:

sql.data %>%
  mutate(running_total=cumsum(revenue))

As seen in the example, the SQL results are made available to the R script in the sql.data variable as a data.frame. The final result of the R script is the result of the last command and is passed to the Cluvio visualization as a data table. Supported R types are:

• primitive types, date, timestamp
• vectors of these (will appear as multiple rows of a single column)
• data.frame

You can optinally name columns in the output as usual in R via the 'names' attribute, e.g. the following will produce a column named "Metric" with 2 rows of data:

res <- c(42, 84)
names(res) <- "Metric"
res

The following libraries are directly available to use in R scripts. Please contact hi@cluvio.com in case you would like to use a package that is not currently supported.

• R (3.4.4) - most standard R functions
• reshape2 (1.4.2)
• plyr (1.8.4)
• dplyr (0.8.3)
• tidyr (1.0.0)
• purrr (0.2.4)
• tibble (2.1.3)
• broom (0.5.2)
• stringr (1.4.0)
• lubridate (1.6.0)
• hms (0.5.3)
• magrittr (1.5)
• glue (1.4.2)
• zoo (1.8-3)
• DataCombine (0.2.21)
• googlesheets4 (0.2.0)
• bayesAB (1.1.1)
• prophet (0.6.1)
• tvm (0.4.0)
• pwr (1.3-0)
• sigr (1.1.3)
• jsonlite (1.8.0)

System and I/O functions are not available.

info

Important Restrictions: The R script runs within a constrained environment:

1. The script has to finish within 1 minute (please contact support with your use-case if you need this limit raised).
2. The script can use at most ~100MB of computer memory.
3. Most system functions and functions for accessing the local filesystem are not available, i.e. the script is meant to process the SQL output in a referentially transparent manner.
4. The output of the R script has to be data (as stated above); there is not much use for generating visuals using R (e.g. using ggplot or shiny) in Cluvio, as the visualization step we apply is the same as with plain SQL results.

Settings

To fine-tune a report's appearance and behavior, like the data refresh rate, you can access the report settings from the top-right corner of the editor UI.

For more details, see Report Settings.