With this article, we start a multi-part series that will cover the spectrum of reasons to use Cube Store, a purpose-built distributed, fast, and reliable data store, as a part of your Cube deployments.

With [more than 2 years](https://cube.dev/blog/introducing-cubestore) in active development, Cube Store accounts for half of Cube’s [Rust code base](https://github.com/cube-js/cube/tree/master/rust) and supports some of the most critical features: in-memory cache, query queue management, and pre-aggregations. Thanks to these features, Cube provides fantastic support for [embedded analytics](https://cube.dev/use-cases/embedded-analytics) and [real-time analytics](https://cube.dev/use-cases/real-time-analytics) use cases, processing client queries to cloud data warehouses and streaming databases with sub-second latency and high concurrency while safeguarding these data sources from load bursts and overspending.

In this article, we’ll focus on the in-memory cache and query queue features. Last year, we [announced](https://cube.dev/blog/replacing-redis-with-cube-store) plans to update these features to use Cube Store and, consequently, remove Redis®* from Cube deployments. [Much work](https://github.com/cube-js/cube.js/issues/5042) has been done on GitHub, and we’ve also run a [webinar](https://cube.dev/events/migrating-from-redis) covering these changes recently. Now, let’s recap and dig deeper.

_&ast; Redis is a registered trademark of Redis Ltd. Any rights therein are reserved to Redis Ltd. Any use by Cube is for referential purposes only and does not indicate any sponsorship, endorsement or affiliation between Redis and Cube._

## In-memory cache in Cube

As the semantic layer for data applications, Cube is designed to sit between data sources (e.g., cloud data warehouses and traditional databases) and data consumers (e.g., BI tools, data notebooks, and front-end apps). It receives queries from these data consumers via any of the supported APIs (i.e., SQL API, REST API, and GraphQL API) and fulfills them in a timely manner.

![Semantic layer](https://ucarecdn.com/0e3e2588-ec07-44bf-a4ee-856f793132be/)

In the most trivial scenario, to fulfill a query, Cube would analyze the query, apply access control rules, transform it using the data model, generate a new SQL query, run this new query against the upstream data source, and deliver the result set back to the data consumer. It takes time, and querying the upstream data source is the most time-consuming..

You can expect the latency to be up to a few seconds when using cloud data warehouses such as Snowflake or BigQuery; it can grow indefinitely when using traditional databases such as Postgres. It depends on multiple factors, including but not limited to the following: the complexity of the query, the volume of data to be processed, the type of data storage (row-based vs. columnar), the database architecture (single-node vs. distributed), and implemented optimizations.

It means that, to ensure low query latency and excellent user experience at the data consumer level, __running queries against upstream data sources should be avoided at all costs, when possible__. Let’s consider one of these possibilities.

Often, multiple identical queries would hit Cube within a short time frame. It usually happens when multiple users open the same dashboard in a BI tool or the same page of a front-end application; it also happens when a single user refreshes a page or navigates back to it. In many cases, a result of a query that was run 5 seconds ago should still be relevant for the same query that has just arrived; moreover, query results often don't depend on the query time at all. For example, the revenue generated by the Oregon branch of your company last week or the volume of deposits withdrawn from Silicon Valley Bank on the week of March 5, 2023, don’t change as time passes.

### Query deduplication

Cube deduplicates identical queries to its APIs.

Let’s unpack how it works:

- When the first-of-a-kind query arrives, it will be fulfilled by querying the upstream data source. The result would also be put in the in-memory cache that Cube maintains.
- Next, when a query identical to the first one arrives, it would be fulfilled from the in-memory cache—almost instantly. We’re talking about milliseconds vs. seconds in the case of upstream data sources.
- As more and more identical queries hit Cube, almost counterintuitively, the overall performance would improve. Indeed, median and 95th percentile latencies for the query would naturally tend to the in-memory cache latency, not that of the upstream data source.

Please watch the following 10-minute demo to see how query deduplication works and how it affects query latencies:

<iframe width="560" height="315" src="https://www.youtube.com/embed/ULVkdUlKpUc?start=2389" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>

Here’s the most insightful part of the demo above:

```bash
% k6 run scripts/1.js | grep iteration
     iterations.....................: 2950   588.92/s
     iteration_duration.............: min=2.46ms med=10.33ms p(95)=23.13ms max=1563.71ms
```

When thousands of queries are being run (specifically, 2950), the maximum latency is well over a second because one query has to hit the upstream data source; the median and p(95) latencies are two orders of magnitude lower, in the range of 10–23 milliseconds. __Cube effectively employs its in-memory cache to deduplicate identical queries and provide the lowest query latency possible.__

It’s worth noting that the in-memory cache is, naturally, a cache, so it’s subject to one of the [two hardest things](https://skeptics.stackexchange.com/questions/19836/has-phil-karlton-ever-said-there-are-only-two-hard-things-in-computer-science) in computer science: cache invalidation. By default, Cube would invalidate the entries in the in-memory cache after a [time interval](https://cube.dev/docs/schema/reference/cube#parameters-refresh-key) that depends on the upstream data source: every 2 minutes for BigQuery, Athena, Snowflake, and Presto; every 10 seconds for all other databases. It makes sense to deduplicate queries against data warehouses more aggressively since running them costs money and consumes limited resource quotas.

### API idempotence and scalability

Cube’s architecture allows scaling its API instances from one to many. Regardless of which API instance was hit by an incoming query, Cube will check if its unified in-memory cache has a relevant cached result.

Such architecture makes Cube’s APIs idempotent. You can distribute the load between API instances however you want (a simple round-robin strategy works excellently), and no sticky behavior is needed. You can also implement more sophisticated load-balancing strategies, e.g., when running [massive multitenant deployments](https://cube.dev/docs/cloud/configuration/deployment-types#production-multi-cluster) in production. It means __you can freely and quickly scale API instances up and down__, no cache warm-up is needed for new instances.

Production deployments in Cube Cloud are able to [auto-scale the number of API instances](https://cube.dev/docs/cloud/configuration/deployment-types#cube-api-instance) from 2 to 10 (and above), so you can process dozens and hundreds of requests per second (RPS). [Talk to us](https://cube.dev/contact) if you’d like to discuss your use case and performance requirements.

## Query queue in Cube

When Cube needs to run a query against the upstream data source, it goes through the following steps:

- First, the query is added to the query queue. Cube maintains a separate query queue for every upstream [data source](https://cube.dev/docs/schema/reference/cube#data-source) and your data model can surely work with [multiple data sources](https://cube.dev/docs/config/multiple-data-sources) at once.
- Second, Cube will pick a query from the queue and execute it against the data source. The maximum number of simultaneously executed queries is defined by the [`CUBEJS_CONCURRENCY` config option](https://cube.dev/docs/reference/environment-variables/#cubejs-concurrency) and depends on the upstream data source: 2 for Postgres, 4 for Redshift, 5 for Athena and Snowflake, 10 for BigQuery, etc. These sane defaults prevent the data source from overloading and data warehouse resource quotas from depleting.
- Lastly, when the query execution is completed, Cube will put its results in the in-memory cache. Immediately thereafter, all API requests that were waiting for the results of this query will be fulfilled.

Please watch the following 3-minute demo to see how the query queue works and how it allows limiting query concurrency on the data source side:

<iframe width="560" height="315" src="https://www.youtube.com/embed/ULVkdUlKpUc?start=3098" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>

Queuing queries instead of running them directly against the upstream data source brings substantial benefits. __Query queue safeguards data sources from excess queries in case of burst load;__ it also allows servicing long-running interactive queries and canceling queries initiated by dropped API connections.

## In-memory cache and query queue implementation

As evident from the previous sections, in-memory cache and query queue are essential parts of Cube that have been present from day one.

### Initial architecture

Initially, they were implemented using [Redis](https://redis.io), an open-source in-memory data store, to keep the cache and queue entries. Cube API instances and refresh workers interacted with Redis to maintain the query queue and use the in-memory cache. The following architecture was used:

![Architecture with Redis](https://ucarecdn.com/18f3c514-1856-49b2-bd4d-1d48c8a043f7/)

It worked really well for many use cases. However, we collected enough evidence over time to decide that Cube needed to move on from Redis.

### Reasons to move on from Redis 

Please read the [blog post](https://cube.dev/blog/replacing-redis-with-cube-store) with detailed reasoning by Pavel Tiunov, CTO at Cube.

Alternatively, refer to the following summary:

- __Operations and reliability considerations.__ Redis requires a complex setup for [scalability](https://redis.io/docs/management/scaling/) and [high availability](https://redis.io/docs/management/sentinel/). To maintain a Redis cluster, you must run at least three Redis and Redis Sentinel nodes. Moreover, cluster coordination consumes a lot of resources under high load.
Use case considerations. Redis works well as a data store for the in-memory cache. However, it was never designed for large columnar payloads and low bandwidth consumption. Also, Redis is less than optimally suited as a data store for the query queue given the [complexity of data flow](https://github.com/cube-js/cube.js/blob/master/packages/cubejs-query-orchestrator/DEVELOPMENT.md).
- __Client stability and code complexity considerations.__ Since the first days of Cube, Redis has required substantial effort to provide stable support on the client side. We had to introduce the support for multiple client libraries (node-redis and ioredis) and the related [`CUBEJS_REDIS_USE_IOREDIS` config option](https://cube.dev/docs/reference/environment-variables#cubejs-redis-use-ioredis). Moreover, we had to maintain complex and error-prone code on the client side to manage the query queue (see examples: [one](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L93,L101), [two](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L130,L137), [three](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L209,L218)).

One more deciding factor was that many Cube deployments—in addition to Redis—include Cube Store, a purpose-built distributed, fast, and reliable data store. So, we updated the implementation of the in-memory cache and query queue to use Cube Store.

### Up-to-date architecture

Up-to-date Cube deployments use Cube Store as the all-in-one data store: for in-memory cache, query queue, and pre-aggregations. Redis is not needed.

![Architecture with Cube Store](https://ucarecdn.com/c97e77a5-1ebf-4542-951c-480a7ccc2509/)

If you still have Redis as a part of Cube deployment, read on for the migration guide.

## How to migrate to Cube Store

Cube supports using Cube Store for in-memory cache and query queue starting from version 0.31.60. To migrate, you will need to upgrade Cube to this version and check the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver).

Migration scenarios slightly differ depending on where you run Cube: in Cube Cloud, in a self-hosted environment, or locally on your computer.

### Migration in Cube Cloud

In Cube Cloud, the migration from Redis is seamless. Every [production cluster](https://cube.dev/docs/cloud/configuration/deployment-types#production-cluster) in Cube Cloud includes a fully managed Cube Store with high availability.

Proceed through the following steps:

1. Pick a deployment, navigate to <Btn>Settings</Btn> / <Btn>General</Btn>, and ensure the Cube version is at least 0.31.60. Choose either a <Btn>Stable</Btn> (preferred) or the <Btn>Latest</Btn> channel:

    ![Versions](https://ucarecdn.com/7798b767-b917-4258-9fd8-54d5f87c9046/)

2. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, go to <Btn>Settings</Btn> / <Btn>Configuration</Btn> and ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore`.

    ![Environment variables](https://ucarecdn.com/3eeb9e89-a8c2-4a73-ab4a-56de840429eb/)

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

### Migration when self-hosting

In a self-hosted environment, the migration is really easy if you already have Cube Store. If you don’t run it yet and don’t want to complicate your setup, consider [using Cube Cloud](https://cubecloud.dev/auth/signup) which provides a seamless migration experience.

Proceed through the following steps:

1. If you don’t run Cube Store yet, you should run at least a Cube Store router. Please refer to the Cube Store [architecture overview](https://cube.dev/docs/caching/running-in-production#architecture) and example Docker configuration.
2. Ensure the version is at least 0.31.60 for all Cube nodes: API instances, refresh workers, Cube Store routers, and Cube Store workers.
3. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore` for all API instances and refresh workers.
4. Remove all [`CUBEJS_REDIS_*` config options](https://cube.dev/docs/reference/environment-variables/#cubejs-redis-url) from all API instances and refresh workers.
5. Remove Redis from your architecture.

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

### Migration on a local computer

If you run a single instance of Cube locally on your computer in the [development mode](https://cube.dev/docs/configuration/overview#development-mode), the migration is really easy because you already have Cube Store running in-process.

Proceed through the following steps:

1. Ensure the Cube version is at least 0.31.60.
2. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore`.

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

## What’s next?

Run Cube Store as part of your Cube deployments. [Get started today in Cube Cloud](https://cubecloud.dev/auth/signup) to try a fully managed Cube experience.

Enjoy the benefits of using Cube Store for the in-memory cache and query queue, including low query latency for identical queries and safeguarding data sources from burst load.

Please don’t hesitate to [talk to us](https://cube.dev/contact) if you have any questions. Also, join our Slack community of 8,000 data practitioners at [slack.cube.dev](https://slack.cube.dev) to discuss your experience with Cube Store.

Igor Lukanin

Deep Dives

Moving on from Redis to a better in-memory cache and query queue

How you win by using Cube Store: Part 1

When I was reading Pedram’s [deep dive on metrics layers](https://pedram.substack.com/p/what-is-the-metrics-layer) a few weeks ago, I couldn’t help but question the tool selection. One can obviously blame my employee-employer affiliation which is hard to deny. However, the real reason for that was this [summary statement](https://pedram.substack.com/p/what-is-the-metrics-layer):

> All three tools have different trade-offs, and their strengths and weaknesses tell of the challenges a metrics layer faces.
> 

While this is a true statement, it dismisses the fact that there’s an ergonomic, feature-complete, and accessible solution on the market that doesn’t suffer from highlighted pitfalls. With that, I’m putting Cube in the spotlight and kindly requesting your ~~blowback~~ [feedback on social media](https://twitter.com/thecubejs/status/1578049949811589123).

## In previous episodes

If you haven’t read the original [deep dive on semantic layers](https://pedram.substack.com/p/what-is-the-metrics-layer), please do. I promise it’s gonna be a rewarding experience.

There, Pedram mentions some prior thought leadership on the topic and introduces a challenge for three tools: Looker, dbt Metrics, and Lightdash. They have to model and provide access to the *Activation Metric*, a crucial characteristic of an imaginary B2B SaaS product with users and workspaces. A workspace is considered active if users perform some activation event, so we want to report on the activation rate daily, weekly, or monthly and how it changes over time. Later, Pedram shows how Looker, dbt Metrics, and Lightdash solve the data modeling and elaborates on their strengths and pitfalls.

Here’s the `workspace_details` table matching the imaginary dataset that was used by all tools to calculate the metrics. I’ve generated 20 million rows and put them into Postgres, so we have something to work with:

![dataset](https://ucarecdn.com/1f7f0a20-b331-4afa-9e63-7313a7e96c05/)

Now, let’s talk about Cube.

## Cube, the semantic layer

Cube is the [semantic layer](https://cube.dev) for building data applications, created to make data consistent and accessible. It solves the many-to-many problem many data teams have by serving as a source of truth for metrics definitions, access control rules, and caching settings.

![Cube in the pipeline](https://ucarecdn.com/dfb299b9-7e27-47f0-a362-0a14c643c740/)

Cube connects to numerous [data sources](https://cube.dev/docs/config/databases), from cloud data warehouses to [streaming platforms](https://cube.dev/blog/headless-bi-with-streaming-data), and delivers data to all kinds of [data consumers](https://cube.dev/docs/config/downstream) via multiple APIs, including the [SQL API](https://cube.dev/blog/expanded-bi-support). It is deliberately visualization-agnostic and delegates data presentation and visualization to BI tools, data notebooks, front-end apps, etc.

Cube is open source and there's also [Cube Cloud](https://cube.dev/cloud) that provides fully managed experience and additional tooling on Free, Standard, and Enterprise tiers (see [pricing](https://cube.dev/pricing)).

## Data modeling

In Cube, we define metrics in [data model](https://cube.dev/blog/open-source-data-modeling) files that seem to be heavily inspired by LookML.

At the top level, everything is grouped in “cubes” that are usually modeled over tables or views in the data warehouse but can also be modeled over pretty complex queries. Cubes include definitions for measures (quantitative features), dimensions (qualitative features), segments (reusable filters), and pre-aggregations (declarative definitions of rollups that drastically speed up queries, more on them later).

Here’s the data model for our activation metric:

```jsx
cube(`WorkspaceActivation`, {
  sql: `SELECT * FROM public.active_workspace_details`,

  measures: {
    count: {
      type: `count`
    },

    n_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
    },

    n_active_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
      filters: [ {
        sql: `${is_active_workspace} IS TRUE`
      } ],
    },

    activation_rate: {
      sql: `ROUND(100 * (1.0 * ${n_active_workspaces} / ${n_workspaces}), 2)`,
      type: `number`
    }
  },

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

    reporting_day: {
      sql: `reporting_day`,
      type: `time`
    },

    is_active_workspace: {
      sql: `is_active`,
      type: `boolean`
    }
  },
});
```

A few observations here:

- Similarly to Looker, we define measures as formulas, not as fully-formed SQL tables.
- While we can use “formatting” functions like `ROUND` in measure definitions, formatting options are deliberately limited. Cube puts the burden of data presentation on downstream tools.
- By default, we don’t have to specify how our reporting date should be broken down. We’ll be able to provide desired time granularity at query time.
- Needless to say, we can define other cubes and [joins](https://cube.dev/docs/schema/reference/joins#joins) between them.

## Query syntax

Cube provides a set of APIs to fetch data: [SQL API](https://cube.dev/docs/backend/sql) (mostly for BI tools and data notebooks), [REST API](https://cube.dev/docs/rest-api), and [GraphQL API](https://cube.dev/docs/backend/graphql) (mostly for front-end applications). A simple query to the SQL API:

```jsx
SELECT reporting_day, activation_rate
FROM WorkspaceActivation;
```

Of course, we can run a more complex query, too:

```jsx
SELECT
  DATE_TRUNC('WEEK', reporting_day) AS reporting_week,
  activation_rate
FROM WorkspaceActivation
ORDER BY 1 DESC;
```

Queries would turn into JSON when expressed in the REST API syntax. First one:

```json
{
  "measures": [
    "WorkspaceActivation.activation_rate"
  ],
  "timeDimensions": [ {
    "dimension": "WorkspaceActivation.reporting_day"
  } ]
}
```

Second one:

```json
{
  "measures": [
    "WorkspaceActivation.activation_rate"
  ],
  "timeDimensions": [ {
    "dimension": "WorkspaceActivation.reporting_day",
    "granularity": "week"
  } ],
  "order": {
    "WorkspaceActivation.reporting_day": "desc"
  }
}
```

Since we haven’t connected a downstream tool yet, we can run queries and explore the metrics in Cube’s [Playground](https://cube.dev/docs/dev-tools/dev-playground) with drop-down inputs for measures and dimensions:

![Playground](https://ucarecdn.com/0a0e01d2-cb3a-4cbe-8f2f-a87b257324bb/)

We can instantly get the JSON queries for the REST API here:

![JSON query](https://ucarecdn.com/51228e1d-33b6-46d7-8bf6-637e04261e2c/)

More conveniently, we can also look up the SQL statement that would be generated by Cube and run against the data warehouse when the query is executed. No surprise here: Cube uses the provided query and data model definitions to compose the final query: 

![Generated SQL query](https://ucarecdn.com/742514b5-ee4e-43dd-8c1b-d03b3284dbcc/)

## Query acceleration

Obviously, queries can be expensive: costly to run, too complex to wait for, or both.

In that case, we can define a pre-aggregation (actually, the recommendation is always to use pre-aggregations in production). Here’s what we need to append to the data model:

```jsx
  preAggregations: {
    main: {
      measures: [ activation_rate ],
      timeDimension: reporting_day,
      granularity: `week`,
      refreshKey: {
        every: `1 day`
      }
    }
  }
```

It instructs Cube to build a rollup and use it to serve queries that contain any subset of provided measures and dimensions (namely, `activation_rate` and `reporting_day` with weekly granularity). Usually, a single pre-aggregation speeds up multiple queries. We can also configure freshness: a pre-aggregation update can be triggered on schedule (e.g., every day) or when an arbitrary SQL statement yields a new result. 

Now, the last query would return the same result, but a completely different query would be executed against the data warehouse:

![Accelerated SQL query](https://ucarecdn.com/786f7cc1-791d-4122-b13a-b3660405f439/)

Truth be told, it wouldn’t be executed against the data warehouse at all. The `prod_pre_aggregations.workspace_activation_main` table is transparently created and maintained in [Cube Store](https://cube.dev/blog/introducing-cubestore), a custom-built massively distributed columnar storage for pre-aggregation data.

There’s an option to skip using Cube Store and store pre-aggregations in a data warehouse, but that’s not the most reasonable thing to do. With pre-aggregations in Cube Store, Cube can serve practically any query with sub-second latency (expect something between 50 ms and 500 ms) and allow for a decent concurrency (say, 100 QPS or maybe 1000 QPS).

Okay, but what would Cube do if we need to query for other granularity? Since it’s aware that queried measures are defined with distinct counts, it won’t be using the rollup and will transparently fall back to a query to the data warehouse (there’s an [option to disallow that](https://cube.dev/docs/caching/using-pre-aggregations#rollup-only-mode) at all times, if we need to).

![Non-accelerated SQL query](https://ucarecdn.com/a0d3bd13-889d-4a92-925d-984ee2dd9a4d/)

## Data consumers

Now, we’re ready to connect downstream tools. We can pick anything that supports Cube’s SQL API, and that’s practically any tool. Cube’s SQL API uses Postgres dialect and wire protocol, so if something works with Postgres as an upstream source, it works with Cube: 

![Supported data sources](https://ucarecdn.com/ba240ef2-11fc-477d-843d-39b0b9fe2649/)

We have numerous options here. Out of respect for the first tech company ever where the [first triple-name-tie is Claire](https://twitter.com/clairebcarroll/status/1570194384980971522), let’s connect Cube to [Hex](https://hex.tech):

![Connection to Hex](https://ucarecdn.com/7c89eb8a-d7dd-41b9-bcdb-c1889b70eba5/)

Indeed, [Hex works with Cube](https://cube.dev/blog/hex-integration-collaborative-data-notebooks):

![Hex notebook](https://ucarecdn.com/3761be9c-722c-461e-a1ba-7dcb01e1441b/)

In case we’re building a front-end app, we can directly `fetch` from the REST API endpoint or use the provided JavaScript client library:

```jsx
import cubejs from '@cubejs-client/core';

const cubejsApi = cubejs.CubejsApi(
  process.env.CUBE_TOKEN,
  { apiUrl: process.env.CUBE_API_URL }
);

const query = {
  measures: [ 'WorkspaceActivation.activation_rate' ],
  timeDimensions: [ {
    dimension: 'WorkspaceActivation.reporting_day',
    granularity: 'week'
  } ],
  order: {
    'WorkspaceActivation.reporting_day': 'desc'
  }
};

const resultSet = await cubejsApi.load(query);

console.log(resultSet.tablePivot());
```

Now you can plot the data with a [charting library](https://awesome.cube.dev/?tools=charts) of your choice:

![Data in the terminal](https://ucarecdn.com/dee1b9f9-74ff-4e06-8522-335922f2afd1/)

## Wrapping up

Hopefully, this write-up provides some insight into how Cube works and helps compare it to Looker, dbt Metrics, and Lightdash from the original [deep dive](https://pedram.substack.com/p/what-is-the-metrics-layer).

Also, if you’re using dbt Metrics, please keep in mind that you can leverage Cube’s [integration with dbt](https://cube.dev/blog/dbt-metrics-meet-cube) that transparently reads metrics definitions from dbt and automagically creates a data model from them.

Currently, Cube uses JSON-like markup for data model definitions and Looker-like metrics organization into cubes. Later in 2022, Cube is likely to [introduce YAML markup](https://forms.gle/4CZRRKK8nD4PvtxZA) for data models and an alternative approach to metrics.

With that, I do have an answer to the final question from Pedram’s article:

> Can a metrics layer be universal enough to gain applicability across the data stack yet still be designed in such a way to be relevant to BI tools?
> 

Yes. Try Cube.

The easiest way to get started with Cube is [Cube Cloud](https://cube.dev/cloud). It provides managed hosting and convenient tools for Cube, including the Playground, the data model editor with syntax highlighting, and—more importantly—the ability to instantly fork data models and query private versions of APIs while the production version stays intact and serves queries.

[Sign up for Cube Cloud](https://cubecloud.dev/auth/signup) on the free tier today and experience that yourself. Feel free to connect your production dataset or tinker with the Postgres instance used in this blog post:

```
Host:      demo-db-examples.cube.dev
Database:  ecom
User:      cube
Password:  12345
```

Cube in the spotlight alongside Looker, dbt Metrics, and Lightdash.

Deep dive: What the heck is the Semantic Layer

The ongoing story of the digital age is still about [taking products apart](https://hbr.org/2014/06/how-to-succeed-in-business-by-bundling-and-unbundling). The wave of [bundling](https://towardsdatascience.com/the-great-data-debate-unbundling-or-bundling-7d7721ee8514), [unbundling](https://blog.fal.ai/the-unbundling-of-airflow-2/), and [rebundling](https://dagster.io/blog/rebundling-the-data-platform) happening in the data space has subsided, but the point remains. Generations of tools in the modern data stack, whatever it is, succeed each other primarily for two reasons:

- Emerging tools provide more flexibility and a lower entry barrier for practitioners.
- Alternatively, teams escape into a safe harbor of a well-known integrated solution that promises big returns on even bigger investments. Indeed, has anyone ever got fired for buying Looker?

Well, one can only so much joke about Looker. Things like Looker’s $2.6B acquisition by Google in 2019, Snowflake’s $3.4B IPO in 2020, or dbt Labs’ [four consecutive venture rounds](https://pedram.substack.com/i/54634871/the-devils-bargain-of-venture-funding) in 2020–2022 leading to a $4.2B valuation fuel the data industry and help put more tools into a data practitioner’s toolbox.

Today, we’re going to talk about Cube and Apache Superset—a bundle of tools representing a finer alternative to Looker in many use cases.

> We've had a webinar on replacing Looker with Cube and Superset. [Check the recording and slides.](https://cube.dev/events/replacing-looker-with-cube-and-superset)

## Free as in “open source”

[Cube](https://cube.dev) and [Superset](https://superset.apache.org) belong to different categories in the data stack. The former is the [headless business intelligence](https://cube.dev/blog/headless-bi) platform, and the latter serves as the data visualization and exploration platform. However, being open source and standing on the shoulders of giants are among many things that they have in common.

Both tools are licensed under Apache License 2.0 and actively developed on GitHub. Over the last 3 years, Cube’s [repository](https://github.com/cube-js/cube.js) has acquired almost 14,000 stars (an unmatched [leader in the category](https://twitter.com/sspaeti/status/1570044606338990081)). Written mostly in Rust, Cube’s data processing and storage are based on the [Arrow DataFusion](https://github.com/apache/arrow-datafusion) query execution framework which uses [Apache Arrow](https://arrow.apache.org/) as its in-memory format. Superset has a longer history (born at Airbnb in 2015 where it replaced Tableau, joined the Apache Incubator program in 2017), has a [repository](https://github.com/apache/superset) with 48,000 stars, and is powered by [Apache ECharts](https://echarts.apache.org/en/index.html), one of the most mature and popular charting libraries. 

![Cube and Superset](https://ucarecdn.com/5f3f2313-c055-4b9e-b345-56568c53aca2/)

You’re definitely free to take and use Cube and Superset at your own discretion, including self-hosting them in your private or public cloud. You’re also free to explore and get started with [Cube Cloud](https://cube.dev/cloud) and [Preset Cloud](https://preset.io/product/), fully managed platforms with freemium pricing models and plenty of quality of life features on top of open source products. Compare that to Looker’s “[request a demo](https://cloud.google.com/resources/looker-demo/?_ga=2.157282873.347220352.1663670208-344442256.1663670208)” paywall that would cost your team a few weeks of qualification calls on Google Meet before you start building.

## Simple as in “separation of concerns”

Cube is intentionally [headless](https://cube.dev/blog/headless-bi) and agnostic to the data presentation layer. However, it does a number of things and does them well:

- connects to a plethora of [data sources](https://cube.dev/docs/config/databases), from cloud data warehouses like BigQuery, Snowflake, and Redshift to streaming platforms like Kafka and Redpanda;
- provides a [data modeling](https://cube.dev/docs/schema/getting-started) layer as the centralized source of truth for metrics definitions;
- secures data with its [access control](https://cube.dev/docs/security) layer;
- makes sure any query runs well under a second with its [caching](https://cube.dev/docs/caching) layer;
- delivers data to any downstream tool, including BI tools like Superset, via its API layer that provides [SQL API, REST API, and GraphQL API](https://cube.dev/docs/config/downstream#ap-is-references).

Superset works by connecting to a data source and querying the data where it lives. The ideal way to use Superset is with a [dataset-centric approach](https://preset.io/blog/dataset-centric-visualization/) to modeling and visualization. 

A dataset-centric approach implies that a well-normalized dataset is used to “derive” richer datasets with extra semantics, making them valuable for analysis and visualization. By creating derived datasets that incorporate aggregate metric calculations earlier in the ETL/ELT process *before* surfacing for visualization, many of the issues with change management, maintenance, logic reuse within dashboards, etc. are significantly reduced for a better experience. 

Cube complements Superset in this way, and this allows for Superset-generated SQL queries to work with no issues when connected to a Cube environment (which is treated as a PostgreSQL database connection). 

![Looker vs Cube + Superset](https://ucarecdn.com/59031c52-890f-402f-9400-0d0d7034c887/)

You’re free to configure and fine-tune each tool and each layer within every tool when building with Cube and Superset. Want to build and schedule a report? Check Superset dashboards. Need to expose new metrics in that report? Cube’s data modeling layer is at your service.

## Compatible as in “no vendor lock-in”

Speaking of data modeling, Cube provides a [data modeling language](https://cube.dev/blog/open-source-data-modeling) that is not tied to any specific interface, visualization, or user experience. Unlike proprietary LookML definitions that require Looker IDE for syntax highlighting and linting, Cube’s data model is written in JavaScript (and [soon-to-be-written](https://forms.gle/4CZRRKK8nD4PvtxZA) in YAML) and feels native in your editor of choice. On top of that, you are able to generate a data model dynamically or even fetch it from a remote endpoint.

Consider an excerpt of Looker view from the recent [deep dive](https://pedram.substack.com/p/what-is-the-metrics-layer) by Pedram:

```yaml
view: workspace_activation {
  sql_table_name: "METRICS"."WORKSPACE_ACTIVATION" ;;

  dimension_group: date {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}."REPORTING_DATE" ;;
  }

  dimension: is_active_workspace {
    type: yesno
    sql: ${TABLE}."IS_ACTIVE_WORKSPACE" ;;
  }

  dimension: workspace_id {
    type: string
    primary_key: yes
    sql: ${TABLE}."WORKSPACE_ID" ;;
  }

  measure: count_workspaces {
    type: count_distinct
    description: "# of Workspaces"
    sql: ${workspace_id} ;;
  }

  measure: count_active_workspaces {
    type: count_distinct
    description: "# of Unique Workspaces Active within 1 Day"
    sql: ${workspace_id} ;;
    filters: [is_active_workspace: "yes"]
  }

  measure: activation_rate {
    type: number
    sql: ${count_active_workspaces} / ${count_workspaces} ;;
    value_format_name: percent_1
  }
}
```

Compare with the equivalent Cube data model (way less of `;;`, to say the least):

```javascript
cube(`WorkspaceActivation`, {
  sql: `SELECT * FROM public.active_workspace_details`,

  measures: {
    count: {
      type: `count`
    },

    n_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
    },

    n_active_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
      filters: [ {
        sql: `${is_active_workspace} IS TRUE`
      } ],
    },

    activation_rate: {
      sql: `ROUND(100 * (1.0 * ${n_active_workspaces} / ${n_workspaces}), 2)`,
      type: `number`
    }
  },

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

    reporting_day: {
      sql: `reporting_day`,
      type: `time`
    },

    is_active_workspace: {
      sql: `is_active`,
      type: `boolean`
    }
  },

  preAggregations: {
    main: {
      measures: [ activation_rate ],
      timeDimension: reporting_day,
      granularity: `week`,
      refreshKey: {
        every: `1 day`
      }
    }
  }
});
```

In a similar fashion, Cube’s API layer is compatible with practically any data consumer. When the time comes, and you realize that, alongside business users using dashboards and reports in Superset, you’d like to have data analysts crunching numbers and seeking insights in data notebooks, Cube will be there to help liberate your data.

![Data consumers](https://ucarecdn.com/b35f44ef-12a5-42d7-a618-74637fc3e0cd/)

You can expect any SQL query generated by your data exploration tool like Superset to work with Cube’s SQL API. Needless to say, equivalent queries to Cube’s REST API and GraphQL API will yield the same data:

```sql
SELECT
  DATE_TRUNC('WEEK', reporting_day) AS reporting_week,
  activation_rate
FROM WorkspaceActivation
ORDER BY 1 DESC;
```

Let’s have a bird’s-eye view here.

The generation of BI tools like Looker encouraged end-users to invest heavily in building out tons of LookML models to populate the semantic layer. While this established Looker as a source of truth in an organization for business metrics, it also created an immense amount of lock-in in your BI tool. You couldn’t take your LookML models with you and use them with another tool if you decided to switch BI tools. Google’s acquisition of Looker accelerated organizations’ anxieties around this lock-in, to the point that Google was actually [forced to integrate LookML with Tableau](https://roundup.getdbt.com/p/look-ml-tableau-metrics-layer). This way, organizations could use LookML for transformation (Looker’s strength) and Tableau for visualization (Tableau’s strength).

The future however is looking to be very different from the past:

- Open-source headless BI platforms like Cube provide unified semantic layers to sit between the database and BI later, eliminating the need, or providing alternate implementations, for expensive solutions like Looker that lock you in.
- Open-source BI tools like Superset with an intentionally thin semantic layer complement Cube and enable *last-mile data transformation* for the explicit purpose of data visualization in your BI tool.

When the need for a complex yet flexible metrics/semantic layer arises, Cube and Superset is a winning open source-based combo!

## Ready-to-go as in “today”

Now, you’re very much welcome to try Cube and Superset today and see them in action. Please join the Slack communities of [Cube](https://slack.cube.dev) and [Superset](https://join.slack.com/t/apache-superset/shared_invite/zt-16jvzmoi8-sI7jKWp~xc2zYRe~NqiY9Q) to share your feedback.

Please tune in to the [webinar on October 4](https://cube.dev/events/replacing-looker-with-cube-and-superset) with [Shreesham Mukherjee](https://www.linkedin.com/in/shreesham/), Developer Relations Engineer at Preset, and [Igor Lukanin](https://www.linkedin.com/in/igorlukanin/), Head of Developer Relations at Cube. We’ll discuss Cube and Superset as an open source alternative to Looker in popular use cases, demo both tools, and take your questions.

See you!

Shreesham Mukherjee

Looker

Respectfully unbundling the elephant in the BI room.

Open source Looker alternative with Cube and Apache Superset

Previously, we’ve [described the parts of headless BI](https://cube.dev/blog/headless-bi), taken an [in-depth look at the data modeling layer](https://cube.dev/blog/open-source-data-modeling), and explored one use case for headless BI: [embedded analytics](https://cube.dev/blog/what-is-embedded-analytics). This week, let’s take a step back and look at the category of data applications.

But first…

## What are data applications?

“Data apps” is an umbrella term for a category of interactive tools that use data to deliver insight or automatically take action. When we talk about data apps, we frequently cite the examples of recommendation engines, data visualization built into applications, and customized internal reporting tools for business teams.

## Isn’t this just embedded analytics?

Embedded analytics takes the kind of exploration that used to happen in dashboards and legacy BI tools, and injects it directly into the applications that internal teams and external customers already use. Headless BI facilitates building embedded analytics more quickly. But embedded analytics is just the beginning.

Despite being more accessible and customized than traditional dashboards, embedded analytics is still primarily a tool for data exploration. By contrast, data applications are capable of data explanation: highlighting trends, surfacing insights, making recommendations. This type of application entails a dynamic, purpose-built user experience, and it is typically developed by software and data engineers, not business analysts.

## What are some use cases of data applications?

__The first type of data applications is an embedded data app.__ Think of this as the evolution of embedded analytics, but unlike embedded analytics’ static dashboards, embedded data features tend to be highly customized, dynamic, and purpose-built. These applications surface insight within the native user experience of another application.

__A business’s internal data products and portals are a second kind of data applications.__ Unlike traditional or embedded exploration dashboards, this type of data application is purpose-built for a specific business unit, and is built with relevant business context. These applications’ custom interactivity allows business users to receive insights without mastering data analysts’ workflows.

__The third type of data applications are end-consumer-facing applications.__ These may be built for customers, partners, or shareholders, and they are not dissimilar from internal applications—but they tend to require a finer level of design polish and customization. Additionally, this type of app must be built for higher performance, reflecting consumers’ expectations of speed.

![](https://ucarecdn.com/f3b2607c-8d11-489e-a09e-df9f94c1384c/)

## How are data applications built?

By their nature, data apps require recourse to large quantities of data. This has been made possible by the rise of the cloud data warehouse and an ever-growing ecosystem of data ingestion, governance, transformation, and orchestration tools.

But given their complexity and power, data apps generally are built by engineering teams, and they require integration with modern engineering workflows, including version control, testing, and continuous integration and deployment practices.

### Building from scratch

Embedding data app functionality into a larger application generally requires building from scratch. What is the architecture of such a solution? 

#### Data store

Naturally, a data application starts with the data—and the basis of the modern data stack is the cloud data warehouse. This can be a general purpose data warehouse like [Snowflake](https://snowflake.com) or a real-time tool like [Firebolt](https://www.firebolt.io/), [ClickHouse](https://clickhouse.com/), or [Materialize](https://materialize.com/).

#### Headless BI layer

A crucial component of a data app is the [headless BI layer](https://cube.dev/blog/headless-bi). Specifically, a major piece of this is __access control__ integrated with the 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 highly concurrent queries with sub-second latency that modern data consumers expect.

The BI layer is also where __data modeling__ is handled, to ensure that a data app’s users consume the same data definitions as users of other internal or external applications. Data modeling and metrics definition should be handled once, and this must be up-stack from every application or dashboard.

Data is then made available via diverse APIs—e.g., SQL, GraphQL, and REST—to be consumed by…

#### A hybrid presentation layer

For the high customization expected of an embedded data application, and when front-end teams are looped in, different charting libraries can be used. These range from [D3](https://d3js.org/) to [Chart.js](https://www.chartjs.org/) and [Highcharts](https://www.highcharts.com/). These most likely will be natively integrated with frontend application frameworks like [React](http://react) or [Angular](https://angularjs.org/).

### Working with a framework

For the second and third types of data applications, the initial layers of the data stack are the same—i.e., the base layer is a data warehouse, followed by a headless BI layer for data modeling, access control, caching, and application APIs.

For the user interface, however, there’s typically less customization required. This creates the opportunity to take advantage of the new category of no code / low code tools like [Appsmith](https://www.appsmith.com/) and [Retool](https://retool.com/), which can be used to quickly build analytics interfaces.

There also are data application frameworks that are helpful here: tools like [Plotly Dash](https://plotly.com/dash/) and [Streamlit](https://streamlit.io) make it possible to turn data scripts into shareable web applications without the need for front-end development.

## What's next?

As it gets easier to build customized experiences, the number and types of data apps will proliferate—but the use case for a basic dashboard-centric experience won’t go away. There will always be cases where needs are best met with traditional charts, or when the quick turnaround requires making something available without tapping engineering resources for help. For these, embedded analytics are and will remain the best choice.

What’s exciting, though, is all of the new opportunities that the modern data application stack makes available. Opportunities for working with ever greater quantities of data, with ever greater complexity, will only grow.


Jonathan E Cowperthwait

Application Development

Moving beyond the dashboard.

What are data apps?

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://ucarecdn.com/d37e69cf-2bec-4469-997b-8782fa6c5d17/

Artyom Keydunov

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

The Modern Data Stack for Embedded Analytics

Previously, we’ve discussed the recent emergence of headless BI, and reviewed [headless BI’s four essential components][1]. Now, let’s take a more in-depth look at one of those layers: data modeling. Here is an introduction to open source data modeling in Cube’s headless BI platform.

How data is modeled is the first fundamental element of the business intelligence stack, because it organizes and streamlines querying a data source. Additionally, the data model defines the relationships between each data entity, which forms the basis of all analysis of that data.

Data modeling is a vital component of the BI stack and must be consistent and universally accessible. And so, its logic should be defined only once, and should be defined in code. Furthermore, this code should be governed by version control, to enable safe iteration as an organization grows.

## A data modeling language for Headless BI

![open source data modeling language for headless BI][image01]

Cube’s entire headless BI platform is open source—and this includes our open source data modeling language. This language is used to describe measures, dimensions, and relationships in a data source. Then, Cube servers use the model written in this language to generate SQL queries against data sources when responding to requests made to our REST, GraphQL, and SQL APIs.

Unlike [other data modeling languages][7], Cube’s data modeling language is not tied to any specific interface, visualization, or user experience. This decoupling makes it possible to use the same model definitions across every application. Each data consumer can have a custom-tailored experience in the data application of their choice while still working with consistent and standardized definitions.

## The design of Cube’s data modeling language

**Cube’s data model is a relational graph of the entities that contain measures and dimensions.** 

In Cube, entities are called “cubes”—inspired by OLAP cubes. In essence, they are data tables with a semantic meta layer that describes both the cube’s measures and dimensions and its relationships to other cubes. 


![open source data modeling - Cube][image02]

**Cube operates on the foundational primitives of *dimensions* and *measures*.** Users can define metrics by combining these primitives.  

***Dimensions***  are properties of entity objects, e.g., a user’s country or a product’s category. 

```javascript
cube(`Products`, {
  dimensions: {
    category: {
      sql: `category`,
      type: `string`
    }
  }
});
```

***Measures*** are aggregations, typically defined as SQL expressions or algebraic operations over other measures:


```javascript
cube(`Orders`, {
  measures: {
    totalValue: {
      sql: `value`,
      type: `sum`
    }
  }
});
```

Finally, cubes are connected to each other and constitute a data graph.


```javascript
cube(`Orders`, {
  joins: {
    Products: {
      relationship: `belongsTo`
      sql: `${Orders}.product_id = ${Products}.id`,
    }
  }
});
```

When users construct metrics, the dimensions and measures they query don’t have to belong to the same cube. They can be located in different places on the data graph. Cube will generate appropriate SQL, including taking care of [chasm / fan-outs][8], to join the underlying tables to respond to the request. 

![open source data modeling - Cube][image03]

**Cube’s approach is different from a query-oriented approach** in which metrics are defined by a custom query and a bunch of attributes are assigned to that query. That approach essentially results in single cubes, each with a single measure and a subset of dimensions and filters. Because these cubes aren’t connected to one another, we can’t reuse dimensions, filters, joins, access controls, caching configuration, etc. 

If we were to continue answering more questions and building more metrics, we would have to build more queries, with duplicated definitions and different join paths. At best, this would be repetitive; at worst, it would lead to inconsistent definitions and a combinatorial explosion of metrics and dimensions. 

Instead, we’re focused on letting users define relationships and reuse definitions to build maintainable and scalable data models.

**Cube’s data modeling language is based on JavaScript.** This may come as a surprise, given that many data modeling frameworks and languages are based on YAML. However, as a markup language, YAML has no runtime and only limited capacity for code organization. This is a big limitation for complex and dynamic data models.

By running a data model in a JavaScript virtual machine, Cube can mutate the model at runtime both reactively and proactively, which enables dynamic data definitions, caching, and access control rules.

**Cube’s data modeling language also manages caching.** Users can define a set of measures and dimensions that are commonly used together, and materialize them as rollup tables. Since the caching rules are defined in code, it’s possible to manage them using best practices including version control, code reviews, and tests in isolated environments.

![open source data modeling - Cube][image06]

**In Cube, data processing and storage are written in Rust**, based on the [Datafusion][9] query execution framework, which uses [Apache Arrow][10] as its in-memory format.

## A data modeling example

Let’s take a look at how to use Cube’s data modeling language.

We’ll model data for an imaginary e-commerce website. Assume the data warehouse has tables like `orders` and `users` with information about transactions and who placed them.

### Orders

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the order</td>
  </tr>
  <tr>
    <td>
      <code>user_id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>value</code>
    </td>
    <td>Value of the order in USD</td>
  </tr>
  <tr>
    <td>
      <code>status</code>
    </td>
    <td>Status of the order, either “processing” or “completed”</td>
  </tr>
</table>

### Users

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>country</code>
    </td>
    <td>Country of the user</td>
  </tr>
</table>

We can represent these two tables in Cube’s modeling language as cubes with this configuration:


```javascript
cube(`Orders`, {
 sql: `SELECT * FROM orders`,
 measures: {
   totalValue: {
     sql: `value`,
     type: `sum`,
     description: 'Revenue from completed orders placed on our website.',
   }
 },
 dimensions: {
   id: {
     sql: `id`,
     type: `number`,
     primaryKey: true
   },
  
   status: {
     sql: `status`,
     description: 'Status of the order, either processing or completed',
     type: `string`
   },
  
   time: {
     sql: `timestamp`,
     type: `time`
   }
 }
});
```

```javascript
cube(`Users`, {
  sql: `select * from users`,
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    country: {
      sql: `country`,
      type: `string`,
      description: `User's country of residence`
    }
  }
});
```

Let’s look at the major elements of the model.

The `sql` property describes the table of data that will be used in cube, which references the underlying table within the data warehouse. It also can support arbitrary SQL queries in complex cases.

The cubes include defined **_measures_** and **_dimensions_**:

 - ***Measures:*** quantitative data, such as the number of units sold, number of unique visits, revenue, and so forth
 - ***Dimensions:*** properties of data entities, such as state, gender, product name, or units of time
 
To build data relationships, we define the `joins` property on one of the cubes:

```javascript
cube(`Orders`, {
  joins: {
    Users: {
      relationship: `belongsTo`
      sql: `${Orders}.user_id = ${Users}.id`,
    }
  }
});
```

## Querying the data model

With the above data model, it’s now possible to query Cube’s APIs to learn total value grouped by country, or a specific company‘s total value from completed orders.

To find out the total value of orders by country, we’d use the following JSON query:

```json
{
  measures: [Orders.totalValue],
  dimensions: [Users.country]
}
```

You also can explore the data model from within a BI tool:

![querying a Cube data model using Superset][image07]

These examples are relatively simple, but a data model and queries can get very complex, with many measures, dimensions, and relationships.

By creating a data model foundation and exposing metrics via many APIs, Cube ensures that data is accessible and consistent for every downstream application.

 - *Want to explore Cube’s data modeling for yourself? [Create a free Cube Cloud account now][2].*
 - *Have questions or input? [Drop us a line][3], [join our Slack][4], or [view our headless BI project on GitHub][5].*
 - *Want updates like these in your inbox? [Subscribe to our newsletter][6].*

[1]: https://cube.dev/blog/headless-bi
[2]: https://cube.dev/cloud
[3]: https://cube.dev/contact
[4]: https://slack.cube.dev
[5]: https://github.com/cube-js/
[6]: https://cube.dev/#email
[7]: https://www.looker.com/platform/data-modeling/
[8]: https://biguru.wordpress.com/2008/05/01/its-all-in-the-universe-handling-chasm-and-fan-traps/
[9]: https://github.com/apache/arrow-datafusion
[10]: https://arrow.apache.org/
[image01]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/713a8abb-a7d2-4c83-b1f7-6b39bcbbd748.png
[image02]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/26a0660e-b04a-4eaf-b863-7a4c7a72c87f.png
[image03]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/86a88814-86a5-4236-9edf-208d18215ea4.png
[image04]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d4290ab8-3c0d-401b-ae7c-4828b784280f.png
[image05]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d701d0de-1b86-450b-9d2e-b302a5aae7e6.png
[image06]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8b3a6449-c759-4263-9198-87bd29e2b44d.png
[image07]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/688c2958-0016-4d3b-8699-18477d524f8b.png

Data Modeling

An introduction to open source data modeling in Cube’s headless BI platform.

Cube’s Open Source Data Modeling Language

With the [explosive growth of cloud data warehouses][1], an entire [ecosystem of new data tools][2] has emerged—transformation, testing, quality, observability, to name a few. On top of this modern data stack, we’ve seen a proliferation of new applications, including dashboards, [embedded analytics][3], automation tools, and vertical-specific reporting tools. And, this boom in the modern data stack necessitates a new generation of business intelligence: headless BI.

Why? Because with every addition to the ecosystem lies the challenge of defining metrics consistently, and making them accessible to every application. This challenge has only grown more urgent—as [others][4] [have][5] [observed][6].

Specifically, the challenge of data consistency poses a problem for users of traditional business intelligence tools—like Tableau, Looker, or Mode. These tools take defining metrics upon themselves and don’t share their definitions with others:

>While these tools enable business users to use their data better, they have one fatal constraint: users can only use the metrics they’ve defined within the four walls of the visualization tool.\[[1][7]\]

In response, we’ve begun to see so-called [“headless” BI][8]. These are platforms that decouple data metrics from the presentation layers that display them—pushing metrics definition up the data stack. 

However, the popularity of the phrase hasn’t been accompanied by a consistent definition of what headless BI even is. **There is more to headless BI than just upstack metrics definition.**

For the benefit of data engineers and data consumers alike, we propose a standardized definition of headless business intelligence. And with it, we describe four essential components of a headless BI tool.

![data-modeling.png][data-modeling]
## 1. Data Modeling
This is the most widely understood aspect of headless BI. Simply, software engineering best practices dictate that **metrics and their governance should 
a) live in code, and b) be deduplicated.** Deduplication refers to removing them from each end-user application in which they’re variously and inconsistently defined, and moving them upstack.

Fundamentally, defining metrics within a data application leads to duplicated effort and duplicated results. These redundancies—and, therefore, inconsistencies—occur because, regardless of its size, no organization only uses one data application. 

Sales teams use a CRM, marketing teams use marketing automation, and executives use dashboards. If each team were to independently count their own definition of “customer” or sum their own interpretation of “annual revenue”, at best these metrics would be defined more times than necessary. What’s more likely is that the definitions wouldn’t match—which is, in every case we can think of, not ideal. This outcome also defeats [the whole point of collecting data and distributing it][9] to every team.

When a headless BI tool defines metrics used by every downstream application, companies get uniform insights instead of inconsistent signals. What’s more, headless BI can help organize and simplify once-complex SQL queries by abstracting them away: a metrics layer can generate SQL queries for defined metrics and dimensions, so downstream applications—and their users—don’t have to. This layer also plays an important role in data governance, including lifecycle management, ownership, and change approval.

Lastly, in addition to its own definitions, a headless BI platform should consume those from further upstream—[e.g., those from transformation tools like dbt.][10]

![access-control.png][access-control]
## 2. Access Control
**Since a headless BI tool is the first layer downstream of one’s data sources, it must also manage and audit access to that data.** These controls can’t be decoupled from the data model, and, therefore, should be defined within it.

Why?

First, this need *also* stems from a goal of deduplication. As matters of both good architecture and security best practices, the rules of who can access what should be defined and enforced once, not redundantly—and possibly inconsistently—by downstream tools.

Second, by instrumenting access control within the data model, you are able to use dynamic metrics definitions. That is, metrics definitions that vary based on the context in which they’re requested. Contextually-informed metrics are particularly useful in a multi-tenant model in which (e.g.) each of your customers ought to access a defined measure, “sales”, that reflects their revenue. Each data consumer gains access only to the subset of metrics to which they’re entitled—without manually redefining “sales” for each.

Implementing access control within a headless BI tool enables making aggregate metrics widely accessible. Additionally, doing so also protects specific sensitive data (e.g., personally identifiable information.) With the precise ability to control access, you can limit it only to an authorized subset of individuals or applications.

The above two layers constitute what could be considered a “metrics store” or a “modeling layer.” But we’ll continue with our assertion and say: they are not sufficient to make up a headless BI solution.

To constitute headless BI, you also need to make data readily accessible to data consumers, and for that you need…

![caching.png][caching]
## 3. Caching
**A headless BI tool is the correct place in which to situate a data caching layer.** At first, it may seem counterintuitive to locate a caching layer up-stack of data applications. However, the guiding principles here are, once again, consistency and deduplication.

For starters, a centrally located caching layer ensures consistent data freshness across tools, using one centrally defined schedule of cache invalidation and warming. Every tool will present the same values at the same time.

Additionally, if a headless BI tool has been tasked with accessing data from a data store and organizing it into definitions, then it is optimally positioned to manage queues of requests from multiple applications and data consumers. 

Without this mediation, multiple applications may make the same requests. Redundant requests both incur needless bandwidth costs and degrade the performance of other concurrent queries.

Headless BI can provide several levels of caching. First, a query-level cache can store the results of the query to ensure that identical queries from a tool, or multiple tools, do not increase the load on the underlying data warehouse. 

Additionally, a second layer can implement aggregate awareness: logic to find the smallest, most efficient table to serve the query. Aggregate tables can be either created externally or within the headless BI tool, and can significantly speed up queries and solve the cold cache problem when maintained in the background.

The prior era of multi-second dashboard loads is over, and it’s not coming back. By managing caching, a headless BI tool ensures sub-second responses in every downstream application, regardless of the number of requests or the volume of data in the underlying data store.

![api-layer.png][api-layer]
## 4. APIs
Finally, we get to the essential nature of headless BI’s “headlessness.” **A headless BI tool must make its data accessible to every “head” application, be it data visualization, dashboarding, embedded analytics, or automation.** This means that a headless BI tool must make its data available via various APIs.

The most obvious candidate here is a SQL endpoint. This enables data consumers to keep using dashboard tools, notebooks, and legacy applications, such as Tableau, that they formerly would have directly connected to a data warehouse.

The next additions are REST and GraphQL. These ensure that the same metrics that reach dashboards are also available to embedded analytics features, end-user-facing applications, and other innovative uses of your data.

By exposing APIs to connect discrete applications, a headless BI tool makes it possible to build new automations. Some examples include a workflow that alerts a sales team to contact a customer based on changes in her account usage, or an upstream data source for applications such as reverse ETL. 

“Business intelligence” no longer solely consists of reacting to data in a dashboard; now, an intelligent business actually has actionable data—and acts on it.

## Headless BI should be open source
We’ve just laid out the essential components of a headless BI system. In theory, this could all be furnished by a vendor in a vertically-integrated closed system. However, **a headless BI system is truly valuable when it integrates into any data stack**, made up of pieces from any number of vendors.

As a practical matter, this is only possible when multiple companies collaborate: each vendor can contribute connectors and optimizations to improve the compatibility of a headless BI tool with their tools.

Furthermore, headless BI is so important, and its benefits to the community are so great, that **this technology must be made available to everyone.** Its stewardship should be guided by community input and community values.

We’re proud of the community that has grown around Cube in the [three years since we open-sourced our headless BI platform][11]. Over 200 contributors have enriched our tool on [our GitHub repo][12], and over 5000 users have joined our community Slack to trade tips and share feedback.

## Headless BI: WIP
What next? There are many optimizations to add, tools with which to integrate, and powerful features to build. Along the way, we’re [soliciting and building connectors][14] to tools like legacy BI applications and gathering feedback in [our Slack][13]. Our [Twitter DMs][15] are open and [our community meets][16] every month. And, with this momentum, [we’re hiring][17] more bright minds to help build Cube full-time. 

Together, we’ll bring data to life and power the next generation of consistent and powerful data applications.

Won’t you join us?

[1]: https://www.theregister.com/2021/11/15/rise_of_the_cloud_data_warehouse/
[2]: https://www.moderndatastack.xyz
[3]: https://645ventures.com/voices/articles/the-commoditization-of-business-intelligence-tools-the-rise-of-embedded-intelligence-and-our-investment-in-cube-dev
[4]: https://benn.substack.com/p/metrics-layer?s=r
[5]: https://basecase.vc/blog/headless-bi
[6]: https://towardsdatascience.com/a-brief-history-of-the-metrics-store-28208ec8f6f1
[7]: https://basecase.vc/blog/headless-bi
[8]: https://betterprogramming.pub/headless-bi-metric-standardization-in-action-afb2ac7e89b6
[9]: https://www.mckinsey.com/business-functions/mckinsey-analytics/our-insights/the-data-driven-enterprise-of-2025
[10]: https://cube.dev/blog/dbt-metrics-meet-cube/
[11]: https://github.com/cube-js/cube.js/releases/tag/v0.3.5-alpha.0
[12]: https://github.com/cube-js/cube.js
[13]: https://slack.cube.dev
[14]: https://github.com/cube-js/cube.js/issues/3906
[15]: https://twitter.com/messages/compose?recipient_id=179985200
[16]: http://cube.dev/community-call
[17]: http://cube.dev/careers/

[data-modeling]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/473518f5-dc4f-449e-b598-09029c36b3c4.png
[access-control]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9b11b846-f021-42f8-881e-6dd6807d84c6.png
[caching]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c03dc534-f19d-4550-9534-3f1d649439ba.png
[api-layer]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/211a28aa-ab08-4c8d-b0a0-42850acd6eb6.png

The four essential components of a headless BI tool

What is Headless BI?

Cube has been an [open-source project](https://github.com/cube-js/cube.js) since 2018. We try our best to listen to the community and our users to make it the best analytics API server on the market today.

We really appreciate all the help and sincere dedication our lovely community has done to provide feedback, submit pull requests, and feature ideas to improve Cube even more. We hope the soon-to-be 12,000 stars on GitHub are a representation of our dedication to making our community happy.

In this article, I want to walk you through creating a new Cube App deployment on Cube Cloud and connecting it to your database.

If you prefer watching a video instead, have a look at this short 5 minute tutorial on our YouTube channel.

<div><div style={{position: 'relative', paddingTop: '56.25%'}}><iframe src="https://www.youtube.com/embed/HVZ4ytDEjqc" style={{position: 'absolute', top: 0, left: 0, width: '100%', height: '100%'}} frameBorder="0" allowFullScreen></iframe></div></div>

Before moving on I want to share a bit about the benefits of using Cube Cloud vs. self-hosting Cube yourself.

## Why use Cube Cloud?

We [launched Cube Cloud this October](https://cube.dev/blog/announcing-cube-cloud/) with the vision of making Cube accessible to everyone, no matter the scale and DevOps experience level.

You already know Cube is an open-source project with almost 12,000 stars on GitHub. It's packed full of cool features, such as auto-generating schemas, auto-generating dashboards, a developer playground, and much more. It's easy to work with and developer-friendly.

Self-hosting Cube can come with its own set of complications due and pitfalls. Let me explain.

### Managed Infrastructure

[Cube requires several pieces of infrastructure](https://cube.dev/docs/deployment/production-checklist) to work reliably at scale. To get both high concurrency and high data size you need:

- Compute resources
- Redis
- Cube Store
- An API gateway
- A refresh worker
- A load balancer
- optionally, a VPC configuration

You can mitigate this by using infrastructure as code tools like AWS CloudFormation, Pulumi, or Terraform, and using Kubernetes as a container orchestrator.

![https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png)

### Monitoring and Scaling

Maintaining application up-time is a serious responsibility. To achieve this with self-hosting, you have to make sure that monitoring is configured for Cube and that you can scale Cube up if there's a spike in traffic.

With Cube Cloud, both monitoring and auto-scaling are available out-of-the-box.

You also get query monitoring in the Queries tab in the sidebar. The entire query history is available, including metrics like query result size and time spent, helping you dig into why some queries take longer than others.

![frame_chrome_mac_light_(72).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0844659b-c006-4d75-98b8-7984a82fee81.png)

You can see application performance metrics in the Metrics tab.

![frame_chrome_mac_light_(73).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b6d12fca-de08-46f8-b736-930969e651a4.png)

### Caching

A major problem when scaling Cube apps is optimizing pre-aggregation build times. Similarly, when trying to determine the status of your pre-aggregations; you need to know whether they're built or not and when they were built.

In Cube Cloud, we can check out the Pre-Aggregations tab to see the status of all pre-aggregations in your Cube deployment, including whether they're building or not and even which of the partitions, if any, are being refreshed.

![frame_chrome_mac_light_(74).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7f43d038-6d95-4e91-bd31-90b8a6feca2c.png)

In production, these pre-aggregations are kept up-to-date automatically for you.

### Development Tools

You get GitHub integration with Cube Cloud. Your deployment can watch a GitHub repository for changes and deploy updates. Connect a deployment to GitHub from the Settings page and you're set.

![frame_chrome_mac_light_(75).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3b1bf669-7c95-4746-baa0-951bfe2e6f60.png)

You can also make changes directly in the Cube Cloud console by entering Development Mode.

![frame_chrome_mac_light_(76).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3c74ca7f-58b7-44c4-9e88-d719ab93b325.png)

You get a dedicated Git branch, complete with a personal API URL and separate pre-aggregations.

### Support

If you have paying customers, having paid support is a lifesaver. If something breaks, and you need help fixing it, having someone to call is a lifesaver. Cube Cloud comes with support on the Enterprise plan, so you can ping us whenever you hit a snag!

## Sign Up to Cube Cloud

Signing up is simple and secure because you can also choose to sign up with your GitHub account. Here's how you sign up.

Jump over to the [sign-up page](https://cubecloud.dev/auth/signup) and enter your email.

![frame_chrome_mac_light_(57).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/651bf8cb-fedf-4f21-bac4-6879ff91633b.png)

Once you click `Sign Up`, you'll receive an email from us to set a password.

![frame_chrome_mac_light_(58).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/376a65f0-d828-4c10-a4b3-c56f8a86e5a9.png)

Next, you select a subdomain for your Cube Cloud account.

![frame_chrome_mac_light_(59).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/eab870f8-a430-4048-84d4-38e0c7238e08.png)

With all this out of the way, you can finally start creating Cube Deployments.

![frame_chrome_mac_light_(60).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4c4c987d-dfa1-4591-8464-a3ec76a2f820.png)

Let me explain how to create a deployment.

## Create a Deployment in Cube Cloud

Creating a deployment consists of four main steps.

1. Create Deployment
2. Set Up the Cube App
3. Set Up the Database Connection
4. Generate the Data Schema

Let me break it down into even more detail.

## Step 1 - Create Deployment

The first step of creating a Cube App from scratch in Cube Cloud is to create a deployment.

Once you click the `create deployment` button it'll open up the first step in the deployment creation UI flow.

![frame_chrome_mac_light_(61).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8edd42ca-a308-4cc5-a358-fb6324a28298.png)

Here you choose what name to give your deployment and pick the cloud provider and region. The reason why to choose either AWS or GCP is based on where you're hosting your own infrastructure and apps.

Let me give you an example. Let's say you have a metrics dashboard that needs to consume data on `us-east-1` on AWS. Using the same AWS region to host the Cube deployment as where you are hosting this metrics dashboard will reduce the network delay and latency between Cube and your app.

Which database you're using is also important when choosing a cloud provider and region. If you have data in BigQuery then you should not choose AWS. If you're using AWS Redshift then vice-versa applies.

### Need a Dedicated VPC?

A reason for using a VPC is if you expect high throughput of up to and above millions of requests per day. Using a VPC will give you a dedicated managed infrastructure that is ready to serve such high numbers of users.

Additionally, if your company required security compliance like SOC2, using a VPC is mandatory.

The best part is that a VPC is readily available for you if you need it. All you need to do is to reach out to us and ask for it. Super simple!

![frame_chrome_mac_light_(62).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/972709c6-6911-4c9e-910c-f316120698d7.png)

But, back to business. Let's get back to creating the Cube app deployment. After selecting a cloud provider and region, add a name for your deployment and click next.

![frame_chrome_mac_light_(63).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f2455031-14b9-49ca-b449-eb113a8deda8.png)

## Step 2 - Set Up the Cube App

Next up, the second step in creating a Cube App from scratch in Cube Cloud is to click the `+ create` button.

![frame_chrome_mac_light_(64).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0fe2ae4e-d596-499d-b26b-5f62f5780683.png)

But, before moving on, I want to ask if you already have a Cube app? If you do, please jump over to my co-worker Hassan's [guide on migrating a Cube app](https://cube.dev/blog/migrating-from-self-hosted-to-cube-cloud/) to Cube Cloud.

In this tutorial, I want to stick to creating a Cube App from scratch, so let me continue where I left off.

## Step 3 - Set Up the Database Connection

After selecting the `+ Create` button and proceeding to create a Cube app from scratch, you'll be prompted to select a database type. This lets you choose from dozens of the most popular databases available on the market today.

![frame_chrome_mac_light_(67).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/81442713-0261-424c-bb09-17b1601c10e0.png)

I'll proceed with selecting PostgreSQL to hook up a sample e-commerce database to show you how simple it really is. The values I'll use are:

```json
Hostname
demo-db.cube.dev

Port
5432

Database
ecom

Username
cube

Password
12345
```

In the UI it'll look exactly like the image below.

![frame_chrome_mac_light_(68).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a0c30616-0a8e-4c85-8f79-4f9ba849eb63.png)

Once you click the `Apply` button, Cube Cloud will test if the connection was successful, and proceed to the next step.

If you run into issues here, make sure to allow the Cube Cloud IPs to access your database. This means you need to open up your firewall to these IPs. In the case of AWS, this would mean adding a security group with allowed IPs.

## Step 4 - Generate the Data Schema

Step four in this case consists of generating a data schema. Start by selecting the database tables to generate the data schema from, then hit `generate`.

![frame_chrome_mac_light_(69).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a959643f-4e0f-4f62-9dc9-4d48a0405002.png)

Cube Cloud will generate the data schema and spin up your Cube deployment. With this, you're done. You've created a Cube deployment, configured a database connection, and generated a data schema!

![frame_chrome_mac_light_(70).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4cd2de24-098f-465e-b7ee-dbadd3e82ab9.png)

You're ready to run queries in the Playground.

## Running Queries in Cube Cloud's Playground

By clicking on the Playground in the left nav you'll open the Playground UI.

Here you can test queries by selecting from all the measures and dimensions in the data schema. This lets you test different dashboards and visualizations for when you want to build your own metrics dashboards.

The image below showcases a bar chart with an **`Orders Count` measure**, an `**Orders Status` dimension**, and an `**Orders Created At` time dimension** **grouped by day for the current month**.

![frame_chrome_mac_light_(71).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/56faf279-2d8c-4d98-97d3-cb960ea5413a.png)

You can use the playground as a testing ground for all queries you want to run in your own dashboards. The playground will also display how long a query took, showing you the response time above the chart itself.

That's it! You've finished creating a Cube Deployment on Cube Cloud.

### What About Query Acceleration?

Congrats! You've configured a Cube app in Cube Cloud. You're ready to add pre-aggregations and run the deployment in cluster mode. We have detailed tutorials explaining these concepts for you to read [here](https://cube.dev/blog/cube-cloud-deep-dive-mastering-pre-aggregations/). You can follow along with the tutorial to add pre-aggregations to your data yourself.

We at Cube also hosted a [workshop about pre-aggregations](https://www.youtube.com/watch?v=1_BP3B_wbec) that you can take a look at to learn more about query acceleration.

## Conclusion

Deploying a Cube App on Cube Cloud consists of four steps. First, select the cloud provider, region, and name. Second, select creating a Cube app from scratch, Third, connect a database. Fourth, generate a data schema.

You should keep in mind there are three ways of deploying Cube apps in Cube Cloud. You can create one from scratch, with the Cube CLI, or with the GitHub integration.

If you choose to use Cube Cloud, you'll get the added benefit of getting managed infrastructure that auto-scales to your needs. Cube Cloud also has some added features compared to self-hosting Cube, including query performance analysis, query response times, APM, and pre-aggregations enabled by default.

I hope you end up trying out Cube Cloud because you'll save time and energy by not managing infra and just focusing on building product value.

[Sign up to Cube Cloud here](https://cube.dev/cloud) to try it out for yourself.

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Adnan Rahic

Cube Cloud Deep Dive: Starting a New Cube App

A few weeks ago, we've launched [Cube Cloud](https://cube.dev/cloud). We built Cube Cloud to help address some of the most common issues we heard from our users, and in today's blog post, I'd like to show you how and why to migrate to Cube Cloud. With Cube Cloud, you get convenient developer tools and fully managed infrastructure for Cube configured with best practices in mind.

## Why should I use Cube Cloud?

First, I want to show you some of the responsibilities you take on when running a self-hosted instance of Cube versus letting Cube Cloud manage the infrastructure for you.

As a busy application developer, time spent on setting up and maintaining various parts of infrastructure is often time taken away from implementing application bug fixes and features. Let's go over some of the reasons benefits Cube Cloud has over a self-hosted Cube installation.

## Infrastructure

While Cube is fairly easy to set up, as application developers, we're more interested in managing our application's complexity as opposed to its operational complexity. [Cube requires several pieces of infrastructure](https://cube.dev/docs/deployment/production-checklist) to work reliably at high scales of both concurrency and data size. This includes:

- Compute resources
- Redis
- Cube Store
- An API gateway
- A refresh worker
- A load balancer
- optionally, a VPC configuration for the above resources

![Cube architecture diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png)

With so many moving parts, you might reduce the deployment complexity by defining your cloud infrastructure as code (e.g., using AWS CDK, Pulumi, or Terraform) and employing a workload orchestrator (e.g., Kubernetes or Nomad). If you have multiple developers working on the project, infrastructure as code would also helps provide them with separate development environments as well as a staging environment before production.

### Monitoring and Scaling

At scale, [Murphy's Law](https://en.wikipedia.org/wiki/Murphy%27s_law) rings truer than ever, and achieving and maintaining application uptime becomes a serious responsibility. To achieve this with self-hosting, we must ensure that monitoring is configured for Cube, and that we can scale Cube up if there's a spike in traffic.

With Cube Cloud, both monitoring and autoscaling are available out of the box. From a developer perspective, it feels like a serverless experience: nothing to spin up and you're only charged for usage. Cube Cloud is able to determine when a particular resource (e.g., the number of API instances) needs to be scaled, and no fiddling around with instance types and security groups is required, unlike on other cloud platforms.

Monitoring queries sent to your Cube Cloud cluster is easy, just click Queries from the sidebar and et voilà! The entire query history is available, including important metrics such as query result size and time spent, helping you dig into why some queries take longer than others.

![Recent queries made to Cube Cloud on the Queries page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/16526c40-abf5-4fdb-b356-a3be560cc2de.png)

Application performance metrics can be found on the Metrics page:

![Metrics for Cube on the Cube Cloud Metrics page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e66c97b4-fef5-4908-9810-dc034243379c.png)

![Metrics for database connections on the Cube Cloud Metrics page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4e488685-1492-4ef5-aab8-1bcb86d1c6d3.png)

Scaling is just as simple; just make sure the deployment is in Cluster mode and that's it - Cube Cloud will scale up or down based on how many requests are coming in. This is **[huge](https://c.tenor.com/17pCTXlqaHMAAAAM/donald-trump-yuge.gif)** for applications with growing datasets or variable traffic.

### Caching

Another scale problem is trying to optimise pre-aggregation build times. Trying to determine the status of configured pre-aggregations, whether they were built or not, when they were built etc. is not usually a one-click operation when self-hosting Cube.

In Cube Cloud, we can check out the Pre-Aggregations page to see the status of all pre-aggregations in our Cube deployment, including whether they're building or not and even which of the partitions, if any, are being refreshed. In production, these pre-aggregations are kept up-to-date automatically for you.

![Cube Cloud Pre-Aggregations page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/32a47d34-c1e0-4fbe-b87b-b3e36457784a.png)

### Development Tools

Cube Cloud can watch a GitHub repository for changes and deploy. Connect a deployment to GitHub from the Settings page and you're off to the races.

![Cube Cloud Build and Deploy settings](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a42597dc-98c8-48fa-b6a1-445cfd252c53.png)

You can make one-off changes directly in the Cube Cloud console by going to the Schema page and entering Development Mode:

![Entering Development Mode in Cube Cloud](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b0580a29-f724-4052-a10a-0ff984b18f5f.png)

Developers even get their own personal Git branches, complete with a personal API URL and separate pre-aggregations:

![Personal Git branch in Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c3d12fba-4d30-4682-817e-c090beecb0b0.png)

### Support

For an application with paying customers, having paid support is a lifesaver: if something isn't working properly, developers need to be able to communicate with someone. Cube Cloud comes with support on the Enterprise plan, so you can reach out with an email:

![Email to Cube Cloud support](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d587f935-9e71-4e16-96fb-166c48825be0.png)

## How do I migrate to Cube Cloud?

Now that we've recapped how Cube Cloud compares to self-hosting, let's have a quick look at how we can move a project to Cube Cloud.

### GitHub

Cube Cloud has first-class support for repositories hosted on GitHub; start by signing in to your account (if you don't have one, [sign up here](https://cube.dev/cloud)) and create a new deployment:

![Creating a new deployment on Cube Cloud](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f2786006-a4f2-433f-b77f-109b4704fd2d.png)

Choose the 'Import from a GitHub repository' option and connect your GitHub account to Cube Cloud:

> Using a monorepo? Remember to fill in the "Project Directory (for monorepos)" field so Cube Cloud can locate your Cube project inside the Git repository.

![Connecting Cube Cloud to a GitHub repository](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f5bde911-1072-46ec-839c-cb6c5820aa3e.png)

On the next page, choose your database and enter its credentials:

> Is your database within a VPC? Contact us by clicking on the VPCs button in the top-right.

![Choosing a database for a new Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/054e726a-6ff9-44fe-8cb9-05b3f55a8527.png)

![Configuring database credentials for a new Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/07302615-c905-4ce2-b87e-57c873571cb8.png)

### Git

Not using GitHub to host your repositories? Not a problem; Cube Cloud deployments are de-facto Git repositories. Make sure your deployment is configured to use Git:

![Configuring Cube Cloud as a Git remote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/bbc7dc5d-7f77-4008-a7f2-9b888cb39d5b.png)

Copy the CLI commands and run them in your local copy of the Cube project to add Cube Cloud as an origin. You'll need to generate Git credentials for authentication; you can also do that from the same page.

### Authentication

Cube Cloud supports the same JWK-based authentication that's available in open-source Cube; we just need to set a few environment variables first. Let's go to our deployment's settings page, and add the necessary configuration:

![Configuring JWK authentication in a Cube Clpud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0399eab3-9bcb-4dc9-9782-f56f7a1bb3bc.png)

### Custom domains

Cube Cloud has support for custom domains, too. Add a new `CNAME` record to your DNS pointing at the host part of your deployment's API URL. In the screenshot below, the `CNAME` value would be `strong-elk.gcp.us-central1.cubecloudapp.dev`:

![Configuring custom domain on a Cube Clpud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/85aa1066-37ee-4e42-b20b-50b2c8b1d8b4.png)

Then come back to Cube Cloud and enter your custom domain, then click the 'Add' button.

### Custom NPM packages

> Custom packages are disabled by default. Shoot us an email at support@cube.dev to get it enabled for your account
>

Some Cube projects make use of third-party code from NPM; some projects may use custom drivers for example, or an error logging library such as Sentry. Cube Cloud fully supports installing dependencies from the NPM registry, simply ensure that the required dependencies are listed under the `dependencies` section of the Cube project's `package.json`.

### Multiple databases

Cube Cloud also supports connecting to multiple databases, just as in the open-source version of Cube. When creating a new deployment, simply skip the database connection wizard and go to the Environment Variables tab on the Settings page. There, simply add the required connection details as variables for each additional database:

![Configuring environment variables for multiple databases for a Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/98cf2c85-9d1f-4ea9-a744-0bdc0858c8aa.png)

Make sure the same environment variables are being used in your `cube.js` configuration file:

![Configuring driverFactory() for multiple databases for a Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ed8debbb-f384-44ca-a114-272476643c46.png)

Just like the open-source version of Cube, Cube Cloud can now communicate with however many databases your application needs. 💯

## Wrapping up

In an ideal world, I would get to focus solely on the needs of my users, not my infrastructure. Cube Cloud takes care of the infrastructure and provides me with convenient developer tools, letting me put my efforts into creating business value. Cube Cloud has a [free tier](https://cube.dev/pricing) for development projects and proof-of-concepts, and provides autoscaling, cache storage, and pre-aggregation warm-up along with other features out-of-the-box.

This post aimed to provide some perspectives into why migrating to Cube Cloud is a good investment for your application, as well as demonstrating the ease of moving a self-hosted project to Cube Cloud. It's really easy to deploy and grows with your application, all while minimising your operational responsibilities so you can focus on what matters; your users. Cube Cloud is now open for registration, sign up today by [clicking here](https://cube.dev/cloud).

Hope you all enjoyed reading this as much as I enjoyed writing it. Until next time 👋

Hassan Khan

Migrating from self-hosted to Cube Cloud

Cube Cloud Deep Dive: Mastering Pre-Aggregations

Sometimes in our daily data visualization, we need to merge several similar data sources so that we can manipulate everything as one solid bunch of data. 

For example, we may have an omnichannel shop where online and offline sales are stored in two tables. Or, we may have similar data sources that have only a single common dimension: time. How can we calculate summary metrics for a period? Joining by time is the wrong way because we can’t apply granularity to get the summary data correctly. 

Furthermore, how can we find seasonal patterns from summarized metrics? And how can we get and process data synchronously to track correlations between channels?

Well, the new data blending functionality in version 0.20.0 of Cube.js takes care of all these cases.

>If you’re not familiar with Cube.js yet, please have a look at this [guide](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide). It will show you how to set up the database, start a Cube.js server, and get information about data schemes and analytical cubes.

Please, keep in mind that we used here another dataset:
```js
$ curl http://cube.dev/downloads/ecom2-dump.sql > ecom2-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom2-dump.sql
```

Now let’s dive into the metrics for an example shop and visualize sales by channel and as a summary.

[Here](https://github.com/cube-js/cube.js/tree/master/examples/data-blending) is the full source and [live](https://data-blending-demo.cube.dev/) demo of the example.

I used React to implement this example, but querying in Cube.js works the same way as in Angular, Vue, and vanilla JS.

Our schema has two cubes:

Orders.js
```js
cube(`Orders`, {
 sql: `SELECT * FROM public.orders`,

 measures: {
   count: {
     type: `count`,
   },
 },

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

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

and OrdersOffline.js
```js
cube(`OrdersOffline`, {
 sql: `SELECT * FROM public.orders_offline`,

 measures: {
   count: {
     type: `count`,
   },
 },

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

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

The existence of at least one-time dimension in each cube is a core requirement for merging the data properly. In other words, the data are suitable for blending only if you can present the data on a timeline. Sales statistics or two lists of users that both have an account created date are appropriate datasets for data blending. However, two lists of countries with only a population value can’t be united this way.

## A Special Query Format for Data Blending
A simple and minimalistic approach is to apply data blending to a query object when we retrieve data from our frontend application. The schema and backend don’t need to be changed.
```js
const { resultSet } = useCubeQuery([
   {
     measures: ['Orders.count'],
     timeDimensions: [
       {
         dimension: 'Orders.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
   {
     measures: ['OrdersOffline.count'],
     timeDimensions: [
       {
         dimension: 'OrdersOffline.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
 ]);
```

The blended data is an array of query objects, so we just combine regular Cube.js query objects into an array with a defined dateRange and [granularity](https://cube.dev/docs/query-format#time-dimensions-format?utm_source=blog&utm_medium=post&utm_campaign=data-blending).

As a result, Cube.js returns an array of regular resultSet objects.

![data-blending.png](https://media.graphcms.com/On8dZ6BKRPOx4gyhXels)

But what if we want to do calculations over blended data sources or create custom metrics? For example, how can we define ratios calculated using data from two sources? How can we apply formulas that depend on data from multiple sources?

In this case, we can use another data blending function. We start by setting up a new cube.

## Data Blending Implementation within a Schema

Let’s create AllSales.js inside the schema folder:

```js
cube(`AllSales`, {
 sql: `
 select id, created_at, 'OrdersOffline' row_type from ${OrdersOffline.sql()}
 UNION ALL
 select id, created_at, 'Orders' row_type from ${Orders.sql()}
 `,

 measures: {
   count: {
     sql: `id`,
     type: `count`,
   },

   onlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'Orders'` }],
   },

   offlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'OrdersOffline'` }],
   },

   onlineRevenuePercentage: {
     sql: `(${onlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   offlineRevenuePercentage: {
     sql: `(${offlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   commonPercentage: {
     sql: `${onlineRevenuePercentage} + ${offlineRevenuePercentage}`,
     type: `number`,
   },
 },

 dimensions: {
   createdAt: {
     sql: `created_at`,
     type: `time`,
   },

   revenueType: {
     sql: `row_type`,
     type: `string`,
   },
 },
});

```

Here we’ve applied a UNION statement to blend data from two tables, but it’s possible to combine even more.

Using this approach, we can easily define and combine values from several blended data sources. We can even use [calculated values](https://cube.dev/docs/measures#calculated-measures?utm_source=blog&utm_medium=post&utm_campaign=data-blending) and SQL formulas.

We can retrieve data from frontend applications and process the results in the usual way:
```js
const { resultSet: result } = useCubeQuery({
   measures: [
     'AllSales.onlineRevenuePercentage',
     'AllSales.offlineRevenuePercentage',
     'AllSales.commonPercentage',
   ],
   timeDimensions: [
     {
       dimension: 'AllSales.createdAt',
       dateRange: ['2022-01-01', '2022-12-31'],
       granularity: 'month',
     },
   ],
 });
```
![data-blending2.png](https://media.graphcms.com/zZxdzysVTjmRlMoSXjVm)

## Conclusion
If we need to visualize data from several sources and apply time granularity to the data, then with data blending, we need to write less code and we can simplify the application logic.

We looked at two ways to implement data blending:
We retrieved data as an array of query objects from a frontend application. This is simple to do and the schema doesn’t need to be modified. We can even merge data from several databases. Furthermore, we can retrieve and process independent data synchronously so that we can visualize it on a timeline.
We blended data by defining a special cube in a schema. This approach allows us to apply aggregate functions to all sources simultaneously and we can define calculated values.

We hope this tutorial will help you to write less code and help to build more creative visualizations. If you have any questions or feedback or you want to share your projects, please use our [Slack](https://slack.cube.dev/) channel or mention us at [Twitter](https://twitter.com/thecubejs).

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

Yulia Kotova

Introducing Data Blending API in Cube

Comparing a single metric over different time periods is a quite common procedure in data analytics. You can evaluate your company’s performance by comparing this year’s revenue with the previous year’s. You can understand which are the low and high sales months in your market, and furthermore, you can predict customer behavior and figure out seasonal patterns.

Analytical systems like Google Analytics, Youtube Studio, and others provide tools for comparing a single metric over different periods of time.

![screen_compare.png](https://media.graphcms.com/HVU1KJcBSPmoRJEoO5sb)

Let’s see how you can implement such analytical visualization with Cube.js.

[Here](https://compare-date-range-demo.cube.dev/) you can see the live demo and the full [source code](https://github.com/cube-js/cube.js/tree/master/examples/compare-date-range) for this example. 

We will explore a sample e-commerce dataset and compare the numbers of orders in the three most recent years. To do this, we need to run a Cube.js server and a simple dashboard application. 

>If you’re not yet familiar with Cube.js, please follow our [guide](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide) to setting up the database, starting a Cube.js server, and getting information about data schemes and analytical cubes.

Now, let’s create the `Orders` cube inside the `schema` folder with the following code:

```js
cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  measures: {
    count: {
      type: `count`,
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});
```

Here the [`count`](https://cube.dev/docs/types-and-formats#measures-types-count?utm_source=blog&utm_medium=post&utm_campaign=compare-date-range) measure is used to calculate the number of orders. The `createdAt` field is used to retrieve an order’s timestamp.

In the next sections, we will implement the example with React, but you can use the same approach with Vue, Angular, and vanilla JS.

## Multiple Queries for a Single Chart
Here’s how we retrieve the data for each year and parse result sets into the format that is accepted by our charting library:
```js
import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';
import * as moment from 'moment';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result22 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2022-01-01', '2022-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result21 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2021-01-01', '2021-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result20 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2020-01-01', '2020-12-31'],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    const parseResultSet = (resultSet) => {
      return {
        name: moment(
          resultSet.tablePivot()[0]['Orders.createdAt.month']
        ).format('YYYY'),
        data: resultSet
          .tablePivot()
          .map((item) => parseInt(item['Orders.count'])),
      };
    };

    const temp = [
      result22 ? parseResultSet(result22) : [],
      result21 ? parseResultSet(result21) : [],
      result20 ? parseResultSet(result20) : [],
    ];

    setData(temp);
  }, [result22, result21, result20]);

  return <Line data={data} title={'multiple queries'} />;
};
```
Once the data is prepared, we send it to the `<Line />` rendering component and, behold, we create an informative chart:

![compare-date-range visualization](https://media.graphcms.com/Sb7W0PirRte3EO0TmsiK)

The procedure is quite simple, isn’t it? However, as you can see, the code is a little bit messy and repetitive. More importantly, it’s not easy to adapt the code to a different amount of years or even to adjust it dynamically in response to users’ actions or application states.

How do we solve this problem? Let’s try another approach.

## A Single Query for a Single Chart

With the 0.20.0 release of Cube.js, it’s possible to retrieve data for different time periods using a single query. 

The [`compareDateRange`](https://cube.dev/docs/query-format#time-dimensions-format?utm_source=blog&utm_medium=post&utm_campaign=compare-date-range) property accepts an array of two or more different time periods and returns data for all of them in one request. 

We can rewrite our previous code in a simpler way:
```js
import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        compareDateRange: [
          ['2022-01-01', '2022-12-31'],
          ['2021-01-01', '2021-12-31'],
          ['2020-01-01', '2020-12-31'],
        ],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    if (result) {
      const temp = result.series().map((data) => {
        return {
          name: data.key.substring(0, 4),
          data: data.series.map((item) => item.value),
        };
      });
      setData(temp);
    }
  }, [result]);

  return <Line data={data} title={'the single query'} />;
};
```

You can see that this code is much shorter. Furthermore, now we’re able to set the time periods via `compareDateRange` dynamically while keeping our code clean.

Please note that we didn’t make any changes in the `<Line />` rendering component, so the chart will look essentially the same.

I hope that this will help you create useful data visualizations using substantially less code.

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

Comparing Data over Different Time Periods

Since the release of drill down support in version 0.19.23, you can build interfaces to let users dive deeper into visualizations and data tables. The common use case for this feature is to let users click on a spike on the chart to find out what caused it, or to inspect a particular step of the funnel—who has converted and who has not.

In this blog post, I'll show you how to define drill downs in the data schema and build an interface to let users explore the underlying chart's data. If you're just starting with Cube, I highly recommend beginning with this [Cube 101 tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) and then coming back here. Also, if you have any questions, don't hesitate to ask them in our [Slack community](http://slack.cube.dev/).

You can check [the online demo of the example here](https://drill-downs-demo.cube.dev/), and [the source code is available on GitHub.](https://github.com/cube-js/cube.js/tree/master/examples/drill-downs)

![drill-down-demo.gif](https://media.graphcms.com/KzGwEwQLSEuDtfHiJSZe)

Let's start hacking! 💻

## Defining a Drill Down in the Data Schema

Let's start by setting up a new project with Cube and configuring drill down support in the data schema. We'll use [PostgresQL](https://www.postgresql.org/) and our example e-commerce dataset for this tutorial. You can download and import it by running the following commands.

```bash
$ curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom-dump.sql
```

Next, install the Cube.js CLI if you don't have it already, and create a new project.

```bash
$ npm -g install cubejs-cli
$ cubejs create drill-downs -d postgres
```

Make sure you have the following credentials in the .env file.

```bash
CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=ecom
```

Now, we're ready to launch the Cube.js server and navigate to the playground running at [http://localhost:4000](http://localhost:4000/).

```bash
$ npm run dev
```

Once you're in the playground, navigate to the Schema tab. Then select the **orders** and **users** tables and click Generate Schema, as in the screenshot below.

![Screen_Shot_2020-06-19_at_7.42.27_PM.png](https://media.graphcms.com/3zOXy0SRyWpBUPHNJEQI)

This will generate a basic data schema for users and orders tables, which already includes the `drillMembers` property on the `count` measure. The `drillMembers` property contains a list of dimensions that will be used to show the underlying data when drilling into that measure.

Let's take a closer look at the `Orders` cube and its `count` measure.

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

It already has the basic dimensions listed in the `drillMembers` property: `id` and `createdAt`. We can add additional dimensions to that list. We also can reference dimensions from joined cubes—in our case, from Users.

Let's add more dimensions to the `drillMembers` property.

```js
measures: {
  count: {
    type: `count`,
    drillMembers: [id, status, Users.firstName, Users.city]
  }
}
```

That's all we need in the data schema to build our drill down. On the frontend, we're going to make a bar chart to display orders over time. When a user clicks on the bar, our app will display the table inside the modal window, with details about the orders in that bar.

## Building the Drill Down UI

We'll use Cube.js templates to generate a frontend app. Navigate to the Dashboard App tab and select the Material-UI React Dashboard. It will take several minutes to set up the Dashboard App and install all the dependencies inside the `dashboard-app` folder in your project.

Please note: although we use React in this example, you can build the same drill down in [Vue.js](https://cube.dev/docs/@cubejs-client-vue), [Angular](https://cube.dev/docs/@cubejs-client-ngx), or [Vanilla JS](https://cube.dev/docs/@cubejs-client-core).

The first step is to render a bar chart. We're going to plot the count of orders over time, grouped by the status. Eventually, we want to let users click on a specific group and day to explore the underlying orders—e.g., orders created on June 20 and already shipped.

Let's create a `dashboard-app/src/DrillDownExample.js` file with the following content.

```jsx
import React, { useEffect, useState } from "react";
import { useCubeQuery } from "@cubejs-client/react";
import {
  BarChart,
  Bar,
  ResponsiveContainer,
  XAxis,
  YAxis,
  CartesianGrid,
  Tooltip as RechartsTooltip,
  Legend
} from "recharts";

const colors = ["#FF6492", "#141446", "#7A77FF"];

const query = {
  measures: ["Orders.count"],
  dimensions: ["Orders.status"],
  timeDimensions: [{
    dimension: "Orders.createdAt",
    granularity: "day",
    dateRange: "last 30 days"
  }]
};

const DrillDownExample = () => {
  const { resultSet } = useCubeQuery(query);

  if (!resultSet) {
    return <p>Loading...</p>;
  }

  return (
    <>
      <ResponsiveContainer width="100%" height={300}>
        <BarChart data={resultSet.chartPivot()}>
          <CartesianGrid strokeDasharray="3 3" />
          <XAxis dataKey="x" />
          <YAxis />
          <RechartsTooltip />
          <Legend />
          {resultSet.seriesNames().map(({ key }, index) => {
            return (
              <Bar
                key={key}
                dataKey={key}
                stackId="a"
                fill={colors[index]}
              />
            );
          })}
        </BarChart>
      </ResponsiveContainer>
    </>
  );
}

export default DrillDownExample;
```

The code snippet above is pretty straightforward. First, we load data with the `useCubeQuery` hook and render it later with Recharts. Next, let's add some interactivity and let users click on the bars!

To be able to show the underlying data, we first need to figure out where the user clicked on the chart, and then construct a query to Cube.js to load that data. The user can click on any day in our bar chart and on any status of the order within that day. To describe that location, Cube.js uses two variables: `xValues` and `yValues`.

For example, the following values mean that the user wants to explore processing orders on June 6:

```jsx
{
  xValues: ["2020-06-06T00:00:00.000"],
  yValues: ["processing", "Orders.count"]
}
```

To generate a query that returns data for a drill down table, we need to use the [ResultSet#drillDown()](https://cube.dev/docs/@cubejs-client-core#result-set-drill-down) method. If we run it with the above values, like this:

```js
resultSet.drillDown(
  {
    xValues: ["2020-06-06T00:00:00.000"],
    yValues: ["processing", "Orders.count"]
  }
)
```

it will return the query, which has all the dimensions from the `drillMembers` property in the data schema, as well as all required filters to specifically load processing orders on June 6.

```json
{
  "measures": [],
  "dimensions": [
    "Orders.id",
    "Orders.status",
    "Users.firstName",
    "Users.city"
  ],
  "filters": [
    {
      "dimension": "Orders.count",
      "operator": "measureFilter"
    },
    {
      "member": "Orders.status",
      "operator": "equals",
      "values": [
        "processing"
       ]
    }
  ],
  "timeDimensions": [
    {
      "dimension": "Orders.createdAt",
      "dateRange": [
        "2020-06-06T00:00:00.000",
        "2020-06-06T23:59:59.999"
      ]
    }
  ]
}
```

Once we have the drill down query, we can use it to load data from the Cube.js API.

To get the values for `xValues` and `yValues` properties, we will use the [ResultSet#chartPivot()](https://cube.dev/docs/@cubejs-client-core#result-set-chart-pivot) and [ResultSet#seriesNames()](https://cube.dev/docs/@cubejs-client-core/#result-set-series-names) methods. [chartPivot()](https://cube.dev/docs/@cubejs-client-core#result-set-chart-pivot) returns `xValues` for every data row, and [seriesNames(](https://cube.dev/docs/@cubejs-client-core#result-set-series-names)) returns `yValues` per series. We're going to use these methods to pass `xValues` and `yValues` to the Recharts to make sure we have them in the `onClick` handler.

First, let's create a click handler, which will accept `xValues` and `yValues`, generate a drill down query, and store it in the state.

```js
const [drillDownQuery, setDrillDownQuery] = useState();
const handleBarClick = (event, yValues) => {
  if (event.xValues != null) {
    setDrillDownQuery(
      resultSet.drillDown(
        {
          xValues: event.xValues,
          yValues
        }
      )
    );
  }
};
```

Now we need to make sure we pass both `xValues` and `yValues` to the `handleBarClick`. Since we pass `resultSet.chartPivot()` to the Recharts `<BarChart />` component as a `data` property, the `xValues` will be available as the property on the `event` object in the `onClick` callback. To pass `yValues`, we need to make the following changes:

```diff
-{resultSet.seriesNames().map(({ key }, index) => {
+{resultSet.seriesNames().map(({ key, yValues }, index) => {
  return (
    <Bar
      key={key}
      dataKey={key}
      stackId="a"
      fill={colors[index]}
+     onClick={event => handleBarClick(event, yValues)}
    />
  );
})}
```

Now, as we have `drillDownQuery` in the state, we can query it in our component.

```js
const drillDownResponse = useCubeQuery(
  drillDownQuery,
  {
    skip: !drillDownQuery
  }
);
```

Later, you can use `drillDownResponse` to render the drill down data however you want. In our example, we use Material-UI Kit and render it as a table within the modal window.

![Screen_Shot_2020-06-22_at_8.12.55_PM.png](https://media.graphcms.com/EDmUly6kQNGqtm5cKGMF)

I hope you found this tutorial helpful for adding interactive drill downs to your application! You can check the online demo of the example here, and the source code is available on GitHub.

If you have any questions, please don't hesitate to reach out to me in [Cube.js Slack community](http://slack.cube.dev/).