Today, we’re announcing our official integration with [Databricks][1]. Here’s how it works—and what it means. 

> __Want a personalized demo or some Cube swag? Join us at the Databricks Data + AI Summit in San Francisco, June 27-30! [Register here][32] for some 1:1 time with the Cube team.__



## What is Databricks?

Databricks is a unified, open, web-based platform for working with Spark. Its multicloud infrastructure provides automated cluster management and IPython-style notebooks to unify data warehousing, AI, and analytics in a single platform. 

The Databricks [Data Lakehouse][2] brings the best of both worlds: the reliability, governance, and performance of data warehouses with the openness, flexibility, and ML capabilities of data lakes. The Data Lakehouse efficiently handles all data types while applying a consistent security and governance approach. 

Unifying data in a single platform fulfills the needs of all your data teams—engineering, analytics, BI, data science, and ML—and beautifully simplifies your modern data stack. 


## What is Cube?

[Cube][3]’s headless business intelligence platform makes lakehouse data consistent, performant, and accessible to every downstream application. We do this with [data modeling that defines your metrics up-stack][4]. 

Cube’s advanced [pre-aggregation][5] capabilities powerfully condense frequently-used queries to optimize response times. Cube then provides this data to every application with GraphQL, REST, and SQL APIs so that every tool—and every data consumer—can work with the same data.

![cube-schema][30]

## The Cube and Databricks Integration

By combining Databricks’ unified analytics and data store with Cube’s data modeling, access control, caching, and APIs, it’s now possible to build a new dashboard or embed analytics features into customer-facing apps in minutes—not weeks. 

Select Cube and Databricks users already have been enjoying this integration in production for use cases like creating dashboards for internal analytics. Here’s one example: by consuming operational data with Kafka, transforming it into insights with Databricks, then using Cube as an API and caching layer to power visualization layers, users have been able to find actionable insights and identify opportunities to optimize their operations.

It’s easy to connect a workspace to Cube using Databricks’ SQL endpoint. Here’s how.

For starters, we recommend using Cube with the [Databricks SQL Endpoint][6]. Follow [Databricks’ docs to create a SQL Endpoint][7]. 


### Creating a new SQL endpoint


 - Log in to your Databricks Workspace account.
 - Navigate to the **SQL Console** on the left hand bar and select **SQL** from the drop-down menu.

![select-sql-tab-in-databricks][17]

 - Click __API Endpoints__, and then on the top right, select __+New SQL Endpoint.__

![click-API-endpoint][18]
![create-new-SQL-endpoint][19]

 - Next, name your endpoint and choose a __Cluster Size__. Make sure to set the __Auto Stop__ to at least 60-minutes of no activity, then click __Create__.
 
![name-new-endpoint][20]

After you’ve configured data access on the SQL Endpoint, it’s time to get your credentials for the SQL Endpoint. 

 - In the __Databricks SQL Console__, go to __Endpoints__ and click on the endpoint you created.

![go-to-endpoints-in-console][21]
![click-on-endpoint-created][22]

 - Navigate to __Connection Details__ and copy the HTTP Path, Port, and Server Hostname:

![connection-details][23]

Once you have your credentials, [generate a Personal Token][8]. 

 - Navigate to the __Admin Console__, click on __Settings__, and select __User Settings__.

![user-settings][24]

 - Toggle over __Personal Access Tokens__ and click on __+Generate New Token__.

![personal-access-token][25]

 - Name your token and set the expiration to __“Lifetime”__, then click __Generate__.

![name-your-token][26]

 - Copy the generated JDBC URL.
 - Attach your newly-generated personal token to the JDBC URL you copied in step 6, e.g. `jdbc:spark://dbc-xxxxxxxx-3957.cloud.databricks.com:443:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/endpoints/0b27a1dfc18f82ae;UID=token;PWD=PERSON-TOKEN`
 - Finally, select the database you’d like to query with Cube. In our example, we’ll use “default”.

![select-database][27]

### Connecting Databricks to Cube

 - Sign into [Cube Cloud][9] and use the wizard to create a new deployment. Select Databricks from the database connection list.

![sign-into-Cube-Cloud][28]

 - Enter your Databricks JDBC URL and personal token as well as your database name to connect.
 
![databricks-jdbc-url][29]


 - If needed, copy Cube Cloud’s IP addresses and add them to your Databricks allow-list. ([Databricks’ documentation for IP access lists is here][10].)
 

That’s it! Now you are ready to use Cube on top of your Databricks to model your data, manage security rules, apply caching, and expose your data definitions to any downstream application via Cube’s REST, GraphQL, or SQL APIs. 

You can check out [more examples of integrations with visualization tools on our website][11].



## Try the Cube and Databricks Integration for yourself

To follow the tutorial above and experience everything this integration offers, check out our [docs][12]—and, if you don’t already have one, sign up for [a free Cube Cloud account][13].



## Tell us what you think

We’d love to hear from you, learn what you’re building, and strategize how to help. Find us via Cube’s [Slack][14] and [GitHub][15] communities—or [schedule a time][16] to chat 1:1.


[1]: https://databricks.com/
[2]: https://databricks.com/product/data-lakehouse
[3]: https://cube.dev/
[4]: https://cube.dev/blog/headless-bi
[5]: https://cube.dev/docs/schema/reference/pre-aggregations
[6]: https://docs.databricks.com/sql/admin/sql-endpoints.html
[7]: https://docs.databricks.com/sql/admin/sql-endpoints.html#create-a-sql-endpoint
[8]: https://docs.databricks.com/administration-guide/access-control/tokens.html#enable-or-disable-token-based-authentication-for-the-workspace
[9]: https://cube.dev/cloud
[10]: https://docs.databricks.com/security/network/ip-access-list.html#add-an-ip-access-list
[11]: https://cube.dev/examples
[12]: https://cube.dev/docs/config/databases/databricks/jdbc#databricks-jdbc
[13]: https://cube.dev/cloud/
[14]: https://slack.cube.dev/
[15]: https://github.com/cube-js/cube.js
[16]: https://cube.dev/contact
[17]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/39cfa0b3-c6b9-44dd-80a2-41bae6dea1a3.png
[18]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/38cfe617-ae91-4a85-a610-12eaba6c9dfe.png
[19]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/02a29df7-26dd-41ba-a442-7fe46cf58246.png
[20]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f64ca517-e89e-437b-92e2-1c48752b8ca4.png
[21]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5b5644d7-d5e1-4bff-87cd-abf40bbe459d.png
[22]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f8bd03ad-4edf-4452-a866-9d3c3140dc42.png
[23]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/6acf6cd1-0423-45f9-8bb2-884e3115e689.png
[24]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8fd9c163-ebae-4b1e-b846-6178df628781.png
[25]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7877ce4f-0e2d-49c1-97f9-56769fef46c7.png
[26]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9dd552af-3dd4-40b4-9b9a-b3fd6c2bc8d2.png
[27]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/580253c6-1077-4291-a4a3-13303d383cda.png
[28]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cd5ae87c-f9d0-432b-9a98-b378580d8d1d.png
[29]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7f3f30a5-33a4-4073-8669-08bc7082c651.png
[30]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e758755f-d633-45da-8ae3-92bc510cc9a1.png
[31]:https://databricks.com/dataaisummit/?itm_data=mainnav-promo-dais2022
[32]:https://cube.dev/events/data-ai-summit/

Tamar Barlev

News & Product Updates

Announcing the Cube and Databricks integration

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://cubedev-blog-images.s3.us-east-2.amazonaws.com/8cc70189-7b87-476a-9a9c-39ebab94458d.png)

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/cloud/getting-started/create) 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).

Isha Terdal

Deepnote

Use Deepnote with Cube to analyze massive datasets consistently.

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

Recently, we’ve been iterating on our blog to improve the UX for Cube’s diverse audience and account for the growing volume of the content.

Now, we’re happy that data engineers and application developers have a better, cleaner way to access the guides and tutorials on using Cube with other tools in the data space.

