Forem: Igor Lukanin

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

Igor Lukanin — Thu, 15 Sep 2022 16:25:54 +0000

Over the last few months, the Observable team has celebrated the first anniversary of the Observable Plot charting library, introduced the Data Table Cell type, and supercharged SQL Cells with summary charts.

Let's see how data notebooks, built with Observable, can work with Cube and fetch data from the recently updated SQL API as well as the REST API.

What is Observable?

Observable is collaborative data canvas that has a few distinctive features compared to other modern data notebooks on the market:

Observable implements the principles of interactive and reactive data exploration popularized by Bret Victor in his visionary talks such as Inventing on Principle and Media for Thinking the Unthinkable.
Observable is based on JavaScript which is not so common in the Python-dominated data notebooks space. Also, it natively works with Observable Plot, an open-source JavaScript library for visualization of tabular data based on D3.js. Not that surprising given that Observable is co-founded by Mike Bostock, the creator of D3.js.
Observable provides a super-rich set of cell types as well as a complete library of tutorials and examples, templates, and notebooks contributed by a community of creators.

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

What is Cube?

Cube is the headless BI platform that sits between your data sources (e.g., data warehouses) and downstream data applications (e.g., data notebooks). Because of its position in the data stack, Cube is able to provide consistent metrics and performance for all your data flowing downstream.

Cube works with all modern data stores, creates a metrics layer with consistent metrics definitions, and delivers them downstream to any data notebook, application, or BI tool via its SQL, REST, or GraphQL APIs.

Extended connectivity. Observable natively supports fetching the data from an API and a number of databases: BigQuery, Snowflake, Postgres, and MySQL. With Cube, you can expand the range of available data sources to almost any data warehouse, query engine, or database (e.g., Athena, ClickHouse, Databricks, or Redshift).

Consistency for Data Explorers

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

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

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

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

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

Defining Consistent Metrics

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

Running Cube. To keep things simple and save time, we'll run fully managed Cube in Cube Cloud. Please proceed to Cube Cloud’s sign up page and follow along the instructions to connect your own data source and generate a data model.

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

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

This is what your deployment in Cube will look like:

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

Key ideas on what we’re seeing above:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Connecting Observable to Cube

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

Using SQL API

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

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

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

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

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

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

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

Now you can run queries against the SQL API:

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

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

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

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

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

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

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

Alternatively, you can import and use Vega-Lite library which is widely popular and used by thousands of data enthusiasts:

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

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

If you'd like to get inspiration for more advanced data visualizations that you can build with Observable, check out the available templates and featured works by the Observable community. Also, consider diving deep into Observable Plot library, the Swiss knife of data visualization in Observable.

Using REST API

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

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

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

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

You'll also need to generate a long-lasting JSON Web token. (If you need a hand, pleae check the Cube docs on JWT generation.) Here's what I've generated for my deployment:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw

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

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

Let's highlight a few things here:

We need to append /load to your API URL specified as a fetch argument.
We need to add the token next to the Bearer part of the Authorization header.
We need to put the Cube query inside the JSON.stringify call.

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

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

Choosing an API to use

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

Cube SQL API:

allows to use Database query cells where you can write concise SQL queries
allows to use Data table cells where you can compose queries in the UI
requires a separate service to set up multitenancy and fetch security context
won't work in published Observable notebooks on a free plan (Observable turns database connections off after a notebook on a free plan is published)

Cube REST API:

requires to use JavaScript cells to write the code to fetch the data which can sometimes get overly verbose
allows to pass security context in a JSON Web Token for multitenancy
will work in published Observable notebooks on a free plan

However, since Observable has very sound pricing plans, compatibility with published notebooks should not be a sole deciding factor here.

Embedding Observable notebooks

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

Notebook. Obviously, you can publish the entire notebook by clicking Publish... on the top of your notebook.

For instance, you can browse the published notebook that we've built during this tutorial. (Note that cells using the SQL API don't work because the notebook is owned by an account on the free plan.)

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

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

Then, you have to choose between several powerful embedding options:

Iframe will give you a code snippet that you can Copy and embed into any HTML document, including a page in your content management system like Gatsby or even another Observable notebook. Wrapped into an iframe tag, the code and styles of embedded content are fully isolated from your application.
With Iframe, you can also Copy URL only and paste it into environments that support the oEmbed standard, e.g., Notion, Medium, or Reddit.
Runtime with JavaScript will give you a code snippet that you can embed into an HTML document as well. The difference is that it will load a tiny JavaScript library and render the cell in place, giving you the freedom to customize how the cell is styled and displayed.
Runtime with React works similarly, the only difference is that your cell is represented as a React component. If you're building a React app, it's the most native way to embed a cell into it.

Wrapping up

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

Would you like to learn more about Cube? Check the documentation and create a free Cube Cloud account today.

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

What are data apps?

Igor Lukanin — Wed, 14 Sep 2022 16:45:59 +0000

Previously, we’ve described the parts of headless BI, taken an in-depth look at the data modeling layer, and explored one use case for headless BI: embedded analytics. This week, let’s take a step back and look at the category of data applications.

But first…

What are data applications?

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

Isn’t this just embedded analytics?

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

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

What are some use cases of data applications?

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

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

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

How are data applications built?

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

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

Building from scratch

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

Data store

Naturally, a data application starts with the data—and the basis of the modern data stack is the cloud data warehouse. This can be a general purpose data warehouse like Snowflake or a real-time tool like Firebolt, ClickHouse, or Materialize.

Headless BI layer

A crucial component of a data app is the headless BI layer. Specifically, a major piece of this is access control integrated with the warehouse’s security controls, because embedded analytics always require multitenancy. A second piece is advanced caching. This is because the data warehouse is a great candidate for a backend, but itself does not support highly concurrent queries with sub-second latency that modern data consumers expect.

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

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

A hybrid presentation layer

For the high customization expected of an embedded data application, and when front-end teams are looped in, different charting libraries can be used. These range from D3 to Chart.js and Highcharts. These most likely will be natively integrated with frontend application frameworks like React or Angular.

Working with a framework

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

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

There also are data application frameworks that are helpful here: tools like Plotly Dash and Streamlit make it possible to turn data scripts into shareable web applications without the need for front-end development.

What's next?

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

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

DeWitt Clause, or Can You Benchmark %DATABASE% and Get Away With It

Igor Lukanin — Thu, 02 Jun 2022 20:36:05 +0000

TL;DR: Please see the list of database vendors who will either praise or punish you for doing a benchmark as well as an entertaining digression into the recent history of computer science.

About 6 months ago, we witnessed what Andy Pavlo wittily characterised as an "old school database benchmark gang war". The timeline of events goes like this:

Databricks, a data lakehouse company founded by the creators of Apache Spark, published a blog post claiming that it set a new data warehousing performance record in 100 TB TPC-DS benchmark. It was also mentioned that Databricks was 2.7x faster and 12x better in terms of price performance compared to Snowflake.
Snowflake, a data warehousing company founded by ex-Oracle and ex-VectorWise experts, responded with a blog post that critically reviewed Databricks' findings, reported different results for the same benchmark, and claimed comparable price/performance to Databricks.
After two days, Databricks followed up with another blog post that confirmed that they stand by the initially reported results.

While this back-and-forth between respected data vendors was informative and amusing, its collateral damage was even more substantial. Databricks shared that they have eliminated the anti-competitive DeWitt clause from their service terms. Snowflake followed up with a similar update to their acceptable use policy.

But what is the DeWitt clause?

More importantly, why would you need to know if a DeWitt clause is included in terms of service in case you'd like to do and publish a performance benchmark? Let's explore.

Backstory of the DeWitt Clause

In 1982, at the very inception of relational databases, David DeWitt, a researcher at the Department of Computer Sciences at the University of Wisconsin-Madison, was working on measuring database performance. His team wrote the Wisconsin Benchmark, tested a number of databases, and published the results.

Apparently, some folks were displeased with the results. Michael Stonebraker, a researcher at the UC Berkeley and co-creator of Ingres, called DeWitt and expressed how upset he was. However, they remained friends anyway as DeWitt recounts: in his talk on YouTube:

Oracle also didn’t perform very well and reacted hastily. According to DeWitt, Oracle CEO Larry Ellison was so displeased that he called the department chair and insisted, "You have to fire this guy.” Oracle also inserted a clause in their terms of use that boiled down to the fact that one can’t publish benchmarks without getting an explicit approval from Oracle.

DeWitt Clause vs. DeWitt Embrace Clause

Nowadays, the DeWitt clause is a common provision in end-user license agreements for proprietary software that prevents from publishing information about the software without an explicit approval from the vendor. Over time, it have become widespread in the software industry and its implications are drastic:

People avoid publishing benchmarks about software products with the DeWitt clause.
Moreover, some won’t even bother doing a benchmark knowing they won’t be able to publish the results.

There are debates whether DeWitt clauses are moral or legal because of the implications above as well as the overall corrosive effect they have on the industry. Once one supplier adds a DeWitt clause, the others can feel that they are at a disadvantage without one. We're lucky it can also work the other way round, like in the Databricks and Snowflake case. However, open source software (OSS) licenses don’t and can't have DeWitt clauses, meaning that the OSS can be legally critiqued, but not their proprietary competitors.

At the same time, some vendors do quite the opposite to the inclusion of a DeWitt clause. Instead, they change their license to the DeWitt Embrace Clause meaning that if you disclose benchmark results for them, you automatically allow them to do the same with you. In a way, DeWitt Embrace Clause is like copyleft for the DeWitt Clause.

Can You Benchmark %DATABASE%?

Now, do you wonder whether some vendor has or hasn't a DeWitt clause?

Shortly after the Databricks and Snowflake debate, Mark Callaghan, an ex-MongoDB and ex-Rockset database expert, checked if some database and public cloud vendors have DeWitt clauses in their terms of use.

In this blog post, I try to extend his research and maintain an ever-green list of database and similar vendors with regard to the DeWitt clause. (If you spot any inaccuracy, which is totally possible, please get in touch via igor@cube.dev, I'll be happy to update the post.)

Last updated: June 2, 2022.

😇 Open-source vendors (without the DeWitt clause)

Open source licenses usually grant users permission to use open source software for any purpose. So, by definition, open source software can't contain DeWitt clauses (because if it did, it wouldn't be open source).

Yes, you can benchmark any of these tools:

Apache Drill, Druid, Flink, Hive, Kafka, Spark
appwrite
ClickHouse (they even maintain a list of benchmarks on GitHub)
CockroachDB (licensed under BSL, not OSI-approved)
Dremio
Elasticsearch (licensed under SSPL, not OSI-approved)
ksqlDB
Materialize (licensed under BSL, not OSI-approved)
MongoDB (licensed under SSPL, not OSI-approved)
PostgreSQL
Presto
Redpanda (licensed under BSL, not OSI-approved)
QuestDB
TimescaleDB
Trino

Also, some open-source vendors collaboratively maintain benchmarking suites such as Time Series Benchmark Suite to help choose the best tools for particular workloads.

😇 Vendors without the DeWitt clause

Cloud vendors can restrict you from benchmarking their services but sometimes they choose not to have the DeWitt clause.

Yes, you can benchmark any of these services:

Ahana Cloud
Altinity Cloud
appwrite Cloud (not launched to GA yet)
ClickHouse Cloud (not launched to GA yet)
Confluent Cloud
Firebolt
Materialize Cloud
MongoDB Cloud
QuestDB Cloud (not launched to GA yet)
Redpanda Cloud
Snowflake
Starburst Galaxy
Supabase
Timescale Cloud

However, many of these cloud vendors disallow abusive benchmarking in their acceptable use policies. So be careful and please don't get banned.

🙃 Vendors with the DeWitt Embrace clause

Some cloud vendors permit you to benchmark their service but require reciprocity: you must make the benchmark reproducible and allow benchmarking of your own service or tool in response.

Yes, you can benchmark any of these services and probably get away with it:

Amazon Web Services, including Athena, Aurora, and Redshift. You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.
CockroachDB Serverless. You can only disclose benchmarking tests if you (i) provide us the necessary information to replicate the tests, (ii) disclose the methodology for the benchmarking tests along with the results, and (iii) allow us to run our own benchmarking tests against your products and services at our discretion.
Databricks. You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Platform Services and may disclose the results of the Benchmark other than for Beta Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Platform Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.
SingleStore. Customer may perform industry standard benchmarks, comparative tests or evaluations (each, a "Performance Report") of the Managed Service. If Customer performs or discloses, or directs or permits any third party to perform or disclose, any Performance Report of any of the Managed Service, Customer (i) will include in any disclosure, and will disclose to SingleStore all information necessary to replicate such Performance Report and the data from the Performance Report, and (il) agree that SingleStore may perform and disclose the results of the Performance Report of Customer's products or services, irrespective of any restrictions on Performance Report.

🤬 Vendors with the DeWitt clause

These vendors chose to restrict benchmarks with DeWitt clauses.

