🧊Connect to Cube

Arcwise connects to the Cube SQL API and allows end-users to query public views (and metrics within views).

Setting up the Cube connection

1. In the Arcwise admin panel, create a new connection and select Cube.dev as the warehouse type.

2. The "Name" can be anything you want (preferably something user-friendly.)

3. Enter your Cube credentials below (can either be a Cube Cloud or self-hosted deployment). Information for an example Cube Cloud instance can be seen below.

1. If everything is successful, the "Tables" tab should display a list of your Cube views. (We also pull out each measure associated with a view so that users can query them through formulas, as shown below).

Support for security contexts, particularly at the user level, is still under development.

(Optional) Configuring the AI Analyst

If you want to make best use of the AI Analyst, head to the AI tab in the Arcwise admin panel to configure some settings:

1. By default, the AI will only query tables that have been explicitly connected to a spreadsheet. You can add patterns in the "Table patterns to auto-include in AI conversations" setting to configure tables/views that should be automatically available to the AI.

2. The "Table metadata" section can be used to add additional context/descriptions at the view & column level to the AI.

3. The "Additional system prompt" can be used to add global context to each AI conversation.

Using cubes in Arcwise

Arcwise offers three primary ways to work with data from Cube.

Pivoting a Cube view

Open the Arcwise extension in Google Sheets and click "Connect Data" to open a browser of all connected data sources. You should be able to find your Cube views by searching the "Database" tab:

After clicking on your view, the right pane displays a list of public dimensions and measures.

The preview and connect buttons will directly pull the (ungrouped) data in the view. This usually isn't what you want to do.

Clicking the "Pivot" button will pull up a pivot table UI where you can freely move dimensions to rows/columns and measures (green) to values:

A basic pivot table on an orders cube, showing Order Count by Product Category vs. Status.

Clicking "Connect" will save the pivot table into the spreadsheet.

Users can also change the "Chart type" at the top right to easily visualize the data in the pivot table as a bar, line, scatter, area, or pie chart:

Bar chart view of the pivot table.

Clicking "Save" will save the visualization into the spreadsheet; users can bring up the visualization editor again by simply double-clicking on the saved visualizations.

Editing a saved pivot table

Once saved in the sheet, there are a few features to call out:

Saved Cube pivot in a Google Sheet

First, the inline overlay within the sheet tab displays a few key actions (from left-to-right: "Edit", "Analyze", "Refresh", "Drilldown".)

• Edit: Brings up the overlay on the right, which allows the pivot fields to be edited. You can also add new "table calculation" formulas under the "Additional fields" section at the bottom.

  • (In the screenshot, "completion_rate" is defined as a table calculation formula: completed_count / order_count)

  • See more here: Arcwise Connected Data

• Analyze: Opens up the AI Analyst, focused on the current pivot. See more at Using AI Analyst in Sheets

• Refresh: Re-runs the query and updates the contents of the current spreadsheet tab.

• Drilldown: If the selected spreadsheet cell is a pivot value, the drilldown view shows the ungrouped rows in the cube that match the filters defined by the rows and columns.

Running metric formulas

In any spreadsheet cell, typing =$ will show a list of public measures exposed by any Cube view:

Metric formula autocompletion

Here is an example of a complete formula that queries the Order Count measure where the status dimension is equal to A1 and the created_at column is after B1:

=$orders_cube.order_count("status", $A1, "created_at", ">"&B$1)

• Each measure is available as a spreadsheet function $view_name.measure_name.

• If no parameters are provided, the measure is queried over all rows (usually not desirable.)

• Otherwise, parameters should come in pairs:

  • The 1st, 3rd, 5th, ... parameters should be the string dimension name associated with the measure's view. (A list of suitable dimensions will be displayed in the autocompletion menu.)

  • The 2nd, 4th, 6th, ... parameters will be used as the comparison value for the immediately preceding dimension.

    • If the comparison is a string, COUNTIF/SUMIF criteria syntax is supported, i.e. you can pass in "<X", ">X", "<=X", "<>X" to compare the specified dimension against a certian value.

    • You can even pass in ranges as the comparison value (e.g. A1:A10) for the result of the metric formula to be an array of values. (Careful that in Google Sheets, you'll need to use the ARRAYFORMULA function if you want to chain computations on top of the function result, e.g. =ARRAYFORMULA($orders_cube.order_count(...) + 1)

IMPORTANT: Arcwise formulas (including measure formulas) deviate from normal spreadsheet formulas in that their values do not update automatically, even when dependencies change. This is to protect against cascading updates overwhelming your database, and to make sure that cell values do not dynamically change without explicit user interactions.

See Using Formulas in Sheets for an overview of how to refresh Arcwise formulas.

Using the AI analyst

See Using AI Analyst in Sheets for more details. The Arcwise AI analyst knows how to automatically generate pivot tables for Cube data (and visualizations based off those pivot tables) - you'll generally want to make sure that your Cube views are auto-included (see (Optional) Configuring the AI Analyst above.)