Observable

You can connect to Cube from Observable, a new kind of collaborative data notebook that's built to uncover new insights, answer more questions, and make better decisions, using the Cube SQL API or Cube REST API.

Here's a short video guide on how to connect Observable to Cube.

Use SQL API in Cube

Cube Cloud

Click Deploy SQL API and then the How to connect your BI tool link on the Overview page of your Cube deployment. Navigate to the BIs and Visualization Tools tab. You should see the screen like the one below with your connection credentials:

Self-hosted Cube

You need to set the following environment variables to enable the Cube SQL API. These credentials will be required to connect to Cube from Observable later.

CUBEJS_PG_SQL_PORT=5432
CUBEJS_SQL_USER=myusername
CUBEJS_SQL_PASSWORD=mypassword

Connecting Cube SQL API from Observable

Observable connects to Cube as to a Postgres database.

Querying data with SQL API

Your cubes will be exposed as tables, where both your measures and dimensions are columns.

Make sure to add a database to your notebook, and select Database query when adding a new block.

You can write SQL in Observable that will be executed in Cube. Learn more about Cube SQL syntax on the reference page.

You can also create a visualization of the executed SQL query.

Use REST API in Cube

Cube Cloud

Click the "How to connect" link on the Overview page, navigate to the REST API tab. You will see your connection credentials, the REST API URL and the authorization token:

Self-hosted Cube

For a Cube instance publicly available at a specific HOST, the REST API URL would be HOST/cubejs-api/v1. Please refer to the REST API page for details.

You will also need to generate a JSON Web Token that would be used to authenticate requests to Cube.

Please check the Security page to learn how to generate a token. We suggest generating a long-lived JWT that won't expire soon.

Connecting Cube REST API from Observable

Observable connects to Cube through the REST API.

Querying data with REST API

First, add two generic JavaScript cells:

Next, copy Cube's REST API URL and the Authorization token and paste them into their respective cells.

cubeRestApi =
  "https://thirsty-raccoon.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load";

Because the Cube REST API has the format of HOST/cubejs-api/v1, don't forget to add the /load endpoint to the end of the data source API.

cubeRestApiJwtToken =
  "Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE2NTgzMzM3OTZ9.gUOoDgo_RJka_ZANdwSw3v8GkM4ZzH9LjxrxKxkGAk0";

Also make sure to add the token next to the Bearer part of the Authorization header.

Get your Cube query in the JSON query format ready. You can copy it from Cube's Playground or compose manually.

Paste the JSON query in another JavaScript cell as an object literal and give it a name, I chose jsonBody for simplicity. Make sure to add a query parameter for your JSON query.

jsonQuery = {
  query: {
    measures: ["orders.count"],
    timeDimensions: [
      {
        dimension: "orders.created_at",
        granularity: "month",
      },
    ],
    order: {
      "orders.created_at": "asc",
    },
  },
};

Next, create another JavaScript cell with a POST request. Paste this POST request in the cell. Don't forget to put the jsonBody object inside the JSON.stringify call.

orders_over_time = fetch(cubeRestApi, {
  method: "POST",
  headers: {
    Authorization: cubeRestApiJwtToken,
    "Content-Type": "application/json",
  },
  body: JSON.stringify(jsonQuery),
})
  .then((response) => response.json())
  .then((json) => json.data);

Next, click the play button on the top right of the cell.

You can also create a visualization of the executed REST API request.

Metabase Power BI