As a [headless BI](https://cube.dev/blog/headless-bi) platform, Cube works with numerous [data sources](https://cube.dev/docs/config/databases) and [data consumers](https://cube.dev/docs/config/downstream/). We've announced six integrations over the last few months ([Tableau](https://cube.dev/blog/tableau-tutorial), [Ahana](https://cube.dev/blog/ahana-integration-querying-multiple-data-sources-with-managed-presto-and-cube), [Supabase](https://cube.dev/blog/supabase-tutorial-fast-data-visualization-apps), [Materialize](https://cube.dev/blog/materialize-integration), [QuestDB](https://cube.dev/blog/time-series-data-analytics-with-questdb), and [dbt](https://cube.dev/blog/dbt-metrics-meet-cube)) that were all accompanied by hands-on technical guides.

![Cube is headless BI](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/855489ee-4cef-4146-876b-e44d0ebcc711.jpeg)

## Navigation Rehaul

Historically, application developers have a very strong presence in Cube’s audience. However, since the announcement of the [SQL API preview](https://cube.dev/blog/introducing-cube-sql) in late 2021 (matched with the recent [expanded BI support](https://cube.dev/blog/expanded-bi-support) announcement), Cube has received a lot of interest in the data engineering community.

So, now we match that with top-level categories in the blog:

![categories](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/969737a6-dcdf-4ae5-8720-674565609762.png)

There you can can find collections of posts covering categories of tools (like [Cloud Data Warehouses](https://cube.dev/blog/category/warehouses)) or individual tools in the data space (like [Amazon Athena](https://cube.dev/blog/category/athena)).

![categories](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/378a311a-fae8-4829-b274-436b9d7e33af.png)

You can find themed collections in other blogs as well (e.g., [Hasura](https://hasura.io/blog/) or [GitHub](https://github.blog/changelog/label/security/)), and we hope that this pattern will get even wider adoption in technical publications:

![themed collections](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/844b0b23-20fd-4077-aa25-378024683008.jpg)

## Focus on Content and Authors

Catering to Cube’s deeply technical audience, we routinely publish guides and tutorials up to 5-6 thousand words in size that take 15-30 rewarding minutes to read.

Now every blog post is accompanied by a table of contents that provides for the convenient navigation through the content and helps build expectations:

![table of contents](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/67b0f8d3-91ab-43e2-b090-1b495f04db4e.jpg)

We also recognize that the blog is collectively produced by the Cube team and community, and sometimes posts are even co-authored. So, now posts are accompanied by authors’ profiles with social links—feel free to reach out and follow!

![authors](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8dab8d58-4536-4884-9ad3-9488da367211.jpg)

It’s always good to put the authors in the spotlight and we appreciate how [Evil Martians](https://evilmartians.com/chronicles) and [Smashing Magazine](https://www.smashingmagazine.com) do that:

![smashing magazine](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3f493317-2380-42c9-8e17-ea6ad39e56de.jpg)

## Sneak Peek Under the Hood

We not only enjoy the end result but we also had fun developing this blog update. Here’s what the Growth Engineering team wants to share.

![Anton and Kseniia](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5d2aed46-7d09-41df-9292-667c77438061.png)

We hope that rehauled navigation and categories make our blog more like a textbook on data engineering and app development, less like an assorted collection of geeky notes.

![Sasha](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/32d4ef9a-caa1-4d06-98d7-34b8d4689249.png)

We tried hard to make sure that users are getting value as soon as possible and there’s no disruption to content updates in the blog.

![Sofi](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5e000bf2-410c-4c3d-b15e-df6971db9454.png)

With excitement, we await Sofi’s story in [Smashing Magazine](https://www.smashingmagazine.com) where she reveals the nitty-gritty details behind the blog post feed layout.

Hope you like these updates to the blog—please share your feedback on [Slack](https://slack.cube.dev) or [Twitter](https://twitter.com/thecubejs). 

Nastya Kataeva

Content organized by data tools, better navigation in hands-on technical guides, and authors in the spotlight.

Refreshed look and feel of the Cube blog

In case you missed it, we recently released [Cube’s SQL API][8], with expanded support for BI tools and data notebooks. 

That’s exciting (we’re *very* excited) because consistent, performant, and secure data is now accessible to any data consumer in your company—from app developers to general business users. Cube’s definitions are consistent across every application. 

Think of your favorite BI tools: if they can connect to a data store via SQL, they’ll work with Cube.

To illustrate the beautiful simplicity of connecting a BI application to Cube via our SQL API, we’re here with a quick tutorial using Tableau Online and Cube Cloud.

Let’s dive in. 

## Get started with Cube Cloud

This tutorial assumes you’re using Cube Cloud—and why wouldn’t you? Our managed service is both the fastest and most secure way to run Cube—and it’s free.

If you don’t have a Cube Cloud project already, [sign up for a free account here][12], then [follow these step-by-step instructions][11] to create a project. Don’t worry if you’d rather not connect your own database to Cube for this tutorial: we’ve got you covered with a sample database.

If you already have Cube Cloud set up, feel free to jump [here][95].

## Define measures and dimensions

When you create a project, you’ll notice that Cube automatically generates a basic data schema. But for this tutorial, let’s define at least one custom [measure][13] or [dimension][14] in Cube’s schema editor, too. You’ll use it in a few minutes.

In the left-hand navigation, select the schema editor and enter Development Mode.

In this example, in the cube `Orders`, we’ll define a new measure, `totalAmount`…

[![001-schema-define.png][1]][1]

…save our change, and Commit and Push it to production:

[![008-commit-and-save.jpg][15]][15]

The new definition is automatically added to Cube’s API, and you’re ready to…

## Grab SQL connection details

Navigate to the overview page and find the “SQL API” section. Click “How to connect your BI tool”.

[![002-sql-api.jpg][2]][2]

Here, you’ll find the details you need to connect Tableau Online to Cube:

[![003-grab-creds.jpg][3]][3]

You’ll need these when you…

## Configure Tableau

Let’s make a new Workbook using the menu at top right:

[![004-new.jpg][4]][4]

In the “Connect to Data” screen, select PostgreSQL.

[![009-connect-to-data.jpg][9]][9]

You’ll be prompted to add the connection details you opened in Cube. Copy, paste, and click “Sign In”.

<video loop autoplay muted playsinline preload="auto" poster="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/37f7ace3-9b78-435d-9ff6-af329185d8f8.png">
  <source src="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b1b2ff0a-2863-4180-b312-bd730ece029b.mp4" type="video/mp4"/>
</video>

## Understand Tables and Fields

If all goes according to plan, you’ll now see a list of each of your [cubes][16] listed as _Tables_.

Drag one of them—we’ll use `Orders` again—into the Workbook editor to begin exploring.

<video loop autoplay muted playsinline preload="auto" poster="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/58fc30aa-0531-4394-ac76-3eacf1ce5d6b.png">
  <source src="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/89d4c3a1-604a-4af6-819b-5ca8cdc90d5d.mp4" type="video/mp4"/>
</video>

Et voila: now, every measure and dimension from Cube is available as a Tableau _Field_, including the custom measure we defined earlier, `totalAmount`.

[![006-tables-and-columns.jpg][6]][6]

## Build a worksheet

Now it’s time to try this out!

Build a simple chart in Tableau: drag a measure to “Rows” and a dimension to “Columns”:

<video loop autoplay muted playsinline preload="auto" poster="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c59a8e90-55ab-46c9-9ee0-11d3aaf3fcf1.png">
  <source src="https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8c159a96-79b4-4674-a9d0-7ccf09cbf4e7.mp4" type="video/mp4"/>
</video>

Just like _that_: your first Cube-powered visualization in Tableau!

## Learn more

As you can see, connecting Tableau to Cube via Cube’s SQL API makes Cube definitions accessible to every data consumer—so the same data can flow to both business users and data application developers.

Interest piqued? Check out our [SQL API docs][93], and our [guide to working with other BI tools][94].

And, watch this space because the fun is just beginning: we’re continuing to test and improve connectors for more BI applications. Have a favorite? [Let us know][92].


[1]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/870c6c3e-42bc-4d0f-b978-807f5307906f.png
[2]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0a7c2fd9-ae76-425b-906d-2f644ef77eff.png
[3]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7831a318-a8f5-4d0c-9e7d-2d58671ab9fa.png
[4]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/64669cb0-78d2-4ad0-ab76-e1cdfae04453.png
[6]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7057552d-01d6-4e2e-9bd0-c1936ff1e03a.jpg
[8]: https://cube.dev/blog/expanded-bi-support
[9]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/dd3de7cb-e553-41e4-afb4-129e0485124a.png
[11]: https://cube.dev/docs/cloud/getting-started/create
[12]: http://cubecloud.dev
[13]: https://cube.dev/docs/schema/reference/measures
[14]: https://cube.dev/docs/schema/reference/dimensions
[15]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c264b798-c308-4e55-aedc-c8c62f414ed5.jpg
[16]: https://cube.dev/docs/schema/reference/cube
[92]: https://slack.cube.dev
[93]: https://cube.dev/docs/backend/sql
[94]: https://cube.dev/docs/config/downstream
[95]: #grab-sql-connection-details

Tableau

Connecting Cube to Tableau tutorial

Bartosz Mikulski

Ahana

See how Ahana and Cube work together to help you set up a Presto cluster and build a single source of truth for metrics without spending days reading cryptic docs.

Announcing the Cube integration with Ahana: Querying multiple data sources with managed Presto and Cube

About 6 months ago, we witnessed what Andy Pavlo [wittily characterised](https://twitter.com/andy_pavlo/status/1459251061085118465) as an "old school database benchmark gang war". The timeline of events goes like this:

* [Databricks](https://databricks.com), a data lakehouse company founded by the creators of Apache Spark, published a [blog post](https://databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html) claiming that it set a new data warehousing performance record in 100 TB [TPC-DS benchmark](https://www.tpc.org/tpcds/). It was also mentioned that Databricks was 2.7x faster and 12x better in terms of price performance compared to Snowflake.
* [Snowflake](https://snowflake.com), a data warehousing company founded by ex-Oracle and ex-VectorWise experts, responded with a [blog post](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) that critically reviewed Databricks' findings, reported different results for the same benchmark, and claimed comparable price/performance to Databricks.
* After two days, Databricks followed up with another [blog post](https://databricks.com/blog/2021/11/15/snowflake-claims-similar-price-performance-to-databricks-but-not-so-fast.html) that confirmed that they stand by the initially reported results.

While this back-and-forth between respected data vendors was informative and amusing, its collateral damage was even more substantial. Databricks shared that they have [eliminated the anti-competitive *DeWitt clause*](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html) from their service terms. Snowflake followed up with a [similar update](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) to their acceptable use policy.

But what is the *DeWitt clause*?

More importantly, why would you need to know if a DeWitt clause is included in terms of service in case you'd like to do and publish a performance benchmark? Let's explore.

## Backstory of the DeWitt Clause

In 1982, at the very inception of relational databases, [David DeWitt](https://www.linkedin.com/in/david-dewitt-30189b50/), a researcher at the Department of Computer Sciences at the University of Wisconsin-Madison, was working on measuring database performance. His team wrote the Wisconsin Benchmark, tested a number of databases, and [published the results](https://pages.cs.wisc.edu/~dewitt/includes/benchmarking/vldb83.pdf).

Apparently, some folks were displeased with the results. [Michael Stonebraker](https://en.wikipedia.org/wiki/Michael_Stonebraker), a researcher at the UC Berkeley and co-creator of Ingres, called DeWitt and expressed how upset he was. However, they remained friends anyway as DeWitt recounts: in his [talk on YouTube](https://www.youtube.com/watch?v=-TIUGC4X2q8&t=420s):

<iframe width="800" height="500" src="https://www.youtube.com/embed/-TIUGC4X2q8?start=420" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

Oracle also didn’t perform very well and reacted hastily. [According to DeWitt](https://www.eweek.com/development/db-test-pioneer-makes-history/), Oracle CEO Larry Ellison was so displeased that he called the department chair and insisted, "You have to fire this guy.” Oracle also inserted a clause in their terms of use that boiled down to the fact that one can’t publish benchmarks without getting an explicit approval from Oracle.

## DeWitt Clause vs. DeWitt Embrace Clause

Nowadays, the DeWitt clause is a common provision in end-user license agreements for proprietary software that prevents from publishing information about the software without an explicit approval from the vendor. Over time, it have become widespread in the software industry and its implications are drastic:

* People avoid publishing benchmarks about software products with the DeWitt clause.
* Moreover, some won’t even bother doing a benchmark knowing they won’t be able to publish the results.

There are debates whether DeWitt clauses are [moral or legal](https://www.dwheeler.com/essays/dewitt-clause.html) because of the implications above as well as the overall corrosive effect they have on the industry. Once one supplier adds a DeWitt clause, the others can feel that they are at a disadvantage without one. We're lucky it can also work the other way round, like in the Databricks and Snowflake case. However, open source software (OSS) licenses don’t and can't have DeWitt clauses, meaning that the OSS can be legally critiqued, but not their proprietary competitors.

At the same time, some vendors do quite the opposite to the inclusion of a DeWitt clause. Instead, they change their license to the DeWitt Embrace Clause meaning that if you disclose benchmark results for them, you automatically allow them to do the same with you. In a way, DeWitt Embrace Clause is like copyleft for the DeWitt Clause. 

## Can You Benchmark %DATABASE%?

Now, do you wonder whether some vendor has or hasn't a DeWitt clause?

Shortly after the Databricks and Snowflake debate, [Mark Callaghan](https://twitter.com/MarkCallaghanDB), an ex-MongoDB and ex-Rockset database expert, checked if some database and public cloud vendors have DeWitt clauses in their terms of use.

In this blog post, I try to extend [his research](http://smalldatum.blogspot.com/2021/11/dewitt-clause-vs-public-cloud.html) and maintain an ever-green list of database and similar vendors with regard to the DeWitt clause. (If you spot any inaccuracy, which is totally possible, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post.)

*Last updated: June 2, 2022.*

### 😇 Open-source vendors (without the DeWitt clause)

Open source licenses usually grant users permission to [use open source software for any purpose](https://opensource.org/docs/osd). So, by definition, open source software can't contain DeWitt clauses (because if it did, it wouldn't be open source).

Yes, you can benchmark any of these tools:

* Apache [Drill](https://github.com/apache/drill), [Druid](https://github.com/apache/druid/), [Flink](https://github.com/apache/flink), [Hive](https://github.com/apache/hive), [Kafka](https://kafka.apache.org), [Spark](https://github.com/apache/spark)
* [appwrite](https://github.com/appwrite/appwrite)
* [ClickHouse](https://github.com/ClickHouse/ClickHouse) (they even maintain a [list of benchmarks](https://github.com/ClickHouse/ClickHouse/issues/22398) on GitHub)
* [CockroachDB](https://github.com/cockroachdb/cockroach) (licensed under BSL, not [OSI-approved](https://opensource.org/licenses/alphabetical))
* [Dremio](https://github.com/dremio/dremio-oss)
* [Elasticsearch](https://github.com/elastic/elasticsearch) (licensed under SSPL, not OSI-approved)
* [ksqlDB](https://ksqldb.io)
* [Materialize](https://github.com/MaterializeInc/materialize) (licensed under BSL, not OSI-approved)
* [MongoDB](https://github.com/mongodb/mongo) (licensed under SSPL, not OSI-approved)
* [PostgreSQL](https://www.postgresql.org)
* [Presto](https://github.com/prestodb/presto)
* [Redpanda](https://github.com/redpanda-data/redpanda) (licensed under BSL, not OSI-approved)
* [QuestDB](https://github.com/questdb/questdb)
* [TimescaleDB](https://github.com/timescale/timescaledb)
* [Trino](https://github.com/trinodb/trino)

Also, some open-source vendors collaboratively maintain benchmarking suites such as [Time Series Benchmark Suite](https://github.com/timescale/tsbs) to help choose the best tools for particular workloads.

### 😇 Vendors without the DeWitt clause

Cloud vendors can restrict you from benchmarking their services but sometimes they choose not to have the DeWitt clause.

Yes, you can benchmark any of these services:

* [Ahana Cloud](https://ahana.io/terms-of-service/)
* [Altinity Cloud](https://altinity.com/legal/)
* [appwrite Cloud](https://appwrite.io/cloud) (not launched to GA yet)
* [ClickHouse Cloud](https://clickhouse.com/cloud/) (not launched to GA yet)
* [Confluent Cloud](https://assets.confluent.io/m/103033bdcb9b3fcd/original/20220201-Confluent_Cloud_Services_Agreement.pdf)
* [Firebolt](https://d7umqicpi7263.cloudfront.net/eula/product/28a79f78-8008-4c02-a971-54ef6d8ed904/898eb1bc-7c2a-4829-b479-67f5a18a51ff.pdf)
* [Materialize Cloud](https://materialize.com/terms-and-conditions/)
* [MongoDB Cloud](https://www.mongodb.com/cloud-terms-and-conditions)
* [QuestDB Cloud](https://questdb.io/terms/) (not launched to GA yet)
* [Redpanda Cloud](https://redpanda.com/cloud/)
* [Snowflake](https://www.snowflake.com/legal/acceptable-use-policy/)
* [Starburst Galaxy](https://www.starburst.io/terms/galaxy-terms-of-service/)
* [Supabase](https://supabase.com/docs/company/terms)
* [Timescale Cloud](https://www.timescale.com/legal/timescale-cloud-terms-of-service)

However, many of these cloud vendors disallow *abusive benchmarking* in their acceptable use policies. So be careful and please don't get banned.

### 🙃 Vendors with the DeWitt Embrace clause

Some cloud vendors permit you to benchmark their service but require reciprocity: you must make the benchmark reproducible and allow benchmarking of your own service or tool in response.

Yes, you can benchmark any of these services and probably get away with it:

* [Amazon Web Services](https://aws.amazon.com/service-terms/), including [Athena](https://aws.amazon.com/athena/), [Aurora](https://aws.amazon.com/rds/aurora/), and [Redshift](https://aws.amazon.com/redshift/). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [CockroachDB Serverless](https://www.cockroachlabs.com/acceptable-use-policy/). *You can only disclose benchmarking tests if you (i) provide us the necessary information to replicate the tests, (ii) disclose the methodology for the benchmarking tests along with the results, and (iii) allow us to run our own benchmarking tests against your products and services at our discretion.*
* [Databricks](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Platform Services and may disclose the results of the Benchmark other than for Beta Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Platform Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [SingleStore](https://www.singlestore.com/blog/eliminating-the-dewitt-clause-for-greater-transparency-in-benchmarking/). *Customer may perform industry standard benchmarks, comparative tests or evaluations (each, a "Performance Report") of the Managed Service. If Customer performs or discloses, or directs or permits any third party to perform or disclose, any Performance Report of any of the Managed Service, Customer (i) will include in any disclosure, and will disclose to SingleStore all information necessary to replicate such Performance Report and the data from the Performance Report, and (il) agree that SingleStore may perform and disclose the results of the Performance Report of Customer's products or services, irrespective of any restrictions on Performance Report.*

### 🤬 Vendors with the DeWitt clause

These vendors chose to restrict benchmarks with DeWitt clauses.

No, you can't benchmark any of these tools and get away with it:

* [Cloudera Data Platform](https://www.cloudera.com/legal/terms-and-conditions/cloudera-online-services-terms.html). *Restrictions. Customer may not: [...] access or use the Services for purposes of monitoring availability, performance or functionality of the Services, or for any benchmarking or competitive purposes.*
* [Decodable](https://www.decodable.co/legal/tos). *Restrictions. Customer may not, and may not cause or permit others to: [...] disclose results of any benchmark tests or performance tests of the Decodable services hereunder without Decodable’s prior written consent.*
* [Dremio Cloud](https://www.dremio.com/legal/terms-of-service/). *Restrictions. Customer shall not, and shall not cause or allow any Authorized User or third party to: [...] except with Dremio’s prior written permission, publish any performance or benchmark tests or analysis relating to Dremio Cloud.*
* [Elastic Cloud](https://www.elastic.co/agreements/global/cloud-services-monthly). *Restrictions [...] You shall not: [...] access or use any Cloud Service for purposes of monitoring its availability, performance or functionality, or for any other benchmarking or competitive purposes, including, without limitation, for the purpose of designing and/or developing any competitive services.*
* [Google Cloud Platform](https://cloud.google.com/terms/service-terms), including [BigQuery](https://cloud.google.com/bigquery) & [Firebase](https://firebase.google.com). *Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if it (a) obtains Google's prior written consent, (b) provides Google all necessary information to replicate the Tests, and (c) allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test of the Services or (ii) disclose the results of any such Test.*
* [InfluxDB Cloud](https://www.influxdata.com/legal/terms-of-use/). *Restrictions. Customer will not: [...] publish or provide any benchmark, comparison or performance test results.*
* [MariaDB SkySQL](https://legal.mariadb.com/agreements/skysql/SkySQL_Terms_of_Service_V2_2020-12-16.pdf) and [MariaDB Xpand](https://legal.mariadb.com/agreements/xpand/xpand-license-terms-v2-2021-02-24.pdf). *Benchmarking. Customer may perform benchmarks or comparative tests or evaluations (each, a "Benchmark") of the SkySQL Services. However, Customer must obtain MariaDB's prior written approval to disclose to a third party the results of any Benchmark of the SkySQL Services.*
* [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/). *BENCHMARK TESTING. You must obtain Microsoft’s prior written approval to disclose to a third party the results of any benchmark test of the software.*
* [Oracle](https://www.oracle.com/us/corporate/contracts/lic-online-toma-ph-de-eng-2817945.pdf). *RESTRICTIONS. [...] You may not: [...] disclose results of any Program benchmark tests without Oracle's prior written consent.*
* [PlanetScale](https://planetscale.com/legal/aup). *Customer will not, and will ensure that its End Users do not: [...] access any portion of the Product for the purpose of building a similar or competitive product or service, or monitor the Product for any benchmarking or competitive purpose.*

### 😶 Vendors for which the author didn't find the data

I wasn't able to identify where a few vendors stand with regard to the DeWitt clause:

* [MySQL HeatWave](https://www.mysql.com/cloud/)
* [Yellowbrick Data](https://www.yellowbrick.com/)

If you can provide any pointers, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post. Also, please reach out if you'd like more databases to be featured here.

## What about Cube?

[Cube](https://cube.dev) is an open-source [headless BI](https://cube.dev/blog/headless-bi) platform that [integrates with a lot of data sources](https://cube.dev/docs/config/databases): cloud data warehouses, query engines, and databases. Many of them are designed for low latency and some of them can provide high concurrency. However, sometimes they don't; you can read our [blog post](https://cube.dev/blog/data-warehouse-performance-and-how-cube-can-help) about that.

In any case, Cube will have no problem delivering your data, with sub-second latency and high concurrency, to any [data consumer](https://cube.dev/docs/config/downstream) out there: BI tools, data notebooks, front-end apps, etc. Thanks to [Cube Store](https://cube.dev/blog/introducing-cubestore), Cube's REST, GraphQL, and SQL APIs are able to respond to any request within 200-300 milliseconds and allow for 100+ concurrent requests—and that can scale.

Please explore how Cube integrates with [databases and other tools](https://cube.dev/blog/category/data) in the data space in our blog. Also, don't hesitate to try [Cube Cloud](https://cubecloud.dev/auth/signup), join our large [Slack community](http://slack.cube.dev), and give Cube a [star on GitHub](https://github.com/cube-js/cube.js) 🌟

Igor Lukanin

Performance

All database vendors who will either praise or punish you for doing a benchmark as well as an entertaining digression into the recent history of computer science

DeWitt clause, or Can you benchmark %DATABASE% and get away with it

Shadid Haque

Supabase

You'll get a performant dynamic dashboard in less than 15 minutes

Supabase tutorial: Building fast data visualization apps

Since we [launched our open source offering][1] in 2019, Cube has helped thousands of companies consume data from any data source, organize it into consistent definitions, and use it with data applications. Developers and data engineers have used Cube to build a thriving ecosystem of embedded analytics features and custom data applications.

However, it’s always been our ambition to make Cube’s power more accessible to a greater diversity of data consumers. 

To get there, data needs to be consistent and performant not just for developers—but also for analysts and general business users. This requires bringing Cube into the tools where these users already work: namely, business intelligence tools such as Tableau, Superset, and Power BI.

Today, we’re proud to announce a major milestone in our efforts. Cube’s updated SQL API makes our headless analytic platform available to users of every BI application.


## Consistent data for every tool

We first launched a [preview of Cube’s SQL API][2] late last year. Since then, we’ve been hard at work responding to your thoughtful feedback and adding support for ways specific BI applications generate SQL queries. Now, it’s possible to connect any data visualization and BI application to Cube via the Postgres SQL interface.

Now, an organization can configure Cube—once—to securely access data sources, define data models, and cache queries. After that, every data consumer across an organization can securely access Cube’s definitions and materializations using vastly simplified queries.

No matter which tool a team uses, everyone in the organization will see the same data definitions. ‘Total Orders’ will mean the same thing in executives’ dashboards, marketers’ campaign reports, and the finance team’s revenue models.

By abstracting away complex queries, Cube reduces the difficulty of exploring data with collaborative data tools and notebooks.

By centrally managing access to up-stack data stores, Cube consistently defines, enforces, and audits who can access what; this eliminates the duplicated effort and inherent risks of defining access control within each downstream tool.

And by managing query-level caching and aggregate tables, Cube reduces the bandwidth costs and degraded performance of directly, repeatedly querying raw data stores.


## Here comes Hydra

We’ve already talked about [using Cube as a “headless” BI layer][3]. With our SQL API, now it’s easier than ever to connect all of your “heads.”

Everything you need to connect Cube to BI tools is available from Cube’s “Overview” window. Just click “How to connect your BI tool” to reveal connection details, then connect to Cube as a PostgreSQL data source from within your BI tool.

![fetching connection details from Cube][10]
![adding Cube to Tableau as a SQL data source][11]

Your [cubes][4] will be exposed as tables, where both your [measures][5] and [dimensions][6] are columns. 

![Cube schema and Tableau columns][12]

This makes it possible to query Cube as a data source from within your BI tool, ensuring consistent data for every data consumer.

![Cube data in Tableau][13]


![Cube data in Superset][14]

## Learn more

The new SQL API is available in both the open source Cube tool and Cube Cloud, the managed Cube service hosted within select regions of the Google Cloud Platform and Amazon Web Services. You may sign up for a free Cube Cloud account at [cube.dev/cloud][7].

Interested in getting started? [See our guide to working with BI tools][15].

Need help? Have feedback? We love hearing from you. [Join the Cube Dev Slack community][8] or [drop us a line][9].


[1]: https://github.com/cube-js/cube.js/releases/tag/v0.3.5-alpha.0
[2]: https://cube.dev/blog/introducing-cube-sql
[3]: https://cube.dev/blog/headless-bi
[4]: https://cube.dev/docs/schema/fundamentals/concepts#cubes
[5]: https://cube.dev/docs/schema/fundamentals/concepts#measures
[6]: https://cube.dev/docs/schema/fundamentals/concepts#dimensions
[7]: https://cube.dev/cloud
[8]: https://slack.cube.dev
[9]: https://cube.dev/contact
[10]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f2119e1a-f6d9-461b-88ca-723cc13df543.gif
[11]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e806c18e-4448-4c8b-a79b-f66eccc6eaad.gif
[12]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/291c4d76-f267-465d-b4f7-990b05e5c4b7.gif
[13]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4794eb01-db59-4fd4-a720-c2e218b00ce4.gif "Querying Cube in Tableau"
[14]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f955f2e7-692b-4afe-94c8-2d047f42131f.gif "Querying Cube in Superset"
[15]: https://cube.dev/docs/config/downstream

Artyom Keydunov

Connect your favorite visualization tools to Cube.

Announcing expanded BI support with our SQL API

I’m a software engineer at [Ternary](https://ternary.app/), a company that provides a FinOps platform to serve Google Cloud (GCP) customers' unique needs. Ternary’s platform empowers cloud engineers, IT finance, and business teams to collaborate more effectively on public cloud cost optimization. The company is in high-growth mode on all fronts, and its platform now supports many of the largest GCP customers. These customers bring the challenge of providing a SaaS platform at scale. 
 
Below, I show how Ternary has solved the challenge of managing the large volume of cost-related data these customers produce, utilizing that data to forecast and optimize spend. I explain the so-called 3-query approach to the analysis of large multidimensional datasets that we use to identify the most interesting data for our users and provide real-world code snippets in the second half of this article.

## Multidimensional analysis of cost datasets

![Ternary dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-dashboard.png)

Ternary’s platform features the ability to break down costs by projects and other dimensions across a time series. For users that have many values in a given dimension (e.g., a few thousand GCP projects), or for those who want to analyze unique combinations of dimensions, we would have to load and analyze substantial result sets on the front-end, not to mention that we would also frequently run into issues with Cube’s response limit of [50,000 rows](https://cube.dev/docs/query-format#query-properties). For example, let’s say I want to look at total spend for every project across the last 30 days while breaking that spend by one or more additional dimensions. Chances are that the result set would contain thousands of rows and if I exceed the limit of 50K rows, I will generate an incomplete dataset and won’t be able to calculate my total costs. 

Ternary is a cost-management platform, so it’s critical that we present complete, accurate data. To enable our users to perform multidimensional analysis of vast volumes of cost data, we asked ourselves what data our users most want to see.

Ternary displays most of its data in line or bar graphs, which are useful only with data points up to a certain amount. A line graph with 50 increments on the y-axis can be hard to read, especially if some y-axis values are much greater than others. At Ternary, we hypothesized that many of our key users would want to focus on what we call, informally, their “most interesting” data points, which we defined as their highest spend in a 30-day period, as well as the general shape of their expenses during that time. It’s debatable what “most interesting” is in every case. But for us, it is usually the groupings that account for the most spend.

Now that we have assumed what our users are really interested in, we imposed limits on our queries so that the user sees only that data. This focus makes our charts more readable and removes the concern about maxing out the Cube response limit.

In limiting the data, we inevitably lose some portion of the entire picture. The top 10 “most interesting” dimension values will likely not account for all of a user’s costs. If there were as many as 100 dimension values, our method would expose but a fraction of these. 

So, to account for any missing data, we roll all the “less interesting” data points into an “other” column. By including this “other” column, we fulfill our above-mentioned mandate to provide complete and accurate data, reflecting total spend and ensuring the accuracy of any subsequent calculations. 

## The 3-query approach to querying top N rows

In order to expose the “most important” data and generate the “other” column, we need to fire off at least three queries. Let’s go through the high-level query breakdown here.

**Scenario:** As a Ternary user, I want to see how much my team has spent day by day, broken down by project ID and category, over the last 30 days.

**Query 1:** Request all of the cost totals day by day, over the last 30 days, with desired query filters. At this point, we will not include dimensions. Note that the “cost” measure in this example is of type “sum.”

![Query 1 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-1.png)

**Query 2:** Request the top N number of the “most interesting” combinations of projects and categories. In this case, we will find out which project-category pairs had the highest overall spend in the last month. For this scenario, let’s assume N is 3. 

![Query 2 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-2.png)

Now that we have a list of the top three combinations, we’ll generate query filters. Note that the new filters must use [Boolean logical operators](https://cube.dev/docs/query-format#boolean-logical-operators) to accurately filter for specific project ID-category storage groupings.

**Query 3:** Request cost day by day over the last 30 days, broken down by project-category pairs, using the filters generated from query two in addition to any user-selected filters. (Note that we will include additional dimensions now.)

![Query 3 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-3.png)

This query would return 3 * 30 = 90 data points. However, there is a discrepancy between the total spend of those three projects and the total overall cost on any given day. To account for this difference, we take the totals from query 1 and subtract the values from query 3 to generate an “other” column, which we also display for the user.

![Final chart](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-final-chart.png)

By this method, we generate a complete overview, identifying the most expensive projects while preserving the total cost using the “other” column. While the overview does not display every project and some specificity is lost, all the data is reflected in one form or another. 

Keep in mind that N can be much larger than three; it should change according to the user’s goals. Lastly, it’s important to acknowledge that while this solution is a “three-step process,” the actual number of queries being run will be 1 + 2m, where m is the number of measures that the user wants to see. This is because we want to gather a separate set of filters for every measure requested. 

For example, say we wanted to see separately the three projects responsible for the most spend and the three projects responsible for the most usage. While we can use query 1 to request all of the selected measure totals across the time series, we will need to fire off queries 2 and 3 for both cost and usage separately. Otherwise, we will muddle the results with an inconsistent number of data points for cost and usage.

To try this out yourself, create the following Cube schema:

```js
cube(`CostByProjectAndCategory`, {
  title: "CostByProjectAndCategory",
  sql: `
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 14 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 15 AS cost, "production" AS project, "Network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 16 AS cost, "production" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 12 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 12 AS cost, "staging" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 13 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 13 AS cost, "staging" AS project,"network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 8 AS cost, "dev" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 9 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 8 AS cost, "dev" AS project,  "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 8 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 9 AS cost, "dev" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 8 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 9 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 7 AS cost, "test" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 6 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 7 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 6 AS cost, "test" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 4 AS cost, "test" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 6 AS cost, "test" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 6 AS cost, "test" AS project, "analytics" AS category
  `,

  measures: {
    cost: {
      sql: "cost",
      type: "sum",
    },
  },

  dimensions: {
    timestamp: {
      sql: "timestamp",
      type: "time",
    },
    project: {
      sql: "project",
      type: "string",
    },
    category: {
      sql: "category",
      type: "string",
    },
  },
});
 
```

The three queries you’ll need to run are shown below, in JSON query format:

**Query 1**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ]
}
```

**Result**

![Query 1 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-1-result.png)

**Query 2**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp"
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "limit": 2
}
```

**Result**

![Query 2 result 1](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-2-result.png)

**Query 3**
```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "filters": [ {
    "or": [
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["production"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["compute"]
          }
        ]
      },
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["staging"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["databases"]
          }
        ]
      }
    ]
  } ],
  "limit": 50000
}
```

**Result**

![Query 3 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-3-result.png)

### Performance

Because including a large number of dimensions complicates the Boolean logical filters applied, and because including a large number of measures increases the number of queries to which we must apply that complicated logic, it’s crucial to consider actual use cases and their resulting user impact. To test Ternary’s new query format, we used the Ternary Reporting Engine: a feature of our platform that allows users to query their data flexibly, by selecting different dimensions, measures, and filters. Here, we ran a number of queries with multiple measures, dimensions, and filters, to get a sense of how complex our queries could be without significantly compromising the app’s performance. We found that, using this 3-query approach, the duration of a request where N = 20 was the same as that of using one query to gather all the data, with the added benefit that our results stayed within the 50K-row limit.

### Cost

Given that Ternary is a cost-management platform, we recognize the importance of considering costs when making changes such as this one. As stated before, the number of Cube queries needed to fulfill a single request on behalf of any given customer would increase, from 1 to 1 + 2m. It follows that the cost to implement a feature in this way could also increase by a similar factor if, for example, using [BigQuery’s on-demand pricing](https://cloud.google.com/bigquery/pricing#on_demand_pricing) like we do, where the amount you are charged is proportional to the amount of data you read. Even if we were to increase the amount we spend, serving this data by a factor of, on average, three, five, or even ten, we determined that it would be a reasonable price to pay for the benefits, including showing customer data that conveys the full picture instead of truncating it as we previously had. 

Each use case is different and should be considered individually. If all queries are accelerated with [pre-aggregations](https://cube.dev/docs/caching#pre-aggregations) or if Cube points toward a Postgres instance, and if the query volume is relatively low, cost considerations may not be as relevant.

Katie Law

User Stories

How Ternary zeroes in on most interesting data.

Ternary's three-query approach to present the most interesting data in less than 50k rows

[Materialize](https://materialize.com) is a different kind of database: on the surface it presents as Postgres, but internally it maintains the results of a query as a materialized view that stays up-to-date as the data changes. While a traditional database does the evaluation when a SELECT statement is issued, Materialize asks for queries upfront and computes the results on the fly as new data arrives.

As we’ll discuss more below, this makes Materialize great for scenarios where you need the complex join and transformation capabilities of a warehouse, but you want to serve the results to applications.

## What is Cube?

Cube is a headless business intelligence platform that makes data accessible and consistent across every application. Cube provides data modeling, access control, caching, and a variety of APIs (SQL, GraphQL, and REST) on top of any data warehouse, database, or query engine, including Materialize.

If you’re familiar with the concepts of “headless CMS” or “headless ecommerce”, you already have the right mental model for Cube. It’s essentially a “headless BI”—say, open-source Looker with less UI and more API—that delivers consistent data to your BI tools, data apps, and notebooks.

## The Integration

Integrating Cube and Materialize was a perfect example of the benefits of building in public and using well-known protocols.

Cube is open source and has an existing [PostgreSQL driver](https://cube.dev/docs/config/databases/postgres). Materialize [contributed](https://github.com/cube-js/cube.js/pull/4320) a lightly modified version of the PostgreSQL driver to help Cube read from Materialize.

![Materialize integration](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d8ed003d-aa86-497b-9e51-219539351db4.png)

## Why are Materialize and Cube useful together?

Data warehouses were built with the end goal of showing people data. They work best for data exploration, analysis and reporting. But just looking at the data is no longer sufficient: now we’re connecting *systems* (APIs, applications, tools and automation) to the data in the warehouse.

When people point systems at data warehouses, they inevitably face performance problems:
* Not built for lookup queries
* Not built for low latency and high concurrency
* Reactivity is slow and time-to-insight is high

Also, there’s always a gap between how the data is stored and how the data is analyzed. Systems that consume the data (e.g., data apps or BI tools) and people working with them are most comfortable dealing with business-level metrics definitions (e.g., “revenue growth MoM by countries”) rather than calculations over the raw data in the warehouse.

With Materialize, you get warehouse-style views of your business data at application-style availability (always up-to-date, low latency queries). Combine that with Cube’s semantic layer and a variety of APIs to get a “live read API for your entire business”.

## Show me an example!

Here’s how you can make Materialize work with Cube for fun and profit. Let’s build an application that consumes a live stream of Hacker News stories but only shows the ones that ranked in top 3 so you can still have plenty of free time.

**Running Materialize and Cube.** Both tools have managed cloud-based offerings ([Materialize Cloud](https://materialize.com/materialize-cloud-access/) and [Cube Cloud](https://cube.dev/cloud/)) but, for the sake of simplicity, we can run them in Docker. The following `docker-compose.yml` file will run Materialize, seed it with data, and then run Cube:

```yaml
version: '2.2'
 
services:
 materialize:
   image: materialize/materialized:v0.26.1
   ports:
     - 6875:6875
 
 seed:
   image: jbergknoff/postgresql-client
   volumes:
     - .:/seed
   entrypoint: ["sh", "seed/seed.sh"]
   depends_on:
     - materialize
 
 cube:
   image: cubejs/cube:latest
   ports:
     - 4000:4000
   environment:
     - CUBEJS_DEV_MODE=true
     - CUBEJS_DB_TYPE=materialize
     - CUBEJS_DB_HOST=materialize
     - CUBEJS_DB_PORT=6875
     - CUBEJS_DB_NAME=materialize
     - CUBEJS_DB_USER=materialize
     - CUBEJS_API_SECRET=SECRET
   volumes:
     - .:/cube/conf
   depends_on:
     - seed
```

In the `seed.sh` file, we’ll use a vanilla PostgreSQL client to connect to the Materialize instance and run three SQL queries: the first one will connect a live [PubNub stream](https://www.pubnub.com/developers/realtime-data-streams/hacker-news-articles/) with Hacker News stories data, the other one will transform the stream into a normalized table view with columns, and the last one will create a materialized view of the stories:

```bash
#!/bin/bash
 
cat > seed.sql << EOL
 
CREATE SOURCE hn_raw
FROM PUBNUB
SUBSCRIBE KEY 'sub-c-c00db4fc-a1e7-11e6-8bfd-0619f8945a4f'
CHANNEL 'hacker-news';
 
CREATE VIEW hn AS
SELECT
  (item::jsonb)->>'link' AS link,
  (item::jsonb)->>'comments' AS comments,
  (item::jsonb)->>'title' AS title,
  ((item::jsonb)->>'rank')::int AS rank
FROM (
  SELECT jsonb_array_elements(text::jsonb) AS item
  FROM hn_raw
);
 
CREATE MATERIALIZED VIEW hn_top AS
SELECT link, comments, title, MIN(rank) AS rank
FROM hn
GROUP BY 1, 2, 3;
 
EOL
 
psql -U materialize -h materialize -p 6875 materialize -f ./seed.sql
```

The only missing piece is Cube’s data model file, placed under `schema/HN.js`. Cube will read from the live-updated materialized view and expose metrics via its APIs. 

```js
cube(`HN`, {
  sql: `SELECT * FROM public.hn_top`,
 
  refreshKey: {
    every: '1 second'
  },

  measures: {
    count: {
      type: `count`
    },
 
    countTop3: {
      type: `count`,
      filters: [ {
        sql: `${rank} <= 3`
      } ]
    },
 
    bestRank: {
      sql: `rank`,
      type: `min`
    }
  },
  
  dimensions: {
    link: {
      sql: `link`,
      type: `string`
    },
 
    comments: {
      sql: `comments`,
      type: `string`
    },
 
    title: {
      sql: `title`,
      type: `string`
    },
 
    rank: {
      sql: `rank`,
      type: `number`
    }
  },
 
  segments: {
    show: {
      sql: `${title} LIKE 'Show HN:%'`
    }
  }
});
```

You copy and paste these files or [pick them from GitHub](https://github.com/cube-js/cube.js/tree/master/examples/materialize). Now, we can run the whole stack with `docker compose up`.

**Exploring the data.** Now, if you navigate to Cube’s Playground at [localhost:4000](http://localhost:4000), you’ll instantly be able to run queries against the Materialize instance with connected stream by picking measures, dimensions, segments, and filters in the UI.

Here’s how you can check how many stories we have had in the data stream (30) and how many of them have even been ranked in top 3 (no surprise, just three):

![First query](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/14277bc2-2177-4350-9d2a-8abda583df6c.png)

However, if you wait a few minutes and refresh the query results, you’ll see that data changes because new stories are posted to HN and existing stories change their ranks.

Then, here’s how you can combine segments and filters to pick only “Show HN” stories ranked in top 3:

![Second query](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/615c7251-480b-4bdb-98b4-ed5e821d965f.png)

**Connecting to APIs.** Cube exposes SQL, GraphQL, and REST APIs, each with its own query syntax. For instance, the query directly above is expressed as the following JSON query accepted by the REST API:

```json
{
  "dimensions": [
    "HN.title",
    "HN.rank"
  ],
  "order": {
    "HN.rank": "asc"
  },
  "filters": [ {
    "member": "HN.rank",
    "operator": "lte",
    "values": [ "10" ]
  } ],
  "segments": [
    "HN.show"
  ]
}
```
Let’s run this query against the REST API and extract the result set with the amazing `jq` utility:

```bash
curl localhost:4000/cubejs-api/v1/load -G -s \
  --data-urlencode "query=$(cat query.json)" | jq '.data'
```
![REST API usage](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a3bd5786-df83-406b-bded-f47e5a099641.png)

Also, if you are a GraphQL aficionado, you can use GraphQL API and an alternative syntax:

![GraphQL API usage](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/63a0e4fc-1f67-4e02-8ebc-71479dc8d923.png)

And, there’s one more thing—Cube’s [real-time data fetch](https://cube.dev/docs/real-time-data-fetch) based on WebSockets is a great match to the real-time experience of Materialize. You can use that to live-feed your data from the streams through Materialize and Cube all the way down to your end-user front-end application. 

**Future improvements.**  The current implementation of Cube’s Materialize driver makes Cube periodiucally poll Materialize for updates. A more effective and native for Materialize way to get data out of Materialize is subscribing to changes using the [TAIL feature](https://materialize.com/docs/sql/tail/). This would require more in-depth changes which we are exploring.

## Try Materialize and Cube for yourself

Feel free to check out Materialize and Cube by running the example above on your machine. The full source code is [available on GitHub](https://github.com/cube-js/cube.js/tree/master/examples/materialize). Also, please don’t hesitate to join Cube’s [Slack community](https://slack.cube.dev) or Materialize’s [Slack community](https://materialize.com/s/chat) to share your feedback.

To give both tools a try in a professional setting, check out [Materialize Cloud](https://materialize.com/materialize-cloud-access/) and [Cube Cloud](https://cube.dev/cloud/). The future is real-time, let’s build it together with Materialize and Cube.



Andy Hattemer

Materialize

With Materialize streaming database and Cube, building real-time applications has never been easier.

Announcing the Cube integration with Materialize: Building real-time applications over data streams

We’ve spent the past few weeks discussing [the composition of headless BI][1] and [Cube’s approach to open source data modeling][2]. Let’s briefly talk about where this technology meets the real world: embedded analytics.

## What is embedded analytics?

Embedded analytics means much more than using iframes to place charts in a dashboard. The real promise of embedded analytics is _bringing rich data experiences to users where they already are_. As [Gartner][3] puts it, “data analysis occurs within a user’s natural workflow, without the need to toggle to another application.”

Financial analysis may be built right into a personal finance application. Personal health metrics can be displayed alongside one’s medical history. Every member of a team can see the business’s KPIs and trends without logging into a BI tool. 

For businesses, embedded analytics affords full control over what data is available and how it’s presented. For data consumers, embedded analytics means finding useful insights within the sites or apps they already use—not in the same old dashboards.

At Cube, we’ve been on the embedded analytics front lines for years. Here are some recent technological and behavioral shifts we’ve observed.

## The rise of the cloud data warehouse

In the past, collecting and processing massive data was so technically complex and expensive that only a few companies did so. Now, cloud data warehouses make it possible for organizations in every industry and of every size to easily and affordably collect and process more data. This has led to cloud data warehouses’ [huge adoption][4].

But it is not enough to just collect lots of data. Businesses need to _use_ the data, too. This has increased the need for customizable, powerful data experiences. **As a result, companies’ adoption of cloud data warehouses has led to greater interest and investment in embedded analytics, too.**

## Data consumers are more diverse

At the same time as more businesses have taken interest in embedded analytics, **there are also more, and more types of, data consumers**. Business intelligence was once the purview of specialized engineers and analysts, but now, every member of an organization is expected to use and understand data to perform their job.

Who is the modern data consumer? For starters, they may be less technical. The modern data consumer [may not be familiar with data engineering concepts like OLAP][5]—and they shouldn’t have to be. The modern data consumer may not even be familiar with legacy BI tools—and, again, they shouldn’t need to be.

To make data accessible and actionable for these data consumers, companies need to build customized user interfaces that move beyond mere iframed graphs. Embedded analytics applications have grown more polished and interactive. 

## Front-end development is easier

A major change in the world of embedded analytics is **the rise of modern front-end development tools**. In the past decade, there have been incredible advances in powerful, simple front-end technologies—particularly [React][7]. It’s now easier than ever to design customized, polished user experiences.

This change hasn’t just made it easier to develop custom data experiences. It also has altered users’ expectations to make such experiences essentially mandatory. Both internal data consumers and end users have new, high standards for usability, richness, and responsiveness.

Since these expectations can’t be met with generic dashboards or out-of-the-box BI platforms, more companies are building customizable and performant embedded analytics features using modern front-end tools.


## There are new tools

It’s become easier for front-end development teams to build highly custom native UIs, but these aren’t necessary for every project. Sometimes, standard visualizations are enough and speed is key. 

**A new generation of collaborative data tools**, including [Hex][6], [Observable][8], and [Streamlit][9], make it possible for data analysts to quickly select the appropriate chart for a dataset and share reports both throughout a company and to end customers.

Organizations need the freedom to choose the right tool for each job—whether it’s a custom application built by front-end teams or shareable dashboards built by data teams. 

## Rethinking the data stack

![modern-embedded-analytics-data-stack][30]

Thinking about these developments, what are the technical requirements of a modern embedded analytics data stack?

## Data warehouse-centric architecture

As [others][10] [have][11] [noticed][12], the cloud data warehouseis a place not only to store and process data, but also to make it interactive. To actually be a backend for data analytics applications, including embedded analytics, the data warehouse needs to be at the core of your data stack.

Having data in a single place significantly reduces operational overhead. On one end, ELT is replacing ETL, and on the other, we see a move away from moving data into “serving” databases. Instead, the trend is towards powering applications with data directly from warehouses. This architecture is simpler  and less error-prone because it removes additional pipelines and storage layers. 

Warehouses still predominantly work with batch data, but there are a lot of recent exciting developments in supporting real-time data. [Firebolt][13], [Clickhouse][14] and [Materialize][15] are actively innovating in the space. 

Finally, this architecture also opens an opportunity for [bring-your-own-data-warehouse applications][16]. This may be beneficial for both vendors and consumers because it simplifies the security architecture and streamlines new software onboarding.

## Headless BI in the middle

[We’ve previously described the four essential components of headless BI][17]. To specifically support embedded analytics, a headless BI solution must have these attributes:

### First-class support for cloud data warehouses

A major piece of this is **access control** integrated with the data warehouse’s security controls, because embedded analytics always require multitenancy. A second piece is **advanced caching**. This is because the data warehouse is a great candidate for a backend, but itself does not support high-concurrency small queries with the low latency that modern data consumers expect.

### Data modeling

The diversity of new data consumers all should consume the same data: “net sales” in your dashboard should mean the same thing as “net sales” in my mobile CRM. To achieve this consistency, data modeling and metrics definition should be handled once, and this must be up-stack from every application or dashboard.

### Diverse APIs

Different data consumers have different expectations and requirements, so it’s inevitable that a company will end up supporting a whole class of data apps. For all of these applications to share the same metrics, a headless BI layer needs to be accessible via multiple APIs, e.g., SQL, GraphQL, and REST.

## A hybrid presentation layer

To support different data consumers, use cases, and teams, an embedded analytics presentation layer should be diverse. 

When high customization is required and front-end teams are looped in, different charting libraries can be used, ranging from [D3][18] to [Chart.js][19] and [Highcharts][20]. These most likely will be natively integrated with frontend application frameworks like React or [Angular][21].

When requirements are less custom, tools like Observable or [Dash][23] come into the picture. It’s faster and cheaper to build with these tools, and they still can be natively integrated into an app and combined with more custom interfaces.

Data analysts and engineers can quickly build appealing data interfaces with modern BI tools like [Superset][24] and [Metabase][25]. These are less customizable, but they don't require front-end knowledge. This usually enables even faster building and iteration. They too can be combined with other presentation layers within a single application. 

Finally, there is the emerging category of no-code/low-code tools for internal tooling. This includes [Appsmith][26] and [Retool][27]. They too can be used to build analytics interfaces.

## Conclusion

We’ve moved past the historical era when embedded analytics was considered a part of a single integrated BI solution. The latest generation of BI vendors already have innovated to run “live” queries directly on top of data warehouses, which allows for cloud data warehouse-centric architectures.

The next evolutionary step is to decapitate BIs and give organizations freedom to switch heads (frontends) or use many of them at the same time. 

This architecture will be enabled by several products, including Cube. As the category matures, and more enterprises adopt this architecture, we’ll see more exciting developments. Buckle up.

_Cube is an API‑first business intelligence platform for data engineers and application developers to make data accessible and consistent across every application. [Get started with Cube for free today][28], or [get in touch][29] to discuss your next embedded analytics project!_

[1]: https://cube.dev/blog/headless-bi
[2]: https://cube.dev/blog/open-source-data-modeling
[3]: https://www.gartner.com/en/information-technology/glossary/embedded-analytics
[4]: https://www.alliedmarketresearch.com/data-warehousing-market
[5]: https://benn.substack.com/p/ghosts-in-the-data-stack?s=r
[6]: https://hex.tech
[7]: https://www.youtube.com/watch?v=7LMsR30p1xM
[8]: http://observablehq.com
[9]: http://streamlit.io
[10]: https://medium.com/snowflake/how-to-build-a-data-app-on-snowflake-db80ac459767
[11]: https://pchase.substack.com/p/thenewbackend?s=r
[12]: https://www.youtube.com/watch?v=PEFP5fLx1dY
[13]: https://www.firebolt.io
[14]: https://clickhouse.com
[15]: https://materialize.com
[16]: https://pchase.substack.com/p/thenewbackend?s=r
[17]: https://cube.dev/blog/headless-bi
[18]: https://d3js.org
[19]: https://www.chartjs.org
[20]: https://www.highcharts.com
[21]: https://angularjs.org
[22]: https://observablehq.com
[23]: https://plotly.com/dash/
[24]: https://superset.apache.org
[25]: https://www.metabase.com
[26]: https://www.appsmith.com
[27]: https://retool.com
[28]: https://cube.dev/cloud
[29]: https://cube.dev/contact
[30]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3b7dcb91-6bd6-479d-b186-be7216969e8f.png

Deep Dives

Here’s how the embedded analytics data stack has changed.

The Modern Data Stack for Embedded Analytics

React

Building a Next.js Dashboard with Dynamic Charts and SSR

*This blog post is the outcome of a collaboration between [Isha Terdal](https://github.com/ishaterdal) from Cube, [Andrey Pechkurov](https://github.com/puzpuzpuz) from QuestDB and [Yitaek Hwang](https://github.com/yitaek), a QuestDB contributor.*

Time series data has now become a critical part of the data applications landscape. And at Cube, we’re super excited about how QuestDB changes the way companies work with it. In this blog, we’ll take a look at how QuestDB and Cube work together to provide a time series data pipeline that is fast, consistent, and reliable.


## What is QuestDB?

[QuestDB](https://questdb.io) is a performant open-source database for time series data. Its codebase is optimized for storing and processing large amounts of time-stamped data efficiently through lightning-fast SQL queries. 

QuestDB also allows for integration with a variety of popular open source tools through the PostgreSQL wire protocol, and its use cases range from real-time analytics and monitoring to market/tick data and industrial telemetry.


## What is Cube?

[Cube](https://cube.dev) is a headless business intelligence platform that can organize all your data into a structured data model. Defining metrics upstream of your applications—so, for instance, defining the granularity of data observed—enables you to access the same data metrics across various applications, use cases, and teams. 

With Cube’s data modeling capabilities, you can shorten your project timeline from weeks to hours. And, with its API-first approach, Cube you can connect to your streamlined data via an API that best suits your application and infrastructure (whether it be a REST, GraphQL, or SQL API).


## How QuestDB and Cube Work Together

So there you have it — a structured data model built on top of a fast repository of time series data. QuestDB is optimized for slicing and dicing time-stamped data through its [SQL extensions](https://questdb.io/docs/concept/sql-extensions/). Cube also offers multiple ways of [pre-aggregating data](https://cube.dev/docs/caching/using-pre-aggregations) effectively building a caching layer on top of your data to speed up performance of slow queries. Together, QuestDB and Cube have your time series data all set up for use downstream.


## Exploring Crypto Prices with QuestDB and Cube

Let’s illustrate how the two app stacks work together. In this example, we’ll show how you can efficiently build an end-to-end crypto price analysis platform with QuestDB and Cube. We will use Cube to expose time series data in QuestDB via multiple APIs.


![full-connection-process](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9313db63-7368-425c-95b0-144e0b370531.png)


### Setting Up QuestDB and Importing Time Series Data

First, you should set up a new project directory in your local system e.g. ‘questdb_cube’.

We’ll begin our demo by using the [Crypto dataset](https://www.kaggle.com/datasets/sudalairajkumar/cryptocurrencypricehistory) to show how we can create a time series database in QuestDB, and then organize the data downstream with Cube. Since both applications can be initiated using [Docker](https://docs.docker.com/get-docker/), let’s start up both engines by stringing them together using Docker Compose.

Then, create a docker-compose.yml file in the project directory with the following contents:


```yaml
version: "2.2"

services: 
  cube: 
    environment: 
      - CUBEJS_DEV_MODE=true
    image: "cubejs/cube:latest"
    ports: 
      - "4000:4000"
    volumes: 
      - ".:/cube/conf"
  questdb: 
    container_name: questdb
    hostname: questdb
    image: "questdb/questdb:latest"
    ports: 
      - "9000:9000"
      - "8812:8812"
```


Add an .env file that gives Cube the details for connecting to QuestDB:


```
CUBEJS_DB_HOST=questdb
CUBEJS_DB_PORT=8812
CUBEJS_DB_NAME=qdb
CUBEJS_DB_USER=admin
CUBEJS_DB_PASS=quest
CUBEJS_DB_TYPE=questdb
```


Run the containers using the following command:

 `docker-compose up -d`


![QuestDB in Docker](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/efb18c3c-882c-4f6b-a920-adf3af725f2b.png)


Navigate to [localhost:9000](http://localhost:9000) to open QuestDB’s Web Console, click on the “Upload” icon on the left-hand panel, and import the [csv files of interest](https://www.kaggle.com/sudalairajkumar/cryptocurrencypricehistory). While this example uses the Ethereum dataset, any of the coin datasets will work perfectly.


![QuestDb Web Console](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/35aae092-6989-4967-ae8a-6fede2a53e0a.png)


Note: Cube works best with table names that do not contain any special characters. So, we’re going to rename our table using the following command:


```sql
RENAME TABLE 'coin_Ethereum.csv' TO 'ethereum';
```


![Table Rename Query](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/299f3352-e426-463c-90bf-fc20001bf8af.png)


Now, we’re able to query the data:

![QuestDB Web Console showing database](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e8a828e7-f72f-4191-b6e5-d1693af856ed.png)


### Building a Cube Data Model

The next step is to organize the time series data from QuestDB in a uniform and accessible manner; we do this by defining a data model.

The [Cube data model](https://cube.dev/docs/schema/fundamentals/concepts) consists of entities we call ‘cubes’ that define metrics by **_dimensions_** (qualitative categories) and **_measures_** (numerical values). So, with that in mind, let’s continue by creating a cube for our Ethereum data.

Navigate to localhost:4000/#/schema and click on the Ethereum table we imported into QuestDB:


![Tables in Cube](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/23687509-dc3b-4e84-b569-a26a53a2c339.png)


Clicking on the ‘Generate Schema’ button automatically bootstraps a cube for the data in our local project directory—a folder named ‘schema’. This folder contains a file called ‘Ethereum.js’ that you can open with any text editor.

By default, the ‘count’ field falls under **measures**; name, symbol, and date fields are auto-populated as **dimensions**. Since we’re interested in price columns, let’s add them in (defined as ‘high’ and ‘low’ in the data model as shown below):


```javascript
cube(`Ethereum`, {
  sql: `SELECT * FROM ethereum`,

  measures: {
    count: {
      type: `count`,
      drillMembers: [name, date]
    }
  },
      
  dimensions: {
    name: {
      sql: `${CUBE}."Name"`,
      type: `string`
    },
        
    symbol: {
      sql: `${CUBE}."Symbol"`,
      type: `string`
    },
        
    date: {
      sql: `${CUBE}."Date"`,
      type: `time`
    },

    high: {
      type: 'number',
      sql: `${CUBE}."High"`
    },

    low: {
      type: 'number',
      sql: `${CUBE}."Low"`
    }
  }
});
```


Then, by clicking on the ‘Build’ tab in Cube, we can see the data:


![Data in Cube](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/bfdcf494-88fc-4fd5-b3aa-18b5f61b7c83.png)


We can also use Cube’s built-in measure type, **'avg’** to calculate average high or low prices:

  
```javascript
  measures: {
    avgHigh: {
      type: 'avg',
      sql: `${CUBE}."High"`
    },
    
    avgLow: {
      type: 'avg',
      sql: `${CUBE}."Low"`
    }
  },
```


Next, we can recreate the classic price-over-time graph:


![Price over Time graph](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5af8cf98-b457-4c71-90f6-d0f49839ac05.png)


Additionally with Cube, you can pre-aggregate data to speed up the queries. Cube will create materialized rollups of specified dimensions and measures internally, and use aggregate awareness logic to route the queries. This logic will use the most optimal pre-aggregation available to serve the query instead of processing the raw dataset. You can define pre-aggregations within your data model as shown below. 


```javascript
  preAggregations: {
    main: {
      measures: [avgHigh, avgLow],
      timeDimemsion: date,
      granularity: `day`
    }
  }
```


You can learn more about Cube [pre-aggregations in the documentation.](https://cube.dev/docs/caching/pre-aggregations/getting-started)


### Consuming Time Series Data via APIs

If you’ve followed along so far, your data is now processed and organized neatly into a data model—aka a cube. So, what’s next? Good question. The answer is simply to connect the data to your application.

The versatility of Cube—given its ‘headlessness’—enables you to seamlessly connect to any data application you need, whether it’s a dashboard, notebook, or the backend of your application.

By taking an API-first approach, Cube as a headless BI tool brings about endless ways of using your data downstream of your time series data sources. And, API endpoints ensure that the metrics are available and consistent across different applications, tools, and teams.

Let’s take a look at the various ways you can connect to the data model you built via its APIs.


![Cube API view](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1da1cc0e-3ecc-44b2-8a10-acb4b9bdb81b.png)


There are three API endpoints that you can use to access your data model: 

1. REST API: If you’re using Cube as the backend for your application, you can connect to it with the [REST API](https://cube.dev/docs/rest-api). 
2. GraphQL API: If you’re looking to use standard GraphQL queries for embedded analytics or other data apps, you can connect to Cube with the [GraphQL API](https://cube.dev/docs/backend/graphql).
3. SQL API: If you’re querying data using standard ANSI SQL format, you can connect to Cube with the [SQL API](https://cube.dev/docs/backend/sql). No need to learn additional syntax—just use SQL to interact with your data. This is especially useful if you are working with BI tools, dashboards, or data science models.


![GraphQL API](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/344d451c-b36f-4461-b193-b87b9fda0a3d.png)


### Conclusion

With QuestDB’s speedy processing of time series data, and Cube’s data modeling capabilities for consistent definitions, we can now efficiently power many use cases with real-time, reliable data. So, think embedded analytics, business intelligence, machine learning—you name it.

Curious to see QuestDB’s incredibly performant time series database in action? Check out the [QuestDB demo](https://demo.questdb.io). Eager to join the QuestDB discussion? Join the QuestDB community on [GitHub](https://github.com/questdb/questdb) and [Slack](https://slack.questdb.io/).

Want to see how QuestDB and Cube can power your project together? [See the docs](https://cube.dev/docs/) and [create a free Cube Cloud account today](https://cube.dev/cloud). Have questions or feedback? [Drop us a line](https://cube.dev/contact), [join our Slack](https://slack.cube.dev/), or [check us out on GitHub](https://github.com/cube-js/). 

QuestDB

Time Series Data Analytics with QuestDB and Cube

Data pipelines, data modeling, and BI tools to collect, store, and analyze data at scale.

Latest news and insights from Cube

Data Engineering

Application Development

All Things Cube