No, you can't benchmark any of these tools and get away with it:

Cloudera Data Platform. Restrictions. Customer may not: [...] access or use the Services for purposes of monitoring availability, performance or functionality of the Services, or for any benchmarking or competitive purposes.
Decodable. Restrictions. Customer may not, and may not cause or permit others to: [...] disclose results of any benchmark tests or performance tests of the Decodable services hereunder without Decodable’s prior written consent.
Dremio Cloud. Restrictions. Customer shall not, and shall not cause or allow any Authorized User or third party to: [...] except with Dremio’s prior written permission, publish any performance or benchmark tests or analysis relating to Dremio Cloud.
Elastic Cloud. Restrictions [...] You shall not: [...] access or use any Cloud Service for purposes of monitoring its availability, performance or functionality, or for any other benchmarking or competitive purposes, including, without limitation, for the purpose of designing and/or developing any competitive services.
Google Cloud Platform, including BigQuery & Firebase. Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if it (a) obtains Google's prior written consent, (b) provides Google all necessary information to replicate the Tests, and (c) allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test of the Services or (ii) disclose the results of any such Test.
InfluxDB Cloud. Restrictions. Customer will not: [...] publish or provide any benchmark, comparison or performance test results.
MariaDB SkySQL and MariaDB Xpand. Benchmarking. Customer may perform benchmarks or comparative tests or evaluations (each, a "Benchmark") of the SkySQL Services. However, Customer must obtain MariaDB's prior written approval to disclose to a third party the results of any Benchmark of the SkySQL Services.
Microsoft SQL Server. BENCHMARK TESTING. You must obtain Microsoft’s prior written approval to disclose to a third party the results of any benchmark test of the software.
Oracle. RESTRICTIONS. [...] You may not: [...] disclose results of any Program benchmark tests without Oracle's prior written consent.
PlanetScale. Customer will not, and will ensure that its End Users do not: [...] access any portion of the Product for the purpose of building a similar or competitive product or service, or monitor the Product for any benchmarking or competitive purpose.

😶 Vendors for which the author didn't find the data

I wasn't able to identify where a few vendors stand with regard to the DeWitt clause:

If you can provide any pointers, please get in touch via igor@cube.dev, I'll be happy to update the post. Also, please reach out if you'd like more databases to be featured here.

What about Cube?

Cube is an open-source headless BI platform that integrates with a lot of data sources: cloud data warehouses, query engines, and databases. Many of them are designed for low latency and some of them can provide high concurrency. However, sometimes they don't; you can read our blog post about that.

In any case, Cube will have no problem delivering your data, with sub-second latency and high concurrency, to any data consumer out there: BI tools, data notebooks, front-end apps, etc. Thanks to Cube Store, Cube's REST, GraphQL, and SQL APIs are able to respond to any request within 200-300 milliseconds and allow for 100+ concurrent requests—and that can scale.

Please explore how Cube integrates with databases and other tools in the data space in our blog. Also, don't hesitate to try Cube Cloud, join our large Slack community, and give Cube a star on GitHub 🌟

Building dashboards over a semantic layer with Superset and Cube

Igor Lukanin — Thu, 14 Apr 2022 18:13:54 +0000

What can be more troubling and horrifying for an Apache Superset user than a scarlet error message revealing that the underlying dataset is broken and charts aren’t going to provide any insights anytime soon? Sigh.

Indeed, modern data pipelines consist of many moving—and sometimes fragile—parts. Superset, the Swiss knife of data exploration and visualization, deserves rock-solid data sources but in the real world, upstream changes are inevitable. They may be connected to immature technology in the data stack as well as ever-changing business requirements and balkanized ownership of data pipelines.

Within minutes, you can find conversations on Superset’s Slack or GitHub revolving around identifying broken dashboards and finding ways to reduce the impact on users:

I’m happy to share that a solution exists. You can use Cube together with Superset and define your datasets over a data modeling layer (or a semantic layer) rather than raw data:

In this tutorial, we’ll explore how Cube, a headless BI platform, can be used together with Superset to build reliable and robust data visualizations on top of a semantic layer. You’ll learn techniques to prevent your dashboards from breaking—and tools that would notify you about incompatible upstream changes in data before your users even notice.

What is Headless BI?

Cube is an open-source headless BI platform with nearly 13,000 stars on GitHub to date. It can be broken down into four layers that are complemented by a head or multiple heads like Apache Superset, another BI tool, or a front-end application with embedded analytics. Here are the layers:

Data modeling. Contains consistent metrics definitions and provides a view of the upstream data in terms of measures and dimensions. Makes data semantic.
Access control. Safeguards data access and enables row-level security, role-based access, and multitenancy. Makes data secure.
Caching. Accelerates data access and makes end-user applications responsive and rewarding to use. Makes apps fast.
APIs. Exposes data to downstream applications via REST, GraphQL, or SQL API. Makes data accessible.

You can configure Cube to connect to any database, declaratively define your metrics, and instantly get an API that you can use with Superset or many other BI tools.

In case of any upstream change (be it a renamed column, a dropped table, or a migrated data source), you can update the data model in Cube and keep hundreds of charts and dozens of dashboards in your Superset installation intact. Sounds refreshing, huh?

Let’s try Superset with Cube and see how it works!

Running Cube with Superset

To keep things simple and save time, we'll run both Cube and Superset in fully managed environments: Cube Cloud and Preset Cloud. Note that both tools have generous free tiers, e.g., you can use Preset Cloud forever for free on the Starter plan. (If you'd like to run Cube or Superset locally with Docker, please see Cube and Supeset instructions, respectively.)

Running Cube. First, please proceed to Cube Cloud’s sign up page and fill in your details. Note that Cube Cloud supports signing up with your GitHub account. Within a few seconds you will be taken to your account where you can create your first Cube deployment:

Proceed with providing a name for your deployment, selecting a cloud provider and a region:

At the next step, choose Create to start a new Cube project from scratch. Then, pick Postgres to proceed to the screen where you can enter the following credentials:

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

Cube will connect to a publicly available Postgres database that I've already set up.

The last part of configuration is the data schema which declaratively describes the metrics we'll be putting on the dashboard. Actually, Cube can generate it for us! Pick the top-level public database from the list and select only the “orders” table:

In a while, your Cube deployment will be up and running:

Defining metrics. Please navigate to the "Schema" tab. You will see the Orders.js file under the "schema" folder. Let’s review it.

This file defines the metrics within the "Orders" cube. It is different from the one in Cube Cloud, but we'll take care of that later.

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

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

    // 'processing', 'shipped', or 'completed'
    status: {
      sql: `status`,
      type: `string`
    },

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

    shippedAt: {
      sql: `shipped_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeShipped: {
      sql: `EXTRACT(DAYS FROM (shipped_at - created_at))`,
      type: `number`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeCompleted: {
      sql: `EXTRACT(DAYS FROM (completed_at - created_at))`,
      type: `number`
    }
  },

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

    // Calculated, uses a dimension reference
    avgDaysBeforeShipped: {
      sql: `${daysBeforeShipped}`,
      type: `avg`
    },

    // Calculated, uses a dimension reference
    avgDaysBeforeCompleted: {
      sql: `${daysBeforeCompleted}`,
      type: `avg`
    }
  },
});

Key learnings here:

the cube is a logical, domain-level entity that includes measures and dimensions
using the sql statement, this cube is defined over the entire public.line_items table; actually, cubes can be defined over arbitrary SQL statements that select data
dimensions (qualitative data features) are defined over textual, temporal, or numeric columns in the dataset
measures (quantitative data features) are defined as aggregations (e.g., count, avg, etc.) over columns or dimensions in the dataset
you can define complex measures and dimensions with custom sql statements or references to other measures and dimensions

Development mode. Now, let's update the schema file in Cube Cloud to match the contents above. First, click Enter Development Mode to unlock the schema files for editing. This essentially creates a "fork" of the Cube API that tracks your changes in the data schema.

Navigate to Orders.js and replace its contents with the code above. Then, save your changes by clicking Save All to apply the changes to the development version of your API. You can apply as many changes as you wish, but we're done for now. Click Commit & Push to merge your changes back to the main branch:

On the "Overview" tab you will see your changes deployed:

Now you can explore the metrics on the "Playground" tab:

When you’re ready, head over to the “Overview” tab, click “Deploy SQL API” and use the “How to connect your BI tool” link shortly thereafter.

You will see the credentials that we’ll use to connect the metrics store that we’ve built with Cube to Superset in a few moments. How?

Running Superset. Please proceed to Preset’s sign up page and fill in your details. Note that Preset Cloud supports signing up with your Google account. Within a few seconds you will be taken to your account with a readily available workspace:

Switching to that workspace will reveal a few example dashboards that you can review later.

Connect Superset to Cube. Navigate to Data / Databases via the top menu, click + Database, select MySQL, and fill in the credentials from your Cube Cloud instance — or use the credentials below:

Host: green-yak.sql.aws-us-east-2.cubecloudapp.dev
Port: 3306
Database name: db
Username: cube@green-yak
Password: 82e26064349b19c340f8b074dfcee4af
Display name: Cube Cloud (it's important)

You can press Connect now.

Define the datasets. Navigate to Data / Datasets via the top menu, click + Dataset, and fill in the following credentials:

Database: Cube Cloud (the one we've just created)
Schema: db
See table schema: Orders

You can press Add now. Then, hover over the newly added dataset and click the pencil icon in the “Actions” column. We’ll need to make sure Superset recognizes a couple of measures. First, navigate to the “Columns” tab and deselect “Is dimension” for avgDaysBeforeShipped and avgDaysBeforeCompleted:

Then, switch to the “Metrics” tab and add these two:

Metric: avgDaysBeforeShipped; SQL expression: AVG(avgDaysBeforeShipped)
Metric: avgDaysBeforeCompleted; SQL expression: AVG(avgDaysBeforeCompleted)

Press Save in the end. Whew, we’re all set! Now, let’s display the data on a chart.

Building a dashboard. Navigate to Charts via the top menu, click + Chart, and use the following configuration for your first chart:

Choose a dataset: Orders (the one we've just created)
Choose a chart type: Table

When you’re done, click Create new chart. Feel free to experiment with parameters on this screen and click Run Query to explore the data. To proceed, please set up a chart like this:

Visualization type: Table
Time column: createdAt
Time grain: any value
Group by: status
Metrics: COUNT(*), avgDaysBeforeShipped, avgDaysBeforeCompleted

Here’s the data table you’ll get:

You can click Save to give your chart a name and add it to a new dashboard:

Looks good? We now have a dashboard in Superset displaying a chart that queries data from Cube using the measures and dimensions in Cube’s data model.

Cube translates queries coming from Superset into queries to the upstream data source. What would happen if anything happens upstream? Here’s when all the fun starts!

Mishap 1: Column renamed or removed

Let’s say you (or your users) open the dashboard one day and behold this orange introduction into a few less-than-productive work hours. Apparently, the “status” column is not present in the data source anymore and a few uninformed business decisions might be made (or put on hold) until your team debugs the circumstances of the removal.

What can be done to mitigate this? Well, let’s reproduce this situation first. You can do so by navigating to Cube Cloud, switching to the Development Mode, and editing the Orders.js file. Just change the SQL expression to reference orders_wo_status table that, obviously enough, has exactly the same data as the orders table but the “status” column is removed:

Then, you can save and commit the changes:

After the Cube Cloud deployment is redeployed in a few moments, you can refresh your dashboard in Superset to switch it into the annoying orange state. (If the dashboard doesn’t break, try pressing the button with three dots in the top right corner and requesting the dashboard to refresh.)

Unbreaking the dashboard, a simple way. Let’s say that the “status” column was removed because it’s redundant: its values can be calculated using the values in “created_at”, “shipped_at”, and “completed_at” columns. Then, how to fix the dashboard after the column removal?

One way to do so is to edit the dataset in Superset. You can delete the missing column and add it back as a calculated one using Superset’s semantic layer. (If you’d like to dig deeper, check out this post in Preset’s blog.)

However, there are a few downsides: first, this calculated column wouldn’t be visible in Superset’s SQL Editor where you might run SQL queries to perform data exploration; second, the calculation wouldn’t propagate upstream. If Superset is not the only tool you use (or would ever use) to explore your data, it’s better to apply the fix upstream rather than make duplicated fixes all over different tools.

Unbreaking the dashboard, a better way. An alternative way is to replace the “status” column with a calculation in Cube’s data model. Then, Superset and all other tools would work as if that column was never removed and your dashboard never broke.

You can go back to Cube Cloud, switch to the Development Mode, and edit the Orders.js file once again. Let’s replace the “status” dimension with the following code snippet:

    status: {
      case: {
        when: [
          { sql: `completed_at IS NOT NULL`, label: `completed` },
          { sql: `shipped_at IS NOT NULL`, label: `shipped` },
        ],
        else: { label: `processing` },
      },
      type: `string`,
    },

You can save and deploy your changes. Note that we’re using a case dimension that provides some syntactic sugar over a regular CASE... WHEN... THEN... ELSE... END SQL statement and provides the ability to reference other dimensions:

Congratulations! Without any changes in Superset, your dashboard is back to a healthy state:

Moreover, if you ever need to introduce a more complex calculation to any dimension or measure, you can always do that in Cube Cloud and still won’t need to change anything in tools that consume data, including Superset.

Mishap 2: Table altered or removed

Let’s say you (or your users) open the dashboard and see this unfortunate orange notification once again. Apparently, the “shipped_at” column doesn’t exist:

The reason is that developers of the upstream system decided to implement the event sourcing design pattern, meaning that there’s no “orders” table anymore. Instead, there’s the “orders_events” table that, obviously enough, has exactly the same data but completely reshaped as a stream of events related to orders’ status changes. In that table, every row represents only partial information about an order and the “timestamp” column replaced previously existing “created_at”, “shipped_at”, and “completed_at” columns. Here’s the DDL statement:

create table orders_events
(
    order_id  integer,
    user_id   integer,
    status    text,
    timestamp timestamp
);

Wanna try that yourself? You can do so by navigating to Cube Cloud, switching to the Development Mode, and editing the Orders.js file. Just change the SQL expression to reference orders_events table:

After the Cube Cloud deployment is redeployed, you can refresh your dashboard in Superset to switch it into the annoying orange state. (If the dashboard doesn’t break, try pressing the button with three dots in the top right corner and requesting the dashboard to refresh.)

Unbreaking the dashboard, a complex way. One way to do so is to introduce a data transformation tool to your data pipeline (assuming you don’t use one) that would transform the data back into the desired state.

However, there are a few downsides: first, this is more like a month-long project than a quick fix to the data pipeline; second, it might bring unnecessary and unwanted complexity to your technology stack. (Note that if you actually use dbt or dbt Metrics, Cube has an integration.)

Unbreaking the dashboard, a better way. An alternative way is to update Cube’s data model to transform data back to the desired state. Then, Superset and all other tools would work as if the table was never removed and your dashboard never broke.

You can go back to Cube Cloud, switch to the Development Mode, and edit the Orders.js file once again. Let’s replace the source code of the file with the following code snippet:

cube(`Orders`, {
  sql: `
    WITH bareOrders AS (
        SELECT order_id AS id, user_id
        FROM public.orders_events
        GROUP BY order_id, user_id
    ),

    orders AS (
        SELECT
          bareOrders.*,
          (CASE WHEN completed.timestamp IS NOT NULL THEN completed.status ELSE (CASE WHEN shipped.timestamp IS NOT NULL THEN shipped.status ELSE processing.status END) END) AS status,
          COALESCE(processing.timestamp, shipped.timestamp, completed.timestamp) AS created_at,
          COALESCE(shipped.timestamp, completed.timestamp) AS shipped_at,
          completed.timestamp AS completed_at
        FROM bareOrders
        LEFT JOIN public.orders_events AS processing
          ON bareOrders.id = processing.order_id AND processing.status = 'processing'
        LEFT JOIN public.orders_events AS shipped
          ON bareOrders.id = shipped.order_id AND shipped.status = 'shipped'
        LEFT JOIN public.orders_events AS completed
          ON bareOrders.id = completed.order_id AND completed.status = 'completed'
    )

    SELECT * FROM orders
  `,

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

    // 'processing', 'shipped', or 'completed'
    status: {
      sql: `status`,
      type: `string`
    },

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

    shippedAt: {
      sql: `shipped_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeShipped: {
      sql: `EXTRACT(DAYS FROM (shipped_at - created_at))`,
      type: `number`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeCompleted: {
      sql: `EXTRACT(DAYS FROM (completed_at - created_at))`,
      type: `number`
    }
  },

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

    // Calculated, uses a dimension reference
    avgDaysBeforeShipped: {
      sql: `${daysBeforeShipped}`,
      type: `avg`
    },

    // Calculated, uses a dimension reference
    avgDaysBeforeCompleted: {
      sql: `${daysBeforeCompleted}`,
      type: `avg`
    }
  },
});

