
I’m a software engineer at [Ternary](https://ternary.app/), a company that provides a FinOps platform to serve Google Cloud (GCP) customers' unique needs. Ternary’s platform empowers cloud engineers, IT finance, and business teams to collaborate more effectively on public cloud cost optimization. The company is in high-growth mode on all fronts, and its platform now supports many of the largest GCP customers. These customers bring the challenge of providing a SaaS platform at scale. 
 
Below, I show how Ternary has solved the challenge of managing the large volume of cost-related data these customers produce, utilizing that data to forecast and optimize spend. I explain the so-called 3-query approach to the analysis of large multidimensional datasets that we use to identify the most interesting data for our users and provide real-world code snippets in the second half of this article.

## Multidimensional analysis of cost datasets

![Ternary dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-dashboard.png)

Ternary’s platform features the ability to break down costs by projects and other dimensions across a time series. For users that have many values in a given dimension (e.g., a few thousand GCP projects), or for those who want to analyze unique combinations of dimensions, we would have to load and analyze substantial result sets on the front-end, not to mention that we would also frequently run into issues with Cube’s response limit of [50,000 rows](https://cube.dev/docs/query-format#query-properties). For example, let’s say I want to look at total spend for every project across the last 30 days while breaking that spend by one or more additional dimensions. Chances are that the result set would contain thousands of rows and if I exceed the limit of 50K rows, I will generate an incomplete dataset and won’t be able to calculate my total costs. 

Ternary is a cost-management platform, so it’s critical that we present complete, accurate data. To enable our users to perform multidimensional analysis of vast volumes of cost data, we asked ourselves what data our users most want to see.

Ternary displays most of its data in line or bar graphs, which are useful only with data points up to a certain amount. A line graph with 50 increments on the y-axis can be hard to read, especially if some y-axis values are much greater than others. At Ternary, we hypothesized that many of our key users would want to focus on what we call, informally, their “most interesting” data points, which we defined as their highest spend in a 30-day period, as well as the general shape of their expenses during that time. It’s debatable what “most interesting” is in every case. But for us, it is usually the groupings that account for the most spend.

Now that we have assumed what our users are really interested in, we imposed limits on our queries so that the user sees only that data. This focus makes our charts more readable and removes the concern about maxing out the Cube response limit.

In limiting the data, we inevitably lose some portion of the entire picture. The top 10 “most interesting” dimension values will likely not account for all of a user’s costs. If there were as many as 100 dimension values, our method would expose but a fraction of these. 

So, to account for any missing data, we roll all the “less interesting” data points into an “other” column. By including this “other” column, we fulfill our above-mentioned mandate to provide complete and accurate data, reflecting total spend and ensuring the accuracy of any subsequent calculations. 

## The 3-query approach to querying top N rows

In order to expose the “most important” data and generate the “other” column, we need to fire off at least three queries. Let’s go through the high-level query breakdown here.

**Scenario:** As a Ternary user, I want to see how much my team has spent day by day, broken down by project ID and category, over the last 30 days.

**Query 1:** Request all of the cost totals day by day, over the last 30 days, with desired query filters. At this point, we will not include dimensions. Note that the “cost” measure in this example is of type “sum.”

![Query 1 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-1.png)

**Query 2:** Request the top N number of the “most interesting” combinations of projects and categories. In this case, we will find out which project-category pairs had the highest overall spend in the last month. For this scenario, let’s assume N is 3. 

![Query 2 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-2.png)

Now that we have a list of the top three combinations, we’ll generate query filters. Note that the new filters must use [Boolean logical operators](https://cube.dev/docs/query-format#boolean-logical-operators) to accurately filter for specific project ID-category storage groupings.

**Query 3:** Request cost day by day over the last 30 days, broken down by project-category pairs, using the filters generated from query two in addition to any user-selected filters. (Note that we will include additional dimensions now.)

![Query 3 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-3.png)

This query would return 3 * 30 = 90 data points. However, there is a discrepancy between the total spend of those three projects and the total overall cost on any given day. To account for this difference, we take the totals from query 1 and subtract the values from query 3 to generate an “other” column, which we also display for the user.

![Final chart](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-final-chart.png)

By this method, we generate a complete overview, identifying the most expensive projects while preserving the total cost using the “other” column. While the overview does not display every project and some specificity is lost, all the data is reflected in one form or another. 

Keep in mind that N can be much larger than three; it should change according to the user’s goals. Lastly, it’s important to acknowledge that while this solution is a “three-step process,” the actual number of queries being run will be 1 + 2m, where m is the number of measures that the user wants to see. This is because we want to gather a separate set of filters for every measure requested. 

For example, say we wanted to see separately the three projects responsible for the most spend and the three projects responsible for the most usage. While we can use query 1 to request all of the selected measure totals across the time series, we will need to fire off queries 2 and 3 for both cost and usage separately. Otherwise, we will muddle the results with an inconsistent number of data points for cost and usage.

To try this out yourself, create the following Cube schema:

```js
cube(`CostByProjectAndCategory`, {
  title: "CostByProjectAndCategory",
  sql: `
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 14 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 15 AS cost, "production" AS project, "Network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 16 AS cost, "production" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 12 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 12 AS cost, "staging" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 13 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 13 AS cost, "staging" AS project,"network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 8 AS cost, "dev" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 9 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 8 AS cost, "dev" AS project,  "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 8 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 9 AS cost, "dev" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 8 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 9 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 7 AS cost, "test" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 6 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 7 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 6 AS cost, "test" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 4 AS cost, "test" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 6 AS cost, "test" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 6 AS cost, "test" AS project, "analytics" AS category
  `,

  measures: {
    cost: {
      sql: "cost",
      type: "sum",
    },
  },

  dimensions: {
    timestamp: {
      sql: "timestamp",
      type: "time",
    },
    project: {
      sql: "project",
      type: "string",
    },
    category: {
      sql: "category",
      type: "string",
    },
  },
});
 
```

The three queries you’ll need to run are shown below, in JSON query format:

**Query 1**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ]
}
```

**Result**

![Query 1 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-1-result.png)

**Query 2**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp"
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "limit": 2
}
```

**Result**

![Query 2 result 1](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-2-result.png)

**Query 3**
```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "filters": [ {
    "or": [
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["production"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["compute"]
          }
        ]
      },
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["staging"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["databases"]
          }
        ]
      }
    ]
  } ],
  "limit": 50000
}
```

**Result**

![Query 3 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-3-result.png)

### Performance

Because including a large number of dimensions complicates the Boolean logical filters applied, and because including a large number of measures increases the number of queries to which we must apply that complicated logic, it’s crucial to consider actual use cases and their resulting user impact. To test Ternary’s new query format, we used the Ternary Reporting Engine: a feature of our platform that allows users to query their data flexibly, by selecting different dimensions, measures, and filters. Here, we ran a number of queries with multiple measures, dimensions, and filters, to get a sense of how complex our queries could be without significantly compromising the app’s performance. We found that, using this 3-query approach, the duration of a request where N = 20 was the same as that of using one query to gather all the data, with the added benefit that our results stayed within the 50K-row limit.

### Cost

Given that Ternary is a cost-management platform, we recognize the importance of considering costs when making changes such as this one. As stated before, the number of Cube queries needed to fulfill a single request on behalf of any given customer would increase, from 1 to 1 + 2m. It follows that the cost to implement a feature in this way could also increase by a similar factor if, for example, using [BigQuery’s on-demand pricing](https://cloud.google.com/bigquery/pricing#on_demand_pricing) like we do, where the amount you are charged is proportional to the amount of data you read. Even if we were to increase the amount we spend, serving this data by a factor of, on average, three, five, or even ten, we determined that it would be a reasonable price to pay for the benefits, including showing customer data that conveys the full picture instead of truncating it as we previously had. 

Each use case is different and should be considered individually. If all queries are accelerated with [pre-aggregations](https://cube.dev/docs/caching#pre-aggregations) or if Cube points toward a Postgres instance, and if the query volume is relatively low, cost considerations may not be as relevant.


Katie Law

Inspiring stories about companies building analytical solutions for end-users with Cube.

Company news and product release notes for prominent feature launches.

News & Product Updates

Community news, accomplishments, and programs.

Community Updates

Mastering Cube by digging into advanced concepts and dissecting core features.

Deep Dives

User Stories

Upcoming workshops and community calls with Cube creators and community members.

Events

Company news, product updates, and engineering deep dives.

All Things Cube

Architecturing data storage and solving common analytical tasks with SQL.

Platforms and tools for business intelligence bringing data insight to internal users.

BI & Data Exploration

Popular cloud-based scalable data stores optimized for BI and analytics.

Cloud Data Warehouses (CDW)

Widespread relational and mostly row-based data stores.

Transactional Databases (OLTP)

Mostly column-based data stores optimized for business intelligence analysis.

Analytical Databases (OLAP)

Data stores optimized for processing timestamps with associated values.

Time Series Databases (TSDB)

Data stores built for stream processing and building real-time applications.

Streaming Databases (RTDB)

Key-value and document-based data stores.

NoSQL & Document Databases

Data processing tools used for copying data from one or more sources into a destination.

Extract, Load, Transform (ETL)

Data Modeling

Optimizing query latency and concurrency for better UX and lesser time-to-insight.

Performance

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

Data Engineering

How Ternary zeroes in on most interesting data.

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


[KazanExpress](https://kazanexpress.ru/) is a marketplace in Russia with free one-day delivery, regardless of the order value and region. We provide over three million products in over 20 product categories including clothing, electronics, and groceries.

Our marketplace consists of 4 offerings:

1. **B2C** — everything related to the happiness of shoppers and the process of buying goods in the marketplace. We have more than a million visitors daily, and we are loved by more than four million shoppers.
2. **B2B** — focuses on the experience of the seller and the process of selling goods in the marketplace. In recent years, more than 10,000 sellers became our partners and we were able to greatly improve their business.
3. **WMS** — a warehouse management system that is responsible for a key part of our business — efficient packaging and fast delivery that allows us to deliver orders to more than 115 cities in Russia.
4. **ERP** — a single system for automating and managing internal business processes of the marketplace. It helps build simple and effective links between different departments.

We were looking for an analytics solution—both for us and our sellers using the B2B product of KazanExpress—that can provide insight into our performance against our business goals. For KazanExpress, this includes how quickly new sellers can onboard to our marketplace, the effectiveness of existing sellers, our revenue stream, and the number of purchases on KazanExpress.  

Our sellers needed insight on things like types of customer search queries, demands for product categories, product revenue forecast to help plan product availability in the warehouse. In addition, they also needed to understand what impact activities such as price changes, promotions, adding new SKUs, and even changing product photos had on their revenue.

## Why we chose Cube at KazanExpress

Our analytics tool needed to be capable of doing aggregations from several types of databases (e.g. ClickHouse, Postgres, etc.) and easy to integrate with the KazanExpress infrastructure. We looked at three options and you'll see the pros and cons for each below:

<table>
  <thead>
    <tr>
      <th width="15%"></th>
      <th width="42.5%">Pros</th>
      <th width="42.5%">Cons</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td><b>Yandex DataLens</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Predefined set of chart types, customizable even by non-technical people</li>
          <li>Support for materialization and caching</li>
          <li>Supported by Yandex</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>No pre-aggregations mechanism</li>
          <li>Large materialized views are not possible <b>(a major roadblock)</b></li>
          <li>Row-level security (RLS) doesn't support integration with 3rd party applications incl. our security layer <b>(a major roadblock)</b></li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>In-house solution with ETL and ClickHouse data marts</b></td>
      <td valign="top">
        <ul>
          <li>We have full control of the architecture and security layer</li>
          <li>Easy to integrate with our existing infrastructure</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>
            Huge amount of developer resources required to implement
            ClickHouse data marts, interaction with security middleware, etc.
          </li>
          <li>frontend developers required to build charts</li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>Cube</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Ability to build OLAP cubes on top of the raw data layer without data volume limitations</li>
          <li>Simple API and integrations with popular frontend charting libraries</li>
          <li>Support for <a href="https://cube.dev/docs/schema/reference/pre-aggregations">pre-aggregations</a></li>
          <li>Support for <a href="https://cube.dev/docs/multitenancy-setup">multitenancy</a></li>
          <li>Support for <a href="https://cube.dev/docs/security#custom-authentication">custom authentication</a></li>
          <li>Row-level Security (RLS) and <a href="https://cube.dev/docs/security#generating-json-web-tokens-jwt">JSON Web Tokens (JWT)</a></li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>frontend developers required to build charts</li>
          <li>
            Lack of infrastructure tools in Cube (e.g.
            instrumentation/monitoring data, official Helm chart/Kubernetes
            deployment guide)
          </li>
        </ul>
      </td>
    </tr>
  </tbody>
</table>

Obviously, Cube had a lot of pros and we decided to pursue the Cube route in July 2021. Below you will see one of our seller’s analytics dashboards that are powered by Cube.

![Seller analytics dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9ed4d541-db24-4fde-9950-1f35959ffbfd.png)

## Implementing Cube at KazanExpress and our workflow

Our setup follows the recommended Cube deployment architecture that consists of Cube API, Cube Refresh Worker, external Redis for caching, and Cube Store cluster for working with pre-aggregations. Currently, Cube is connected to three Postgres databases and ClickHouse and you can see our architecture diagram below.

![Architecture Diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9c78020d-6f04-4857-ab22-49bbc0b88914.png)

In the Cube API, we implemented integration with our internal authorization provider to grant access to data in Cube using the same token used throughout our application.

Currently, we have 3 staging deployments in our K8s cluster. We templated the Cube platform using the Helm chart to simplify the deployment process and make it reproducible/extendable/observable. Here are our staging deployments:

- **Feature.** A feature deployment like in Git flow. This is the playground where we can make any experiments with Cube or develop new features. And at the same time, we can test visualization on the frontend.
- **Development.** A place where all approved features are located and we might build releases with required load-testing, regression testing, etc.
- **Production.** A working copy of the platform that we are confident with and available to our sellers.

Each deployment has a full copy of the Cube platform that consists of Cube API, Cube Refresh Worker, Cube Store with its own bucket on S3, and Redis for caching. This workflow gives us the capability to test our hypotheses easily and instantly deliver features to our customers.

We had three people (a frontend developer, a backend developer, and a designer) that were involved in the implementation of Cube at KazanExpress that took about 3 1/2 weeks. We also implemented Cube in three phases:

- **Phase 1.** Deployment of the Cube API into our Kubernetes cluster.
- **Phase 2.** Setting up the Cube API with a refresh worker and pre-aggregations.
- **Phase 3.** Setting up Cube Store.

## Looking ahead

We look forward to continuing to expand our analytics offering with Cube to our sellers. This includes adding more Postgres data sources and providing anonymous statistics on shoppers' search queries, buyers' demographics/interests, product category information on impressions/views/sellers, etc.

## Interested in joining KazanExpress' success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.


Oleg Taizov

Open-source RDBMs emphasizing extensibility and SQL compliance.

PostgreSQL

MySQL and PostgreSQL-compatible relational database built for AWS.

Amazon Aurora

Open-source column-oriented data store optimized for performance.

ClickHouse

How KazanExpress is using Cube for analytics in their marketplace offerings


[Gadsme](https://www.gadsme.com/) (literally Ads in Game) was founded in 2019 to provide an advertising platform designed for the gaming and e-sports industries. We are engaged in the serving of non-intrusive advertising embedded within scenes of video games.

Gadsme work with major video game studios and serve millions of ads every day. We keep innovating to bring new brand experiences to this area.

![Gadsme stock photo](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/66b39395-d91d-469c-a29d-7af29f21f708.jpeg)

## How Gadsme started using Cube

The Gadsme team first heard of Cube in 2019 after reading a post on Hacker News. At the time, we were focusing more on developing our cross-platform advertising SDK. When it came to building our analytics solution, Cube was the perfect tool to ease iterations around how data insights can be exposed efficiently to our clients. Now, all our dashboards (e.g., for ad requests, impression, fill rate, [eCPM](https://en.wikipedia.org/wiki/Cost_per_mille#Effective_cost_per_mille), etc.) and reporting API use Cube under the hood.

As an AdTech company, Gadsme collects millions of events every day. All those events are stored in BigQuery. With Cube and its built-in pre-aggregations feature, we were able to expose meaningful data with speed and maintain complete control of analytics costs.

## How Cube was implemented at Gadsme and our contributions to the Cube community

Our team used pre-aggregations since day one. We were first using Postgres as an external [pre-aggregations database](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage) and now we have been using Cube Store in production since July 2021. This last move to Cube Store brought a noticeable speed-up in the loading times of data and graphs for our client dashboards.

We run Cube on Kubernetes, and we spent some time improving the deployment automation of Cube using Helm Charts. As other members of the community were asking for it, we decided to [make a contribution](https://github.com/cube-js/cube.js/pull/3277), and our Helm Charts are available for everyone in the [example folder of Cube](https://github.com/cube-js/cube.js/tree/master/examples/helm-charts). This was the first major contribution from Gadsme to the Cube project, and contributing back if possible to open source community is a key part of our values.

We also use GraphQL to build all our APIs. We decided not to expose Cube directly and preferred building a GraphQL proxy on top of Cube's [REST API](https://cube.dev/docs/rest-api) (see our architecture diagram below).

![Architecture Diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/63a6a07f-4d68-4925-a0d9-1c1df168e888.png)

In November, we started a new [contribution](https://github.com/cube-js/cube.js/pull/3555) to the Cube project to bring the first official support of a GraphQL API. This new API is available since Cube [v0.28.58](https://github.com/cube-js/cube.js/releases/tag/v0.28.58) and supports the following features:

* API autogeneration based on Cube definitions
* Directives support
* Compatibility with the authorization layer (e.g., [JSON Web Tokens](https://cube.dev/docs/security/context), [queryRewrite](https://cube.dev/docs/config#query-rewrite), [multitenancy](https://cube.dev/docs/multitenancy-setup), etc.)

We also had the opportunity to discuss our GraphQL contribution during the [November community call](https://www.youtube.com/watch?v=EQ5Wl4yn71c&list=PLtdXl_QTQjpb1dHZCM09qKTsgvgqjSvc9), and we’d welcome your feedback on our contributions not only for GraphQL, but also for Helm Charts. 

## Interested in joining Gadsme's success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.


Luc Vauvillier

Query language for APIs with universal support in modern front-end frameworks.

GraphQL

Convenient APIs to access and consume analytics on the frontend.

APIs

Serverless data warehouse of Google Cloud Platform.

Petabyte-scale data warehouse of Amazon Web Services.

Amazon Redshift

Google BigQuery

Cloud-agnostic data platform supporting AWS, GCP, and Azure.

Snowflake

How Gadsme is using Cube for analytics on their in-game advertising platform


[ShopBack](https://corporate.shopback.com/) is the largest shopping rewards and discovery platform in Asia-Pacific, with a presence in Australia, Indonesia, South Korea, the Philippines, Singapore, Malaysia, Taiwan, Thailand, and Vietnam.

From a small team of six in 2014, we now house over six hundred employees across the region, having scaled to nine countries to provide a more rewarding shopping experience for over 28 million users. Our platform enables users to make better purchase decisions while delivering performance-based marketing with high and measurable return on investment to merchants.

ShopBack continues to innovate, evolve, and create value for our users and merchant partners. In 2020 alone, we powered close to US$3 billion in sales for over 5,000 merchant partners across the Asia-Pacific. To date, we have also awarded over US$200 million of cashback to our users.

![ShopBack info graphics](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b8895d67-8413-4ee1-89c9-765f76e81279.png)

## Why ShopBack started using Cube

For all transactions on our ShopBack sites, we need to analyze all transactions, including purchase value, sales volume, etc. In January 2020, we started working on a new project that required a lot of dashboard reporting of aggregated data for both internal and external (e.g. merchant partners) application users. One of the options considered was building an in-house application and storing the data in a graph database. However, in consultation with [Yann AïtBachir](https://www.linkedin.com/in/yaitbachir/) (Head of Data at ShopBack), our engineers decided to go with Cube as it was a better fit for our use case as our data was very much relational and will be pre-aggregated into OLAP cubes for analytics.

![Shopback quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/46935f81-2009-43d4-aede-fe8fca409a75.png)

Since we first started using Cube, there has been an ongoing effort to add new Cube schemas, measures, etc., as our data continued to grow while improving the performance of our queries at the same time. To address performance concerns, we implemented [pre-aggregations](https://cube.dev/docs/caching/pre-aggregations/getting-started) in early 2021. Before implementing pre-aggregations, our [p95](https://findanyanswer.com/what-does-p95-mean-in-statistics) query loading time could take as long as ~50 seconds, which is an insanely bad user experience. Once we started implementing pre-aggregations, we were able to go as low as ~20 seconds p95. After we started further optimizing our pre-aggregations with indexes, modifying our approach to pre-aggregations for unique queries, and a couple of optimizations outside of Cube, we were able to get to below 5 seconds p95.

We still have a few more optimizations planned to reach our target of <1 second p95, but this goes to show that proper application of pre-aggregations can drastically improve the performance of your Cube queries. The performance improvement with pre-aggregations is further demonstrated in the two figures below:

![Figure 1: Chart loading improvements](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/92903a28-4597-46bf-a4f3-60100287519c.png)

![Figure 2: Queries loading time](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2a63e83f-d2b4-4e3f-8b9e-4020c2863e59.png)

## Implementing Cube at ShopBack

Since we started working with Cube in 2020, we have had nine people working on this project full-time with six engineers, 2 product managers, and 1 QA engineer. In addition, we had several other engineers who helped over the past 1½ year as we implemented Cube at ShopBack.

![Figure 3: Architecture Diagram Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d27316b5-d9e5-4ab6-b796-2999d1b4ef20.png)

What you see on the figure above is an architectural diagram of our Cube implementation.

* **Main Application.** The Node.js application in AWS uses an integrated Cube API instance to serve requests to our React frontend application. It fetches 95% of our data from our pre-aggregations, and the remaining 5% have consistently small datasets, so we have not seen the need to pre-aggregate it. Cube responses are also cached within Redis whenever there’s a successful first fetch. Given that our application allows users to use multiple different filters, this significantly reduces the load times for filters that have already been used for the day.
* **Data Pipeline.** Our application acts as a downstream service that receives/pulls in data from our other services. All of this data goes through our ETL process (built in-house), which then stores the processed data into our Postgres database.
* **Caching.** Our architecture uses pre-aggregations that are being stored in the same Postgres database and a non-clustered instance of Redis for storing fetched data and tracking the latest refreshed pre-aggregations for a multi-tenant application setup. The refreshing of our pre-aggregations is done by a separate instance of our application that acts as the [refresh worker](https://cube.dev/docs/deployment/production-checklist#set-up-refresh-worker); this refresh-worker tracks the refresh keys stored in our database (as seen below) to keep track of which pre-aggregations need to be refreshed. Making the [refresh keys](https://cube.dev/docs/caching/using-pre-aggregations#refresh-strategy) manageable like this allows us to fully control whenever we want to trigger a refresh in the middle of the day should we want to.

While working with Cube, here are some of the challenges/learnings that we came across:

* **Making sure we are hitting pre-aggregations.** With the amount of data that we have, we had to make sure that we pre-aggregate our different use-cases correctly. For example, you cannot make a pre-aggregation with a granularity set as a month if there are queries with date ranges that can start/end in the middle of the month. We learned that if we didn’t pay attention to these details, we may be surprised by not “hitting” our pre-aggregations database.
* **Performance.** While it was important to make sure that pre-aggregations are being hit, it was also important to remember that these pre-aggregations are still database tables that are queried on (and in our case, they’re stored in the same source database). In other words, if you end up with a pre-aggregation with a lot of data and a lot of cardinalities, then we also need to index those pre-aggregations. We learned that [indexing](https://cube.dev/docs/schema/reference/pre-aggregations#indexes) our pre-aggregation tables has dramatically improved the performance of our queries. Below you can see a sample of our pre-aggregations that can easily have millions of rows in the pre-aggregation table. As mentioned above, adding indexes (in this case to columns `status` and `outletId`) helped contribute significantly to speeding up our queries.

```js
preAggregations: {
  overview: {
    type: 'rollup',
    measures: [ count, amount ],
    dimensions: [ status, outletId ],
    timeDimension: occuredAt,
    granularity: `day`,
    partitionGranularity: `month`, 
    segments: [ validRecords ],
    refreshKey: {
      sql: `SELECT refresh_key
            FROM preaggregation_meta
            WHERE preaggregation = 'Records'`,
    },
    indexes: {
      overviewIndex: {
        columns: [ status, outletId ],
      }, 
    }, 
  }, 
}, 
```

* **Going beyond raw SQL queries.** In general, it’s fairly easy to create raw SQL for your queries with their own joins/nested statements. However, when we started using pre-aggregations, we realized that the original SQL statements weren’t flexible enough, and you couldn’t simply “port SQL to Cube.” Now, rather than starting with SQL, we consider how we will implement Cube from the outset.
* **Extracting refresh-related information.** We are currently trying to get more logging data from refreshes for the ShopBack team and our clients (e.g., "chart data last refreshed at *hh:mm:ss*"). We extract refresh-related information by looking for select keywords in refresh logs, but if there are easier ways to do this, we’d love to learn more!

The next major Cube project for us is implementing [Cube Store](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage) to achieve lower latency and higher concurrency. We are looking forward to implementing Cube Store after our region’s shopping campaigns/festivals and once our team has enough bandwidth (you can check out what else our tech team is working on [here](https://medium.com/shopback-tech-blog)). From our investigation into Cube Store, we know that we will need to explore some workarounds:
  
* For security reasons, we do not set our AWS keys in our instance environment variables. As such, we do not store values in `CUBESTORE_AWS_ACCESS_KEY_ID` and `CUBESTORE_AWS_SECRET_ACCESS_KEY`, and these are causing unexpected behaviors with our network calls.
* With the Cube Dev team, we’re investigating a low-level error message regarding the Parquet library when we have a lot of data (e.g., 4M rows).

## Working with the Cube community

Another key benefit of working with Cube is the community, as we believe it is a great example of an active community. I worked with other open source communities before joining ShopBack, and I typically saw the number of questions/issues pile up to a pretty high number. On the other hand, with the Cube community, whenever we have questions, they typically get answered within 24 hours of posting them.
  
Also, the community members are quite collaborative in terms of helping each other out. I have had a number of direct messages on [Slack](https://slack.cube.dev) with other community members to discuss how they did their implementations/deployments. In return, I also try to reply to threads that I feel I can help with.

## Interested in joining ShopBack's success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.


John Kevin Go

How ShopBack is using Cube to keep pace with their business & data growth


Based in Malaysia, [FeedMe](https://feedmepos.com/) was founded in 2019 and provides a Point of Sale (POS) system for the food and beverage industry across South East Asia. In addition to POS equipment that you see in restaurants, FeedMe provides reporting to their customers. The reporting ranges from sales reports, marketing analytics, inventory management, and others.  

FeedMe wanted to provide a robust reporting system that allowed users to generate their own custom reports. However, their previous solution that was based on SQL queries and JavaScript to process data across MongoDB, CouchDB, and BigQuery was becoming increasingly difficult to maintain and scale. FeedMe uses CouchDB in their client POS systems that can work either online or offline. CouchDB data is then fed into the BigQuery data lake for reporting.  FeedMe’s backend systems are written in JavaScript, so they wanted to use JavaScript to process data from MongoDB and BigQuery.  

## Taking a phased approach to deploy Cube.js

The FeedMe team found Cube.js through Google searches as they were looking for a solution that can help them develop a data schema standard.  They soon learned that Cube.js could help simplify their data processing and decided to take a phased approach to deploy Cube.js. 

In the first phase, they only focused on migrating part of their data schema using Cube.js’ [Dynamic Schema Creation](https://cube.dev/docs/schema/dynamic-schema-creation#top). They found this process to be relatively straightforward and spent about two weeks to complete this first phase.

In the next phase, the team spent another three weeks migrating the rest of their data schema and then implementing more advanced features such as [pre-aggregations](https://cube.dev/docs/schema/reference/pre-aggregations) and [Cube Store](https://cube.dev/docs/deployment/overview#cube-store). The FeedMe team liked the more flexible caching mechanism in Cube.js since they can customize [refreshKey](https://cube.dev/docs/schema/reference/pre-aggregations#refresh-key) and [rollup](https://cube.dev/docs/schema/reference/pre-aggregations#parameters-type-rollup) definitions. With Cube.js pre-aggregations and Cube Store, FeedMe’s BigQuery cost is down by about ⅔. (Before Cube.js, they could only rely on BigQuery’s built-in cache.)

Also, without pre-aggregations, it could take several minutes for their customers to query several months of sales data which wasn’t ideal.  When the data is cached and pre-aggregated, a similar query would take less than 10 seconds. 

## How FeedMe is using Cube.js

At FeedMe, Cube.js is deployed via Docker along with Kubernetes, and they use [Chart.js](https://awesome.cube.dev/tools/chartjs) for visualizations. You will find more details in the architectural diagram below.

![Architecture Diagram Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cd35818c-31b5-4fcf-9e69-866de2f670b1.png)

Cube.js has been in production at FeedMe since May 2021, and below is a sample screenshot of a sales dashboard. 

![Sales Dashboard Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/469f653f-9dcf-432c-b628-54adbf313764.png)

The following screenshot shows a dashboard called the daily Close Up summary.

![Close Up Summary Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cdfbbc45-fdaf-466d-aa39-e27314be13f1.png)

## Working with the Cube.js community

The FeedMe team used the [Cube.js documentation](https://cube.dev/docs/) as an onboarding tool to get familiar with Cube.js and its features in the early days. They also liked the collaborative nature of the Cube.js community, especially on [Slack](https://slack.cube.dev/), as they were navigating different features such as [rollupJoin](https://cube.dev/docs/schema/reference/pre-aggregations#rollup-join), pre-aggregations, and Cube Store. 

They also provided feedback to the Cube Dev team when they discovered an [issue](https://github.com/cube-js/cube.js/issues/3081) with pre-aggregations. Enhancements to the rollupJoin and pre-aggregations will further streamline how the FeedMe team provides custom reports for their users while maintaining their BigQuery costs. 


## Interested in joining FeedMe's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.


Ray Paik

Source-available document-oriented data store.

MongoDB

How FeedMe used Cube.js to redesign their reporting system


Core to our business at [Simon Data](https://www.simondata.com/) is a data platform that enables our clients to access, operationalize, and centralize their marketing data.  The ultimate goal of Simon Data is to give marketers an intuitive, non-technical platform in which to manage sophisticated data operations required to drive great customer marketing outcomes.

![Simon Data Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a4a475ea-dcc6-4fea-8386-6ae3843c8ed8.png)

The warehouse is powered by a complex, multi-tenant environment comprising datasets of varying lineages, schemas, and business purposes. Some datasets are common across client accounts — such as data we produce or import through marketing channel integrations — while other datasets are client-specific. While this delivers significant leverage for our customers to orchestrate bespoke end-user impressions, it also introduces a brain-bending taxonomical challenge to the engineers looking to drive analytical insights across the entire system.

Since the product’s creation, the platform has seen several analytics products built into it. Early last year, we organized a focused team to consolidate them on a standard foundation. At that same time, we also wanted to rethink how we integrated analytics in our application to streamline the development and deployment processes.

We knew the best path forward was to build a framework that could be used both now and in the future for analytics product development on any part of our core platform. To pull this off, we had some goals for the system.

We wanted a seamless development experience, support for querying arbitrary data, fluency in managing various schemas, and an ability to rapidly prototype and develop the user experience. Equally important was the ability to transparently present the queries and transformations used to produce results to facilitate QA by multiple stakeholders.

## What our users wanted

When we talked with users and business stakeholders, we discovered several pain points:

- Users complained about difficulty configuring custom reports and limited dashboarding capabilities.
- Developers spent too much time maintaining multiple different and complex implementations of analytics products.
- Salespeople wanted an easy-to-use and effective out-of-the-box reporting dashboard to present to prospective clients.
- Client Solutions Managers wanted more transparency in how the system calculated results.
- Product managers and engineers were concerned about the level of effort required to build new analytics products.

## Enter Cube.js

We started with a holistic review of the analytics products we already had to gather requirements for the new reporting suite and took a close look at how clients were (or were not) using them. Alongside that, we also did a systematic review of technologies we could use to build a new reporting platform.

We looked at many commercial products and open-source projects and narrowed the list down to a handful of contenders, including [Apache Kylin](http://kylin.apache.org/) and Druid from the open-source space and Looker from the enterprise. Cube.js quickly rose to the top of that list.

What we liked most about Cube.js (no other offering provided all of these):

- It was designed for precisely the problem we were trying to solve, which was embedding analytics into an existing application, and it fits nicely into our existing technology stack.
- First-class support for the tools we use: Snowflake on the backend and React/Recharts on the frontend.
- Pluggable authentication and schema configuration hooks we could use to integrate with the rest of our platform. Specifically, we use [driverFactory](https://cube.dev/docs/config#options-reference-driver-factory) to provide credentials for Snowflake and MySQL (for external pre-aggregations).
- Out-of-the-box support for deploying as a serverless application on AWS Lambda with API Gateway, ElastiCache, and Aurora/MySQL.
- A fantastic developer sandbox app, [Developer Playground](https://cube.dev/docs/dev-tools/dev-playground), which provides interactive query development, troubleshooting/debugging, the export of queries, and even ready-made Recharts components.
- [Multiple layers of caching](https://cube.dev/docs/caching) to provide the performance necessary for interactive data visualization in our application.
- An integrated framework for building frontend data components leveraging our massive data warehouse, one that was lightweight and enabled rapid iteration without a lot of boilerplate, nor the need to wire together lots of different libraries.

## Where we had to invest in Cube.js

Marketers interact with Simon Data through our easy-to-use web application. We needed a way to query data from multiple independent data warehouses, each with its different schema, and present the results in a common interface. This was a challenge on two fronts: development and deployment.

First, we had to invest in integrating Cube.js with our infrastructure to provide credentials and schema configuration for each individual client's data warehouse. Cube.js provided hooks we could use, so this was reasonably straightforward.

However, we needed to give developers a way to run Cube.js to mirror the production environment while being able to rapidly iterate on schema, query, and frontend component development. This meant building a common application bootstrap and configuration for Cube.js that we could use to launch it in dev, staging, and production environments.

One neat trick we pulled off here was getting Cube.js to seamlessly serve both its playground application and API queries from our web app when running in development mode. This is important for us to be able to run everything (i.e. both Cube.js and Simon Data web app) on our laptops and have the web app be able to send queries to the Cube.js Playground running locally.

Another challenge we faced was safely and securely deploying configurations to production without impacting what was already running. We achieved this by making schemas immutable.

When we want to deploy a new schema or patch an existing one, we create a whole new schema or increment the current schema with the changes. Once we have the latest schema working in our development environment, we can test it out in the staging environment and deploy it to production. We can do all of this in the background, so we only switch the production web application over to the new schema once we have complete confidence in it (see Figures 2 & 3 below).

Our repeatable deploy process sets up the new schema in Snowflake and Cube.js simultaneously, which gives strong consistency between the Snowflake and Cube.js configurations.

We have defined schemas as a set of Snowflake views and Cube.js cubes that are developed and deployed together. We create a schema by writing view templates that map onto the data in Simon’s warehouse designed for the given customer to provide a standard interface to create the cubes on top of (see Figure 1 below). Our developers write these view and cube templates using [Jinja](https://palletsprojects.com/p/jinja/) from their IDEs and can rapidly iterate on them locally using our end-to-end development environment integration. Once the schema is complete and committed, our deployer tool dynamically renders the templates by filling in variables specific to each customer’s datasets. This pattern was the key to building repeatable schemas and deploying them consistently in our complex multi-tenant environment.

Finally, we created a comprehensive, automated end-to-end integration test suite (written in Python) that exercises each cube across all schemas that are live in Cube.js. This serves two important purposes:

1. It validates our production environment.
2. It keeps the Cube.js pre-aggregations database up to date ahead of user interaction.

The integration test runs periodically and also after the deployment of any new schemas. Once the test passes, we can safely switch the web application to the new schema with confidence.

### How Cube.js is deployed at Simon Data

![Figire 1](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/329b1deb-0db8-4c31-a91a-8aa93d4c817c.png)
**Figure 1: Schema is the unit of deployment**

- Each schema gets deployed into both Cube.js and Snowflake at the same time.
- A schema is composed of one or more sources. Each source defines a particular data source.
- Sources are where developers work. During development, the Cube, View, and Table definitions are created as a single unit (a source) that powers one or more in-app features.
- In a source, each Cube (Cube.js config files) has a dependent View (with a SQL query, which the Cube is defined on top of), and each View has one or more dependent Tables (with a name and list of columns that are verified in validation).
- Tables represent the upstream data in the data warehouse that we want to bring into the web application with an in-app reporting feature.

![Figure 2](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/fc652e61-0640-463f-b8ed-0b7d0375b12d.png)
**Figure 2: Deployment process**

- Create a new schema in the Cube.js Playground.
- Deploy the schema into Snowflake & Cube.js.
- Switch the Simon Web App to use the new schema.

![Figure 3](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3d5dc184-927a-4216-9bef-345227ed7b99.png)
**Figure 3: Schema migration**

We can develop a new schema and deploy it in the background, then switch the Simon web app over when ready.

## How are we better of now?

We delivered three major wins to our clients and business stakeholders with this platform:

**Speed.** We can develop and ship analytics products without dealing with any net new infrastructure development going forward. Our efforts are focused on what matters: the data and the UI. Integrating the Cube.js Playground into our development stack enables us to rapidly build a schema and query in Cube.js and then export that for implementation in the front end, all of which a developer can quickly do on a laptop.

**Flexibility.** We have a framework that enables us to seamlessly query any data in Snowflake and present it in the web application. The real win here is that we won't have to choose between maintainability and time to market to get this flexibility -- we can have both.

**Stability (Antifragility).** When we're ready to ship a new product feature, we can deploy it with the confidence that we will not introduce regression on existing functionality because our schemas are immutable. We can also roll out new features to client accounts in a controlled manner, one by one or in batches, and even keep the changes hidden to perform production validation before they are released to end users.

## Results and future plans

We’ve already built net-new analytics products on this platform that solve fundamental client pain points, proving that the platform works. We’re also starting to realize the velocity gains when iterating on these products to add functionality and refine the user experience.

Now we’re starting to look at consolidating some of the earlier analytics products onto the new framework. Finally, our business stakeholders now have transparency into the calculations that power analytics products in the application, giving them confidence in the data and helping them more easily answer client questions.

## Interested in joining Simon Data's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.


Louis Zuckerman

How Simon Data addressed their runaway analytics complexity with Cube.js


[COTA](https://cotahealthcare.com/) was founded in 2011, and they combine oncology expertise with advanced technology and analytics to organize real-world medical treatment data to guide cancer research and care.

COTA builds solutions to better support oncologists' clinical decision-making and researchers' development of new drugs and therapies. One such product is the Real World Analytics (RWA) solution that helps clinicians and researchers make sense of fragmented and often incomplete electronic health records (EHR) data and provide insight into the patient population, treatment patterns, disease outcomes, etc.

COTA has access to millions of electronic oncology patient records with a vast amount of associated data unmatched in the oncology healthcare industry. During COTA's recent [Series&nbsp;D funding](https://cotahealthcare.com/cota-secures-34m-in-growth-capital-and-triples-data-access/) announcement, they shared the expansion of their data access by more than 300%.

Data analytics is thus critical for COTA, and they strive to apply innovative ways to improve the technology used in the healthcare space. The COTA team wanted to replace off-the-shelf solutions like Qlik and Tableau, which require heavy customization and specialty configuration knowledge with a more developer-friendly ecosystem.

With Cube.js, they found that match and leveraged its reference architecture to save development cycles that previously involved writing custom queries, custom data munging, and processing. When the COTA team discovered Cube.js in 2019, they became early adopters of Cube.js soon after it was open sourced. You will find Cube.js in action today in this [RWA demo video](https://cotahealthcare.wistia.com/medias/1jl9z5dmz4).

![Sudhakar Velamoor's quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9f423675-6db9-435c-979a-b4987dd45fcc.png)

## How COTA is using Cube.js

Today, Cube.js is a foundational tool for COTA's products, with Cube.js [Data Schema](https://cube.dev/docs/getting-started-cubejs-schema) serving as the single source of truth for their data and Cube.js API powering their applications.

The COTA team uses Google Cloud Platform to host and run their applications, including the Cube.js deployment. Their deep, longitudinal datasets feature upwards of 150 tables from structured and unstructured sources, which come together in their data products to form a cohesive, comprehensive patient journey.

While assessing possible architecture options for the Cube.js deployment, the COTA team initially explored using Google Cloud Functions to run Cube.js in serverless mode, and they were among the first Cube.js users who were [interested in support](https://github.com/cube-js/cube.js/issues/87) for Google Cloud Functions. However, they ultimately decided to use microservices hosted on Google Compute Engine.

![COTA architecture](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/1f3fad8d-f65c-4659-a679-ef0edbf3c7f1.png)

The COTA team uses Angular on the front-end of their solutions. They have been an early proponent of [Angular support](https://cube.dev/docs/frontend-introduction/angular) in Cube.js while initially integrating Cube.js via its vanilla JavaScript library, which provided framework-agnostic methods to query Cube.js API. Today they use Angular 10 and leverage TypeScript support in Cube.js. They also use the [Plotly](https://plotly.com/javascript/) charting library wrapped with the [angular-plotly.js](https://github.com/plotly/angular-plotly.js) component for data visualization in their applications.

Since multiple queries are often run against Cube.js API by users while navigating the applications, the COTA team wanted to ensure that queries can be canceled to conserve resources. For example, when a user queries multiple times, they want to cancel the ongoing queries and only run the current (or the latest) query. To achieve that, they used [mutex support](https://cube.dev/docs/@cubejs-client-core#types-load-method-options), which synchronizes multiple concurrent requests performed by the Cube.js API via `mutexKey` and `mutexObj`:

```jsx
let resultSet = this.cubejsClient.load({
    dimensions,
    measures,
    filters,
    timeDimensions,
    limit,
    order,
  }, {
    mutexKey,
    mutexObj: this.mutexObject // mutexObject is initialised as {}
  });
```

[In-memory cache and pre-aggregations](https://cube.dev/docs/caching) in Cube.js also helped the COTA team achieve desired performance since the same table can be viewed in different ways based on applications. They are happy with the performance so far; however, they also experienced that pre-aggregations is likely one of the more complicated features in Cube.js. Ensuring they use all pre-aggregations features correctly required a fair amount of effort and community support on [Slack](https://slack.cube.dev).

The COTA team also employs [dynamic schema generation](https://cube.dev/docs/schema/dynamic-schema-creation) with zero-setup `originalSql` pre-aggregations to materialize results for every `CustomerID` and `ProductID` pair of dimensions. This approach significantly reduces the response time for their per-customer or per-product queries:

```jsx
asyncModule(async () => {
  const types = ['CustomerID', 'OrderDetail.ProductID'];

  cube(`GeneticTest`, {
    sql: `SELECT * FROM Order`,

    measures: {
      count: { sql: `orderid`, type: `countDistinct` }
    },
    dimensions: {
      orderId: { sql: `orderid`, type: `number` }
    },
    ...(types
      .map(type => ({
        [${getDimensionName(marker)}]: {
          sql: JSON_VALUE(${CUBE}.Order, `$.${type}`),
          type: `string`
        },
      }))
      .reduce((a, b) => Object.assign(a, b))
    ),
    preAggregations: {
      main: { type: `originalSql` }
    }
  })
});
```

The COTA team also provided early feedback on the [advanced boolean logic](https://cube.dev/docs/query-format#boolean-logical-operators), as this feature made it easier for the team to read and write code. Before the advanced boolean logic, it wasn't easy to do complex nested queries, so they used multiple queries by getting an inner query result and computing another query on that result set. With the advanced boolean logic, they took advantage of the [Angular-QueryBuilder](https://zebzhao.github.io/Angular-QueryBuilder/) to create complex logic to get a result with a single API call.

## Deploying Cube.js at COTA

For the Cube.js implementation at COTA, there were 4 people involved: a product manager, a data analyst, and two software engineers. The release of the minimum viable product (MVP) for their RWA product took about 6-8 months.

As the COTA team was working on the Cube.js implementation, they took advantage of community support and insights on [Slack](https://app.slack.com/client/TC0C1HURK/CC0403RRR) and the [community forum](https://forum.cube.dev/). They also appreciated the ability to file bugs and feature requests on [GitHub](https://github.com/cube-js/cube.js/issues). More recently, they learned from feature demo discussions during [Monthly Community Calls](https://www.youtube.com/playlist?list=PLtdXl_QTQjpb1dHZCM09qKTsgvgqjSvc9) on ways to fine-tune their Cube.js implementations (e.g., optimizing `countDistinct` pre-aggregations).

## Future plans

As areas for improvement in Cube.js, the COTA team would like to see more out-of-the-box Angular support from Cube.js to help with the speed of integration at COTA. For example, implementing `AngularHttpTransport` would help them leverage features like `HttpInterceptor` to simplify canceling the requests.

![Shivam Mathura's quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9e94f574-fa44-44ff-b9fe-636fd6c0e8a1.png)

Internally, the COTA team also wants to migrate Cube.js to containerized [Docker deployments](https://cube.dev/blog/cubejs-loves-docker/) to align with the rest of their infrastructure at the company.

## Interested in joining COTA's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

Igor Lukanin

How COTA uses Cube.js to aid the development of new drugs and therapies

[Cyndx](https://cyndx.com) tools serve some of the largest financial services companies worldwide to help them identify emerging companies, growth opportunities, and other investment opportunities. Along with their AI-driven algorithms, Cyndx uses data from over 12 million companies and more than 1 million acquisitions, capital raises, investments, and investors data in their database to provide predictive analytics and help its clients identify target lists in a fraction of the time of traditional workflows.

Recently, Cyndx decided to expand its product and develop functionality to explore the data analytics that would allow its end users to dig deeper than their existing platform. The Cyndx team evaluated several commercial business intelligence (BI) products in the past. Still, most of the solutions required a lot of custom work for frontend/design and integrating with their AI. After one of the Cyndx analysts discovered Cube.js, they put together a demo within a day that looked like the Cyndx platform.

![cyndx-quote (1).png](https://media.graphcms.com/6jZT0zArTT22BxUhhm8t)

## How Cyndx is using Cube.js

On its frontend, Cyndx uses the React framework with movable/resizable components. They also use visualization components from [Nivo](https://nivo.rocks/), a data visualization library built on top of [D3.js](https://d3js.org). An extended version of the Cube.js Developer Playground is used for creating the individual visualizations.

On the backend, the Cube.js API instance is hosted on a Google Kubernetes Engine (GKE) cluster. Cube.js is making calls to Cyndx BigQuery data warehouse. For performance, BigQuery caching has been key, but they also do database clustering and partition keys.

Further, they are leveraging a custom backend which is used to store permissions and custom dashboard configurations on a per user basis. The permissions gate access to the dashboards while the dashboard configurations are retrieved on page load to give a user a personalized experience. 

## Testing at Cyndx

To ensure the quality of the data being released, Cyndx runs numerous tests both on the frontend visualizations and on the backend data. Tools such as [Datadog](https://www.datadoghq.com) are used for browser testing to ensure functionality like drill-downs/links are working and to monitor long-running queries that may take too long. 

Additionally, Cyndx also have around 1,000 tests on data itself using [dbt](https://www.getdbt.com) before data gets promoted to production. The Cyndx team has also been active in the dbt community to help build the underlying data structure going into Cube.js and reducing the overhead on Cube.js by standardizing what tables look like.

## Self-hosting and custom data visualizations

For Cyndx, it was essential to be able to manage their own security, and the Cyndx team liked the fact that they can self-host Cube.js rather than deal with third-party servers.

Other key benefits of Cube.js were the ability to integrate directly with the React framework and using Cyndx's own CSS so that new features such as plotting looked like the rest of the Cyndx platform. The screenshots below show the Cyndx platform (with mock data) where Cube.js is used.

![Untitled-5.png](https://media.graphcms.com/RdtYBZHrQwaxFtNqFJx8)

![Untitled-6.png](https://media.graphcms.com/nNNs7cERSB2C4J0fVrqM)

## Cyndx team and plans

To implement Cube.js, Cyndx had a dedicated frontend engineer and analyst as well as a part-time backend engineer and designer. Cube.js Developer Playground has been instrumental for the team and is on everyone's computer. The team was also able to get responsive community support from [Cube.js Slack](https://slack.cube.dev/). 

The Cyndx team successfully went live with Cube.js in October 2020, and they are looking forward to several improvements in Cube.js that can help enhance their platform. For example, they default to quarterly (vs. year-over-year) views at Cyndx, so a better way to address granularity in Cube.js would be beneficial. They would also like to see more custom functions such as "calculated fields" and enhancements in pulling in data from REST APIs (e.g., Salesforce API).

## Future: Self-serve analytics

In the future, Cyndx is planning on rolling out self-serve analytics to its end-users. Clients have expressed a strong interest in digging deeper and customizing the analytics for their organizations. Many investors focus on specific regions and/or specific segments of the market, and they would like the analytics to reflect what is relevant for them. This type of customization is not possible without self-serve analytics as it is not possible to enumerate all of the use cases that Cyndx's end-users may be looking for.

This personalization of the analytics will be done through a multi-phase project in which Cyndx first extends the Cube.js Developer Playground so that it looks and feels like their web app. The ability to make such customizations was a crucial component in their selection of Cube.js for their backend modeling tool. Many other enterprise offerings for analytics will allow customizing the visuals, but the user interface for building out visuals was often not editable.

Through the use of the customized playground, the end-users will be able to develop dashboards customized to both the user's individual needs and jointly edit dashboards necessary for their organization's workflows. This type of permission logic is easily extensible on top of Cube.js backend and was an additional reason for selecting the tool.

## Interested in joining Cyndx's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Cube.js Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

How Cyndx enhanced its financial platform with Cube.js

[Jobber](https://getjobber.com/), the leading provider of business management software, helps small home service businesses stay organized, connect with customers, grow revenue, and better compete against large corporations. The company's technology supports more than 50 industries, including HVAC, plumbing, lawn care, cleaning, and more. Since launching in 2011, businesses using Jobber have serviced over 15 million households in more than 47 countries. 

![zeisler-quote.png](https://media.graphcms.com/Jiqz2GcDRliDe6QP8E7T)

One of the important tools for Jobber's customers is dashboards that can provide a snapshot of what's happening in their businesses to schedule their day, optimize routing, keep track of invoices, accept payments, and more, even when people are not in traditional brick and mortar offices. Today, more than 100,000 service professionals use Jobber's platform and rely on it to stay on top of—and grow—their businesses.

![Untitled.png](https://media.graphcms.com/NWbbXWdWRym4mvp56NA3)

In the past, the Jobber team was retrieving information by doing direct queries against a production database from their Ruby on Rails application through [Active Record](https://guides.rubyonrails.org/active_record_basics.html) ORM. However, as their business scaled and with close to a decade of data, the dashboard performance started to get slower. The team worked on things like caching, query optimization, and database tuning to address performance issues but realized that more needed to be done.

![weinrich-quote.png](https://media.graphcms.com/wHVLFnwNRBC2SnfSQz37)

## How Jobber is using Cube.js

When Jobber first discovered Cube.js, they started studying the documentation and examples and liked the flexibility. The Jobber team also liked the [two-level caching](https://cube.dev/docs/caching#top) system in Cube.js as they previously had to implement a lot of that with their own code. Now they simply use Cube.js almost as a black box that they communicate with and can remove the optimization code they had to maintain in the past. Jobber's first use case with Cube.js was using daily [rollup pre-aggregations](https://cube.dev/docs/pre-aggregations#rollup) with the two-level caching. 

Another important and timely feature was the [external pre-aggregations](https://cube.dev/docs/pre-aggregations#external-pre-aggregations) capability of Cube.js. With their source database being a read-only database replica, building the pre-aggregations inline within the source database wasn't an option.

![weinrich-quote-2.png](https://media.graphcms.com/5rId8uhyS6SOIoVGwJ6T)

As you can see in the architecture diagram below, Jobber has a single PostgreSQL database backend, and they recently started using React for the frontend. Jobber has a read-only replica of the database to populate the rollup pre-aggregations database and wraps the Cube.js REST API with their own API for handling things like authentication (Cube.js is a trusted sub-system in this configuration). Jobber also leverages [multitenancy](https://cube.dev/docs/multitenancy-setup) with a query transformer to enforce at runtime that all data queries filter to the authenticated tenant. Other security features include full [certificate verification](https://cube.dev/docs/connecting-to-the-database#enabling-ssl) for database connections.

![Jobber-Conceptual Architecture.png](https://media.graphcms.com/UXwvIj0HS1288WBP6UA2)

Splitting Cube.js like this—an API server which handles the queries and a scheduled refresh server which updates the pre-aggregation rollups periodically—required leveraging some special configuration options for the orchestrator:

- **API Server**: Uses `CubejsServer` but configured so that it will only respond with data from the rollup pre-aggregations database even if queries attempt to access data outside of the available rollups (`rollupOnlyMode: true`), and additionally, should skip the consistency checks and background refreshing of the rollups (`externalRefresh: true`).

```jsx
orchestratorOptions: {
  preAggregationsOptions: {
    externalRefresh: true,
  },
  rollupOnlyMode: true,
},
```

- **Scheduled Refresh Server**: Uses `ServerCore` (no need to run a web server here!) to execute `runScheduledRefresh` repeatedly (with a five-second cooldown) until the rollups have completed refreshing. Note that leveraging a read replica requires setting up `readOnly: true` on the `driverFactory` configuration.

Jobber's architecture could be a viable option in their environment for anyone who is querying production databases (vs. querying some big data data sources).

## Deploying, testing, and including Cube.js into the CI pipeline

There were several team members at Jobber that were involved in Cube.js development and included two developers, one designer, and a product manager. The team initially used Cube.js out-of-the-box and utilized the Cube.js Developer Playground extensively, with a few additional refinements, such as converting the auto-generated project to TypeScript. Jobber uses Docker for their development environment, and they surface the Cube.js microservice in a container. 

The Jobber team also developed tests for things like additional schema validations on their CI build. One of the things the team ran into early on was the table name truncation that tends to happen with PostgreSQL. So one of their tests actually asks Cube.js what table names it's going to generate, and if the table name is longer than what PostgreSQL allows, the test will fail early in the CI process. 

The Jobber team also did two rounds of performance testing. First, the team wanted to understand where the bottlenecks for Cube.js were (e.g., CPU vs. memory) when saturated with incoming requests and observed how it responded while ramping up simulated concurrent requests.

In the load test graph below, you can see how their test showed signs of achieving maximum throughput (for the given server configuration), with response times (blue) starting to get shaky as the number of concurrent requests (yellow) ramps up. This particular test was getting 100% Redis cache hits, and the bottleneck was the CPU.

![Untitled-2.png](https://media.graphcms.com/pxNLuyThRTi0rC29dC5U)

Next, they used simulated traffic in the frontend to mimic actual customers' traffic (and data requests) more accurately and began experimenting with different scaling options (vertical and horizontal). This allowed them to observe more complex performance characteristics because both caching tiers were getting exercised.

Once on beefier servers, the CPU was no longer the bottleneck, and the next bottleneck encountered was related to memory! This manifested in a runaway memory ramp (at a certain amount of traffic) where Node.js would consume all the available memory and eventually pause the entire runtime for several seconds to run a "mark sweep" style garbage collection.

![Untitled-3.png](https://media.graphcms.com/gjZl4ZejQhKsr1EdrTyA)

After some research into how Node.js' memory configuration works—[it's complicated](https://github.com/v8/v8/blob/2b17c752d117bf0ededd23206bb38b69e641a976/src/heap/heap.h#L1106-L1136), often described incorrectly online, and changed between versions 10 and 12—the Jobber team optimized the usage of the server resources by [running concurrent copies](https://devcenter.heroku.com/articles/node-concurrency) of the Node.js process using a library called [throng](https://www.npmjs.com/package/throng).

The graph below shows the service's response time shape over three days. You actually see that each day, as the traffic starts increasing up to the peak volume (represented as the vertical white lines), the Cube.js response time actually goes down (almost counterintuitively). This is because the first hit of the day is going against the rollup pre-aggregations database (level 2), but subsequent looks at the dashboard hit the faster query result cache in Redis (level 1).

![Untitled-4.png](https://media.graphcms.com/P8MVx2q6QGu2YYlANt5n)

## Contributing to Cube.js

Jobber's team was also very active contributors to Cube.js and in many ways co-developers as they were evaluating and deploying Cube.js. This [GitHub query](https://github.com/search?l=&q=repo%3Acube-js%2Fcube.js+author%3Ajcw-+author%3Acolefichter+state%3Aclosed&type=issues) shows contributions from two team members at Jobber. They actually only show contributions via pull requests and don't include Slack or other channels.

This type of active engagement from users clearly demonstrates the value of open source. The contributions help accelerate development and, more importantly, provide valuable insight to the Cube Dev team on actual use cases in production environments.

![klotz-quote (1).png](https://media.graphcms.com/XnB22Z8Qxi1zSMstJvIA)

If you want to see Cube.js in action today at Jobber, you can visit the [Jobber's Help Center page](https://help.getjobber.com/hc/en-us/articles/360051851213-Home#h_01EDPE0KKPBMDHE96TF3DXCAD9). In the screenshot below, Cube.js is used for the four line-series charts.

![sidenavClosed.png](https://media.graphcms.com/flj48F6LShyE6kDpNVGU)

After the successful deployment, the Jobber team is continuing to explore other areas where Cube.js can reduce engineering costs while delivering great features for their customers.

## Interested in joining Jobber's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.


How Jobber uses Cube.js to help customers run their businesses with high performance dashboards

*Originally published at [sublimd Blog](https://sublimd.com/en/blog/2019/06/sublimd-analytics-meets-cube.js/)*

We are [sublimd](https://sublimd.com/en/) from Switzerland, an award-winning medical software platform to radically reduce paperwork in hospitals, enhance treatment quality and revolutionize clinical research.

In the beginning of 2019, we have received the first request for our new module *sublimd Analytics* from a client. At that time, we had established an open source business intelligence server solution in our product. We were struggling a lot with preconfiguring our analytics dashboards and ship it ready-to-use to our (non-technical) customers. Acting from necessity, we were looking for an easier solution to cover our needs: having full control over the customer configuration (tracked in a version-control system).

![sublimd-cubejs-1.png](https://media.graphcms.com/fE8vtoBGShufgkfZ7Wji)

By chance, [Cube.js](https://cube.dev/) attracted our attention on an independent comparison website. It felt almost too good to be true: *Cube.js* fits our technology stack including *Node.js, MySQL and Redis.* Which means a much simpler deployment than before. Beyond that, the framework lets developers the freedom to implement the frontend with your custom look and feel. After creating a small prototype, our goal of a seamless integration in our software suite appeared realistic again.

![sublimd-cubejs-2.png](https://media.graphcms.com/D0eF1tFDSzSxZQso6UeB)

During the integration, we were really pleased how mature *Cube.js* already was. Even more impressive that it has been open-sourced only for two to three months. As our organization was de facto the first developer team who was working with the framework, we got an overwhelming premium support by the *Cube.js* team (Kudos to Pavel and Artyom). In return, we have sent back a lot of feedback and occasionally contributed to the project with commits to *GitHub*. It is our effort among other things to provide *TypeScript* definitions.

Our first version of the product still had the usual teething troubles. So we have improved it and a few weeks ago, the brand new version has been successfully rolled out to our first *sublimd Analytics* customer. The customer is happy, the software works like a charm and reveals unprecedented information of processes in the hospital.

![sublimd-cubejs-3.png](https://media.graphcms.com/NlF7EDeRbCilDveqWThE)

What’s next? Our analytics module does not yet tap the full potential. We have thousands of health data points to pre-process and visualize in appealing charts. *Cube.js* is our framework of choice with building blocks including a caching and pre-aggregation layer to allow us to build a high-performance analytics tool for healthcare. This is another success story of open source software where a distributed team of engineers from all over the world solves a real-world problem.

Thomas Kaul

sublimd Analytics meets Cube.js


_Originally published at [Qualibrate Blog](https://qualibrate.com/blog/data-science/our-path-towards-advanced-quality-reporting-analytics/)_

Here at [Qualibrate](https://qualibrate.com) we’ve been trying to manage the best way to give users the flexibility of
advanced reporting and simplicity in generating great dashboards, and while Kibana, Grafana, et al.
are great products, we wanted to give our users a seamless experience when they’re using our platform.

After discussing the variety of options available versus the case of building our own,
we found [Cube.js](https://cube.dev) from [Statsbot.co](https://statsbot.co). We thought that their offering was perfect for our use case,
but there was a big caveat—our current platform uses the Vue.js for which there was no implementation.
That’s when the spark ignited; why should we reinvent the wheel by trying to copy an incredible product,
when instead we could take advantage of what a great community gives us and try to work on this together.
We started using Cube.js in our workflow when we saw there was a opportunity to make an
improvement on the MongoDB connector. That moment, our relationship with the community began.

Once we were able to map our database schemas and curate our relationships, our work began.
We tried to match the React components as closely as possible in Vue.js.
This is the result.

## Set up a demo backend

_If you already have Cube.js Backend up and running you can skip this step._

Since we’re using MongoDB for our product, we’ll use it for our tutorial as well.
Please follow [this guide](https://cube.dev/blog/building-mongodb-dashboard-using-node.js) to set up the Cube.js backend.

## Using the Vue.js library

Let’s create a new Vue CLI project using @vue/cli and navigate into it.

```sh
$ vue create cubejs-mongo-example && cd cubejs-mongo-example
```

Next, install the `@cubejs-client/vue` dependency.

```sh
$ npm install --save @cubejs-client/core @cubejs-client/vue
```

Also, we're going to add `vue-multiselect`, `vue-chartkick` and `Chart.js` for this example

```sh
$ npm install --save vue-multiselect vue-chartkick chart.js
```

Now we can start the development server

```sh
$ npm run serve
```

Voila! Your application will be launched and is available at http://localhost:8080
and should look like this:
![Screen 1](https://media.graphcms.com/nNGn515PRT25o6f0iGNw)

## Environment Variables

Vue applications configuration variables can be loaded through a .env file and
will be available if using the `VUE_APP_` convention ([More information available here]( https://cli.vuejs.org/guide/mode-and-env.html#environment-variables ))

```sh
VUE_APP_CUBEJS_API_TOKEN=<YOUR-API-TOKEN>
VUE_APP_CUBEJS_API_URL=<YOUR-API-URL>
```
Whenever your application is running, this configuration will be accessible through
the `process.env` object.

## Configure the dependencies

`src/main.js` is the default entrypoint to any generated `@vue/cli` project;
it’s where the Vue app is mounted. In this case, we’re putting global components
so that they’re available everywhere.

```js
import Vue from 'vue';
import VueChartkick from 'vue-chartkick';
import Chart from 'chart.js';
import App from './App.vue';

Vue.config.productionTip = false;
// Add ChartKick components with Chart.js Adapter
Vue.use(VueChartkick, { adapter: Chart });

new Vue({
  render: h => h(App),
}).$mount('#app');
```

## Using the QueryRenderer

The `<query-renderer/>` component takes a Cube.js object and a formulated query
in order to fetch the data and return a `resultSet`. It handles the request asynchronously
and renders the slot once it’s been resolved.

```js
<template>
  <div class="hello">
	// Query Renderer component
	<query-renderer :cubejs-api="cubejsApi" :query="query">
  	<template v-slot="{ loading, resultSet }">
      	<chart-renderer
        	v-if="!loading"
        	:result-set="resultSet" />
  	</template>
	</query-renderer>
  </div>
</template>

<script>
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/vue';
import ChartRenderer from './ChartRenderer.vue';

const cubejsApi = cubejs(
  process.env.VUE_APP_CUBEJS_API_TOKEN,
  { apiUrl: process.env.VUE_APP_CUBEJS_API_URL },
);

export default {
  name: 'HelloWorld',
  components: {
	QueryRenderer,
	ChartRenderer,
  },
  data() {
	const query = {
  	measures: ['Orders.count'],
  	timeDimensions: [
    	{
      	dimension: 'LineItems.createdAt',
      	granularity: 'month',
    	},
  	],
	};

	return {
  	cubejsApi,
  	selected: undefined,
  	query,
	};
  },
  methods: {
	customLabel(a) {
  	return a.title;
	},
  },
};
</script>
```

Here is a simple example of a `<chart-renderer/>` component for displaying information
coming from `resultSet` mapping the result into an object that is compatible with the
`vue-chartkick` `line-chart` data series:

```js
<template>
  <div class="chart-renderer">
	<line-chart :data="series"></line-chart>
  </div>
</template>

<script>
export default {
  name: 'ChartRenderer',
  props: {
	resultSet: {
  	type: Object,
  	required: true,
	},
  },
  computed: {
	series() {
  	const seriesNames = this.resultSet.seriesNames();
  	const pivot = this.resultSet.chartPivot();
  	const series = [];
  	seriesNames.forEach((e) => {
    	const data = pivot.map(p => [p.x, p[e.key]]);
    	series.push({ name: e.key, data });
  	});
  	return series;
	},
  },
};
</script>
```

Up until this point it should look something like this:

![Screen 2](https://media.graphcms.com/soafIs2dTGGJGWf2PG5n)

## Using the QueryBuilder


The `<query-builder/>` component is a utility that gets the schema information
from the API and allows you to generate the query with some helper methods.
Some of these include `setMeasures` and `addMeasures`, which change the underlying query
and update the `resultSet` on the background. Every available method is part of the
[scoped slots](https://vuejs.org/v2/guide/components-slots.html#Scoped-Slots) object.
The only required prop is `cubejsApi`. It expects an instance of your Cube.js API client returned by the Cube.js method.


[Here you can find more detailed reference of the `QueryBuilder` component](https://cube.dev/docs/@cubejs-client-vue)


```js
<template>
  <div class="hello">
	<query-builder :cubejs-api="cubejsApi">
  	<template v-slot="{ measures, setMeasures, availableMeasures, loading, resultSet }">
    	// Render whatever content
  	</template>
	</query-builder>
  </div>
</template>

<script>
import cubejs from '@cubejs-client/core';
import { QueryBuilder } from '@cubejs-client/vue';

const cubejsApi = cubejs(
  process.env.VUE_APP_CUBEJS_API_TOKEN,
  { apiUrl: process.env.VUE_APP_CUBEJS_API_URL },
);

export default {
  name: 'HelloWorld',
  components: {
	QueryBuilder,
  },
  data() {
	return {
  	cubejsApi,
	};
  },
};
</script>
```

Then we’re going to add the capability to select some dimensions and measures.
It will update the query and render the content accordingly. The `setMeasures` and
`setDimensions` methods set the collection directly to the query since these multiselect
controls make it a bit easier. `availableMeasures` and `availableDimensions` get the information
from the schema to show allowed inputs.

```html
<template>
  <div class="hello">
	<query-builder :cubejs-api="cubejsApi" :query="query">
  	<template v-slot="{ measures, setMeasures, availableMeasures, dimensions, setDimensions, availableDimensions, loading, resultSet }">
    	<div class="selects">
      	<multiselect
        	:multiple="true"
        	:customLabel="customLabel"
        	@input="setMeasures"
        	:value="measures"
        	:options="availableMeasures"
        	placeholder="Please Select"
        	label="Title"
        	track-by="name"/>
      	<multiselect
        	:multiple="true"
        	:customLabel="customLabel"
        	@input="setDimensions"
        	:value="dimensions"
        	:options="availableDimensions"
        	placeholder="Please Select"
        	label="Title"
        	track-by="name"/>
    	</div>
      	<chart-renderer
        	v-if="!loading && measures.length > 0"
        	:result-set="resultSet" />
  	</template>
	</query-builder>
  </div>
</template>
```

This is a fully working example and should look like this.

![Screen 3](https://media.graphcms.com/i9IR7fkNShqcxAOCqxfk)

```js
<template>
  <div class="hello">
	<query-builder :cubejs-api="cubejsApi" :query="query">
  	<template v-slot="{ measures, setMeasures, availableMeasures, dimensions, setDimensions, availableDimensions, loading, resultSet }">
    	<div class="selects">
      	<multiselect
        	:multiple="true"
        	:customLabel="customLabel"
        	@input="setMeasures"
        	:value="measures"
        	:options="availableMeasures"
        	placeholder="Please Select"
        	label="Title"
        	track-by="name"/>
      	<multiselect
        	:multiple="true"
        	:customLabel="customLabel"
        	@input="setDimensions"
        	:value="dimensions"
        	:options="availableDimensions"
        	placeholder="Please Select"
        	label="Title"
        	track-by="name"/>
    	</div>
      	<chart-renderer
        	v-if="!loading && measures.length > 0"
        	:result-set="resultSet" />
  	</template>
	</query-builder>
  </div>
</template>

<script>
import cubejs from '@cubejs-client/core';
import Multiselect from 'vue-multiselect';
import { QueryBuilder } from '@cubejs-client/vue';
import ChartRenderer from './ChartRenderer.vue';

const cubejsApi = cubejs(
  'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg',
  { apiUrl: 'https://awesome-ecom.gcp-us-central1.cubecloudapp.dev/cubejs-api/v1' },
);

export default {
  name: 'HelloWorld',
  components: {
	Multiselect,
	QueryBuilder,
	ChartRenderer,
  },
  data() {
	const query = {
  	measures: [],
  	timeDimensions: [
  	],
	};

	return {
  	cubejsApi,
  	selected: undefined,
  	query,
	};
  },
  methods: {
	customLabel(a) {
  	return a.title;
	},
  },
};
</script>
<style src="vue-multiselect/dist/vue-multiselect.min.css"></style>

<style scoped>
.hello {
  padding: 0 10rem;
}

.selects {
  display: flex;
}

.selects .multiselect {
  margin: 0.5rem;
}
</style>
```


Similar to measures, `availableMeasures`, and `updateMeasures`, there are properties
to render and dimensions, segments, time, filters, and chart types to manage.
You can find the full list of properties [in the documentation](https://cube.dev/docs/@cubejs-client-vue#query-builder).

Ricardo Tapia

Approachable, performant, and versatile JavaScript framework.

Declarative component-based JavaScript library originally created by Facebook.

React

Web framework for React web applications by the Vercel team providing SSR & SSG.

Next.js

Fully extensible web developer's platform.

Angular

Popular front-end frameworks for developing modern web applications.

Front-end Frameworks

Qualibrate has been trying to manage the best way to give their users the flexibility of advanced reporting and simplicity in generating great dashboards. After they started using Cube, they could take advantage of a great community, improve the MongoDB connector and fill the gap along with Vue implementation to Cube.