Over the last few months, the Observable team has celebrated the [first anniversary](https://observablehq.com/@observablehq/analyzing-star-wars-movies-the-first-anniversary-of-observ) of the Observable Plot charting library, introduced the [Data Table Cell](https://observablehq.com/@observablehq/introducing-data-table-cell) type, and supercharged [SQL Cells](https://observablehq.com/@observablehq/sql-cell) with summary charts.

Let's see how data notebooks, built with Observable, can work with Cube and fetch data from the recently [updated SQL API](https://cube.dev/blog/expanded-bi-support) as well as the REST API.

![Sample notebook](https://ucarecdn.com/592d7dc7-3342-4593-af73-2abab6f06829/)

## What is Observable?

[Observable](https://observablehq.com) is collaborative data canvas that has a few distinctive features compared to other modern [data notebooks](https://cube.dev/blog/category/notebook) on the market:

1. Observable implements the principles of interactive and reactive data exploration popularized by Bret Victor in his visionary talks such as [Inventing on Principle](https://www.youtube.com/watch?v=PUv66718DII) and [Media for Thinking the Unthinkable](https://www.youtube.com/watch?v=oUaOucZRlmE). 
2. Observable is based on JavaScript which is not so common in the Python-dominated data notebooks space. Also, it natively works with [Observable Plot](https://observablehq.com/@observablehq/plot), an open-source JavaScript library for visualization of tabular data based on [D3.js](https://github.com/d3/d3). Not that surprising given that Observable is co-founded by [Mike Bostock](https://bost.ocks.org/mike/), the creator of D3.js.
3. Observable provides a super-rich set of cell types as well as a complete library of [tutorials and examples](https://observablehq.com/tutorials), [templates](https://observablehq.com/templates), and [notebooks](https://observablehq.com/community) contributed by a community of creators.

Observable notebooks run in the cloud and in your web browser. They can also be embedded as a whole and as individual blocks.

## What is Cube? 

Cube is the [headless BI](https://cube.dev/blog/headless-bi) platform that sits between your data sources (e.g., data warehouses) and downstream data applications (e.g., data notebooks). Because of its position in the data stack, Cube is able to provide consistent metrics and performance for all your data flowing downstream.

![Observable and Cube](https://ucarecdn.com/a35d923f-b2a5-455c-853c-b8fb54fea12c/)

Cube works with all [modern data stores](https://cube.dev/docs/config/databases), creates a metrics layer with consistent metrics definitions, and delivers them downstream to any data notebook, application, or BI tool via its SQL, REST, or GraphQL APIs.

**Extended connectivity.** Observable natively supports [fetching the data](https://observablehq.com/@observablehq/getting-data-into-a-notebook) from an API and a number of [databases](https://observablehq.com/@observablehq/databases): BigQuery, Snowflake, Postgres, and MySQL. With Cube, you can expand the range of available data sources to almost any data warehouse, query engine, or database (e.g., Athena, ClickHouse, Databricks, or Redshift).

## Consistency for Data Explorers

One of the benefits of using Observable and Cube together is providing a common ground for all teams working together within an organization.

It's not uncommon that inconsistent data is being used across different teams within a company. It generally originates to varying metrics definitions across the many tools that each team uses. At some point, teams realize that they use different SQL queries to calculate metrics and, unfortunately, mislead their users with inconsistent insights, despite using the same data. 

**Let’s go through a super high-level example.** Imagine both the product and customer success teams want to calculate product success "over the last year". The product team queries the 2021 data (01/01/2021 to 12/31/2021), whereas customer success queries it as year-over-year (06/01/2021 to 06/01/2022). 

Since the data pipelines of both teams are siloed, neither team necessarily has visibility into how the other defined the query; it’s likely these teams use different tools too. This discrepancy in query results can absolutely misalign the teams working towards a single goal, but working off of disparate data.

In case of Cube used upstream of Observable notebooks and all other data applications, a misalignment has no chance to happen. Cube will deliver consistent metrics via its various APIs to all tools.

## Defining Consistent Metrics

Let’s see how Cube complements Observable in the data pipeline. In this example, we’ll use a e-commerce dataset and build a data model around it. We’ll also customize the data model to demonstrate how Cube helps maintain and evolve metrics definitions.

**Running Cube.** To keep things simple and save time, we'll run fully managed Cube in [Cube Cloud](https://cube.dev/cloud). Please proceed to Cube Cloud’s [sign up page](https://cubecloud.dev/auth/signup) and follow along the [instructions](https://cube.dev/docs/product/getting-started/core/create-a-project) to connect your own data source and generate a data model.

For convenience, you can also use the following credentials to get up and running in no time:

```
Hostname:  demo-db-examples.cube.dev
Port:      5432
Database:  ecom
Username:  cube
Password:  12345
```

This is what your deployment in Cube will look like:

![Cube deployment](https://ucarecdn.com/8177c022-44d5-49df-9188-28cfc71fb847/)

**Defining the data model.** To see the data model you created above, navigate to the <Btn>Schema</Btn> tab. You will see files named `LineItems.js`, `Orders.js`, `Users.js`, etc. under the `schema` folder which defines the *cubes* in your data model.

![Data schema](https://ucarecdn.com/53461723-80ab-4353-a940-3a6a2cb86f4c/)

Key ideas on what we’re seeing above:
* The *cube* is a logical entity that groups measures (quantitative data) and dimensions (qualitative data) together into [metrics definitions](https://cube.dev/docs/schema/reference/cube).
* In this case, we have defined a cube over the entire `public.line_items` table.
* [Measures](https://cube.dev/docs/schema/reference/measures) are defined as aggregations (e.g. count, sum, etc.) over columns in the table.
* [Dimensions](https://cube.dev/docs/schema/reference/dimensions) are defined over textual, numeric, or temporal columns in the table.
* You can define complex measures and dimensions with custom SQL statements and references to other measures and dimensions.

**Customizing the data model.** We'll define a completely new rolling window measure in the data model.

Let’s assume you want to calculate the total revenue brought in from sold line items over all time. This can be done by writing a SQL query in your local Jupyter notebook or any other BI tool you may be using. But what happens if you want to observe the total revenue over different time granularities? Or add another dimension to observe the revenue data?

This is where your `LineItems` cube can be modified to abstract away your varied data requests. Simply define the metrics in your data model once, and then use them time and again across teams to achieve your business goals.

For this example, we’ll customize the data model in the cloud by adding the "total revenue" as a measure to the `LineItems` cube.

```js
// A rolling window measure
revenue: {
  sql: `price`,
  type: `sum`,
  rollingWindow: {
    trailing: `unbounded`,
  },
}
```

While Cube Cloud has a ton of time-saving features, we'll use a particularly useful one now. Click on <Btn>Enter Development Mode</Btn> to unlock the schema files for modifications. This essentially creates a development environment (or "branch") where you can make changes to the data model.

The following code snippet shows the contents of the `LineItems.js` file (which defines the metrics for the `LineItems` cube) with the `revenue` rolling window measure added to the measures section.

Navigate to the `LineItems.js` file and replace its contents with the snippet below.

```js
cube(`LineItems`, {
  sql: `SELECT * FROM public.line_items`,

  joins: {
    Products: {
      sql: `${CUBE}.product_id = ${Products}.id`,
      relationship: `belongsTo`
    },

    Orders: {
      sql: `${CUBE}.order_id = ${Orders}.id`,
      relationship: `belongsTo`
    }
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },

    quantity: {
      sql: `quantity`,
      type: `sum`
    },

    price: {
      sql: `price`,
      type: `sum`
    },

    // A rolling window measure
    revenue: {
      sql: `price`,
      type: `sum`,
      rollingWindow: {
        trailing: `unbounded`,
      }
     }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  }
});
```

Click <Btn>Save All</Btn> to apply the changes to the development version of your API. Lastly, click <Btn>Commit & Push</Btn> to merge your changes back to the main branch. You will see your changes deployed in the <Btn>Overview</Btn> tab.

Awesome! You’ve just built a customized data model to suit your project. The next part involves bringing your data to Observable.

## Connecting Observable to Cube

Cube offers three different APIs to deliver data (SQL, REST, and GraphQL) to downstream applications. SQL API and REST API are both fine options to access Cube from Observable notebooks, with their own pros and cons.

### Using SQL API

Obviously, the very first step is to [sign up](https://observablehq.com) for an Observable account if you don't have one already. Then, let's create a new blank notebook:

![New notebook](https://ucarecdn.com/6c346037-85dc-473e-92bc-93d0ce394ebd/)

To read data from the SQL API, we need to add the <Btn>Database query</Btn> cell:

![Database query](https://ucarecdn.com/7c4b3260-c444-423d-b87c-c63c175654cc/)

After adding the cell, click <Btn>Select a database...</Btn> and then <Btn>+ Create database</Btn> to navigate to the Databases page in Settings:

![Databases in Settings](https://ucarecdn.com/fb5c5e01-3e6d-4b0d-be9e-5a7a34c75f9f/)

On that page, click on <Btn>+ New database</Btn> and then navigate back to Cube Cloud for a second. On the <Btn>Overview</Btn> tab, click on <Btn>Deploy SQL API</Btn> to generate your credentials:

![SQL API](https://ucarecdn.com/db421dee-747c-4129-a0a7-05583132f834/)

You can then enter these credentials to the <Btn>New database</Btn> popup in Observable. For convenience, you can also use the following credentials:

```
Host:      grim-dola.sql.aws-us-west-2.cubecloudapp.dev
Port:      5432
Database:  grim-dola
Username:  cube
Password:  4457642218d9552e74db8bafff1e3047
```

![New database](https://ucarecdn.com/960f42b4-95f0-47bd-8c8f-b4ce361a5921/)

Now you can navigate back to your notebook and select Cube from the dropdown in your <Btn>Database query</Btn> cell:

![Database query cell](https://ucarecdn.com/932d29d6-8f65-42e4-9b54-bcb346ade792/)

Now you can [run queries](https://cube.dev/docs/backend/sql#querying-fundamentals) against the SQL API:

![Query result](https://ucarecdn.com/ece1aa7f-4849-4e17-b2ce-1dadf292fd1e/)

It's worth noting that there's another cell type called <Btn>Data table</Btn> which is very convenient. Similar to <Btn>Database query</Btn>, you can select Cube as the database. However, you don't need to  write the SQL by hand. You can proceed by selecting a table, representing to a cube in your data model:

![Data table](https://ucarecdn.com/95845ac2-a048-40cc-9685-7d9bdc37bee2/)

Then, you can filter, pick select measures and dimensions, sort, and slice the data. Observable will generate the SQL for you under the hood:

![Data table with the result](https://ucarecdn.com/f144e4d2-344b-43c7-87a9-a6fa383eed53/)

To visualize the data, you should give the cell a name (e.g., `orders_by_date`) and select from a plethora of available chart types:

![Available visualizations](https://ucarecdn.com/8ad72b5e-0a6a-4799-8187-2debfa436868/)

If needed, you can also apply a last-mile transformation with JavaScript:

```javascript
orders_by_date_t = orders_by_date
  .map(e => ({ ...e, count: parseInt(e.count) }))
```

Then, you would probably pick and customize one of the many Observable Plot snippets:

![Plot](https://ucarecdn.com/f627afdf-4a7d-4021-b72e-9124d17c595e/)

```javascript
Plot.plot({
  marks: [
    Plot.ruleY([0]),
    Plot.lineY(orders_by_date_t, {x: "createdAt", y: "count"})
  ]
})
```

Alternatively, you can import and use [Vega-Lite](https://vega.github.io/vega-lite/) library which is widely popular and used by thousands of data enthusiasts: 

![Vega-Lite](https://ucarecdn.com/592d7dc7-3342-4593-af73-2abab6f06829/)

```javascript
import {vl} from "@vega/vega-lite-api"

vl.markLine()
  .data(orders_by_date_t)
  .encode(
    vl.x().fieldT("createdAt").timeUnit("utcyearmonthdate").title("Date"),
    vl.y().fieldQ("count").title("Count")
  )
  .render()
```

If you'd like to get inspiration for more advanced data visualizations that you can build with Observable, check out the available [templates](https://observablehq.com/templates) and [featured works](https://observablehq.com/featured-creators) by the Observable community. Also, consider diving deep into [Observable Plot](https://observablehq.com/collection/@observablehq/plot) library, the Swiss knife of data visualization in Observable.

### Using REST API

Now let's see how you can also read data from the REST API. First, we need to add the generic <Btn>JavaScript</Btn> cell:

![JavaScript cell](https://ucarecdn.com/ff0dfd08-ead2-44ee-8a62-a48e701b666e/)

Then, let's navigate back to Cube Cloud. On the <Btn>Overview</Btn> page, we can copy the endpoint URL for the REST API:

![REST API](https://ucarecdn.com/e0b8ae9a-676d-422f-b6ef-dbe873df5764/)

Here's what I've copied from my Cube Cloud deployment:

```
https://awesome-ecom.gcp-us-central1.cubecloudapp.dev/cubejs-api/v1
```

You'll also need to generate a long-lasting JSON Web token. (If you need a hand, pleae check the [Cube docs](https://cube.dev/docs/security#generating-json-web-tokens-jwt) on JWT generation.) Here's what I've generated for my deployment:

```
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw
```

Now we're ready to insert this code snippet in the JavaScript cell:

```javascript
orders_over_time = fetch("https://awesome-ecom.gcp-us-central1.cubecloudapp.dev/cubejs-api/v1/load", {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw',
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      query: {
        measures: ['Orders.count'],
        timeDimensions: [{
          dimension: 'Orders.createdAt',
          granularity: 'month'
        }]
      }
    })
  })
  .then(response => response.json())
  .then(json => json.data)
  .then(data => data.map(e => ({
    count: parseInt(e["Orders.count"]),
    createdAt: d3.utcParse(e["Orders.createdAt.month"])
  })))
```

Let's highlight a few things here:
* We need to append `/load` to your API URL specified as a `fetch` argument.
* We need to add the token next to the `Bearer` part of the `Authorization` header.
* We need to put the Cube query inside the `JSON.stringify` call.

It's enough to make an API call and fetch the data. In a similar fashion, here's how you can visualize the data with Vega-Lite: 

![Vega-Lite](https://ucarecdn.com/592d7dc7-3342-4593-af73-2abab6f06829/)

```javascript
my_published_cell = vl.markLine()
  .data(orders_over_time)
  .encode(vl.x().fieldT("createdAt").timeUnit("utcyearmonth").title("Date"),
          vl.y().fieldQ("count").title("Orders"))
  .render()
```

### Choosing an API to use

You should pick an API with these pros and cons in mind:

Cube [SQL API](https://cube.dev/docs/backend/sql):
* allows to use <Btn>Database query</Btn> cells where you can write concise SQL queries
* allows to use <Btn>Data table</Btn> cells where you can compose queries in the UI
* requires a separate service to set up multitenancy and fetch security context
* won't work in published Observable notebooks on a free plan (Observable [turns database connections off](https://observablehq.com/@observablehq/databases#cell-42) after a notebook *on a free plan* is published)

Cube [REST API](https://cube.dev/docs/rest-api):
* requires to use <Btn>JavaScript</Btn> cells to write the code to fetch the data which can sometimes get overly verbose
* allows to pass security context in a JSON Web Token for multitenancy
* will work in published Observable notebooks *on a free plan*

However, since Observable has very sound [pricing plans](https://observablehq.com/pricing), compatibility with published notebooks should not be a sole deciding factor here.

## Embedding Observable notebooks

Observable provides a very rich set of embedding options, far exceeding what other data notebooks provide in terms of customizability.

**Notebook.** Obviously, you can publish the entire notebook by clicking <Btn>Publish...</Btn> on the top of your notebook.

![Publish](https://ucarecdn.com/ae1d68a5-640f-4801-965e-c3ca844aff70/)

**Cells.** You can publish an individual cell or a set of cells as well. An interesting quirk is that you can only publish a cell if it has a name: to do that for JavaScript cells, assign the contents to a variable. Then, you can click <Btn>Embed</Btn> in the dropdown menu:

![Embed](https://ucarecdn.com/43aaffb0-3ff5-4bf6-b547-c9ecdb7d9661/)

You are free to choose whether you're publishing all cells in the notebook, a subset of cells, or just a single one:

![Embedding options](https://ucarecdn.com/d5cce206-bcf4-40e4-93d5-97d3fedc62c6/)

Then, you have to choose between several powerful [embedding options](https://observablehq.com/@observablehq/embeds):

1. <Btn>Iframe</Btn> will give you a code snippet that you can <Btn>Copy</Btn> and embed into any HTML document, including a page in your content management system like Gatsby or even another Observable notebook. Wrapped into an `iframe` tag, the code and styles of embedded content are fully isolated from your application. 
2. With <Btn>Iframe</Btn>, you can also <Btn>Copy URL only</Btn> and paste it into environments that support the [oEmbed](https://oembed.com) standard, e.g., Notion, Medium, or Reddit.
  ![Embedding to Notion](https://ucarecdn.com/adde8f0b-6fe5-4b38-813f-a21470f757dc/)
3. <Btn>Runtime with JavaScript</Btn> will give you a code snippet that you can embed into an HTML document as well. The difference is that it will load a tiny JavaScript library and render the cell in place, giving you the freedom to customize how the cell is styled and displayed.
4. <Btn>Runtime with React</Btn> works similarly, the only difference is that your cell is represented as a React component. If you're building a React app, it's the most native way to embed a cell into it.

## Wrapping up

If you've picked Observable as your data notebook of choice, consider using it with Cube. It will provide consistent metrics for your data analysis and data apps you'll build and publish.

Would you like to learn more about Cube? Check the [documentation](https://cube.dev/docs/) and create a free [Cube Cloud account](https://cubecloud.dev/auth/signup) today.

Also, please feel free to [join our community](https://slack.cube.dev/) of more than 6000 developers and data engineers on Slack, [drop us a line](https://cube.dev/contact), or give Cube a [star on GitHub](https://github.com/cube-js/cube.js). Good luck!

Igor Lukanin

Observable

Use Observable with Cube for interactive data exploration.

Observable tutorial: Analyze data in a JavaScript-native data notebook

Data science and analytics are all about metrics. However, since a single metric can be calculated in multiple ways with different SQL queries, it’s important for all teams working collaboratively to be on the same page.

A consistent data model upstream of Hex can help derive end results that can be easily shared across teams. Read on to see how Hex and Cube can work together to bring consistency and performance to your data pipeline.

## What is Hex?

[Hex](https://hex.tech) is a modern data workspace that makes it easy to connect to data, analyze it in collaborative SQL and Python-powered notebooks, and share work as interactive data apps and stories. With Hex, you can *stop churning out analyses, and start creating knowledge*.

You will find these three major elements in Hex:
* **Logic View**, a notebook-based interface where you can develop your analysis: connect to data sources, write SQL and Python code, and visualize the results (collaboratively and under version control).
* **App Builder**, an integrated interface builder where you can arrange elements from the Logic View into an interactive app with dynamic reports and dashboards (and even schedule automatic data update).
* **Share Dialog**, a feature to invite stakeholders, customers, and team members to collaborate on your analysis, interact with it, or leave comments. You can also expose the analysis as a publicly-accessible app.

## What is Cube?

[Cube](https://cube.dev) is the [headless BI](https://cube.dev/blog/headless-bi) platform for accessing data from [modern data stores](https://cube.dev/docs/config/databases), organizing it into consistent metrics definitions, and delivering them to downstream applications. 

Why would you want to have Cube in your data pipeline? Essentially, because Cube will provide consistent data for all teams using Hex. You might be already aware of this concept since it's really similar to the way Transform metrics store [integrates](https://hex.tech/blog/transform-hex) with Hex.

![Cube + Hex](https://ucarecdn.com/625ddee4-8585-4a1d-91eb-405cd2a9c04c/)

Cube will also provide amazing performance, accelerating the access to data for all Hex notebooks and users, making your workflow fast and fun.

*This is how Cube and Hex look together IRL:*

<div style={{ position: 'relative', paddingBottom: '54.84375%', height: 0 }}><iframe src="https://www.loom.com/embed/8a35dfd4713947ddbfd4b7e0aa6142a1" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style={{ position: 'absolute', top: 0, left: 0, width: '100%', height: '100%' }}></iframe></div>

## What Cube brings to table

Let’s explore how having Cube in the data pipeline is beneficial to Hex users.

**Data source support.** Hex has [great connectivity](https://learn.hex.tech/docs/connect-to-data/data-connections/overview) to well-known data warehouses and databases. Cube supports even more [data sources](https://cube.dev/docs/config/databases) (including ClickHouse, Firebolt, and Presto) as well as data federation via cross-database joins. 

**Data modeling.** Even if you can connect to all the data sources in the world, it won’t make much of a difference unless you can effectively work with data. Defining the right SQL query over and over again for every new business scenario (or team member) is like having to unravel a pair of tangled wired earphones for the hundredth time in a row.

With Cube’s open source [data modeling layer](https://cube.dev/blog/open-source-data-modeling), you can do this once: maintain all the metrics definitions in one place and abstract away the complexities of your original dataset. By the time data reaches your Hex notebook, it is ready to plug and play.

**Performance.** One of Hex’s features is the ability to cache cells within the notebook. Cube also provides you with an upstream, centralized caching layer. It ensures consistent performance and data freshness across use cases or teams, with each collaborating member receiving access to the same values at the same time. 

**Native SQL.** If you have used [Hex with Transform](https://hex.tech/blog/transform-hex), you already experienced the benefits of pairing a metrics store with a collaborative data workspace. For instance, here’s how you would query Transform from a Hex notebook using the custom `mql_query` function to fetch the "revenue" metric by the "country" dimension:

```sql
SELECT *
FROM mql_query(<revenue by country>)
```

Here’s how the same result set can be retrieved via Cube’s SQL API. In line with the common “select aggregates, group by dimensions” convention, Cube allows to write native, syntactically correct SQL queries, similar to how you’d query a data warehouse directly:

```sql
SELECT country, MEASURE(revenue)
FROM data 
GROUP BY 1
```

## How Cube and Hex can be connected

**Running Cube.** To keep things simple and save time, we'll run fully managed Cube in [Cube Cloud](https://cube.dev/cloud). Please proceed to the [sign up page](https://cubecloud.dev/auth/signup) and follow along the [instructions](https://cube.dev/docs/product/getting-started/core/create-a-project) on connecting to your data source and generating an initial data model.

You can use the following credentials to get up and running in no time:

```
Hostname:  demo-db-examples.cube.dev
Port:      5432
Database:  ecom
Username:  cube
Password:  12345
```

This is what your deployment in Cube Cloud will look like:

![Cube Cloud deployment](https://ucarecdn.com/438920db-a99c-4b15-a268-df3009ec22d5/)

**Working with the data model.** To see the data model you created above, navigate to the "Schema" tab. You will see files named `LineItems.js`, `Orders.js`, `Users.js`, etc. under the "schema" folder which defines the cubes in your data model.

![Data model](https://ucarecdn.com/490d930a-dc2a-4521-accd-64df4cd7f5b5/)

Key ideas on what we’re seeing above:

* The cube is a logical entity that groups measures (quantitative data) and dimensions (qualitative data) together into [metrics definitions](https://cube.dev/docs/schema/reference/cube).
* In this case, we have defined a cube over the entire `public.line_items` table.
* [Measures](https://cube.dev/docs/schema/reference/measures) are defined as aggregations (e.g. count, sum, etc.) over columns in the table.
* [Dimensions](https://cube.dev/docs/schema/reference/dimensions) are defined over textual, numeric, or temporal columns in the table.
* You can define complex measures and dimensions with custom SQL statements and references to other measures and dimensions.

**Customizing the data model.** Of course, you can that. For this example, we’ll update it by adding the "total revenue" as a measure to the `LineItems` cube:

```javascript
// A rolling window measure
revenue: {
  sql: `price`,
  type: `sum`,
  rollingWindow: {
    trailing: `unbounded`,
  },
}
```

To do this, click on the **Enter Development Mode** button in Cube Cloud to unlock the schema files for modifications. This essentially creates a development environment (or "branch") where you can make changes to the data model.

The following code snippet shows the contents of the LineItems.js file (which defines the metrics for the "LineItems" cube) with the "revenue" rolling window added to the measures section. Navigate to the `LineItems.js` file and replace its contents with the snippet below:

```javascript
cube(`LineItems`, {
  sql: `SELECT * FROM public.line_items`,

  preAggregations: {
   // Pre-Aggregations definitions go here
   // Learn more here: https://cube.dev/docs/caching/pre-aggregations/getting-started 
  },

  joins: {
    Products: {
      sql: `${CUBE}.product_id = ${Products}.id`,
      relationship: `belongsTo`
    },

    Orders: {
      sql: `${CUBE}.order_id = ${Orders}.id`,
      relationship: `belongsTo`
    }
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },

    quantity: {
      sql: `quantity`,
      type: `sum`
    },

    price: {
      sql: `price`,
      type: `sum`
    },

   // A rolling window measure
    revenue: {
      sql: `price`,
      type: `sum`,
      rollingWindow: {
        trailing: `unbounded`,
      }
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  }
});
```

**Accessing the data model in Hex.** You just need to [sign up](https://app.hex.tech/signup?source=nav) for Hex and add a data connection with a Postgres database: Cube’s SQL API speaks PostgreSQL dialect of SQL which makes it compatible with all kinds of data tools, Hex included. You can take your credentials in the SQL API section on the Overview page of Cube Cloud:

![SQL API credentials](https://ucarecdn.com/517cf43a-bcbe-47e9-b5ba-5afc74249554/)

Alternatively, you can take these example credentials:

```
Host:      grim-dola.sql.aws-us-west-2.cubecloudapp.dev
Port:      5432
Database:  db
User:      cube@grim-dola
Password:  4457642218d9552e74db8bafff1e3047
```

Then, enter the credentials into the new data connection dialog in Hex:

![Untitled](https://ucarecdn.com/c3303b01-9878-4b99-8b02-1f8689c3b338/)

Now you can run queries to Cube through SQL cells in Hex. Let's use this (pretty much straightforward) SQL query to fetch the revenue measure we've created earlier in the data model:

```sql
SELECT
    DATE_TRUNC('year', createdAt) AS year,
    MEASURE(revenue) AS revenue
FROM LineItems
WHERE createdAt BETWEEN '2020-01-01' AND '2023-01-01'
GROUP BY 1
ORDER BY 1
```

Here's what you'll get:

![Result set](https://ucarecdn.com/84f437fd-7f77-4fde-8c82-6e9939357e44/)

Now you're only one step away from adding a visualization of the total revenue by year (a Chart cell, already visible on the screenshot above, is ideal for this):

![Untitled](https://ucarecdn.com/a342f7c0-eda0-498c-92a6-e35a7d16f7c5/)

Of course, you can instantly convert your Hex notebook into an interactive data app that can be shared with stakeholders and team members. It's as easy as switching from the Logic View to the App Builder in the top bar:

![App Builder](https://ucarecdn.com/1d2a6f61-f7a1-4fc9-a5d6-d86f5cf68b29/)

## Publishing and embedding Hex apps

One you're done with your analysis, you can publish a version of the data app. Hex will event show you the difference between the current and the previous version of the logic in the Logic View tab:

![Publishing](https://ucarecdn.com/919096c7-73a3-47cf-b517-28997408bb00/)

Once your app is published, you can access it by a link or embed individual cells. Hex provides two option for embedding:
* a link to a [web page](https://app.hex.tech/768546e0-e3c7-430f-aa60-daa904f2ab5f/app/f7a52a3f-5f9a-41e2-9361-bd69852c7a18/latest?embedded=true&embeddedStaticCellId=533c0b3b-4544-4efd-971f-df344a70ffcf) with an individual cell that you can insert in apps like Notion;
* raw HTML code you can insert into a front-end app you're building.

With these options, you can easily combine Hex apps with other apps to share the knowledge across your organization. Check [Hex docs](https://learn.hex.tech/docs/sharing/embedding) to learn more about embedding.

## Wrapping up

By connecting Hex with Cube upstream, you will always have consistent metrics definitions as the starting line for your data analysis and data apps you'll build and publish.

Would you like to explore more about Cube? [Check the docs](https://cube.dev/docs/) and create a free [Cube Cloud account](https://cubecloud.dev/auth/signup) today.

Also, please feel free to [join our community](https://slack.cube.dev/) of 6000 developers and data engineers on Slack, [drop us a line](https://cube.dev/contact), or give Cube a [star on GitHub](https://github.com/cube-js/cube.js). See you there!

Isha Terdal

Use Hex with Cube to collaborate on consistent metrics.

Announcing the Cube and Hex integration: Headless BI meets notebooks and data apps

Last month, just two days after our [update to the SQL API](https://cube.dev/blog/expanded-bi-support), Deepnote has announced its [general availability](https://deepnote.com/blog/out-of-beta-cl3m2ng1zsh3c0cllswq1rzy8) to the world's best data teams. 

## What is Deepnote?

[Deepnote](https://deepnote.com/home) represents a new generation of [Jupyter](https://jupyter.org)-compatible notebooks that enables real-time collaboration between teams of data engineers, data scientists, and analysts. With Deepnote, you can explore, analyze, and present your data in infinite ways while using Cube to consistently organize your data upstream. 

You can think of Deepnote as the "Google Docs" of data science: it's a data notebook that allows for instantaneous collaboration through shared notebooks and workspaces. The notebook application runs in the cloud that provides all compute resources so you don't need to provision your own hardware. In a way, Deepnote a fully managed [multi-user Jupyter server](https://github.com/jupyterhub/jupyterhub) done right and for your convenience.

## What is Cube? 

Cube is the [headless BI](https://cube.dev/blog/headless-bi) platform that sits between your data source and downstream data applications. Because of its position in the data stack, Cube is able to provide consistent metrics definitions for all your data flowing downstream.

Cube works with all [modern data stores](https://cube.dev/docs/config/databases), creates a metrics layer that standardizes the data for any purpose, and delivers it downstream to any data notebook, application, or BI tool via SQL, REST, or GraphQL APIs. 

## Consistency for Data Teams

One of the biggest benefits of using Deepnote and Cube together is providing a common starting line for various teams working together within an organization.

![Deepnote and Cube](https://ucarecdn.com/9332b6c9-4fcc-4f07-b80b-80043696662f/)

Companies that work cross-functionally and collaboratively often face a similar problem: inconsistent data, especially across different teams. It generally originates to varying metrics definitions across the many tools that each team uses. At some point, teams wrangling the same data realize that they use different SQL queries to calculate metrics and, unfortunately, mislead their users with inconsistent insights. 

**Let’s go through a super high-level example.** Imagine both the product and customer success teams want to calculate product success "over the last year". The product team queries the 2021 data (01/01/2021 to 12/31/2021), whereas customer success queries it as year-over-year (06/01/2021 to 06/01/2022). 

Since the data pipelines of both teams are siloed, neither team necessarily has visibility into how the other defined the query; it’s likely these teams use different tools too. This discrepancy in query results can absolutely misalign the teams working towards a single goal, but working off of disparate data. 

**Now, imagine a company that uses both Deepnote and Cube.** Cube maintains, stores, and defines the data models upstream of the teams’ data applications. Meanwhile, Deepnote allows for cross-functional collaboration, exploration, and visualization of those consistently defined metrics. No more data or workflow silos—just reliable data to drive the whole organization in the same direction.

## Defining Consistent Metrics

Let’s illustrate how Cube complements Deepnote in the data engineering pipeline. In this example, we’ll use a e-commerce dataset and build a data model around it. We’ll also customize the data model a bit to show how powerful Deepnote and Cube are together.

**Running Cube.** To keep things simple and save time, we'll run fully managed Cube in [Cube Cloud](https://cube.dev/cloud). Please proceed to Cube Cloud’s [sign up page](https://cubecloud.dev/auth/signup) and follow along the [instructions](https://cube.dev/docs/product/getting-started/core/create-a-project) on connecting to your data source and generating a data model.

You can use the following credentials to get up and running in no time:

```
Hostname:  demo-db-examples.cube.dev
Port:      5432
Database:  ecom
Username:  cube
Password:  12345
```

This is what your deployment in Cube will look like:

![Cube deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ddef76aa-2f72-4d58-acb7-aa949488df84.png)

**Defining the data model.** To see the data model you created above, navigate to the "Schema" tab. You will see files named `LineItems.js`, `Orders.js`, `Users.js`, etc. under the "schema" folder which defines the *cubes* in your data model.

![Data schema](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1b0540ff-0d99-43e5-bfc6-268955bdabbe.png)

Key ideas on what we’re seeing above:
* The *cube* is a logical entity that groups measures (quantitative data) and dimensions (qualitative data) together into [metrics definitions](https://cube.dev/docs/schema/reference/cube).
* In this case, we have defined a cube over the entire `public.line_items` table.
* [Measures](https://cube.dev/docs/schema/reference/measures) are defined as aggregations (e.g. count, sum, etc.) over columns in the table.
* [Dimensions](https://cube.dev/docs/schema/reference/dimensions) are defined over textual, numeric, or temporal columns in the table.
* You can define complex measures and dimensions with custom SQL statements and references to other measures and dimensions.

**Customizing the data model.** While Cube Cloud has a ton of time-saving features, let’s play around with a particularly useful one: the rolling window measure.

Let’s assume you want to calculate the total revenue brought in from sold line items over all time. This can be done by writing a SQL query in your local Jupyter notebook or any other BI tool you may be using. But what happens if you want to observe the total revenue over different time granularities? Or add another dimension to observe the revenue data?

This is where your "LineItems" cube can be modified to abstract away your varied data requests. Simply define the metrics in your data model once, and then use them time and again across teams to achieve your business goals.

For this example, we’ll customize the data model in the cloud by adding the "total revenue" as a measure to the LineItems cube.

```js
// A rolling window measure
revenue: {
  sql: `price`,
  type: `sum`,
  rollingWindow: {
    trailing: `unbounded`,
  },
}
```

To do this, click on the **Enter Development Mode** button to unlock the schema files for modifications. This essentially creates a development environment (or "branch") where you can make changes to the data model.

The following code snippet shows the contents of the `LineItems.js` file (which defines the metrics for the "LineItems" cube) with the "revenue" rolling window added to the measures section.

Navigate to the `LineItems.js` file and replace its contents with the snippet below.

```js
cube(`LineItems`, {
  sql: `SELECT * FROM public.line_items`,

  joins: {
    Products: {
      sql: `${CUBE}.product_id = ${Products}.id`,
      relationship: `belongsTo`
    },

    Orders: {
      sql: `${CUBE}.order_id = ${Orders}.id`,
      relationship: `belongsTo`
    }
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },

    quantity: {
      sql: `quantity`,
      type: `sum`
    },

    price: {
      sql: `price`,
      type: `sum`
    },

    // A rolling window measure
    revenue: {
      sql: `price`,
      type: `sum`,
      rollingWindow: {
        trailing: `unbounded`,
      }
     }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  }
});
```

Click **Save All** to apply the changes to the development version of your API. Lastly, click **Commit & Push** to merge your changes back to the main branch. You will see your changes deployed in the "Overview" tab.

Awesome! You’ve just built a customized data model to suit your project. The next part involves connecting your data to Deepnote.

## Connecting Deepnote to Cube

Cube offers three different APIs to deliver your data (SQL, REST, and GraphQL) to downstream applications, and the SQL API would be the best option to bring standardized data to Deepnote.

Click on the **Deploy SQL API** button to generate your credentials:

![SQL API](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f6990853-85b1-48a9-9d70-429b96d018e5.png)

Obviously, the first step is to [sign up](https://deepnote.com/sign-up) for a Deepnote account if you don't have one already. Then, follow these instructions to connect your Deepnote to Cube. (Please note that you have to select *PostgreSQL* integration type, contrary to what's shown in the video.)

<div style={{ "position": "relative", "padding-bottom": "66%", "height": 0 }}><iframe src="https://www.loom.com/embed/8eda22ada48344629606622425276daa" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style={{ "position": "absolute", "top": 0, "left": 0, "width": "100%", "height": "100%" }}></iframe></div><br/>

You can also visualize, add comments, and explore the full palette of [features](https://docs.deepnote.com/features/command-palette) in Deepnote.

## Wrapping up

By connecting Deepnote with Cube upstream, you have consistently defined data as the starting line for your data science or business analysis projects.

Want to see how Deepnote and Cube can power your project? [Check the docs](https://cube.dev/docs/) and create a free [Cube Cloud account](https://cube.dev/cloud) today.

Have questions or feedback? [Drop us a line](https://cube.dev/contact), join our [Slack community](https://slack.cube.dev) of more than 5000 data folks, and give Cube a [star on GitHub](https://github.com/cube-js/cube.js).

Data Notebooks

Deepnote

Hex

Observable

Observable tutorial: Analyze data in a JavaScript-native data notebook

Announcing the Cube and Hex integration: Headless BI meets notebooks and data apps

Deepnote tutorial: Collaborative data analysis in a Jupyter-compatible data notebook

Get Cube updates to your inbox

Upgrade your data stack today