As you can see, Cube’s data model is flexible enough to allow cubes to be defined over arbitrary SQL expressions that can contain common-table expressions (CTE), unions, joins, etc. The rule of thumb for structuring the data model in Cube is that cubes, measures, and dimensions should make sense at the logical, domain level. And if your data is stored differently, you should feel free to reshape it in Cube’s data model.

You can save and deploy your updates in Cube Cloud. Again, without any changes in Superset, your dashboard is back to a healthy state:

Mishap 3: Data source changed

Let’s say something rare yet impactful happened: the team decided to upgrade the data pipeline, introduce new tools, etc. Eventually, the data will move from Postgres to BigQuery, new ETL and data transformation tools will be used. What can we do to prevent the dashboards from breaking or avoid rebuilding the dashboards in Superset?

Since we’ve already connected Superset to Cube, we can sort things out on Cube’s side. Just like Superset, Cube supports numerous data sources and a Cube instance can use any subset of these data sources at the same time.

Unbreaking the dashboard. Here’s how you can configure Cube to connect to Postgres and BigQuery and update the data model so the orders data is read from BigQuery.

In Cube Cloud, you’ll need to switch to the Development Mode. First, let’s update Orders.js to match the following snippet. Check out the new dataSource option and the updated definitions for daysBeforeShipped and daysBeforeCompleted that use BigQuery-specific TIMESTAMP_DIFF function instead of Postgres-specific EXTRACT function:

