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 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 is the headless BI platform for accessing data from modern data stores, 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 with Hex.

Cube + Hex

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:

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 to well-known data warehouses and databases. Cube supports even more data sources (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, 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, 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:

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:

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. Please proceed to the sign up page and follow along the instructions 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

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

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.
  • In this case, we have defined a cube over the entire public.line_items table.
  • Measures are defined as aggregations (e.g. count, sum, etc.) over columns in the table.
  • 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:

// 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:

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 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

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

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:

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

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

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

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

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 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 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 and create a free Cube Cloud account today.

Also, please feel free to join our community of 6000 developers and data engineers on Slack, drop us a line, or give Cube a star on GitHub. See you there!