cube(`Orders`, {
  dataSource: 'bigquery',

  sql: `SELECT * FROM superset_examples_2.orders`,

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

    // 'processing', 'shipped', or 'completed'
    status: {
      sql: `status`,
      type: `string`
    },

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

    shippedAt: {
      sql: `shipped_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeShipped: {
      sql: `TIMESTAMP_DIFF(shipped_at, created_at, DAY)`,
      type: `number`
    },

    // Calculated, uses SQL functions and column references
    daysBeforeCompleted: {
      sql: `TIMESTAMP_DIFF(completed_at, created_at, DAY)`,
      type: `number`
    }
  },

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

    // Calculated, uses a dimension reference
    avgDaysBeforeShipped: {
      sql: `${daysBeforeShipped}`,
      type: `avg`
    },

    // Calculated, uses a dimension reference
    avgDaysBeforeCompleted: {
      sql: `${daysBeforeCompleted}`,
      type: `avg`
    }
  }
});

Next, let’s edit the cube.js file to match the following snippet:

// Cube.js configuration options: https://cube.dev/docs/config

const PostgresDriver = require('@cubejs-backend/postgres-driver');
const BigQueryDriver = require('@cubejs-backend/bigquery-driver');

module.exports = {
  contextToAppId: ({ dataSource }) => dataSource,

  dbType: ({ dataSource }) => {
    return dataSource === 'bigquery'
      ? 'bigquery'
      : 'postgres';
  },

  driverFactory: ({ dataSource }) => {
    return dataSource === 'bigquery'
      ? new BigQueryDriver()
      : new PostgresDriver();
  }
};

Note the driverFactory extension point where the database selection happens: for cubes with the bigquery data source, BigQuery will be used, and Postgres will be used otherwise. Also note that we don’t need to specify the credentials for Postgres and BigQuery explicitly because they are securely stored in Cube Cloud’s settings. Not sharing BigQuery credentials with you because who would do that?

With these changes in Cube Cloud and without any changes in Superset, your dashboard will be back to a healthy state:

Oh, and there’s one more thing! Cube also supports data federation meaning that you can join data from different data sources, e.g., from Snowflake and Athena, if needed. To implement that, you can use pre-aggregations and declaratively set up a rollupJoin pre-aggregation that would seamlessly join data from multiple data sources under the hood.

Cherry on top: Alerts

In all examples above it was either us or our users who were to discover the broken dashboard. In production scenarios, that definitely should not happen. What can we do to get a notification before users see a broken dashboard?

Since we’ve decided to run Cube in Cube Cloud, we can use the Alerts feature there (not to be confused with Superset’s Alerts and Reports feature which is a convenient way to send reports with dashboard data to email). Alert conditions are checked every minute. If an alert is triggered, configured recipients will get email notifications.

You can set up alerts for events like database unavailability or pre-aggregations build errors. In production scenarios, Cube is always used with pre-aggregations. It means that if a database becomes unavailable or the data within the database changes in a way that Cube can’t rebuild a pre-aggregation, you will instantly receive a notification.

Configuring alerts. You can go to Cube Cloud, click on your avatar in the top right corner, click “Alerts” to get to the Alerts page, then click New Alert to set up one.

Let’s go with almost default settings:

Event type: Pre-aggregation build failure
Deployments: All
Send alerts to: All users on this account

You can click Add now.

Testing alerts. The simplest way to test this newly setup alert is to configure pre-aggregations and then break the data model, e.g., change the table name to a wrong one.

You can go back to Cube Cloud, switch to the Development Mode, and edit the Orders.js file once again. Let’s update the very beginning of the file in this fashion:

cube(`Orders`, {
  dataSource: 'bigquery',

  // An utterly wrong table name
  sql: `SELECT * FROM superset_is_not_great`,

  // A simple pre-aggregation scheduled to refresh every hour
  preAggregations: {
    main: {
      refreshKey: {
        every: '1 hour'
      },
      dimensions: [
        status
      ]
    }
  },

// Dimensions and measures go below

After you deploy your changes, Cube Cloud would try to build the pre-aggregation, it probably wouldn’t find the table with this wicked name and an email notification would hit your inbox:

If you don’t have thousands of active Supserset users, chances are that you’ll know about a broken dashboard way ahead of your users. (Superset’s built-in caching mechanism could also help serve unbroken dashboards to users for some time.)

Wrapping up

Thanks for following this tutorial. My sincere hope is that now you have a much better understanding of these topics:

How Headless BI tools (and Cube in particular) can be helpful to build a think semantic layer and manage the data model upstream of Superset.
How Cube can help make your Superset dashboards robust and unbreakable—and let you know if anything breaks in advance.

Please don't hesitate to like and bookmark this post, write a comment, and give a star to Cube and Superset on GitHub. I hope these tools would be a part of your toolkit from now on.

Good luck and have fun!

Combining dbt Metrics with API, Caching, and Access Control

Igor Lukanin — Wed, 16 Mar 2022 17:32:43 +0000

If you're using dbt to transform data in your data warehouse and you're excited about dbt Metrics feature, here's how you can add caching, access control, and a variety of APIs (such as REST, GraphQL, or SQL) on top.

dbt quickly became the major part of the modern data stack and the favorite tool of data practitioners. As Cube community grows, especially among data engineers, we more often see dbt being used upstack from Cube to define transformations and data model.

After a community-driven feature proposal for dbt Metrics and an insightful discussion that followed, our community requested an integration with dbt that would allow keeping metrics definitions in dbt as the single source of truth while leveraging the rest of Cube.

dbt Metrics meet Cube, the headless BI platform

At Cube, we are building headless business intelligence, and want to make data accessible and consistent across every application, ranging from embedded analytics to internal dashboarding and reporting tools. It’s an amazing opportunity for us to integrate with dbt Metrics, enhance it with Cube’s caching and access control, and deliver to every application via our SQL, REST, and GraphQL APIs.

Today, we are happy to announce an integration with dbt Metrics. Cube can now read metrics from dbt, merge them into Cube’s data model, provide caching and access control, and expose metrics via our APIs to downstream applications.

In the tutorial below we’ll use dbt to define metrics, Cube to apply caching (and get a ~8x performance boost), provide access control, and expose data to Apache Superset via Cube’s SQL API and to other tools and applications via our REST API or GraphQL API.

Building a metrics layer with dbt Metrics

Everything starts with a great dataset, so I’ve taken GitHub Activity Data, a snapshot of more than 2.8 million (~10 %) open-source repositories on GitHub, publicly available on Google Cloud Platform. As of March 2022, it contains information about 257 million unique commits, their authors, messages, files, etc.

Let’s say we’re interested in learning all about productivity of individuals and teams expressed in the number of commits and commit message lengths:

We would start with defining a dbt model like this in commits.sql:

SELECT
    TIMESTAMP_SECONDS(`author`.`time_sec`) AS timestamp,
    `author`.`email` AS author_id,
    `author`.`name` AS author_name,
    REGEXP_EXTRACT(`author`.`email`, r'@(.+\..+)$') AS author_domain,
    LENGTH(subject) AS subject_length,
    LENGTH(message) AS message_length
FROM `bigquery-public-data.github_repos.commits`

The next step would be to add the dbt metrics definitions into schema.yml:

version: 2

metrics:
  - name: commits_count
    label: Commits Count
    model: ref('commits')
    description: "A count metric for commits"

    type: count

    timestamp: timestamp
    time_grains: [day, week, month, year]

    dimensions:
      - author_domain
      - author_name

  - name: users_count
    label: Users Count
    model: ref('commits')
    description: "A count metric for users"

    type: count_distinct
        sql: author_id

    timestamp: timestamp
    time_grains: [day, week, month, year]

    dimensions:
      - author_domain

Defined metrics will be available for programmatic introspection via Metadata API and manual exploration in GraphiQL IDE:

Exposing dbt Metrics via an API

As a headless BI platform, Cube consists of four logical layers: metrics, acceleration, access control, and API. Our metrics layer is able to read metrics definitions from dbt via the Metadata API and translate them into equivalent Cube data model.

You can get started with Cube within minutes in Cube Cloud or set up Cube locally with Docker. You’ll need to provide the credentials for your data warehouse; there are plenty of data sources supported by Cube.

Next, connecting Cube to dbt is as trivial as adding this code snippet to your data model:

import dbt from '@cubejs-backend/dbt-schema-extension'
import { dbtJobId, dbtApiKey } from '../config'

asyncModule(async () => {
  await dbt.loadMetricCubesFromDbtCloud(dbtJobId, dbtApiKey)
})

In an instant, you’ll be able to explore the data in Cube Playground:

Cube will take care of SQL generation and querying the data in your data warehouse that was previously transformed with dbt:

SELECT
  `github_commit_stats_commits`.author_domain `author_domain`,
  `github_commit_stats_commits`.author_name `author_name`,
  COUNT(*) `commits_count`
FROM
  `cube-devrel-team`.`dbt_prod`.`commits` AS `github_commit_stats_commits`
WHERE
  `github_commit_stats_commits`.author_domain IN (?, ?)
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10000

This gives you the luxury of using a plenty of API flavors provided by Cube:

SQL API — to expose the metrics to popular BI tools, e.g., Apache Superset (check this tutorial to see it in action)
REST API — to expose the metrics to even more BI tools (e.g., Appsmith, Bubble, or Retool) or integrate Cube into your report-generation or machine learning pipeline
GraphQL API and integrations with popular front-end frameworks such as React, Angular, and Vue — for embedded analytics in custom-built applications (check one of many tutorials that we have)

Fetching data via the REST API is as simple as sending a JSON-encoded query and parsing the JSON-encoded result set. Cube provides convenient JavaScript client libraries for that purpose, but even a curl or wget call will work just great:

Bringing the same data into Apache Superset or another data visualization tool via Cube’s SQL API requires just a few clicks in the UI that will yield an equivalent SQL query and result set:

Take a closer look! Superset generates a fairly straightforward SQL query (shown on the screenshot above) and sends it to Cube. Actually, you can write your custom SQL queries, like the one below, where you can reference measures and dimensions from your data model. Curious what MEASURE(commitsCount) is? Well, we see a lot of value in valid SQL, that why you can list your dimensions in the GROUP BY statement and enclose your measures using the MEASURE aggregate function (if your BI tool of choice would generate something like COUNT instead of MEASURE, Cube will readily accept that, too):

SELECT authorDomain, MEASURE(commitsCount)
FROM db.`GithubCommitStatsCommits`
WHERE authorDomain IS NOT NULL
GROUP BY authorDomain

Now we’ve learned how dbt Metrics can be exposed to other tools via various APIs provided by Cube. Next, let’s explore how dbt users can leverage Cube’s acceleration and access control layers.

Accelerating dbt Metrics with pre-aggregations

Cube’s acceleration layer serves as a smart cache on top of the metrics layer.

Based on the configuration provided, Cube will pre-emptively fetch data from the data warehouse, pre-aggregate it into analytical rollups, and store them in Cube Store, a custom-built distributed data store, in a columnar format. In most cases, Cube Store enables Cube to fulfill API requests within 300 ms, and allows for concurrencies up to 100 QPS.

You can see on the Apache Superset screenshot above that an unaccelerated request took more than 2 seconds. Of course, if we run the same request once again, we’ll get the result mush faster because it will be cached by Superset. Let’s see what can be done so any request would yield a sub-second response at all times.

The Cube way to achieve this is to use pre-aggregations. We’ll extend the data model with a new cube (think, “a namespace for metrics”) containing the same metrics and a single pre-aggregation:

cube('GithubCommitStatsCommitsCached', {
  extends: GithubCommitStatsCommits,

  preAggregations: {
    main: {
      measures: [ commitsCount ],
      dimensions: [ authorDomain, authorName ],
      timeDimension: timestamp,
      granularity: 'day',
      partitionGranularity: 'year'
    }
  },
})

This caching configuration instructs Cube to use the main pre-aggregation for all queries that match it, i.e., contain a subset of listed measures and dimensions. It also says that day is the minimum time grain we’re interested in using. On top of that, it advises Cube to split the pre-aggregated data into partitions (one per year) and distribute these partitions across all Cube Store nodes so they all can be used in parallel to fulfil queries.

That’s basically it. Cube will seamlessly build the pre-aggregation, store it in Cube Store, and transparently dispatch queries that match it against Cube Store rather that the original data source, e.g., BigQuery. Here’s a side by side comparison of the query hitting BigQuery (1.8 s) ⚠️ and the same query fulfilled by a pre-aggregation in Cube Store (215 ms) ⚡

Also note that, depending on your usage patterns, not hitting your cloud data warehouse for every client-side query might not only improve the performance of your data application but allow for a substantial total cost reduction.

That’s how Cube’s acceleration layers works. What about access contol?

Securing dbt Metrics and implementing multitenancy

Cube’s access control layer lets you manage who is able to access data and which data it is. Subsequently, Cube has built-in mechanisms for row-level security and multitenancy.

You can mandate that every query need to be accompanied by a security context (think “securely signed set of credentials and meta data”) that identifies a user or an application running a query. Cube allows you to validate the security context and the query, possibly amending the contents of the query to enforce access control rules.

We’ll use the queryRewrite extension point to require certain meta data to be provided with every query and used in a mandatory filter. With this configuration, no query can be run “unfiltered”:

module.exports = {
  queryRewrite: (query, { securityContext }) => {
    if (!securityContext.domain) {
      throw new Error("Please specify the domain");
    }

    query.filters.push({
      member: 'GithubCommitStatsCommitsCached.authorDomain',
      operator: 'equals',
      values: [ securityContext.domain ],
    });

    return query;
  }
};

If anyone tries, they’ll get an error message. However, with a security context provided (see its JSON representation below), the query will yield the properly filtered results according to access control rules:

And that’s the shortest demo of how Cube’s access control layers works.

Next steps for dbt users

Please feel free to check out the full source code of dbt models and the Cube project shown above on GitHub. Also, don’t hesitate to give Cube a star on GitHub as well as to follow us on Twitter or LinkedIn 🤘

If you’re a dbt user and you haven’t tried dbt Metrics, please do. You’ll be able to abstract your metrics definitions from the presentation layer and keep them in the single source of truth. Note that, as of March 2022, dbt Metrics are still an experimental feature, so if things doesn’t work as expected, please provide your feedback and find the time to file issues in dbt’s GitHub repository. That is much appreciated.

Also, if you see the value in integrations with BI tools and custom-built applications as well as query acceleration and access control that are built into Cube, please follow the steps outlined in this tutorial and share your success or ask questions in Cube’s Slack community.

Building a metrics dashboard with Superset and Cube

Igor Lukanin — Tue, 30 Nov 2021 17:46:32 +0000

TL;DR: In this tutorial, we'll learn how to build a metrics dashboard with Apache Superset, a modern and open-source data exploration and visualization platform. We'll also use Cube, an open-source metrics store, as the data source for Superset that will enable our dashboards to load in under a second — quite the opposite to what you'd usually expect from a BI tool, right?

Here's how the end result will look like:

Now we're all set. Let's see what's on the shelves of this metrics store 🏪

What is Apache Superset?

Apache Superset is a data exploration and visualization platform or, in layman's terms, a tool that you can use to build dashboards with charts for internal users. Born at a hackathon at Airbnb back in 2015, with more than 41,000 stars on GitHub, now it's a leading open-source business intelligence tool.

Superset has connectors for numerous databases, from Amazon Athena to Databricks to Google BigQuery to Postgres. It provides a web-based SQL IDE and no-code tools for building charts and dashboards.

Running Superset. Now let's run Superset to explore these features. To keep things simple, we'll run a fully managed Superset in Preset Cloud, where you can use it forever for free on the Starter plan. (If you'd like to run Superset locally with Docker, please see these instructions.)

First, please proceed to the sign up page and fill in your details. Note that Preset Cloud supports signing up with your Google account. Within a few seconds you will be taken to your account with a readily available workspace:

Switching to that workspace will reveal a few example dashboards that you can review later.

Now, let's navigate to Data / Databases via the top menu and... Oops! We need a metrics store to connect to. Let's see how Cube can help us build one.

What is Cube?

Cube is an open-source metrics store with nearly 12,000 stars on GitHub to date. It serves as a single source of truth for all metrics and provides APIs for powering BI tools and building data apps. You can configure Cube to connect to any database, define your metrics via a declarative data schema, and instantly get an API that you can use with Superset or many other BI tools.

Running Cube. Similarly to Superset, let's run a fully managed Cube in Cube Cloud that has a free plan as well. (If you'd like to run Cube locally with Docker, please see these instructions.)

First, please proceed to the sign up page and fill in your details. Note that Cube Cloud supports signing up with your GitHub account. Within a few seconds you will be taken to your account where you can create your first Cube deployment:

Proceed with providing a name for your deployment, selecting a cloud provider and a region:

At the next step, choose Create to start a new Cube project from scratch. Then, pick Postgres to proceed to the screen where you can enter the following credentials:

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

Cube will connect to a publicly available Postgres database that I've already set up.

In a while, your Cube deployment will be up and running:

Defining metrics. Please navigate to the "Schema" tab. You will see files like LineItems.js, Orders.js, Users.js, etc. under the "schema" folder.

Let's review LineItems.js which defines the metrics within the "LineItems" cube. This file is different from the one in Cube Cloud, but we'll take care of that later.

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

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

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

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

    // A calculated measure that reference other measures.
    // See https://cube.dev/docs/schema/reference/measures#calculated-measures
    avgPrice: {
      sql: `${CUBE.price} / ${CUBE.quantity}`,
      type: `number`
    },

    // A rolling window measure.
    // See https://cube.dev/docs/schema/reference/measures#rolling-window
    revenue: {
      sql: `price`,
      type: `sum`,
      rollingWindow: {
        trailing: `unbounded`,
      },
    }
  },

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

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

  dataSource: `default`
});

Key learnings here:

the cube is a logical entity that groups measures and dimensions together
using the sql statement, this cube is defined over the entire public.line_items table; actually, cube can be defined over an arbitrary SQL statement that selects data
measures (quantitative data) are defined as aggregations (e.g., count, sum, etc.) over columns in the dataset
dimensions (qualitative data) are defined over textual, numeric, or temporal columns in the dataset
you can define complex measures and dimensions with custom sql statements or references to other measures

Navigate to LineItems.js and replace its contents with the code above. Then, save your changes by clicking Save All to apply the changes to the development version of your API. You can apply as many changes as you wish, but we're done for now. Click Commit & Push to merge your changes back to the main branch:

On the "Overview" tab you will see your changes deployed:

Now you can explore the metrics on the "Playground" tab:

Good! We've built a metrics store that we can connect to Superset. How?

Please go back to the "Overview" tab and click How to connect. The "SQL API" tab will have a toggle that enables the API for Superset and other BI tools. Turning it on will provide you with all necessary credentials:

Now, let's build a dashboard!

Building a dashboard in Superset

We'll need to go through a few steps:

connect Superset to Cube
define the datasets
create charts and add them to a dashboard

Let's go!

Connect Superset to Cube. Switch back to the workspace we've created earlier. Then, navigate to Data / Databases via the top menu, click + Database, select MySQL, and fill in the credentials from your Cube Cloud instance — or use the credentials below:

Host: aquamarine-moth.sql.aws-us-east-2.cubecloudapp.dev
Port: 3306
Database name: db
Username: cube@aquamarine-moth
Password: 6300005f8da3ef74fa64a5bf9b1b6fcd
Display name: Cube Cloud (it's important)

You can press Connect now.

Define the datasets. Navigate to Data / Datasets via the top menu, click + Dataset, and fill in the following credentials:

Database: Cube Cloud (the one we've just created)
Schema: db
See table schema: LineItems

You can press Add now. Then, please repeat this for Users and Orders.

Create charts and a dashboard. We'll take a leap and create everything in a single step.

In Superset, you can export a dashboard with all charts as a JSON file and import it later. Navigate to Dashboards and click the link with an icon on the right:

Download this file to your machine, select it, and click Import:

Whoa! Now we have a complete dashboard for Acme, Inc. Click on it to view:

Looks nice, doesn't it? Let's explore what's under the hood and how you can build it on your own.

Diving deep into Superset

Anatomy of a dashboard. You can see that charts on the dashboard are aligned by the grid. To rearrange them, click on the pencil icon in the top right corner. You can add tabs, headers, dividers, Markdown blocks, etc. Of course, you can also add charts.

The simplest chart. Navigate to Charts via the top menu and click on any chart with the Big Number visualization type, e.g., Customers. In my opinion, that's the simplest chart you can create in Superset. It contains a single metric, and I doubt a chart can get simpler than that.

Let's dissect how a chart is defined:

Visualization type: Big Number — that's where you can select or change the chart type
Time column: createdAt — interestingly enough, any chart should have this time column defined even if the displayed data has no temporal components
Metric: COUNT(*) — that's the most important part of any chart configuration; upon clicking on this metric, you'll see that you can either select a saved definition, "simply" select a column and an aggregation, or write a "custom SQL" expression

When all config options are set, press Run to fetch the data, then Save to persist a chart or add it on a dashboard (no need to do it now, it's already added).

A less simple chart. Navigate to Charts via the top menu and click on any chart with the Big Number with Trendline visualization type, e.g., Revenue. Still, it contains a single metric as well as a sketchy chart in the bottom.

Let's dissect how this chart is defined (only new options):

Time grain: Month — defines the temporal granularity for metrics calculations
Time range: Last year — specifies the date range for this chart
Metric: revenue — it's interesting; click on this metric to learn that it's defined using "custom SQL"; that's because the aggregation has already been performed by Cube, no need to aggregate aggregated values, right?

Other charts. Actually, now you know everything you need to explore and dissect other charts. Just keep in mind that Superset has plenty of customizations you can apply to charts — see the "Customize" tab for inspiration.

Viewing SQL. For any chart, you can reveal the SQL query to Cube which is generated by Superset to fetch the data. Press the burger button (with triple horizontal lines) in the top right corner, then View query. Good ol' SQL, nice:

Also, if you wanna use the aforementioned SQL IDE, navigate to SQL Lab / SQL Editor via the top menu.

Making business intelligence fast ⚡

There's only one thing left to explore, but it's a huge one.

Let's navigate back to the Acme, Inc. dashboard. It takes 2-3 seconds to load, and the infinity-shaped spinners are clearly visible. They are not annoying, but honestly — wouldn't you like this dashboard to load instantly? Yep, well under a second.

Cube provies an out-of-the-box caching layer that allows to pre-compute and materialize the data required to serve the queries. All you need to do is define which queries should be accelerated. It's done declaratively in the data schema files. (Please also note that Superset has its own lightweight caching layer that might be handy in cases when you need to push your Cube + Superset to the limit.)

Please go back to your Cube Cloud instance, enter the development mode, switch to the "Schema" tab, and update your data schema files with small snippets as follows.

First, LineItems.js should look like this:

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

  // Copy me ↓
  preAggregations: {
    main: {
      measures: [ CUBE.count, CUBE.revenue, CUBE.price, CUBE.quantity ],
      timeDimension: CUBE.createdAt,
      granularity: 'day'
    }
  },
  // Copy me ↑

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

  ...

Second, Orders.js should look like this:

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

  // Copy me ↓
  preAggregations: {
    main: {
      measures: [ CUBE.count ],
      dimensions: [ CUBE.status ],
      timeDimension: CUBE.createdAt,
      granularity: 'day'
    }
  },
  // Copy me ↑

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

  ...

Lastly, Users.js should look like this:

cube(`Users`, {
  sql: `SELECT * FROM public.users`,

  // Copy me ↓
  preAggregations: {
    main: {
      measures: [ CUBE.count ],
      dimensions: [ CUBE.city, CUBE.gender ]
    }
  },
  // Copy me ↑

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

  ...

Don't forget to click Save All, then Commit & Push, and check that your changes were deployed at the "Overview" tab. In the background, Cube will build the necessary caches.

It's time to get back to your dashboard and refresh it. Now refresh it one more time. See? The dashboard loads in under a second. ⚡

Of course, you have plenty of options to fine-tune the caching behavior, e.g., specify the cache rebuilding schedule.

Wrapping up

Thanks for following this tutorial. I encourage you to spend some time in the docs and explore other features of Apache Superset. Also, please check out Preset docs that are packed with great content, e.g., on creating charts.

Also, thanks for learning more about building a metrics store with Cube. Indeed, it's a very convenient tool to serve as a single source of truth for all metrics.

Please don't hesitate to like and bookmark this post, write a comment, and give a star to Cube and Superset on GitHub. I hope these tools would be a part of your toolkit when you decide to build a metrics store and a business intelligence application on top of it.

Good luck and have fun!

Google Charts Dashboard: a Tutorial with an Artistic Touch of MoMA 🖼

Igor Lukanin — Thu, 21 Oct 2021 16:52:13 +0000

In this tutorial, we'll learn how to visualize data with Google Charts, a free charting service and JavaScript library by Google. We'll also use Cube, an open-source API for building data apps, to provide access to the public dataset with The Museum of Modern Art collection data. In the end, we'll have a dashboard with charts that tell all about MoMA's contemporary artworks.

Here's how the end result will look like:

Want to try it? Here's the live demo you can use right away. Also, the full source code is available on GitHub.

Now we're all set. Please check your ticket and proceed to Floor 1, Charting Gallery 🎫

What is Google Charts?

Google Charts is a charting service by Google that provides a rich selection of data visualization types, renders charts using HTML5 and SVG, provides cross-browser compatibility and cross-platform portability (meaning that charts look nice both on desktop and mobile).

Why is it a charting service, not a charting library? Indeed, Google Charts provide a JavaScript library that takes the data and renders charts on the web page. However, unlike other data visualization tools, Google Charts don't render all the charts on the client side. Instead, for some of the charts, they pass the data to Google servers, render a chart there, and then display the result on the page.

Such an approach has its pros:

Rendering code is browser- and platform-independent which provides increased compatibility and portability. If it renders once, it will render anytime.
The size of the JavaScript library is fixed and doesn't depend on the features used. And it's actually really tiny — less than 20 KB (gzipped).

But it also has its cons:

For some charts, data has to be uploaded to Google servers for the chart to be rendered. If you deal with sensitive data, please check the Google APIs Terms of Service. Also, make sure to always check the Data Policy sections in the docs. In this tutorial, we'll be using a public dataset, so it's not a big deal.

Which charts are available? Among the usual suspects like line charts, bar charts, or pie charts you can find a few distinctive ones:

Calendar charts that you must have seen numerous times at GitHub profiles.
Gantt charts that you might have wished to never encounter because of their affinity to "enterprise software".
Diff charts that combine a couple of scatter charts, pie charts, or bar charts into an image that visualizes the difference between two similar datasets.
Vega charts that provide a way to render charts defined with Vega and Vega-Lite visual grammars in Google Charts.

Enjoying the sight so far? Please proceed to Floor 2, Modern Arts 🎫

What is MoMA?

The Museum of Modern Art is an art museum in New York, USA. It was established 91 years ago, on November 7, 1929, and it's often identified as one of the largest and most influential museums of modern art in the world. MoMA's collection includes almost 200,000 works of architecture and design, drawing, painting, sculpture, photography, prints, illustrated books, film, and electronic media.

On GitHub, MoMA publishes and periodically updates a public dataset which contains ~140,000 records, representing all of the works that have been accessioned into MoMA’s collection and cataloged in our database. It includes basic metadata for each work (e.g., title, artist, date made, medium, dimensions, and date of acquisition). This dataset is placed in the public domain using a CC0 License (so we're free to use it in this tutorial) and available in CSV and JSON formats.

I've imported this dataset to a publicly available Postgres instance that we'll use in a few minutes to explore the data. Proceed to Floor 3, Cubism 🎫

What is Cube?

We're building a dashboard, so it would be very convenient to access the data from the front end via an API. Cube comes particularly handy for this purpose.

Cube is a popular open-source product with more than 11,000 stars on GitHub to date. It serves as an API for building data apps. You can configure Cube to connect to any database, describe your data with a declarative data schema, and instantly get an API that you can use in your app.

Let's spin up an API for the MoMA dataset. First, please make sure you have Docker installed on your machine. It's recommended to run Cube with Docker or use a managed instance in Cube Cloud.

Second, let's create a new folder for your Cube app and navigate to it:

mkdir google-charts-moma
cd google-charts-moma

Third, run this snippet to create a new docker-compose.yml file with the configuration. We'll also use environment variables from the .env file to instruct Cube how to connect to Postgres:

cat > docker-compose.yml << EOL
version: '2.2'
services:
  cube:
    image: cubejs/cube:latest
    ports:
      - 4000:4000
      - 3000:3000
    env_file: .env
    volumes:
      - .:/cube/conf
EOL

Then, run this snippet to create the .env file with Postgres credentials. In this tutorial, we're using a publicly available Postgres database that I've already set up. Check the docs to learn more about connecting Cube to Postgres or any other database.

cat > .env << EOL
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_HOST=demo-db-examples.cube.dev
CUBEJS_DB_NAME=moma
CUBEJS_DB_USER=cube
CUBEJS_DB_PASS=12345
CUBEJS_API_SECRET=SECRET
CUBEJS_DEV_MODE=true
EOL

That is all we need to let Cube connect to Postgres. The last part of configuration is the data schema which declaratively describes the contents of the database. Let's put it under the schema folder:

mkdir schema
touch Artworks.js

Please copy and paste this data schema into Artworks.js, then follow the comments in the file:

cube(`Artworks`, {
  // Cube definition.
  // It says that the data is kept in the "artworks" table.
  // Learn more in the docs: https://cube.dev/docs/schema/getting-started
  sql: `SELECT * FROM public.artworks`,

  // Quantitative information about the data, e.g., count of rows.
  // It makes sense for all rows rather than individual rows
  measures: {
    count: {
      type: `count`,
    },

    minAgeAtAcquisition: {
      type: `number`,
      sql: `MIN(${CUBE.ageAtAcquisition})`
    },

    avgAgeAtAcquisition: {
      type: `number`,
      sql: `SUM(${CUBE.ageAtAcquisition}) / ${CUBE.count}`
    },

    maxAgeAtAcquisition: {
      type: `number`,
      sql: `MAX(${CUBE.ageAtAcquisition})`
    }
  },

  // Qualitative information about the data, e.g., an artwork's title.
  // It makes sense for individual rows of data rather than all rows
  dimensions: {
    title: {
      sql: `${CUBE}."Title"`,
      type: `string`
    },

    artist: {
      sql: `${CUBE}."Artist"`,
      type: `string`
    },

    classification: {
      sql: `${CUBE}."Classification"`,
      type: `string`
    },

    medium: {
      sql: `${CUBE}."Medium"`,
      type: `string`
    },

    // We can use SQL functions here
    year: {
      sql: `SUBSTRING(${CUBE}."Date" FROM '[0-9]{4}')`,
      type: `number`
    },

    date: {
      sql: `${CUBE}."Date"`,
      type: `number`
    },

    dateAcquired: {
      sql: `${CUBE}."DateAcquired"`,
      type: `time`
    },

    yearAcquired: {
      sql: `DATE_PART('year', ${CUBE}."DateAcquired")`,
      type: `number`
    },

    ageAtAcquisition: {
      case: {
        when: [
          {
            sql: `${CUBE.yearAcquired}::INT - ${CUBE.year}::INT > 0`,
            label: { sql: `${CUBE.yearAcquired}::INT - ${CUBE.year}::INT` }
          }
        ],
        else: {
          label: `0`
        }
      },
      type: `number`
    },

    heightCm: {
      sql: `ROUND(${CUBE}."Height (cm)")`,
      type: `number`
    },

    widthCm: {
      sql: `ROUND(${CUBE}."Width (cm)")`,
      type: `number`
    },
  },

  dataSource: `default`
});

Whew! Now we're finally ready to run Cube:

docker compose up

Now, let's review the data in the MoMA dataset. Cube provides the Developer Playground, a convenient web-based tool that helps explore the data, at localhost:4000. Navigate to the Developer Playground in your browser and explore the UI. You can retrieve arbitrary data, slice and dice the dataset by selecting dimensions and measures.

For example, you can check how many artworks MoMA has for the artists that have "Sarah" in their name. To do so, select the Artworks.count measure, the Artworks.artist dimension, and also make sure to add a filter for the Artworks.artist dimension that allows only the names containing "Sarah".

Feel free to tinker with other measures and dimensions. Once you're done, let's get to building the dashboard. Proceed to Floor 4, Abstract Art 🎫

Building a dashboard with Google Charts

Now it's time to develop a front-end application telling the story behind the MoMA artworks collection with charts and other types of data visualizations. For simplicity, let's build it with pure JavaScript and without any frameworks (however, unofficial packages are available on NPM for React, Angular, and Vue as well as TypeScript definitions in the DefinitelyTyped repository).

Basic dashboard. First, let's create a subfolder for the dashboard under the google-charts-moma folder:

mkdir dashboard-app
cd dashboard-app

Second, let's start with an HTML file with the following contents. You can name it basic.html and put in that dashboard-app folder. We'll go through this file line by line. Follow the comments!

<html>
<head>
  <!-- Load Cube API library (UMD version for in-browser use) -->
  <script type="text/javascript" src="https://unpkg.com/@cubejs-client/core@0.28.38/dist/cubejs-client-core.umd.js"></script>

  <!-- Load Google Charts API -->
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

  <script type="text/javascript">
    // Cube API token and host.
    // Change to "http://localhost:4000" to use your own Cube.
    // See the docs to learn more about authentication: https://cube.dev/docs/security
    const cubeToken = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw";
    const cubeHost = "https://heavy-lansford.gcp-us-central1.cubecloudapp.dev";

    // Instantiate Cube API client
    const cubeApi = cubejs(cubeToken, { apiUrl: cubeHost + "/cubejs-api/v1" });

    // Load Google Charts API and packages ('corechart' is the main one)
    google.charts.load('current', { packages: [ 'corechart', 'table' ]});

    // Set a callback to run when the Google Visualization API is loaded
    google.charts.setOnLoadCallback(drawCharts);

    // Function that creates the charts.
    // We'll start with just a couple of them
    function drawCharts() {
      drawChartForArtworkArtists();
      drawChartForArtworkYears();
    }

    // Function that creates a table chart.
    // See the docs to learn more: https://developers.google.com/chart/interactive/docs/gallery/table
    function drawChartForArtworkArtists() {
      // Cube query that will fetch:
      // — artists' names (1)
      // — count of artworks for each artist (2)
      // — but only for those artworks that are labeled as paintings (3)
      // Learn more about query format in the docs: https://cube.dev/docs/query-format
      const query = {
        dimensions: [
          "Artworks.artist" // 1
        ],
        measures: [
          "Artworks.count" // 2
        ],
        filters: [
          {
            member: "Artworks.classification",
            operator: "equals",
            values: [ "Painting" ] // 3
          }
        ]
      };

      // Let's execute the query via the Cube API...
      cubeApi
        .load(query)
        .then(resultSet => {
          // ...and process the result set.
          // First, let's turn it into an array of two-element arrays with names and counts
          const rows = resultSet.tablePivot().map(function(row) {
            return [ row['Artworks.artist'], parseInt(row['Artworks.count']) ];
          });

          // Second, let's convert it into Google Charts data table.
          // Note that we're passing an array of column names as the first row
          const data = google.visualization.arrayToDataTable([
            [ 'Artist', 'Paintings' ],
            ...rows
          ]);

          // Third, let's specify a few options like pagination
          const options = {
            showRowNumber: true,
            page: 'enable',
            pageSize: 20,
            width: '100%'
          };

          // Eventually, let's render the chart.
          // It will be inserted in place of an HTML element with a known id
          const chart = new google.visualization.Table(document.getElementById('chart__artists'));
          chart.draw(data, options);
        });
    }

    // Function that creates a scatter chart.
    // See the docs to learn more: https://developers.google.com/chart/interactive/docs/gallery/scatterchart
    function drawChartForArtworkYears() {
      // Cube query that will fetch:
      // — production years (1)
      // — acquisition years (2)
      // — count of artworks for each "year created" / "year acquired" pair (3)
      // — but only for those artworks that are labeled as paintings (4)
      // — and have known production year and acquisition year (5, 6)
      const query = {
        dimensions: [
          "Artworks.year", // 1
          "Artworks.yearAcquired" // 2
        ],
        measures: [
          "Artworks.count" // 3
        ],
        filters: [
          {
            member: "Artworks.classification",
            operator: "equals",
            values: [ "Painting" ] // 4
          },
          {
            member: "Artworks.yearAcquired",
            operator: "set" // 5
          },
          {
            member: "Artworks.year",
            operator: "set" // 6
          }
        ]
      };

      cubeApi
        .load(query)
        .then(resultSet => {
          const maxCount = resultSet
            .tablePivot()
            .reduce(function(max, row) {
              return max < row['Artworks.count'] ? row['Artworks.count'] : max;
            }, 0);

          const rows = resultSet.tablePivot().map(function(row) {
            return [
              parseInt(row['Artworks.year']),
              parseInt(row['Artworks.yearAcquired']),
              'point { opacity: ' + (row['Artworks.count'] / maxCount).toFixed(1) + '; }'
            ];
          });

          // Note that the third "column" of data is special.
          // It says we'll be passing styles of the dots on the scatter chart
          const data = google.visualization.arrayToDataTable([
            [ 'Year created', 'Year acquired', { type: 'string', role: 'style' } ],
            ...rows
          ]);

          // The chart type is different, and the options are different as well
          const options = {
            title: 'Year created vs. Year acquired',
            hAxis: { viewWindowMode: 'maximized', title: 'Year created' },
            vAxis: { viewWindowMode: 'maximized', title: 'Year acquired' },
            pointSize: 3,
            height: 500,
            width: '100%',
            legend: 'none'
          };

          new google.visualization
            .ScatterChart(document.getElementById('chart__years'))
            .draw(data, options);
        });
    }
  </script>

  <title>Google Charts Dashboard</title>

  <style>
    body {
      padding: 5em;
    }

    .dashboard {
      display: grid;
      grid-template-columns: 1fr;
      grid-column-gap: 5em;
    }

    .group {
      display: grid;
      grid-template-columns: 1fr 1fr;
      grid-column-gap: 5em;
    }
  </style>
</head>

<body>
<div class="dashboard">
  <!-- Charts within the dashboard -->
  <div class="group">
    <div id="chart__artists"></div>
    <div id="chart__years"></div>
  </div>
</div>
</body>
</html>

Here's what you should get once you save the contents of this file and open it in the browser. It's a table and a fancy diagonal chart — because it's unlikely that MoMA can acquire an artwork before it was created, right?

Less than 200 lines of code allowed us to include all libraries, query an API, transform the data, configure charts, and render them side-by-side on the page. Not that bad!

However, I wouldn't call it a full-fledged dashboard until it allows interaction with elements and change how the data is represented. Let's explore how to work with events and cross-link the charts.

Interactive dashboard. Please create another file, you can call it index.html. It will contain 2-3 more lines of code, so we'll have more charts and some bits of code that allow working with events. Copy and paste the code from GitHub — and let's go through the most interesting parts together.

Now we have many charts and many functions to draw them:

function drawCharts(artistFilters) {
  drawChartForArtworkArtists(artistFilters);
  drawChartForArtworkMediums(artistFilters);
  drawChartForArtworkYears(artistFilters);
  drawChartForArtworkWidthsHeights(artistFilters);
  drawChartForArtworkAcquisitions(artistFilters);
  drawChartForArtworkAcquisitionsIn1964(artistFilters);
  drawChartForArtworkAcquisitionsAge(artistFilters);
}

One of the charts, the table with artists' names, has got an event listener that is fired every time you select one or many table rows or clear the selection. As you can see, we somehow build the filter for the data using the buildQueryFilter function and then pass this filter to every other chart:

google.visualization.events.addListener(chart, 'select', function() {
  const artistsFilter = buildQueryFilter(resultSet, chart, "Artworks.artist");

  drawChartForArtworkMediums(artistsFilter);
  drawChartForArtworkYears(artistsFilter);
  drawChartForArtworkWidthsHeights(artistsFilter);
  drawChartForArtworkAcquisitions(artistsFilter);
  drawChartForArtworkAcquisitionsIn1964(artistsFilter);
  drawChartForArtworkAcquisitionsAge(artistsFilter);
});

Here's how we build the filter. We access selected rows via chart.getSelection(). If there are no rows selected, the filter will be undefined. If there are any, we'll find the values of selected cells and use them as values in the Cube query filter:

function buildQueryFilter(resultSet, chart, member) {
  const selectedRowNumbers = chart
    .getSelection()
    .map(function(x) { return x.row; });

  if (selectedRowNumbers.length === 0) return

  const values = resultSet
    .tablePivot()
    .filter(function(row, i) {
      return selectedRowNumbers.indexOf(i) !== -1
    })
    .map(function(row) {
      return row[member]
    })

  return {
    member,
    operator: "equals",
    values
  };
}

That's all you need to add interactivity to the dashboard. See how the charts change upon the selection of one or many artists in the first table:

It's a kind of art, isn't it? 🧑‍🎨

Actually, these charts and this whole tutorial are inspired by the work of others that I'd like to mention here: "A Nerd’s Guide To The 2,229 Paintings At MoMA" by FiveThirtyEight, "MoMA on GitHub" by YYYY-MM-DD, and this tweet by Steven Lubar.

Cleaning Off the Paint

Thank you for reading and following this tutorial! I encourage you to spend some time in the docs and explore what else Google Charts are capable of. You'll find even more chart types, configuration options, and advanced features.

Also, thanks for learning about Cube and building dashboards. I hope you enjoyed it.

Please don't hesitate to like and bookmark this post, write a comment, and give a star to Cube on GitHub. I hope that you'll try Cube and Google Charts in your next production gig or your next pet project.

Good luck and have fun! Now, proceed to the exit! 🎫

Awesome dataviz tools for software developers 📊📈

Igor Lukanin — Thu, 24 Jun 2021 15:54:50 +0000

TL;DR: We've built awesome.cube.dev to help you choose the best charting libraries and other tools for your needs. Please have a look, it's awesome.

Hey friends! 👋

Have you ever wondered which tool or library to choose to add a chart, a map, or maybe a data grid to your app built with the %BEST_FRONTEND_FRAMEWORK% and the %BEST_PROGRAMMING_LANGUAGE%?

I'm happy to know for sure that you have! Just a few months ago, I talked to developers in the Cube.js community and also ran this survey that got dozens of replies:

DEV Survey ✅ — Front-end Data Visualization Tools

Igor Lukanin for Cube ・ Mar 22 '21

#javascript #webdev #discuss

On behalf of the Cube.js team, let me share what I learned.

Choosing a tool is hard 😬

I discovered that there are two types of criteria that developers evaluate when the need to choose a dataviz tool arise.

First, you need to check if a particular tool is compatible with the technology stack that you're using, and by that I mean the front-end framework and the programming language. Obviously, Recharts is a neat tool but there's no way to use it in a Vue app because it's purposefully built for React. Same is true for the language: according to the survey, 2/3 of all developers use TypeScript, so making sure that the tool has built-in type definitions or something available via DefinitelyTyped is also crucial.

It's also nice to check the license. Discovering that something that you've already built into your app is not open-source and free to use may be painful. In the other case, when you specifically look for a paid tool with enterprise support, checking the license and pricing beforehand is crucial as well. According to the survey, 20 % of all developers use paid tools.

Then, you'll see how easy it is to get started, the hard way. The adoption curve is heavily influenced by the ease of use and available docs and examples. Developers particularly praised the tools that have readily available "getting started" pages with code examples as well as the tools with excessive number of examples. D3.js and ECharts are obvious winners in this category.

In the end, you might face the sad truth: a tool is rooted in the past. Your success will depend on the flexibility that a tool can provide for your use cases. And if it can't, soon you'll find yourself checking if there's much activity in the issues and pull requests sections on GitHub. Let's hope you'll find that a tool currently has a vibrant and active community.

Now, choosing a tool is easy 😎

Equipped with this evidence, we've committed to solve the dataviz tools choice problem once and for all. We've built an open-source list of data visualization tools for software developers that will support you at every step.

In the very beginning, you'll have the chance to pick the front-end framework of your choice and specify if you care about TypeScript support. If you're an open-source aficionado, there's an option for that, too. Also, if you come for something specific (e.g., a data grid), you'll have a filter for that as well.

In an instant, you'll see the awesome tools matching your criteria. For those tools that gathered a lot of mentions in the survey results, you'll see badges like "Easy to customize" or "Full-fledged" that you can use to set expectation.

Definitely feel free to pick some candidates and deep dive into their pages. You'll be able to build an understanding of what to expect in terms of flexibility and the community dynamics. Checking the last release date and the number of outstanding issues are also wise things to do.

Ready to get started? You'll find links to tutorials and guides that, hopefully, will help you get on track. And if you're stuck, there're a few links for that, too.

As the last but not the least resort, feel free to come and join Cube.js community on Slack. It has thousands of developers building apps for which Cube.js serve as the open-source API layer that delivers data to the dataviz tools on the front-end.

Now, it's time to visit awesome.cube.dev and see it in action! If you like it, don't hesitate to share a link with your friends. In any case, leave us a comment: we'd love to know what you think 😇

P. S. Just like Cube.js, awesome.cube.dev is open-source. We appreciate your issues and pull requests.

Introducing Cube Store: High concurrency and sub-second latency for any database

Igor Lukanin — Thu, 29 Apr 2021 17:49:14 +0000

TL;DR: Cube.js is a popular open-source tool to build analytical APIs. With the release of Cube Store, a performant distributed data store written in Rust, Cube.js is ready to provide high concurrency and sub-second latency for any SQL-compliant database, data warehouse, or query engine (e.g., PostgreSQL, BigQuery, or AWS Athena).

You can use open-source Cube.js and Cube Store to build an analytical API that will respond to highly concurrent requests from your application with sub-second latency regardless of data volume or workload. Even if you have a billion-scale dataset and thousands of concurrent users, Cube.js and Cube Store will get you covered.

Cube Store, the custom pre-aggregation storage layer for Cube.js, is now generally available and strongly recommended for use in production. Let's explore the motivation and design decisions behind Cube Store and learn more about the benefits of using Cube Store for new and existing Cube.js users.

Why Cube Store?

Cube.js is an open-source analytical API platform that allows developers to build an analytical API and a semantic layer based on data schema over any SQL-compliant data store. In the simplest scenario, Cube.js uses in-memory cache and query queue to provide better performance than the data store is able to deliver. However, it's usually the least scalable and cost-effective solution, so it's not recommended for production.

In a real-world scenario, Cube.js speeds up analytical queries using pre-aggregations. These are the condensed representations of source data that are pre-computed, partitioned, stored, and refreshed in a way that allows for super-fast execution of select queries.

Historically, pre-aggregations were stored internally, alongside the source data in a database (e.g., PostgreSQL or MySQL), or externally, in a custom-provisioned instance of PostgreSQL or MySQL, as the only feasible option for read-only or cost-ineffective data sources (e.g., AWS Athena or BigQuery). Usually, they will be renewed asyncronously by a dedicated refresh worker instance. It's an acceptable solution, however, the pre-aggregation database becomes a bottleneck and limits the scalability of the analytical API.

Pre-aggregations in traditional databases often won't allow for high concurrency and low latency of the analytical API. They also were the source of frustration due to poor performance in important use cases (e.g., with high cardinality of pre-aggregated data, with partitioned data, with joins across several databases), unsupported features (e.g., approximate distinct counts via HyperLogLog algorithm), or implementation details (e.g., support for long table names or support for various SQL types).

Cube Store is designed to resolve these issues and provide a performant pre-aggregation storage layer for Cube.js. It's a drop-in replacement for any currently used pre-aggregation storage and the go-to solution for new Cube.js apps. Being a distributed data store itself, Cube Store can be scaled horizontally with ease and predictability.

From now on, Cube Store is the recommended pre-aggregation storage layer for production use. It doesn't have the limitations of other databases used to store pre-aggregations. On top of that, Cube Store provides guaranteed high concurrency and sub-second latency, performance optimizations for analytical queries, and additional features such as data federation via cross-database joins. You'll get these advantages of Cube Store regardless of your data source and the data volume.

How Cube Store works

Cube Store uses a well-known and proven distributed query engine architecture. In every Cube Store cluster:

a single router node handles incoming connections, manages database metadata, builds query plans, and orchestrates their execution
multiple worker nodes ingest warmed up data and execute queries in parallel
a local or cloud-based blob storage keeps pre-aggregated data in columnar format

Cube Store was designed with performance in mind. It's written entirely in Rust to leverage low-level optimizations, effectively making Rust the top-1 language by lines of code in Cube.js codebase. Cube Store allows for almost unlimited horizontal scalability by increasing the number of worker nodes. It stores pre-aggregated data in Parquet format which was deliberately chosen for its efficiency and performance.

Cube Store is based on well-known open-source building blocks. Together with Parquet, Cube Store uses Apache Arrow for its efficient in-memory data structures and DataFusion as a query execution framework.

Cube Store has been used in Cube Cloud for more than 6 months. A few select early access users have been exposed to Cube Store in Cube Cloud and experienced substantial performance improvements thanks to Cube Store.

How to use Cube Store

Cube Store is ready to be used while developing new Cube.js applications and also as a drop-in replacement for the pre-aggregation storage layer in existing Cube.js apps.

Development. When Cube.js is run in development mode, a single-node in-process instance of Cube Store is enabled. It requires zero configuration and allows to instantly use the pre-aggregations. All you need is to mark them as external:

cube(`Cube`, {
  sql: `select * from table`,

  preAggregations: {
    main: {
      type: `rollup`,
      external: true,

      // ...
    },
  },
});

Cube.js will build pre-aggregations and store them in Cube Store. You can review the console output of Cube.js to see if queries are served using pre-aggregations. You can also see matched rollups on the Cache tab in Cube.js Developer Playground available at localhost:4000.

Production. A Cube Store cluster should be run in production and consist of a single router instance and several worker instances. There's an official cubejs/cubestore Docker image as well as a Docker Compose template you can use as a starting point.

Just like Cube.js, Cube Store can be configured via environment variables, e.g., Cube Store nodes assume the worker role when CUBESTORE_WORKER_PORT is set and ingest pre-aggregated data from the blob storage specified with CUBESTORE_GCS_BUCKET or CUBESTORE_S3_BUCKET.

Benchmarks. Depending on the currently used pre-aggregation storage and the data volume, after switching to Cube Store, you can expect your API performance to stay at least at the same level or increase substantially. You can review and use a benchmark template leveraging k6, an open-source load testing tool, to do your own measurements prior to switching to Cube Store.

Next steps

Here at the Cube.js team, we wholeheartedly encourage you to use Cube Store in development and consider migrating your production deployments to Cube Store as well.

Get started with Cube.js today. Review the release notes and documentation, share your experience in the #cube-store channel in Slack or post to Discourse, and don't hesitate to file Cube Store-related issues on GitHub.

Also, feel free to sign up for the waiting list of Cube Cloud which provides a fully managed Cube Store cluster for all deployments. Cube Cloud is currently in early access but will be generally available later this year.

React Pivot Table with AG Grid and Cube.js 🔢

Igor Lukanin — Tue, 23 Mar 2021 16:04:27 +0000

TL;DR: In this tutorial, we'll learn how to add a pivot table to a React app using AG Grid, the self-proclaimed "best JavaScript grid in the world", on the front-end and Cube.js, an analytical API platform, on the back-end. We'll build a pivot table data visualization, explore the features of AG Grid, and learn why Cube.js is a great fit for AG Grid.

What is a pivot table?

Pivot tables, also known as multi-dimensional tables or cross-tables, are tables that display the statistical summary of the data in usual, flat tables. Often, such tables come from databases, but it's not always easy to make sense of the data in large tables. Pivot tables summarize the data in a meaningful way by aggregating it with sums, averages, or other statistics.

Here's how a pivot table is explained in Wikipedia. Consider you have a flat table like this with e-commerce T-shirt inventory data: regions, ship dates, units, prices, etc.

An inventory might be overwhelmingly lengthy, but we can easily explore the data with a pivot table. Let's say we want to know how many items were shipped to each region on each date. Here's the pivot table that answers exactly to this question:

Analytics 101. Note that how many items is an aggregated, numerical value — a sum of items that were shipped. In analytical applications, such aggregated values are called "measures". Also note that each region and each date are categorial, textual values that be can enumerate. In analytical apps, such categorial values are called "dimensions".

Actually, that's everything one should know about data analytics to work with pivot tables. We'll use this knowledge later.

Why AG Grid?

AG Grid is a feature-rich implementation of a JavaScript data table. It supports React, Angular, and Vue as well as vanilla JavaScript. Honestly, it's no exaggeration to say that it contains every feature possible (for a data table):

AG Grid's authors emphasize that it's particularly useful for building enterprise applications. So, it's understandable that it comes in two versions:

free and open-source, MIT-licensed Community version
free-to-evaluate but paid and non-OSS Enterprise version

Almost all features are included in the Community version, but a few are available only as a part of the Enterprise version: server-side row model, Excel export, various tool panels, and — oh, my! — pivoting and grouping.

It's totally okay for the purpose of this tutorial, but make sure to purchase the license if you decide to develop a production app with an AG Grid pivot table.

Here's what our end result will look like:

Want to try it? Here's the live demo you can use right away. Also, the full source code is on GitHub.

Now we're all set, so let's pivot! 🔀

How to Create an Analytical API

Pivot tables are useless without the data, and the API is where the data comes from in a real-world app. And the more data we have, the better it is.

So, what are we going to do? We'll use Cube.js:

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications

Cube.js is an open-source analytical API platform. It allows you to create an API over any database and use that API in any front-end app. In this tutorial, we'll connect Cube.js to a database and we'll use the API in our React app.

Cube.js provides an abstraction called a "semantic layer," or a "data schema," which encapsulates database-specific things, generates SQL queries for you, and lets you use high-level, domain-specific identifiers to work with data.

Also, Cube.js has a built-in caching layer that provides predictable, low-latency response query times. It means that, regardless of your data volume and database, an API built with Cube.js will serve data to your app in a performant way and help create a great user experience.

Let's try it in action.

The first step is to create a new Cube.js project. Here, I assume that you already have Node.js installed on your machine. Note that you can also use Docker to run Cube.js. Run in your console:

npx cubejs-cli create react-pivot-table -d postgres

Now you have your new Cube.js project in the react-pivot-table folder containing a few files. Let's navigate to this folder.

The second step is to add database credentials to the .env file. Cube.js will pick up its configuration options from this file. Let's put the credentials from a publicly available Postgres database there. Make sure your .env file looks like this, or specify your own credentials:

# Cube.js environment variables: https://cube.dev/docs/reference/environment-variables

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_HOST=demo-db.cube.dev
CUBEJS_DB_PORT=5432
CUBEJS_DB_SSL=true
CUBEJS_DB_USER=cube
CUBEJS_DB_PASS=12345
CUBEJS_DB_NAME=ecom

CUBEJS_DEV_MODE=true
CUBEJS_WEB_SOCKETS=true
CUBEJS_API_SECRET=SECRET

Here's what all these options mean:

Obviously, CUBEJS_DB_TYPE says we'll be connecting to Postgres.
CUBEJS_DB_HOST and CUBEJS_DB_PORT specify where our Postgres instance is running, and CUBEJS_DB_SSL turns on secure communications over TLS.
CUBEJS_DB_USER and CUBEJS_DB_PASS are used to authenticate the user to Postgres.
CUBEJS_DB_NAME is the database name where all data schemas and data tables are kept together.
The rest of the options configure Cube.js and have nothing to do with the database.

The third step is to start Cube.js. Run in your console:

npm run dev

And that's it! Here's what you should see:

Great, the API is up and running. Let's move on! 🔀

How to Define a Data Schema

Before we can tinker with the data, we need to describe it with a data schema. The data schema is a high-level domain-specific description of your data. It allows you to skip writing SQL queries and rely on Cube.js to generate them for you.

As the console output suggests, please navigate to localhost:4000 — this application is Cube.js Developer Playground. It's able to generate an initial version of the data schema automatically. Go to the "Schema" tab, select all tables under "public", and click the "Generate Schema" button.

That's all. You can check that in the schema folder there's a number of files containing the data schema files: Orders.js, Products.js, Users.js, etc.

Now we have the data schema in place. Let's explore the data! 🔀

How to Explore the Data

Go to the "Build" tab, click "+ Dimension" or "+ Measure," and select any number of dimensions and measures. For example, let's select these measures and dimensions:

Orders Count measure
Line Items Price measure
Line Items Quantity measure
Products Name dimension
Orders Status dimension
Users City dimension

As the result, you should get a complex, lengthy table with the data about our e-commerce enterprise:

Looks interesting, right? Definitely feel free to experiment and try your own queries, measures, dimensions, time dimensions, granularities, and filters.

Take note that, at any time, you can click the "JSON Query" button and see the query being sent to Cube.js API in JSON format which, essentially, lists the measures and dimensions you were selecting in the UI.

Later, we'll use this query to fill our upcoming pivot table with data. So, let's move on and build a pivot table! 🔀

How to Build an Analytical App

Okay, I'll be honest, Cube.js Developer Playground has one more feature to be explored and used for the greater good.

Let's go to the "Dashboard App" tab where you can generate the code for a front-end application with a dashboard. There's a variety of templates for different frameworks (React and Angular included) and charting libraries but you can always choose to "create your own".

Let's choose "React", "React Antd Dynamic", "Bizcharts", and click "OK". Just in a few seconds you'll have a newly created front-end app in the dashboard-app folder. Click "Start dashboard app" to run it, or do the same by navigating to dashboard-app and running:

npm run start

Believe it or not, this dashboard app will allow you to run the same queries you've already run the Developer Playground. On the "Explore" tab, you can create a query, tailor the chart, and then click "Add to dashboard". On the "Dashboard" tab, you'll see the result.

Impressive? We'll go further than that, and replace the dashboard with the pivot table right now. 🔀

How to Add a Pivot Table

We'll need to follow a series of simple steps to add AG Grid, tune it, review the result, and understand how everything works. I should say that AG Grid has excellent documentation with versions for vanilla JavaScript, React, Angular, and Vue. However, here's an even more condensed version of the steps you need to follow to set up AG Grid.

First, let's install the AG Grid packages. Make sure to switch to the dashboard-app folder now. AG Grid can be installed via packages or modules, but the former way is simpler. Let's run in the console:

npm install --save ag-grid-enterprise ag-grid-react

Note that we're installing ag-grid-enterprise version. There's also ag-grid-community that contains a subset of the enterprise features but the pivot table feature is included in the enterprise version only. It's going to work but it will print a giant warning in the console until you obtain a license:

Second, let's create a pivot table component. Add a new file at the src/components/Grid.js location with the following contents. Basically, it sets AG Grid up, adds data from Cube.js API, and does the pivoting. It's not very lengthy, and we'll break this code down in a few minutes:

import React, { useEffect, useState } from 'react';
import { useCubeQuery } from '@cubejs-client/react';
import { Button, Space, Layout } from 'antd';
import { AgGridColumn, AgGridReact } from 'ag-grid-react';
import 'ag-grid-enterprise';
import 'ag-grid-community/dist/styles/ag-grid.css';
import 'ag-grid-community/dist/styles/ag-theme-alpine.css';

const query = {
  'order': {
    'Orders.count': 'desc',
  },
  'measures': [
    'Orders.count',
    'LineItems.price',
    'LineItems.quantity',
  ],
  'dimensions': [
    'Products.name',
    'Orders.status',
    'Users.city',
  ],
};

const Grid = () => {
  const [ rowData, setRowData ] = useState([]);
  const { resultSet } = useCubeQuery(query);

  useEffect(() => {
    if (resultSet) {
      setRowData(resultSet
        .tablePivot()
        .map(row => Object
          .keys(row)
          .reduce((object, key) => ({
            ...object,
            [key.replace('.', '-')]: row[key],
          }), {}),
        ),
      );
    }
  }, [ resultSet ]);

  const columnDefs = [
    ...query.dimensions,
    ...query.measures,
  ].map(field => ({
    headerName: field.split('.')[1],
    field: field.replace('.', '-'),
  }));

  return (
    <Layout>
      <Layout.Header style={{ backgroundColor: '#43436B' }}>
        <Space size='large'>
          <a href='https://cube.dev' target='_blank' rel='noreferrer'>
            <img src='https://cubejs.s3-us-west-2.amazonaws.com/downloads/logo-full.svg' alt='Cube.js' />
          </a>
          <Space>
            <Button href='https://github.com/cube-js/cube.js' target='_blank' ghost>GitHub</Button>
            <Button href='https://slack.cube.dev' target='_blank' ghost>Slack</Button>
          </Space>
        </Space>
      </Layout.Header>
      <div className='ag-theme-alpine' style={{ height: 700 }}>
        <AgGridReact
          defaultColDef={{
            flex: 1,
            minWidth: 150,
            sortable: true,
            resizable: true,
          }}
          aggFuncs={{
            'min': ({ values }) => values.reduce((min, value) => Math.min(min, Number(value)), 0),
            'max': ({ values }) => values.reduce((max, value) => Math.max(max, Number(value)), 0),
            'sum': ({ values }) => values.reduce((sum, value) => sum + Number(value), 0),
            'avg': ({ values }) => (values.reduce((sum, value) => sum + Number(value), 0) / values.length).toFixed(0),
          }}
          autoGroupColumnDef={{ minWidth: 250 }}
          pivotMode={true}
          sideBar={'columns'}
          rowData={rowData}
        >
          {columnDefs.map((column, i) => {
            const name = column.field.replace('-', '.');
            const isDimension = Object.values(query.dimensions).indexOf(name) !== -1;
            const isMeasure = Object.values(query.measures).indexOf(name) !== -1;

            return (
              <AgGridColumn
                key={i}
                headerName={column.headerName}
                field={column.field}
                enablePivot={true}
                enableRowGroup={isDimension}
                enableValue={isMeasure}
                pivot={column.headerName === 'status'}
                rowGroup={column.headerName === 'name'}
                allowedAggFuncs={[ 'sum', 'max', 'avg', 'min' ]}
                aggFunc={isMeasure ? 'sum' : null}
              />
            );
          })}
        </AgGridReact>
      </div>
    </Layout>
  );
};

export default Grid;

To make everything work, now go to src/App.js and change a few lines there to add this new Grid component to the view:

+ import Grid from './components/Grid';
  import './body.css';
  import 'antd/dist/antd.css';

  // ...

  const AppLayout = ({
    children
  }) => <Layout style={{
    height: '100%'
  }}>
-   <Header />
-   <Layout.Content>{children}</Layout.Content>
+   <Grid />
  </Layout>;

  // ...

Believe it or not, we're all set! 🎉 Feel free to start your dashboard-app again with npm run start and prepare to be amused. Here's our data grid:

You can even turn "Pivot Mode" off with the knob in the top right corner, remove all measures and dimensions from "Row Groups" and "Values", and behold the raw ungrouped and unpivoted data as fetched from Cube.js API:

Amazing! Let's break the code down and review the features of AG Grid! 🔀

How Everything Works

All relevant code resides inside the src/components/Grid.js component. We'll explore it from the top to the bottom.

In the imports, you can see this React hook imported from the Cube.js client React package. We'll use it later to send a query to Cube.js API:

// Cube.js React hook
import { useCubeQuery } from '@cubejs-client/react';

Next, AG Grid imports go. It has a convenient AgGridReact component that we'll use. However, in complex scenarios, you'll need to use the onGridReady callback to get access to the Grid API and tinker with it directly. Also, note that AG Grid provides style definitions and a few themes you can import and use.

// AG Grid React components & library
import { AgGridColumn, AgGridReact } from 'ag-grid-react';
import 'ag-grid-enterprise';

// AG Grid styles
import 'ag-grid-community/dist/styles/ag-grid.css';
import 'ag-grid-community/dist/styles/ag-theme-alpine.css';

Next, meet the Cube.js query in JSON format. I hope you remember this query from Developer Playground where it was available on the "JSON Query" tab:

const query = {
  'order': {
    'Orders.count': 'desc',
  },
  'measures': [
    'Orders.count',
    'LineItems.price',
    'LineItems.quantity',
  ],
  'dimensions': [
    'Products.name',
    'Orders.status',
    'Users.city',
  ],
};

Now we jump into the functional Grid component. Time for React stuff! Here we define a state variable where we'll store the rows to be displayed in our table. Also, we use the useCubeQuery hook to send the request to Cube.js API. Then, in useEffect, we get the result, transform it into tabular format with the convenient tablePivot method, and assign it to the state. (Remapping is needed because Cube.js returns column names in the Cube.measure and Cube.dimension format but AG Grid doesn't work with dots in the names.)

const [ rowData, setRowData ] = useState([]);
const { resultSet } = useCubeQuery(query);

useEffect(() => {
  if (resultSet) {
    setRowData(resultSet
      .tablePivot()
      .map(row => Object
        .keys(row)
        .reduce((object, key) => ({
          ...object,
          [key.replace('.', '-')]: row[key],
        }), {}),
      ),
    );
  }
}, [ resultSet ]);

Then we extract the column names from the dataset. We'll use them later:

const columnDefs = [
  ...query.dimensions,
  ...query.measures,
].map(field => ({
  headerName: field.split('.')[1],
  field: field.replace('.', '-'),
}));

Time for JSX! Note that the AgGridReact component is wrapped with a div.ag-theme-alpine to apply the custom Ag Grid styles. Also, note how default column styles and properties are set.

The last three lines are the most important ones because they activate the pivot table, enable a convenient sidebar you might know from Excel or similar software, and also wire the row data into the component:

<div className='ag-theme-alpine' style={{ height: 700 }}>
  <AgGridReact
    defaultColDef={{
      flex: 1,
      minWidth: 150,
      sortable: true,
      resizable: true,
    }}
    // ...
    autoGroupColumnDef={{ minWidth: 250 }}
    pivotMode={true}    // !!!
    sideBar={'columns'} // !!!
    rowData={rowData}   // !!!
  >

Here's the most complex part. To transform the row data into a pivot table, we need to specify the column or columns used on the left side and on the top side of the table. With the pivot option we specify that data is pivoted (the top side of the table) by the "status" column. With the rowGroup option we specify that the data is grouped by the "name" column.

Also, we use aggFunc to specify the default aggregation function used to queeze the pivoted values into one as sum. Then, we list all allowed aggregation functions under allowedAggFuncs.

{columnDefs.map((column, i) => {
  // ...

  return (
    <AgGridColumn
      key={i}
      headerName={column.headerName}
      field={column.field}
      enablePivot={true}
      enableRowGroup={isDimension}
      enableValue={isMeasure}
      pivot={column.headerName === 'status'}
      rowGroup={column.headerName === 'name'}
      allowedAggFuncs={[ 'sum', 'max', 'avg', 'min' ]}
      aggFunc={isMeasure ? 'sum' : null}
    />
  );
})}

Here's how these functions are implemented. Nothing fancy, just a little bit of JavaScript functional code for minimum, maximum, sum, and average:

aggFuncs={{
  'min': ({ values }) => values.reduce((min, value) => Math.min(min, Number(value)), 0),
  'max': ({ values }) => values.reduce((max, value) => Math.max(max, Number(value)), 0),
  'sum': ({ values }) => values.reduce((sum, value) => sum + Number(value), 0),
  'avg': ({ values }) => (values.reduce((sum, value) => sum + Number(value), 0) / values.length).toFixed(0),
}}

You can click on "Values" to change the aggregation function used for every column, or set it programmatically as specified above:

And that's all, folks! 🎉 Thanks to AG Grid and Cube.js, we had to write only a few tiny bits of code to create a pivot table.

I strongly encourage you to spend some time with this pivot table and explore what AG Grid is capable of. You'll find column sorting, a context menu with CSV export, drag-and-drop in the sidebar, and much more. Don't hesitate to check AG Grid docs to learn more about these features.

Thank you for following this tutorial, learning more about Cube.js, building a pivot table, and exploring how to work with AG Grid. I wholeheartedly hope that you enjoyed it 😇

Please don't hesitate to like and bookmark this post, write a comment, and give a star to Cube.js or AG Grid on GitHub. I hope that you'll try Cube.js and AG Grid in your next production gig or your next pet project.

Good luck and have fun!

DEV Survey ✅ — Front-end Data Visualization Tools

Igor Lukanin — Mon, 22 Mar 2021 21:48:08 +0000

TL;DR: PLease complete this survey. Thank you! 🙏

Hey friends 👋 I guess that if you've ever been building a web application in your life, chances are you were using a charting library, a data grid, or any other data visualization tool to present your data in a meaningful and comprehensible way.

The overwhelming success with thousands of views and hundreds of bookmarks under this post in Cube.js blog proves my point 🙂

Understanding front-end data visualization tools ecosystem in 2021 📊📈

Nikita Kakuev for Cube ・ Feb 5 '21

#javascript #webdev #frontend #dataviz

Cube.js an open-source project that provides developers with an easy way to create an analytical API over their data stored in any database. More importantly, that API can be paired with any front-end frameworks and data visualization libraries.

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications

In our Slack and in private conversations, the Cube.js team is often asked for a recommendation of a charting library, or a data grid, or any other data visualization tool that works great with Cube.js.

All of them do, but we decided to create a website with a list of data visualization tools that can be used by developers to choose the best tool for their projects.

As the first step, we want to understand how you, as developers, approach this choice and which criteria are the most relevant. That’s why we’re kindly asking you to take part in this short survey:

DEV Survey ✅ — Front-end Data Visualization Tools

Thank you in advance! 😇🙏

Surely, we’ll make the results public in the Cube.js blog, so feel free to subscribe to it and stay tuned!

Also, after you fill the survey, feel free to drop a few lines in the comments. What are the best data visualization on public websites that you know? What are the best tools to make such data visualizations? Let's discuss!

Impact of COVID-19 on people's habits worldwide

Igor Lukanin — Thu, 04 Mar 2021 18:55:18 +0000

TL;DR. I've built a web application that reveals the impact of the COVID-19 pandemic on people's lives and habits. Also, I tell how I built it.

Here's how this application looks like:

Surely, you can select any country — definitely try your own. You will explore how COVID-19 affected social mobility or how "stay at home" requirements were followed (or not).

Insights

Let's take Israel. You can clearly see three waves and the positive effect of "stay at home" requirements — after they are introduced, every wave spreads with lesser speed.

Let's take Germany. You can see how Germans interact with the rules: after the first "stay at home" requirements are lifted, park activity grows, and after the second "stay at home" requirements are introduced, parks instantly become deserted.

Let's take Singapore. Obviously enough, you can see Singapore doing a great job containing the virus. The third wave is nearly unexistent.

Tutorial

You can build this application from scratch using BigQuery public datasets, JavaScript, React, and Cube.js. Read on:

Using BigQuery Public Datasets to research the impact of COVID-19 🦠

Igor Lukanin for Cube ・ Mar 4 '21

#webdev #javascript #database #tutorial

Also, don't hesitate to share your insights in the comments 🙏