DEV Community: Cube

What is Embedded Analytics?

Artyom Keydunov — Thu, 22 Sep 2022 19:58:06 +0000

We’ve previously discussed the modern data stack for embedded analytics—but what is embedded analytics? Let’s take a step back and review this popular category of data application.

Embedded Analytics Definition

Embedded analytics is the integration of data analytics and visualization capabilities within a user’s natural workflow—within internal tooling, web portals, and products like apps.

Such an integration enables users to conduct analytics directly inside the applications they use rather than having to switch between various applications to get insights.

Let’s illustrate with an example: a seller on an e-commerce platform, “TrapezoidSpace,” would like insights about their revenue.

Without embedded analytics on the e-commerce platform, the seller would have to manually request a sales report from the e-commerce platform, import it into secondary analytics software, then work with the data to get the necessary insights.

However, with embedded analytics, they could get the insights they need with a real-time dashboard visualizing revenue presented to them directly within the e-commerce platform.

Embedded Analytics Use Cases

There are many use cases for embedded analytics. We can divide them by the end users and the types of information they present.

External vs. Internal Embedded Analytics

Embedded analytics can be internal-facing—meaning the end user is within an organization—or external-facing, meaning the end user is an organization’s customer.

Take our previous example of TrapezoidSpace: here, the seller is an external end-user of the platform’s embedded analytics features.

In contrast, let’s take the example of TrapezoidSpace’s customer success manager. They track the time-to-resolution of tickets (TTR) using a bespoke ticket management system that visualizes trends in TTR. That would be an example of internal-facing embedded analytics.

Types of Embedded Analytics

People use embedded analytics in many ways, but real-time reports and dashboards are the most common.

Real-time static and interactive reports are tabular views of information with the ability to refine data by setting parameters and scheduling. In contrast, dashboards and data visualizations are charts and graphs that present and visualize metrics to make finding insights and trends easier.

Embedded Analytics Benefits

Generally, embedded analytics contributes to a user-centric UX that adds value and drives adoption.

Whether users are external or internal, the point of an investment in any tooling is to bring value and thereby drive the tool’s adoption. And, providing a user-centric design and seamless UX tailored to the target user is one of the only ways to get people to (willingly) use products.

So, conveniently locating BI and analytics functionality within the user’s workflow significantly enhances a product’s UX. Improved UX promotes the more effective use of the tool, creates greater value, and increases the tool’s adoption.

Externally, embedded analytics increases revenue and competitive advantage.

In a survey of project managers, developers, and executives, 96% reported that embedded analytics contributes to revenue growth. They credited this to better UX and their ability to add revenue streams based on highly sought-after functionalities.

Adding high-value capabilities to products allowed businesses to introduce tiered or subscription-based analytics services—opening the door to acquiring new customers and upselling to recurring ones.

Internally, embedded analytics increases productivity and the accessibility of data-driven decision-making.

The efficiency with which users can conduct analysis with embedded analytics significantly boosts time effectiveness (and, therefore, cost-effectiveness). With auto-generated dashboards and filtering capabilities, users can draw insights faster and avoid the cumbersome processes of manually pulling data and conducting analysis in separate applications.

Easy access to analytics also allows those with less experience in data analysis to make data-driven decisions. In a 2022 survey, 92% of companies cited their biggest obstacle in becoming ‘data-driven’ as people, processes, and change management. However, a custom-built internal embedded analytics platform—one that is tailored to the team’s workflow—doesn’t require complex training in generic BI tools.

Rather than having to facilitate learning of generic BI tools which aren’t set in its work’s context, an organization can create tools that are already infused with the working context. And so, instrumenting intuitively tailored tools powered by embedded analytics removes training bottlenecks and dependencies—increasing productivity and value-add.

Embedded Analytics vs. Business Intelligence

The difference between embedded analytics and traditional business intelligence is simple. While traditional BI means you conduct your analytics in a separate set of tools, embedded analytics allows users to access insights in-app. Their data visualizations, dashboards, and reports live in the application in which they conduct operations, allowing for real-time refreshes and faster learning.

This contrast may seem somewhat minute, but it actually saves employees a lot of time—up to 5 hours a week. These savings increase productivity, allow for constantly updated information, and streamline the entire analytics workflow.

How Cube can help you power your Embedded Analytics

Cube is a headless business intelligence tool that accesses data from raw data sources, models the data, caches query results, and makes the data available to downstream data applications via REST, GraphQL, and SQL APIs.

“Headlessness” refers to tooling that separates its functionalities from the end user’s interface—making it the perfect basis for building branded and customizable embedded analytics for any application.

Cube is unique in that it provides a centralized, upstream semantic layer to define metrics, making all of the analytics it powers consistent and accurate. This semantic layer and its caching capabilities make sure that an application is performant and cost-effectively provides correct, up-to-date information to all of the users who access it.

Similarly, Cube’s centralized data access control supports multi-tenancy and allows role-based and column-based governance, which ensures users are only accessing data appropriate to them. This type of security is especially crucial in external-facing embedded analytics, where many separate entities—for example, each seller on TrapezoidSpace—need to access their data and their data only.

Cube enables developers to build consistent, secure, and performant embedded analytics efficiently; dashboards in hours, not weeks.

It can be the foundation for building embedded analytics applications with its compatibility with many popular front-end tools and business applications. So many, in fact, that we built a wiki about our favorite tools in the modern data stack—including tools for building embedded analytics (aptly named “awesome.cube.dev”).

Learn more

Want to learn more about how to build embedded analytics applications with Cube or headless BI?

Get in touch with us and the rest of the Cube community on Slack or Github.

In the mood for some hands-on help with building a modern embedded analytics data stack? Schedule a time to chat with us 1:1.

What are data apps?

Artyom Keydunov — Thu, 22 Sep 2022 19:55:58 +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.

Embedded Analytics with Tableau and Cube

Artyom Keydunov — Thu, 22 Sep 2022 19:52:56 +0000

These days, customer-facing analytics are everywhere.

We are, after all, in The Data Age—hence why you’re reading this blog and why we’re writing it. And because of the ubiquity of data, as consumers, we’ve become extraordinarily curious; we want to know everything about our businesses, investments, and lap times.

Consumers are pining for insights—presented in fast-loading, beautifully customized dashboards. And so, many companies are turning to embedded analytics with Tableau.

But as more consumers demand analytics, raising the standards of UI customization, interactivity, and speed, what if there were a more tailored, more performant way to give users the insights they need?

Well, there is: embedded analytics with Tableau and Cube. Let’s explain.

What is Embedded Analytics?

Simply put, embedded analytics is the practice of integrating data analytics and visualization capabilities with a user’s natural workflow, such as their web portals, internal tools, or applications.

For example: with embedded analytics, rather than needing to export sales data from their e-commerce platform and then conduct analysis separately in Excel, a seller could derive insights from dashboards and reports available to them directly within their commerce portal.

Of course, embedded analytics can be used in internal- and external-facing applications to provide real-time static and interactive reports, dashboards, and data visualizations.

There are many benefits of using embedded analytics. First, it can save the user time and effort and add a lot of value. In turn, this saved time and gleaned insights contribute to a user-centric UX that drives adoption—even among those who aren’t as analytics-experienced. And higher adoption means a better ROI on internal tooling and an increase in revenue for external tooling.

Embedded Analytics with Tableau

So, why do so many companies turn to embedded analytics with Tableau?

Tableau is one of the leading platforms for data visualization and dashboarding. It's based on its own VizQL, which expresses data visually by translating drag-and-drop actions into data queries.

This drag-and-drop capability makes data analytics and business intelligence accessible to a much wider pool of people—which is a great thing. Clearly, given its smashing success, Tableau met a hot demand for data democratization and did it well.

However, things get a little more hinky when it comes to the embedded analytics stack. This is because users not only use Tableau for analytics within Tableau itself but also use it to power embedded analytics within other applications.

The problem is that having your embedded analytics with Tableau sit directly on top of your data warehouse comes with a few shortcomings, such as:

Difficulty with customizability and designing user-centric UIs
Duplicated effort and opportunities for inconsistency when creating multiple sets of data definitions and security contexts
Weaker analytics performance

1) Difficulty with customizability and designing user-centric UIs

Tableau has many visualization layer options, but it just doesn’t have one for every audience or use case. You can undoubtedly brand the Tableau visualizations embedded in your app; however, there are restrictions on what you can build with the visualization layers Tableau offers (e.g., extremely use-case-specific drill-downs).

And so, Tableau’s visualization templates may work just fine for 80% of your applications, but those that truly need a highly tailored, custom UI will require you to build a custom presentation layer from scratch.

The problem is that using multiple different visualization layers together with Tableau—meaning, both custom-built and from Tableau—leads to…

2) Duplicated effort and opportunities for inconsistency

So you want to use Tableau and a separate charting library to build a custom presentation layer? That makes perfect sense when you want to combine the advantages of Tableau’s presentation layers with the flexibility of a custom UI.

However, the challenge lies in consistently orchestrating data modeling, security, and caching across all of your presentation layers.

By connecting multiple presentation layers to your data warehouse, you’ll need to individually orchestrate your data models and access controls so they’re uniformly defined. That’s a lot of duplicated effort—which opens the door to gaps and errors.

3) Weaker analytics performance

Lastly, powering embedded analytics with Tableau without a BI layer between Tableau and the data warehouse can cause latency in your user-facing visualizations and dashboards.

Latency is a true user adoption killer: a Google study showed that bounce likelihood increased by 32% when a page’s load time increased from one second to three, and by 90% when page load time increased from one to five seconds. So, performance is essential.

In a stack in which Tableau’s presentation layer sits directly on top of your data source, your users request data from the data source for every query. At best, this is redundant and costly, as multiple users may be requesting the same information.

At worst, however, building a stack like this can result in latency and lower performance. Request volume across users is unpredictable; you can’t control how many people simultaneously request data or consume visualizations. And so, if you have multiple presentation layers, each with duplicate data modeling, security, and caching layers, each making separate and redundant requests directly of the data source—that opens the door to latency.

Latency can be a problem even if you only use Tableau (don’t worry, we happen to have a fix). But, with multiple presentation layers, the chance of uneven performance across them is much higher.

The best of both worlds: embedded analytics with Tableau and Cube

Alright—let’s get to the solution.

Tableau is a fantastic tool (in fact, here’s how to use Tableau with Cube.) Combining it with custom presentation layers is a great way to get both efficient, out-of-the-box visualizations and user-centric UI for your embedded analytics.

So how can you fix the issues above? With upstream, centralized data modeling, security, and caching orchestration. Hi, we’re Cube.

Cube streamlines building embedded analytics with Tableau and custom presentation layers

Cube is a headless BI platform that enables your data modeling, data access control, and caching layers to live upstream of every presentation layer and data application in your stack.

So, rather than individually orchestrating role-based access controls or metrics definitions for each presentation layer, you’re centralizing and defining them once—removing duplicated effort and its inevitable inconsistencies.

Cube ensures uniformly performant embedded analytics powered by Tableau and custom presentation layers

Cube’s robust caching layer manages the throughput of data requests and acts as a buffer between your data source and your data application. Its advanced pre-aggregation capabilities, query queue, and refresh keys protect your data warehouse from redundant, costly, and latency-causing queries.

Designed to protect the data source from the possibility of even an unlimited number of concurrent requests, Cube can significantly speed up your embedded Tableau dashboards and visualizations.

Cube also makes embedded analytics more performant even when built with a combination of Tableau and custom presentation layers. By allowing Cube to do the heavy lifting of data modeling, security, and caching orchestration, the presentation layers become very “thin”—essentially, not needing to process much. This enables them to become uniformly performant and allows the application’s embedded analytics to become more performant as a whole.

Learn More

Interest piqued? We thought it might be.

Give your users a performant, data-rich embedded analytics experience with Tableau and Cube—especially since Cube is free to use and we already have a tutorial for using Tableau with Cube.

Get in touch with us and the rest of the Cube community on Slack or Github.

In the mood for some hands-on help with building a modern embedded analytics data stack? Schedule a time to chat with us 1:1.

Headless BI with streaming data

Artyom Keydunov — Thu, 22 Sep 2022 19:45:23 +0000

Demand to make data operational and real-time is ever-growing. Both consumers and enterprises expect applications to react to changes in real time, and be proactive with intelligent notifications and alerts.

How can Cube help?

At Cube, our primary sources of data have been cloud data warehouses with batched data—but since our first days, we have wanted to work with streaming data too. Our ambition has been to create a seamless experience for data engineers to build applications on top of a single, headless data access layer, regardless of whether the data is a stream, a batch, or a union of both.

Today, we’re happy to announce a major step in building streaming support into Cube, and to share our plans for what’s next.

We are hosting an online event to reveal these features. Register now to join us on October 13.

The challenge

Historically, it has been hard to work with truly real-time data. Specifically, it’s been challenging to combine historical and real-time data. For example, in use cases such as notifications or recommendations, we can’t rely only on last-minute data; we need to look back to run analysis, and then act in real-time, based on both the most recent data and historical trends.

There’s also the concern of needing to master unique languages for specific data sources; in the past, it often was necessary to build one-off integrations for each streaming application to accommodate real-time streams.

The opportunity

Streaming data enables a variety of use cases, including real-time, in-product analytics; automation; personalization; and alerting. Fortunately, recent innovations have made it easier for us to accommodate real-time streams.

In the last few years, streaming SQL technologies such as ksqlDB, Materialize, and Apache Flink have significantly progressed. These technologies enable us to process streaming data and run analysis with SQL—without needing to learn a new language or build specific language-unique integrations.

ksqlDB support

We’re excited to announce that it’s now possible to use Cube to build data modeling, caching, and access control layers on top of streaming SQL, just as you can with data from cloud data warehouses.

Cube now can connect to streaming SQL engines and expose streaming data via our REST, GraphQL, and SQL APIs to your downstream applications. Our REST API already supports websockets and provides a seamless experience for developers and data engineers to build real-time data apps. Our data modeling, caching, access control, and querying APIs are the same whether you work with streaming or batch data.

Want to see it in action? Check out our live ksqlDB demo application featuring a real-time dashboard built with Kafka, ksqlDB, and Cube.

That alone opens many opportunities for building on top of streaming data, but we’re also committed to addressing the complex problem of merging batch and streaming data, so that we can provide a single interface for unioned data.

Lambda architecture support

We’re also introducing lambda pre-aggregations. These follow the lambda architecture design to create a union of streaming and batch data. Cube pre-aggregates batch data from the cloud data warehouse into one pre-aggregation, data from streaming SQL engines into another, then unites these pre-aggregations during query processing—so we can return merged data.

For developers or data consumers, the interface remains the same—REST, GraphQL, or SQL queries. Our Lambda architecture makes it possible to consume data as a single dataset regardless of whether it is streaming, batch, or combined data.

Cube hides the complexity of this architecture within our data modeling layer, and therefore significantly simplifies the data consumption process. In addition, our access control and security layers are applied using the same rules as you’ve configured them for batched data.

Coming next

Naturally, our work isn’t done yet. The above features are generally available soon—and available today to Cube users who request access. Soon, you’ll also have access to

Materialize streaming pre-aggregations
Flink SQL support
Spark Streaming support

If you’re interested in early access to these features, to help influence their development and craft the future of streaming headless BI, simply get in touch.

Everything you need to build powerful real-time applications
We’re excited to launch our Kafka and ksqlDB integration and lambda pre-aggregations today because these bring the power and simplicity of our headless BI architecture to new types of data applications.

These innovations are available to everyone, for free: request ksqlDB preview access, connect a real-time data source, then (please!) give us your feedback.

We look forward to powering the next generation of data applications—we can’t wait to see what you build.

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!

How to build tree-shakeable JavaScript libraries

Dan Onoshko — Wed, 14 Sep 2022 17:09:02 +0000

If you’re a web developer, I’m sure that you’ve heard about tree-shaking. It’s often understood as the removal of unused code from JavaScript bundles. However, do you know that not all code that looks unused is tree-shakeable?

In this blog post, we’ll learn more about tree-shaking and tree-shakable design. You’ll know to write JavaScript code in a tree-shakeable way and produce smaller bundles. If you’re building a JavaScript library, is a must.

This is a part of the series of posts on best practices where I’m sharing bits of my experience contributing to open source. It’s both my hobby and job at Cube where we create and maintain open source tools for building data applications.

What is tree-shaking?

Wait! If tree-shaking is the removal of unused code from bundles, then how is it different from “dead code elimination” that has been a feature of JavaScript minifiers for quite a while? It’s a fair question, and the answer is that tree-shaking is a later term that is only relevant if JavaScript bundlers and ES6 imports/exports are used.

Bundlers analyze ES6 imports/exports at build time to find unused code. Since ES6 imports/exports are a part of JavaScript syntax, it’s quite easy to perform a static analysis of used and unused exports.

On the other hand, CommonJS exports are resolved at run time and can be dynamic. That’s why static analysis of used and unused exports is impossible for CommonJS modules.

Let’s compare how Webpack bundles CommonJS and ES6 modules. In this example, we export two functions (sum and div) from a CommonJS module but only one of them (sum) is imported and used. (Note that here the code in bundle.js is shown before minification.)

// CommonJS
// module.js

exports.sum = function sum(a, b) {
  return a + b;
};

exports.div = function div(a, b) {
  return a / b;
};

// index.js

const { sum } = require('./module');

console.log(sum);

// bundle.js

(() => {
  var __webpack_modules__ = ({
    739:
      ((__unused_webpack_module, exports) => {
        exports.sum = function sum(a, b) {
          return a + b;
        };

        exports.div = function div(a, b) {
          return a / b;
        };
      })
  });

  function __webpack_require__(moduleId) {
    /* ... */
  }

  (() => {
    const { sum } = __webpack_require__(739);

    console.log(sum);
  })();
})();

You can see that Webpack added the __webpack_require__ function to the bundle to support CommonJS module loading. Also, the div function is present in the bundle and won’t be removed after minification because it’s assigned to an object property (exports.div) and will be considered “used”. Compare to an ES6 module:

// ESM
// module.js

export function sum(a, b) {
  return a + b;
}

export function div(a, b) {
  return a / b;
}

// index.js

import { sum } from './module.js';

console.log(sum);

// bundle.js

(() => {
  "use strict";

  function sum(a, b) {
    return a + b;
  }

  function div(a, b) {
    return a / b;
  }

  console.log(sum)
})();

You can see that the div function was copied “as is” and is not used in any way. When you run Webpack in production mode, it will remove this function. (Internally, Webpack relies on Terser for minification; Rollup performs tree-shaking and dead code elimination on its own.)

What is tree-shakable code?

In the example above, two functions were exported from the module and only the “used” one made it to the bundle. What if not only functions are exported? (Note that here the code in bundle.js is shown after minification.)

// module.js

export const foo = 'bar'.toUpperCase();

export const PI_2 = Math.PI * 2;

export function sum(a, b) {
  return a + b;
}

// index.js

import { sum } from './module.js'

console.log(sum);

// bundle.js

(() => {
  "use strict";
  "bar".toUpperCase(), Math.PI;
  console.log((function(a, b) {
    return a + b;
  }));
})();

You might be surprised that all exported entities are present in the bundle, although only the sum function was imported. Let’s explore why.

Pure code

Removing unused functions is quite straightforward. If a function is never called, its declaration should be considered “dead code” and removed from the bundle. It works because function declarations have no side effects: they don’t update global variables, don’t work with DOM, etc. (Please don’t confuse function declarations with function calls; the latter may have side effects.)

Consider the first exported entity: 'bar'.toUpperCase(). This is a function call and it may have side effects. Well, we know that toUpperCase is a pure function, meaning calling it has no side effects. However, minifiers know nothing about the standard library. That’s why the function call wasn't removed from the bundle so the code doesn’t break.

Now consider the second exported entity: Math.PI * 2. This is an arithmetic operation (a constant is multiplied by two) and should not have any side effects. Now take a closer look: what does Math.PI do? It denotes access to a property of an object and it may have side effects. Access to a variable is always pure, but access to a property may invoke a getter method that can do anything. Minifiers don’t know how pure that code is so they leave it intact. (Obviously, this logic applied to setters as well.)

How to make pure code tree-shakable?

If you know that some code is pure, there’s a way to tell minifiers about that. By convention, placing the /* #__PURE__ */ comment before a function call or a constructor call (new) marks them as pure. If you need to mark access to a property as pure, you can wrap it with a pure function:

// module.js

export const foo = /* #__PURE__ */ 'bar'.toUpperCase();

function getPi2() {
  return Math.PI * 2;
}

export const PI_2 = /* #__PURE__ */ getPi2();

export function sum(a, b) {
  return a + b;
}

// index.js

import { sum } from './module.js'

console.log(sum);

// bundle.js

(() => {
  "use strict";
  console.log((function(a, b) {
    return a + b;
  }));
})();

Now it looks better, right?

Here are a few real-world examples of code purification from my contributions to open source:

In Chart.js, tree-shaking didn’t work as expected because of assignments to class properties (replaced with static properties).
In react-chartjs-2, some exported entities are constructed by calling a factory method (marked as pure).

You can experiment with dead code detection yourself in Terser REPL.

`sideEffects: false`

If you’re building a library, you should know about the sideEffects property in the package.json file. It's similar to /* #__PURE__ */ but on a module level instead of a statement level. By setting it to false, you can mark the entire code of your library as pure:

{
  "sideEffects": false
}

You can also set it to an array of files that contain side effects:

{
  "sideEffects": ["./dist/polyfill.js"]
}

You can learn more about the sideEffects property in the Webpack documentation. Please pay attention to this behavior: a package, marked as pure with sideEffects: false, will be skipped entirely if imported as a whole.

// module.js

export const foo = 'bar'.toUpperCase();

export const PI_2 = Math.PI * 2;

export function sum(a, b) {
  return a + b;
}

// index.js

import './module.js'

console.log('noop');

// bundle.js

(() => {
  "use strict";
  console.log('noop');
})();

However, if individual entities are imported, everything works as described in the previous section and you still need to use /* #__PURE__ */:

// module.js

export const foo = 'bar'.toUpperCase();

export const PI_2 = Math.PI * 2;

export function sum(a, b) {
  return a + b;
}

// index.js

import { sum } from './module.js'

console.log(sum);

// bundle.js

(() => {
  "use strict";
  "bar".toUpperCase(), Math.PI;
  console.log((function(a, b) {
    return a + b;
  }));
})();

Things to consider when using sideEffects: false:

If your library code is already covered with /* #__PURE__ */, using sideEffects: false will have no effect.
If your library provides global style sheets (not CSS modules), they should also be added to the array of files that contain side effects:

{
  "sideEffects": ["./dist/index.css"]
}

It’s required because such style sheets are usually imported directly:

import 'mylib/dist/index.css'

Beware when developing a library: bundlers will look for sideEffects: false not only in the dependencies of your library but in its package.json as well.

Tree-shakable design

Now we know how tree-shaking works in principle. However, we should also design a library in a tree-shakeable way. For instance, if the entire library is packed in a single class, tree-shaking wouldn’t be able to remove any code.

Here are the rules of tree-shakable design:

All entities, including class methods, that might not be used should be exported separately (so they can potentially be removed from the bundle).
Classes and objects should be exported cautiously (because their contents can’t be removed from the bundle).
All code should be free of side effects by default. Minimal, if any, instances of non-pure code should be permitted.

Consider the following examples.

Example A

Imagine we’re writing an internationalization library:

// non-tree-shakable

import I18n from 'my-i18n-lib';

const i18n = new I18n(config);

console.log(i18n.__('foo'));
// console.log(i18n.__n('bar', 5));

// tree-shakable

import { I18n, translate, translatePlural } from 'my-i18n-lib';

const i18n = new I18n(config);
const __ = i18n.bind(translate);

console.log(__('foo'));

🙅 In the non-tree-shakable variant, a single giant class is exported. The bundle will include all class methods, including unused ones.

🙆 In the tree-shakable variant, potentially unused class methods are exported separately as functions. The bundle will include only used functions.

Example B

Imagine we’re writing a data visualization library:

// non-tree-shakable

import { BarChart } from 'my-charting-lib';

new BarChart('#chart', data, {
  axisX: {
    type: 'auto'
  },
  axisY: {
    type: 'fixed' // 'step'
  }
});

// tree-shakable

import {
  BarChart,
  AutoScaleAxis,
  FixedScaleAxis,
  StepAxis
} from 'my-charting-lib';

new BarChart('#chart', data, {
  axisX: {
    type: AutoScaleAxis
  },
  axisY: {
    type: FixedScaleAxis
  }
});

🙅 In the non-tree-shakable variant, all axis types should be imported inside BarChart so it can use some of them according to the options provided. The bundle will include all classes for all axis types.

🙆 In the tree-shakable variant, only necessary axis types are imported and provided to BarChart. The bundle will include classes only for used axis types.

(Full disclosure: this is a real-world design decision from Chartist, an open-source data visualization library I maintain and contribute to.)

Example C

Imagine we’re writing a more complex data visualization library like Chart.js. In that case, you may choose to implement dependency injection:

import {
  Chart,
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Legend
} from 'chart.js';

Chart.register(
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Legend
);

new Chart(ctx, {
  type: 'line',
  data: data,
  options: {
    plugins: {
      legend: {
        position: 'top',
      },
      title: {
        display: true,
        text: 'Chart.js Line Chart',
      },
    }
  }
});

🙆 In this tree-shakable implementation, dependency injection via Chart.register is used. Library code is very well split into individual parts that are exported separately. The bundle will include classes only for used entities.

Keeping code tree-shakable

A one-time effort to write or rewrite a JavaScript library in a tree-shakable way is not enough. One day, due to lousy code or code review, tree-shaking might brake.

However, you can use the Size Limit library to control the size of a bundle or individual exports. Here’s an example configuration:

// .size-limit.json

[
  {
    "path": "dist/chart.esm.js",
    "limit": "19 KB",
    "import": "{ BarController, BubbleController, DoughnutController, LineController, PolarAreaController, PieController, RadarController, ScatterController }"
  },
  {
    "path": "dist/chart.esm.js",
    "limit": "14 KB",
    "import": "{ ArcElement, LineElement, PointElement, BarElement }"
  },
  {
    "path": "dist/chart.esm.js",
    "limit": "27 KB",
    "import": "{ Decimation, Filler, Legend, SubTitle, Title, Tooltip }"
  },
  {
    "path": "dist/chart.esm.js",
    "limit": "22 KB",
    "import": "{ CategoryScale, LinearScale, LogarithmicScale, RadialLinearScale, TimeScale, TimeSeriesScale }"
  }
]

Size Limit integrates with CI such as GitHub Actions and will alert you if any pull request drastically increases the bundle size, possibly due to broken tree-shaking.

What’s next?

Please use tree-shaking in your code at all times. If you’d like to learn how tree-shaking works by example, check out this sandbox I’ve built.

If you have an existing application or library:

make sure it complies with the three rules of tree-shakable design;
make sure pure functions are marked as such with /* #__PURE__ */;
make sure Size Limit is used to notify if tree-shaking brakes.

Good luck!

What are data apps?

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

But first…

What are data applications?

Isn’t this just embedded analytics?

What are some use cases of data applications?

How are data applications built?

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

Headless BI layer

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

A hybrid presentation layer

Working with a framework

What's next?

Browserslist: building modern web apps for diverse global audience

Nastya Kataeva — Wed, 24 Aug 2022 16:25:00 +0000

Nowadays, web applications are usually built for diverse audience that spans worldwide across countries and continents. Upon checking web analytics, you would probably find that your audience is almost evenly divided between Americas, Asia, and Europe—and you surely can’t ignore Africa and Oceania:

Web browser usage differs substantially across regions. For instance, iOS devices are far less popular in Netherlands than in the US. In China, you will see popular local browsers like QQ Browser or UC Browser that aren’t present anywhere else in the world:

However, support for modern web technology also differs across browsers and browser versions:

So, we as web developers need to find the right balance between the user experience (UX) of the applications we’re building and our own developer experience (DX):

we’d like our apps to work flawlessly in any browser that our audience uses
we’d also like our apps to be smaller in size so they download faster
at the same time, we’d like to use modern JavaScript and CSS features

Of course, we have great tooling for that: Autoprefixer, PostCSS and Stylelint for CSS transformation, Babel and Webpack for JavaScript transpilation and bundling, ESLint for code analysis, and many others.

But how do we tell all these tools about the audience of our application, browsers, and supported features?

Browserslist to the rescue! Browserslist is a library that helps share the browser compatibility configuration with front-end tools. All popular tools, including the ones listed above, already work with Browserslist. More tools integrate as we speak: for instance, Next.js joined the club in the v12.2 release.

Browserslist guarantees that your web application would work in all target browsers; JavaScript and CSS code would be correctly transpiled. On top of that, you’ll get smaller bundle size and application load time.

How to get started with Browserslist

First, you need to add a Browserslist config right to the package.json:



"browserslist": [
  ">0.1%"
]

This config matches all browser versions that are used by at least 0.1 % of the audience worldwide. Autoprefixer, Babel, and other tools will find target browsers automatically and bundle the code accordingly.

But what exactly are these target browsers and their versions? Browserslist provides a CLI interface to check that:



npx browserslist ">0.1%"

Voila! We get browsers ranging from QQ Browser and UC Browser to Internet Explorer and Opera Mini. They are still not going anywhere:

Actually, the queries may be much more complicated than that: let’s include the most recent versions of all browsers that have been just released, exclude dead browsers, and leave only those ones that support ES6 modules:



npx browserslist ">0.1% and last 2 versions and not dead and supports es6-module"

Browserslist’s CLI interface is good enough to quickly check a single query. However, when composing and experimenting with a query for your production application, this can be helpful:

better data visualization for audience coverage
query linter and query syntax docs

Goodbye, console. Meet browsersl.ist website.

Easy way to check compatible browsers

browsersl.ist website is the companion tool for Browserslist. Check it out!

First, it can help with the audience coverage. It’s recommended to use the defaults query as the starting point because this query provides reasonable configuration for most users. It covers global audience and matches recent versions of popular and well-maintained browsers worldwide:

You can view the coverage for particular continents and countries. Apparently, seals and penguins as well as other inhabitants of the research stations in Antarctica prefer to use Safari:

The coverage data is shared with the Can I Use database, so you can check which browser versions support certain features such as CSS Container Queries:

Browserslist has a rich query syntax to fine-tune the query to your audience. So, browsersl.ist website provides complete documentation on query features; you might be surprised to find there that you can also target Node.js versions:

Next up: try Browserslist and browsersl.ist today

Browserslist is a must-have tool for building modern web applications.

Here at Cube, where we build the API for modern data apps, we use it across all our websites, including blog, docs, and data application examples. With Browserslist, we’re sure that Cube’s global developer community gets the best experience possible. We’re also okay to serve ~10 % less JavaScript and CSS code because of smaller bundles.

Now, it's time to visit browsersl.ist and see it in action! Whether you’re new to Browserslist or not, browsersl.ist is the best playground.

Don't hesitate to share a link to browsersl.ist with your friends or post queries with surprising or unexpected results on social media. We'd love to know what you think, so please leave your feedback in the comments and tag us on Twitter.

Parsing logs from multiple data sources with Ahana and Cube

Bartosz Mikulski — Wed, 08 Jun 2022 14:31:39 +0000

Ahana provides managed Presto clusters running in your AWS account.

Presto is an open-source distributed SQL query engine, originally developed at Facebook, now hosted under the Linux Foundation. It connects to multiple databases or other data sources (for example, Amazon S3). We can use a Presto cluster as a single compute engine for an entire data lake.

Presto implements the data federation feature: you can process data from multiple sources as if they were stored in a single database. Because of that, you don’t need a separate ETL (Extract-Transform-Load) pipeline to prepare the data before using it. However, running and configuring a single-point-of-access for multiple databases (or file systems) requires Ops skills and an additional effort.

However, no data engineer wants to do the Ops work. Using Ahana, you can deploy a Presto cluster within minutes without spending hours configuring the service, VPCs, and AWS access rights. Ahana hides the burden of infrastructure management and allows you to focus on processing your data.

What is Cube?

Cube is a headless BI platform for accessing, organizing, and delivering data. Cube connects to many data warehouses, databases, or query engines, including Presto, and allows you to quickly build data applications or analyze your data in BI tools. It serves as the single source of truth for your business metrics.

This article will demonstrate the caching functionality, access control, and flexibility of the data retrieval API.

Integration

Cube's battle-tested Presto driver provides the out-of-the-box connectivity to Ahana.

You just need to provide the credentials: Presto host name and port, user name and password, Presto catalog and schema. You'll also need to set CUBEJS_DB_SSL to true since Ahana has secures Presto connections with SSL.

Check the docs to learn more about connecting Cube to Ahana.

Example: Parsing logs from multiple data sources with Ahana and Cube

Let's build a real-world data application with Ahana and Cube.

We will use Ahana to join Amazon Sagemaker Endpoint logs stored as JSON files in S3 with the data retrieved from a PostgreSQL database.

Suppose you work at a software house specializing in training ML models for your clients and delivering ML inference as a REST API. You have just trained new versions of all models, and you would like to demonstrate the improvements to the clients.

Because of that, you do a canary deployment of the versions and gather the predictions from the new and the old models using the built-in logging functionality of AWS Sagemaker Endpoints: a managed deployment environment for machine learning models. Additionally, you also track the actual production values provided by your clients.

You need all of that to prepare personalized dashboards showing the results of your hard work.

Let us show you how Ahana and Cube work together to help you achieve your goal quickly without spending days reading cryptic documentation.

You will retrieve the prediction logs from an S3 bucket and merge them with the actual values stored in a PostgreSQL database. After that, you calculate the ML performance metrics, implement access control, and hide the data source complexity behind an easy-to-use REST API.

In the end, you want a dashboard looking like this:

How to configure Ahana?

Allowing Ahana to access your AWS account

First, let’s login to Ahana, and connect it to your AWS account. We must create an IAM role allowing Ahana to access our AWS account.

On the setup page, click the “Open CloudFormation” button. After clicking the button, we get redirected to the AWS page for creating a new CloudFormation stack from a template provided by Ahana. Create the stack and wait until CloudFormation finishes the setup.

When the IAM role is configured, click the stack’s Outputs tab and copy the AhanaCloudProvisioningRole key value.

We have to paste it into the Role ARN field on the Ahana setup page and click the “Complete Setup” button.

Creating an Ahana cluster

After configuring AWS access, we have to start a new Ahana cluster.

In the Ahana dashboard, click the “Create new cluster” button.

In the setup window, we can configure the type of the AWS EC2 instances used by the cluster, scaling strategy, and the Hive Metastore. If you need a detailed description of the configuration options, look at the “Create new cluster” section of the Ahana documentation.

Remember to add at least one user to your cluster! When we are satisfied with the configuration, we can click the “Create cluster” button. Ahana needs around 20-30 minutes to setup a new cluster.

Retrieving data from S3 and PostgreSQL

After deploying a Presto cluster, we have to connect our data sources to the cluster because, in this example, the Sagemaker Endpoint logs are stored in S3 and PostgreSQL.

Adding a PostgreSQL database to Ahana

In the Ahana dashboard, click the “Add new data source” button. We will see a page showing all supported data sources. Let’s click the “Amazon RDS for PostgreSQL” option.

In the setup form displayed below, we have to provide the database configuration and click the “Add data source” button.

Adding an S3 bucket to Ahana

AWS Sagemaker Endpoint stores their logs in an S3 bucket as JSON files. To access those files in Presto, we need to configure the AWS Glue data catalog and add the data catalog to the Ahana cluster.

We have to login to the AWS console, open the AWS Glue page and add a new database to the data catalog (or use an existing one).

Now, let’s add a new table. We won’t configure it manually. Instead, let’s create a Glue crawler to generate the table definition automatically. On the AWS Glue page, we have to click the “Crawlers” link and click the “Add crawler” button.

After typing the crawler’s name and clicking the “Next” button, we will see the Source Type page. On this page, we have to choose the “Data stores” and “Crawl all folders” (in our case, “Crawl new folders only” would work too).

On the “Data store” page, we pick the S3 data store, select the S3 connection (or click the “Add connection” button if we don’t have an S3 connection configured yet), and specify the S3 path.

Note that Sagemaker Endpoints store logs in subkeys using the following key structure: endpoint-name/model-variant/year/month/day/hour. We want to use those parts of the key as table partitions.

Because of that, if our Sagemaker logs have an S3 key: s3://the_bucket_name/sagemaker/logs/endpoint-name/model-variant-name/year/month/day/hour, we put only the s3://the_bucket_name/sagemaker/logs key prefix in the setup window!

Let’s click the “Next” button. In the subsequent window, we choose “No” when asked whether we want to configure another data source. Glue setup will ask about the name of the crawler’s IAM role. We can create a new one:

Next, we configure the crawler’s schedule. A Sagemaker Endpoint adds new log files in near real-time. Because of that, it makes sense to scan the files and add new partitions every hour:

In the output configuration, we need to customize the settings.

First, let’s select the Glue database where the new tables get stored. After that, we modify the “Configuration options.”

We pick the “Add new columns only” because we will make manual changes in the table definition, and we don’t want the crawler to overwrite them. Also, we want to add new partitions to the table, so we check the “Update all new and existing partitions with metadata from the table.” box.

Let’s click “Next.” We can check the configuration one more time in the review window and click the “Finish” button.

Now, we can wait until the crawler runs or open the AWS Glue Crawlers view and trigger the run manually. When the crawler finishes running, we go to the Tables view in AWS Glue and click the table name.

In the table view, we click the “Edit table” button and change the “Serde serialization lib” to “org.apache.hive.hcatalog.data.JsonSerDe” because the AWS JSON serialization library isn’t available in the Ahana Presto cluster.

We should also click the “Edit schema” button and change the default partition names to values shown in the screenshot below:

After saving the changes, we can add the Glue data catalog to our Ahana Presto cluster.

Configuring data sources in the Presto cluster

Go back to the Ahana dashboard and click the “Add data source” button. Select the “AWS Glue Data Catalog for Amazon S3” option in the setup form.

Let’s select our AWS region and put the AWS account id in the “Glue Data Catalog ID” field. After that, we click the “Open CloudFormation” button and apply the template. We will have to wait until CloudFormation creates the IAM role.

When the role is ready, we copy the role ARN from the Outputs tab and paste it into the “Glue/S3 Role ARN” field:

On the Ahana setup page, we click the “Add data source” button.

Adding data sources to an existing cluster

Finally, we can add both data sources to our Ahana cluster.

We have to open the Ahana “Clusters” page, click the “Manage” button, and scroll down to the “Data Sources” section. In this section, we click the “Manage data sources” button.

We will see another setup page where we check the boxes next to the data sources we want to configure and click the “Modify cluster” button. We will need to confirm that we want to restart the cluster to make the changes.

Writing the Presto queries

Before configuring Cube, let’s write the Presto queries to retrieve the data we want.

The actual structure of the input and output from an AWS Sagemaker Endpoint depends on us. We can send any JSON request and return a custom JSON object.

Let’s assume that our endpoint receives a request containing the input data for the machine learning model and a correlation id. We will need those ids to join the model predictions with the actual data.

Example input:

{"time_series": [51, 37, …, 7], "correlation_id": "cf8b7b9a-6b8a-45fe-9814-11a4b17c710a"}

In the response, the model returns a JSON object with a single “prediction” key and a decimal value:

{"prediction": 21.266147618448954}

A single request in Sagemaker Endpoint logs looks like this:

{"captureData": {"endpointInput": {"observedContentType": "application/json", "mode": "INPUT", "data": "eyJ0aW1lX3NlcmllcyI6IFs1MS40MjM5MjAzODYxNTAzODUsIDM3LjUwOTk2ODc2MTYwNzM0LCAzNi41NTk4MzI2OTQ0NjAwNTYsIDY0LjAyMTU3MzEyNjYyNDg0LCA2MC4zMjkwMzU2MDgyMjIwODUsIDIyLjk1MDg0MjgxNDg4MzExLCA0NC45MjQxNTU5MTE1MTQyOCwgMzkuMDM1NzA4Mjg4ODc2ODA1LCAyMC44NzQ0Njk2OTM0MzAxMTUsIDQ3Ljc4MzY3MDQ3MjI2MDI1NSwgMzcuNTgxMDYzNzUyNjY5NTE1LCA1OC4xMTc2MzQ5NjE5NDM4OCwgMzYuODgwNzExNTAyNDIxMywgMzkuNzE1Mjg4NTM5NzY5ODksIDUxLjkxMDYxODYyNzg0ODYyLCA0OS40Mzk4MjQwMTQ0NDM2OCwgNDIuODM5OTA5MDIxMDkwMzksIDI3LjYwOTU0MTY5MDYyNzkzLCAzOS44MDczNzU1NDQwODYyOCwgMzUuMTA2OTQ4MzI5NjQwOF0sICJjb3JyZWxhdGlvbl9pZCI6ICJjZjhiN2I5YS02YjhhLTQ1ZmUtOTgxNC0xMWE0YjE3YzcxMGEifQ==", "encoding": "BASE64"}, "endpointOutput": {"observedContentType": "application/json", "mode": "OUTPUT", "data": "eyJwcmVkaWN0aW9uIjogMjEuMjY2MTQ3NjE4NDQ4OTU0fQ==", "encoding": "BASE64"}}, "eventMetadata": {"eventId": "b409a948-fbc7-4fa6-8544-c7e85d1b7e21", "inferenceTime": "2022-05-06T10:23:19Z"}

AWS Sagemaker Endpoints encode the request and response using base64. Our query needs to decode the data before we can process it. Because of that, our Presto query starts with data decoding:

with sagemaker as (
  select
  model_name,
  variant_name,
  cast(json_extract(FROM_UTF8( from_base64(capturedata.endpointinput.data)), '$.correlation_id') as varchar) as correlation_id,
  cast(json_extract(FROM_UTF8( from_base64(capturedata.endpointoutput.data)), '$.prediction') as double) as prediction
  from s3.sagemaker_logs.logs
)
, actual as (
  select correlation_id, actual_value
  from postgresql.public.actual_values
)

After that, we join both data sources and calculate the absolute error value:

, logs as (
  select model_name, variant_name as model_variant, sagemaker.correlation_id, prediction, actual_value as actual
  from sagemaker
  left outer join actual
  on sagemaker.correlation_id = actual.correlation_id
)
, errors as (
  select abs(prediction - actual) as abs_err, model_name, model_variant from logs
),

Now, we need to calculate the percentiles using the approx_percentile function. Note that we group the percentiles by model name and model variant. Because of that, Presto will produce only a single row per every model-variant pair. That’ll be important when we write the second part of this query.

percentiles as (
  select approx_percentile(abs_err, 0.1) as perc_10,
  approx_percentile(abs_err, 0.2) as perc_20,
  approx_percentile(abs_err, 0.3) as perc_30,
  approx_percentile(abs_err, 0.4) as perc_40,
  approx_percentile(abs_err, 0.5) as perc_50,
  approx_percentile(abs_err, 0.6) as perc_60,
  approx_percentile(abs_err, 0.7) as perc_70,
  approx_percentile(abs_err, 0.8) as perc_80,
  approx_percentile(abs_err, 0.9) as perc_90,
  approx_percentile(abs_err, 1.0) as perc_100,
  model_name,
  model_variant
  from errors
  group by model_name, model_variant
)

In the final part of the query, we will use the filter expression to count the number of values within buckets. Additionally, we return the bucket boundaries. We need to use an aggregate function max (or any other aggregate function) because of the group by clause. That won’t affect the result because we returned a single row per every model-variant pair in the previous query.

SELECT count(*) FILTER (WHERE e.abs_err <= perc_10) AS perc_10
, max(perc_10) as perc_10_value
, count(*) FILTER (WHERE e.abs_err > perc_10 and e.abs_err <= perc_20) AS perc_20
, max(perc_20) as perc_20_value
, count(*) FILTER (WHERE e.abs_err > perc_20 and e.abs_err <= perc_30) AS perc_30
, max(perc_30) as perc_30_value
, count(*) FILTER (WHERE e.abs_err > perc_30 and e.abs_err <= perc_40) AS perc_40
, max(perc_40) as perc_40_value
, count(*) FILTER (WHERE e.abs_err > perc_40 and e.abs_err <= perc_50) AS perc_50
, max(perc_50) as perc_50_value
, count(*) FILTER (WHERE e.abs_err > perc_50 and e.abs_err <= perc_60) AS perc_60
, max(perc_60) as perc_60_value
, count(*) FILTER (WHERE e.abs_err > perc_60 and e.abs_err <= perc_70) AS perc_70
, max(perc_70) as perc_70_value
, count(*) FILTER (WHERE e.abs_err > perc_70 and e.abs_err <= perc_80) AS perc_80
, max(perc_80) as perc_80_value
, count(*) FILTER (WHERE e.abs_err > perc_80 and e.abs_err <= perc_90) AS perc_90
, max(perc_90) as perc_90_value
, count(*) FILTER (WHERE e.abs_err > perc_90 and e.abs_err <= perc_100) AS perc_100
, max(perc_100) as perc_100_value
, p.model_name, p.model_variant
FROM percentiles p, errors e group by p.model_name, p.model_variant

How to configure Cube?

In our application, we want to display the distribution of absolute prediction errors.

We will have a chart showing the difference between the actual value and the model’s prediction. Our chart will split the absolute errors into buckets (percentiles) and display the number of errors within every bucket.

If the new variant of the model performs better than the existing model, we should see fewer large errors in the charts. A perfect (and unrealistic) model would produce a single error bar in the left-most part of the chart with the “0” label.

At the beginning of the article, we looked at an example chart that shows no significant difference between both model variants:

If the variant B were better than the variant A, its chart could look like this (note the axis values in both pictures):

Creating a Cube deployment

Cube Cloud is the easiest way to get started with Cube. It provides a fully managed, ready to use Cube cluster. However, if you prefer self-hosting, then follow this tutorial.

First, please create a new Cube Cloud deployment. Then, open the "Deployments" page and click the “Create deployment” button.

We choose the Presto cluster:

Finally, we fill out the connection parameters and click the “Apply” button. Remember to enable the SSL connection!

Defining the data model in Cube

We have our queries ready to copy-paste, and we have configured a Presto connection in Cube. Now, we can define the Cube schema to retrieve query results.

Let’s open the Schema view in Cube and add a new file.

In the next window, type the file name errorpercentiles.js and click “Create file.”

In the following paragraphs, we will explain parts of the configuration and show you code fragments to copy-paste. You don’t have to do that in such small steps!

Below, you see the entire content of the file. Later, we explain the configuration parameters.

const measureNames = [
  'perc_10', 'perc_10_value',
  'perc_20', 'perc_20_value',
  'perc_30', 'perc_30_value',
  'perc_40', 'perc_40_value',
  'perc_50', 'perc_50_value',
  'perc_60', 'perc_60_value',
  'perc_70', 'perc_70_value',
  'perc_80', 'perc_80_value',
  'perc_90', 'perc_90_value',
  'perc_100', 'perc_100_value',
];

const measures = Object.keys(measureNames).reduce((result, name) => {
  const sqlName = measureNames[name];
  return {
    ...result,
    [sqlName]: {
      sql: () => sqlName,
      type: `max`
    }
  };
}, {});

cube('errorpercentiles', {
  sql: `with sagemaker as (
    select
    model_name,
    variant_name,
    cast(json_extract(FROM_UTF8( from_base64(capturedata.endpointinput.data)), '$.correlation_id') as varchar) as correlation_id,
    cast(json_extract(FROM_UTF8( from_base64(capturedata.endpointoutput.data)), '$.prediction') as double) as prediction
    from s3.sagemaker_logs.logs
  )
, actual as (
  select correlation_id, actual_value
  from postgresql.public.actual_values
)
, logs as (
  select model_name, variant_name as model_variant, sagemaker.correlation_id, prediction, actual_value as actual
  from sagemaker
  left outer join actual
  on sagemaker.correlation_id = actual.correlation_id
)
, errors as (
  select abs(prediction - actual) as abs_err, model_name, model_variant from logs
),
percentiles as (
  select approx_percentile(abs_err, 0.1) as perc_10,
  approx_percentile(abs_err, 0.2) as perc_20,
  approx_percentile(abs_err, 0.3) as perc_30,
  approx_percentile(abs_err, 0.4) as perc_40,
  approx_percentile(abs_err, 0.5) as perc_50,
  approx_percentile(abs_err, 0.6) as perc_60,
  approx_percentile(abs_err, 0.7) as perc_70,
  approx_percentile(abs_err, 0.8) as perc_80,
  approx_percentile(abs_err, 0.9) as perc_90,
  approx_percentile(abs_err, 1.0) as perc_100,
  model_name,
  model_variant
  from errors
  group by model_name, model_variant
)
SELECT count(*) FILTER (WHERE e.abs_err <= perc_10) AS perc_10
, max(perc_10) as perc_10_value
, count(*) FILTER (WHERE e.abs_err > perc_10 and e.abs_err <= perc_20) AS perc_20
, max(perc_20) as perc_20_value
, count(*) FILTER (WHERE e.abs_err > perc_20 and e.abs_err <= perc_30) AS perc_30
, max(perc_30) as perc_30_value
, count(*) FILTER (WHERE e.abs_err > perc_30 and e.abs_err <= perc_40) AS perc_40
, max(perc_40) as perc_40_value
, count(*) FILTER (WHERE e.abs_err > perc_40 and e.abs_err <= perc_50) AS perc_50
, max(perc_50) as perc_50_value
, count(*) FILTER (WHERE e.abs_err > perc_50 and e.abs_err <= perc_60) AS perc_60
, max(perc_60) as perc_60_value
, count(*) FILTER (WHERE e.abs_err > perc_60 and e.abs_err <= perc_70) AS perc_70
, max(perc_70) as perc_70_value
, count(*) FILTER (WHERE e.abs_err > perc_70 and e.abs_err <= perc_80) AS perc_80
, max(perc_80) as perc_80_value
, count(*) FILTER (WHERE e.abs_err > perc_80 and e.abs_err <= perc_90) AS perc_90
, max(perc_90) as perc_90_value
, count(*) FILTER (WHERE e.abs_err > perc_90 and e.abs_err <= perc_100) AS perc_100
, max(perc_100) as perc_100_value
, p.model_name, p.model_variant
FROM percentiles p, errors e group by p.model_name, p.model_variant`,

preAggregations: {
// Pre-Aggregations definitions go here
// Learn more here: https://cube.dev/docs/caching/pre-aggregations/getting-started
},

joins: {
},

measures: measures,
dimensions: {
  modelVariant: {
    sql: `model_variant`,
    type: 'string'
  },
  modelName: {
    sql: `model_name`,
    type: 'string'
  },
}
});

In the sql property, we put the query prepared earlier. Note that your query MUST NOT contain a semicolon.

We will group and filter the values by the model and variant names, so we put those columns in the dimensions section of the cube configuration. The rest of the columns are going to be our measurements. We can write them out one by one like this:

measures: {
  perc_10: {
    sql: `perc_10`,
    type: `max`
  },
  perc_20: {
    sql: `perc_20`,
    type: `max`
  },
  perc_30: {
    sql: `perc_30`,
    type: `max`
  },
  perc_40: {
    sql: `perc_40`,
    type: `max`
  },
  perc_50: {
    sql: `perc_50`,
    type: `max`
  },
  perc_60: {
    sql: `perc_60`,
    type: `max`
  },
  perc_70: {
    sql: `perc_70`,
    type: `max`
  },
  perc_80: {
    sql: `perc_80`,
    type: `max`
  },
  perc_90: {
    sql: `perc_90`,
    type: `max`
  },
  perc_100: {
    sql: `perc_100`,
    type: `max`
  },
  perc_10_value: {
    sql: `perc_10_value`,
    type: `max`
  },
  perc_20_value: {
    sql: `perc_20_value`,
    type: `max`
  },
  perc_30_value: {
    sql: `perc_30_value`,
    type: `max`
  },
  perc_40_value: {
    sql: `perc_40_value`,
    type: `max`
  },
  perc_50_value: {
    sql: `perc_50_value`,
    type: `max`
  },
  perc_60_value: {
    sql: `perc_60_value`,
    type: `max`
  },
  perc_70_value: {
    sql: `perc_70_value`,
    type: `max`
  },
  perc_80_value: {
    sql: `perc_80_value`,
    type: `max`
  },
  perc_90_value: {
    sql: `perc_90_value`,
    type: `max`
  },
  perc_100_value: {
    sql: `perc_100_value`,
    type: `max`
  }
},
dimensions: {
  modelVariant: {
    sql: `model_variant`,
    type: 'string'
  },
  modelName: {
    sql: `model_name`,
    type: 'string'
  },
}

The notation we have shown you has lots of repetition and is quite verbose. We can shorten the measurements defined in the code by using JavaScript to generate them.

We had to add the following code before using the cube function!

First, we have to create an array of column names:

const measureNames = [
  'perc_10', 'perc_10_value',
  'perc_20', 'perc_20_value',
  'perc_30', 'perc_30_value',
  'perc_40', 'perc_40_value',
  'perc_50', 'perc_50_value',
  'perc_60', 'perc_60_value',
  'perc_70', 'perc_70_value',
  'perc_80', 'perc_80_value',
  'perc_90', 'perc_90_value',
  'perc_100', 'perc_100_value',
];

Now, we must generate the measures configuration object. We iterate over the array and create a measure configuration for every column:

const measures = Object.keys(measureNames).reduce((result, name) => {
  const sqlName = measureNames[name];
  return {
    ...result,
    [sqlName]: {
      sql: () => sqlName,
      type: `max`
    }
  };
}, {});

Finally, we can replace the measure definitions with:

measures: measures,

After changing the file content, click the “Save All” button.

And click the Continue button in the popup window.

In the Playground view, we can test our query by retrieving the chart data as a table (or one of the built-in charts):

Configuring access control in Cube

In the Schema view, open the cube.js file.

We will use the queryRewrite configuration option to allow or disallow access to data.

First, we will reject all API calls without the models field in the securityContext. We will put the identifier of the models the user is allowed to see in their JWT token. The security context contains all of the JWT token variables.

For example, we can send a JWT token with the following payload. Of course, in the application sending queries to Cube, we must check the user’s access right and set the appropriate token payload. Authentication and authorization are beyond the scope of this tutorial, but please don’t forget about them.

After rejecting unauthorized access, we add a filter to all queries.

We can distinguish between the datasets accessed by the user by looking at the data specified in the query. We need to do it because we must filter by the modelName property of the correct table.

In our queryRewrite configuration in the cube.js file, we use the query.filter.push function to add a modelName IN (model_1, model_2, …) clause to the SQL query:

module.exports = {
  queryRewrite: (query, { securityContext }) => {
    if (!securityContext.models) {
      throw new Error('No models found in Security Context!');
    }
    query.filters.push({
      member: 'percentiles.modelName',
      operator: 'in',
      values: securityContext.models,
    });
    return query;
  },
};

Configuring caching in Cube

By default, Cube caches all Presto queries for 2 minutes. Even though Sagemaker Endpoints stores logs in S3 in near real-time, we aren’t interested in refreshing the data so often. Sagemaker Endpoints store the logs in JSON files, so retrieving the metrics requires a full scan of all files in the S3 bucket.

When we gather logs over a long time, the query may take some time. Below, we will show you how to configure the caching in Cube. We recommend doing it when the end-user application needs over one second to load the data.

For the sake of the example, we will retrieve the value only twice a day.

Preparing data sources for caching

First, we must allow Presto to store data in both PostgreSQL and S3. It’s required because, in the case of Presto, Cube supports only the simple pre-aggregation strategy. Therefore, we need to pre-aggregate the data in the source databases before loading them into Cube.

In PostgreSQL, we grant permissions to the user account used by Presto to access the database:

GRANT CREATE ON SCHEMA the_schema_we_use TO the_user_used_in_presto;
GRANT USAGE ON SCHEMA the_schema_we_use TO the_user_used_in_presto;

If we haven’t modified anything in the AWS Glue data catalog, Presto already has permission to create new tables and store their data in S3, but the schema doesn’t contain the target S3 location yet, so all requests will fail.

We must login to AWS Console, open the Glue data catalog, and create a new database called prod_pre_aggregations. In the database configuration, we must specify the S3 location for the table content.

If you want to use a different database name, follow the instructions in our documentation.

Caching configuration in Cube

Let’s open the errorpercentiles.js schema file. Below the SQL query, we put the preAggregations configuration:

preAggregations: {
  cacheResults: {
    type: `rollup`,
    measures: [
      errorpercentiles.perc_10, errorpercentiles.perc_10_value,
      errorpercentiles.perc_20, errorpercentiles.perc_20_value,
      errorpercentiles.perc_30, errorpercentiles.perc_30_value,
      errorpercentiles.perc_40, errorpercentiles.perc_40_value,
      errorpercentiles.perc_50, errorpercentiles.perc_50_value,
      errorpercentiles.perc_60, errorpercentiles.perc_60_value,
      errorpercentiles.perc_70, errorpercentiles.perc_70_value,
      errorpercentiles.perc_80, errorpercentiles.perc_80_value,
      errorpercentiles.perc_90, errorpercentiles.perc_90_value,
      errorpercentiles.perc_100, errorpercentiles.perc_100_value
    ],
    dimensions: [errorpercentiles.modelName, errorpercentiles.modelVariant],
    refreshKey: {
      every: `12 hour`,
    },
  },
},

After testing the development version, we can also deploy the changes to production using the “Commit & Push” button. When we click it, we will be asked to type the commit message:

When we commit the changes, the deployment of a new version of the endpoint will start. A few minutes later, we can start sending queries to the endpoint.

We can also check the pre-aggregations window to verify whether Cube successfully created the cached data.

Now, we can move to the Playground tab and run our query. We should see the “Query was accelerated with pre-aggregation” message if Cube used the cached values to handle the request.

Building the front-end application

Cube can connect to a variety of tools, including Jupyter Notebooks, Superset, and Hex. However, we want a fully customizable dashboard, so we will build a front-end application.

Our dashboard consists of two parts: the website and the back-end service. In the web part, we will have only the code required to display the charts. In the back-end, we will handle authentication and authorization. The backend service will also send requests to the Cube REST API.

Getting the Cube API key and the API URL

Before we start, we have to copy the Cube API secret. Open the settings page in Cube Cloud's web UI and click the “Env vars” tab. In the tab, you will see all of the Cube configuration variables. Click the eye icon next to the CUBEJS_API_SECRET and copy the value.

We also need the URL of the Cube endpoint. To get this value, click the “Copy API URL” link in the top right corner of the screen.

Back end for front end

Now, we can write the back-end code.

First, we have to authenticate the user. We assume that you have an authentication service that verifies whether the user has access to your dashboard and which models they can access. In our examples, we expect those model names in an array stored in the allowedModels variable.

After getting the user’s credentials, we have to generate a JWT to authenticate Cube requests. Note that we have also defined a variable for storing the CUBE_URL. Put the URL retrieved in the previous step as its value.

const jwt = require('jsonwebtoken');
CUBE_URL = '';
function create_cube_token() {
  const CUBE_API_SECRET = your_token; // Don’t store it in the code!!!
  // Pass it as an environment variable at runtime or use the
  // secret management feature of your container orchestration system

  const cubejsToken = jwt.sign(
    { "models": allowedModels },
    CUBE_API_SECRET,
    { expiresIn: '30d' }
  );

  return cubejsToken;
}

We will need two endpoints in our back-end service: the endpoint returning the chart data and the endpoint retrieving the names of models and variants we can access.

We create a new express application running in the node server and configure the /models endpoint:

const request = require('request');
const express = require('express')
const bodyParser = require('body-parser')
const port = 5000;
const app = express()

app.use(bodyParser.json())
app.get('/models', getAvailableModels);

app.listen(port, () => {
  console.log(`Server is running on port ${port}`)
})

In the getAvailableModels function, we query the Cube Cloud API to get the model names and variants. It will return only the models we are allowed to see because we have configured the Cube security context:

Our function returns a list of objects containing the modelName and modelVariant fields.

function getAvailableModels(req, res) {
  res.setHeader('Content-Type', 'application/json');
  request.post(CUBE_URL + '/load', {
    headers: {
      'Authorization': create_cube_token(),
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({"query": {
      "dimensions": [
        "errorpercentiles.modelName",
        "errorpercentiles.modelVariant"
      ],
      "timeDimensions": [],
      "order": {
        "errorpercentiles.modelName": "asc"
      }
    }})
  }, (err, res_, body) => {
    if (err) {
      console.log(err);
    }
    body = JSON.parse(body);
    response = body.data.map(item => {
      return {
        modelName: item["errorpercentiles.modelName"],
        modelVariant: item["errorpercentiles.modelVariant"]
      }
    });
    res.send(JSON.stringify(response));
  });
};

Let’s retrieve the percentiles and percentile buckets. To simplify the example, we will show only the query and the response parsing code. The rest of the code stays the same as in the previous endpoint.

The query specifies all measures we want to retrieve and sets the filter to get data belonging to a single model’s variant. We could retrieve all data at once, but we do it one by one for every variant.

{
  "query": {
    "measures": [
      "errorpercentiles.perc_10",
      "errorpercentiles.perc_20",
      "errorpercentiles.perc_30",
      "errorpercentiles.perc_40",
      "errorpercentiles.perc_50",
      "errorpercentiles.perc_60",
      "errorpercentiles.perc_70",
      "errorpercentiles.perc_80",
      "errorpercentiles.perc_90",
      "errorpercentiles.perc_100",
      "errorpercentiles.perc_10_value",
      "errorpercentiles.perc_20_value",
      "errorpercentiles.perc_30_value",
      "errorpercentiles.perc_40_value",
      "errorpercentiles.perc_50_value",
      "errorpercentiles.perc_60_value",
      "errorpercentiles.perc_70_value",
      "errorpercentiles.perc_80_value",
      "errorpercentiles.perc_90_value",
      "errorpercentiles.perc_100_value"
    ],
    "dimensions": [
        "errorpercentiles.modelName",
        "errorpercentiles.modelVariant"
    ],
    "filters": [
      {
        "member": "errorpercentiles.modelName",
        "operator": "equals",
        "values": [
          req.query.model
        ]
      },
      {
        "member": "errorpercentiles.modelVariant",
        "operator": "equals",
        "values": [
          req.query.variant
        ]
      }
    ]
  }
}

The response parsing code extracts the number of values in every bucket and prepares bucket labels:

response = body.data.map(item => {
  return {
    modelName: item["errorpercentiles.modelName"],
    modelVariant: item["errorpercentiles.modelVariant"],
    labels: [
      "<=" + item['percentiles.perc_10_value'],
      item['errorpercentiles.perc_20_value'],
      item['errorpercentiles.perc_30_value'],
      item['errorpercentiles.perc_40_value'],
      item['errorpercentiles.perc_50_value'],
      item['errorpercentiles.perc_60_value'],
      item['errorpercentiles.perc_70_value'],
      item['errorpercentiles.perc_80_value'],
      item['errorpercentiles.perc_90_value'],
      ">=" + item['errorpercentiles.perc_100_value']
    ],
    values: [
      item['errorpercentiles.perc_10'],
      item['errorpercentiles.perc_20'],
      item['errorpercentiles.perc_30'],
      item['errorpercentiles.perc_40'],
      item['errorpercentiles.perc_50'],
      item['errorpercentiles.perc_60'],
      item['errorpercentiles.perc_70'],
      item['errorpercentiles.perc_80'],
      item['errorpercentiles.perc_90'],
      item['errorpercentiles.perc_100']
    ]
  }
})

Dashboard website

In the last step, we build the dashboard website using Vue.js.

If you are interested in copy-pasting working code, we have prepared the entire example in a CodeSandbox. Below, we explain the building blocks of our application.

We define the main Vue component encapsulating the entire website content. In the script section, we will download the model and variant names. In the template, we iterate over the retrieved models and generate a chart for all of them.

We put the charts in the Suspense component to allow asynchronous loading.

To keep the example short, we will skip the CSS style part.

<script setup>
  import OwnerName from './components/OwnerName.vue'
  import ChartView from './components/ChartView.vue'
  import axios from 'axios'
  import { ref } from 'vue'
  const models = ref([]);
  axios.get(SERVER_URL + '/models').then(response => {
    models.value = response.data
  });
</script>

<template>
  <header>
    <div class="wrapper">
      <OwnerName name="Test Inc." />
    </div>
  </header>
  <main>
    <div v-for="model in models" v-bind:key="model.modelName">
      <Suspense>
        <ChartView v-bind:title="model.modelName" v-bind:variant="model.modelVariant" type="percentiles"/>
      </Suspense>
    </div>
  </main>
</template>

The OwnerName component displays our client’s name. We will skip its code as it’s irrelevant in our example.

In the ChartView component, we use the vue-chartjs library to display the charts. Our setup script contains the required imports and registers the Chart.js components:

import { Bar } from 'vue-chartjs'
import { Chart as ChartJS, Title, Tooltip, Legend, BarElement, CategoryScale, LinearScale } from 'chart.js'
import { ref } from 'vue'
import axios from 'axios'
ChartJS.register(Title, Tooltip, Legend, BarElement, CategoryScale, LinearScale);

We have bound the title, variant, and chart type to the ChartView instance. Therefore, our component definition must contain those properties:

const props = defineProps({
  title: String,
  variant: String,
  type: String
})

Next, we retrieve the chart data and labels from the back-end service. We will also prepare the variable containing the label text:

const response = await axios.get(SERVER_URL + '/' + props.type + '?model=' + props.title + '&variant=' + props.variant)
const data = response.data[0].values;
const labels = response.data[0].labels;
const label_text = "Number of prediction errors of a given value"

Finally, we prepare the chart configuration variables:

const chartData = ref({
  labels: labels,
  datasets: [
    {
      label: label_text,
      backgroundColor: '#f87979',
      data: data
    }
  ],
});

const chartOptions = {
  plugins: {
    title: {
      display: true,
      text: props.title + ' - ' + props.variant,
    },
  },
  legend: {
    display: false
  },
  tooltip: {
    enabled: false
  }
}

In the template section of the Vue component, we pass the configuration to the Bar instance:

<template>
  <Bar ref="chart" v-bind:chart-data="chartData" v-bind:chart-options="chartOptions" />
</template>

If we have done everything correctly, we should see a dashboard page with error distributions.

Wrapping up

Thanks for following this tutorial.

We encourage you to spend some time reading the Cube and Ahana documentation.

Please don't hesitate to like and bookmark this post, write a comment, give Cube a star on GitHub, join Cube's Slack community, and subscribe to the Ahana newsletter.

Building fast data visualization apps with Cube and Supabase

Shadid Haque — Fri, 03 Jun 2022 13:23:27 +0000

This tutorial teaches you how to build a performant dynamic dashboard from your Supabase data in less than 15 minutes using Cube.

Here’s what we are building.

Get the completed code for this tutorial here.

You can also try the live demo app in this link.

Data visualization provides you with a graphical representation of your data. If you have massive datasets, data visualization can reveal trends and help you make data-driven decisions for your organizations.

However, there are many challenges when aggregating and visualizing massive datasets. This tutorial demonstrates how Cube can help you overcome these challenges.

Configuring our database

Let’s use a big dataset from kaggle for our demo application. I am going to use the online Payments Fraud detection datasets.

Next, head over to app.supabase.io and create a new database. Create a new table called fraud and import the CSV data. You can import CSV data using the import option in your Supabase dashboard, as shown in the following picture.

After some time the data will be uploaded and you will have a new fraud table with data.

Exploring queries for data visualization

Next, we are going to create and run some queries. Head over to the SQL editor in your Supabase dashboard menu and run the following query.

SELECT count(*) from fraud;

This query gives us the count of records in the fraud table. Let’s analyze the query time for this SQL query. Open up the developer tool in your browser and analyze the query execution time.

For me, it took about 4.32s, which is slow but not bad considering we are working with a dataset that has over 6 million rows. However, we rarely work with simple queries such as this one for complex data analysis. Let’s create a more complex query.

The following query fetches relevant columns such as fraud type, isfraud, isflaggedfraudand calculates the sum of fraud amount and counts frauds.

SELECT
  "fraud".type "fraud__type",
  "fraud"."isFraud" "fraud__isfraud",
  "fraud"."isFlaggedFraud" "fraud__isflaggedfraud",
  sum("fraud".amount) "fraud__amount",
  count(*) "fraud__count"
FROM
  public.fraud AS "fraud"
GROUP BY
  1,
  2,
  3
LIMIT
  10000;

This query takes about 6 seconds to run. For a modern application, this is not a very good response time.

Imagine a dashboard application that takes about 6 seconds for a single reporting table to load. It will not deliver a robust user experience. According to Google's market research, users get more frustrated as page load time goes over 3 seconds. If you build a public-facing data application and have a very slow response time, users are most likely to bounce from your website.

So what’s making the queries slow? Supabase is actually not the bottleneck here. Supabase uses PostgreSQL under the hood.

Postgres is a traditional Row oriented database. Row-oriented databases store information in the disk row by row.

id	Name	City	Age
1	Dave	Los Angeles	29
2	Matt	Toronto	26
3	Jeff	New York	23

These types of databases are very efficient at reading and writing single rows. For instance, if I want to add a new record, I can add another row to the end of the table. If I know the id of a record, I can look up the row and read all the columns.

This makes Postgres a great database for applications that heavily depend on reading and writing data.

However, when executing aggregation row-oriented databases are not performant. For instance, if I want to get the sum of all the ages in the previous table, I must read each row and all its columns. Then I have to add up the age values. Even though we only need the age column, we read all the columns, which is not very memory efficient. Therefore Postgres has its shortcoming in data-intensive aggregations and analytics tasks.

You can learn more about how Row oriented databases work and their limitations in this blog post.

Column-oriented databases such as BigQuery, and Snowflake is really good at aggregating data. However, it is often more challenging to manage and sync multiple databases of different paradigms when building applications.

How does Cube resolve this issue?

Cube is an open-source API-first headless business intelligence platform that connects to your data sources and makes queries fast, responsive, cost-effective, and consistent across your applications. Cube’s API layer is able to perform efficient aggregation on your data and serve it to applications.

You run your Cube API as a service (following the microservices architecture pattern). The following diagram demonstrates the overall application architecture with Cube.

Getting started with Cube

The easiest way to get started with Cube is with Cube Cloud. It provides a fully managed Cube cluster ready to use. However, if you prefer self-hosting, then follow this tutorial.

In this tutorial, you will create a new Cube deployment in Cube Cloud. You can select a cloud platform of your choice.

Next, select start from scratch to get started with a fresh instance.

Next, you will be asked to provide your database connection information. Select PostgreSQL.

Head back to your Supabase dashboard to retrieve the database connection information. From there please select the Database option and take note of the connection information.

Next, fill in the database connection information in Cube Cloud.

Hostname:  <your-supabase-db-id>
Port:      5432
Database:  postgres
Username:  postgres
Password:  <your-supabase-password>

Cube can auto-generate a Data Schema from your SQL tables. A Cube Data Schema is used to model raw data into meaningful business definitions. The data schema is exposed through the querying API, allowing end-users to query a wide variety of analytical queries.

We will select the fraud table for schema generation. It will take a couple of minutes for our Cube instance to get provisioned.

Create pre-aggregations in Cube to increase query performance****

One of Cube’s most used features are pre-aggregations. Pre-aggregations reduce the execution time of a query.

In Cube, pre-aggregations are condensed versions of the source data. They are materialized ahead of time and persisted as tables separately from the raw data. To learn more about pre-aggregations follow this tutorial.

We have also created in-depth video workshops on pre-aggregations. Feel free to check them out as well.

In your Cube dashboard select Schema and then select Enter Development Mode. Select Fraud.js in the files and add the following code to your schema.

// Fraud.js

cube(`Fraud`, {
  sql: `SELECT * FROM public.fraud`,
  preAggregations: {
    main: {
      measures: [Fraud.amount, Fraud.count],
      dimensions: [Fraud.type, Fraud.isfraud, Fraud.isflaggedfraud, Fraud.nameorig]
    }
  },
  joins: {},
  measures: {
    count: {
      type: `count`,
      drillMembers: [nameorig, namedest]
    },
    amount: {
      sql: `amount`,
      type: `sum`
    }
  },
  dimensions: {
    type: {
      sql: `type`,
      type: `string`
    },
    nameorig: {
      sql: `${CUBE}."nameOrig"`,
      type: `string`
    },
    oldbalanceorg: {
      sql: `${CUBE}."oldbalanceOrg"`,
      type: `string`
    },
    newbalanceorig: {
      sql: `${CUBE}."newbalanceOrig"`,
      type: `string`
    },
    namedest: {
      sql: `${CUBE}."nameDest"`,
      type: `string`
    },
    isfraud: {
      sql: `${CUBE}."isFraud"`,
      type: `string`
    },
    isflaggedfraud: {
      sql: `${CUBE}."isFlaggedFraud"`,
      type: `string`
    }
  }
});

Please save the changes and the pre-aggregation will be applied to your SQL queries.

Analyzing data with the Developer Playground

Select the developer playground option from your Cube dashboard. The Developer Playground is a tool that lets you experiment with your data and generate various data visualizations.

Let's create a new Query. Please select the measures and dimensions as shown in the following image and then select Run. It makes an identical query to our previous SQL query.

Notice that it takes only about 30 to 35 milliseconds to run the query and get the data back. That’s almost a 200x performance boost in the best-case scenario.

Autogenerate front-end code from Cube

Cube also gives us the ability to autogenerate part of our front-end code. For instance, if we want the table in the previous example as a React component, we can generate it from Cube.

In your Chart menu select the Edit option and Cube will create a new table component in the codesandbox.

Next, let’s say we want to visualize the number of different types of frauds committed. We want to present this information as a pie chart. We can select Count as measures and Type as dimensions in the Cube dashboard to do this. We select the Pie chart option. We can also specify that we want React and the Chart.js library to generate our visualization.

Once the visualization is done you can open the front-end code by selecting Edit.

Putting it all together in a React App

Let’s put together a front-end React app for our data visualization. Create a new React app by running the following commands.

$ npx create-react-app supabase-demo
$ cd supabase-demo

Next, add all the required npm dependencies to your project.

$ npm i @cubejs-client/core \
        antd \
        use-deep-compare \
        recharts \
        @cubejs-client/react --save

First of all we initialize cube by adding the following code to our App.js file.

import { useState } from 'react';
import cubejs from "@cubejs-client/core";
import { Button } from 'antd';
import TableRenderer from './components/Table';
import PieChart from './components/PieChart';
import ChartRenderer from './components/BarChart';
import { CubeProvider } from '@cubejs-client/react';

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE2NTMyODIzNDQsImV4cCI6MTY1NTg3NDM0NH0.6__5oRpMmh8dEbBmhN-tkFOVc-B8CNU8IkxX7E_z5XI",
  {
    apiUrl: "https://inherent-lynx.aws-us-east-1.cubecloudapp.dev/cubejs-api/v1"
  }
);

function App() {
  const [showPieChart, setShowPieChart] = useState(false);

  return (
    <CubeProvider cubejsApi={cubejsApi}>
      <div className="App">
        <div>
          <Button onClick={() => setShowPieChart(false)}>Show Details Table</Button>
          <Button onClick={() => setShowPieChart(true)} >View by Frauds type</Button>
        </div>
        {
          showPieChart ? (
            <>
              <PieChart />
              <ChartRenderer />
            </>
          ) : <TableRenderer />
        }
      </div>
    </CubeProvider>
  );
}

export default App;

Next, go ahead and create two components one for showing the table view and the other for showing the Pie chart. Following is the code for the Table component.

// partials of src/components/Table.js

import { useEffect, useState, useContext } from "react"
import { CubeContext } from '@cubejs-client/react'
import { Spin, Table } from "antd"

// Declaire Pivot Configuration [Constant for each chart]
const pivotConfig = {
  x: [
    "Fraud.type",
    "Fraud.newbalancedest",
    "Fraud.isfraud",
    "Fraud.isflaggedfraud"
  ],
  y: ["measures"],
  fillMissingDates: true,
  joinDateRange: false
}

const TableRenderer = () => {
  const { cubejsApi } = useContext(CubeContext);
  const [data, setData] = useState(null)
  const [error, setError] = useState(null)
  const [columns, setColumns] = useState([])

  useEffect(() => {
    // Load data from Cube.js API on component mount
    cubejsApi
      .load({
        "measures": [
          "Fraud.amount",
          "Fraud.count"
        ],
        "timeDimensions": [],
        "order": {
          "Fraud.nameorig2": "desc"
        },
        "dimensions": [
          "Fraud.type",
          "Fraud.isfraud",
          "Fraud.isflaggedfraud"
        ],
        "limit": 10000
      })
      .then((resultSet) => {
        setColumns(resultSet.tableColumns(pivotConfig));
        setData(formatTableData(columns, resultSet.tablePivot(pivotConfig)))

      })
      .catch((error) => {
        setError(error);
      })
  }, [])

  if(!data) {
    return <Spin />;
  }

  return (
    <Table 
      columns={columns}
      pagination={true} 
      dataSource={data} 
    />
  )
}

// helper function to format data
const formatTableData = (columns, data) => {
  function flatten(columns = []) {
    return columns.reduce((memo, column) => {
      if (column.children) {
        return [...memo, ...flatten(column.children)];
      }

      return [...memo, column];
    }, []);
  }

  const typeByIndex = flatten(columns).reduce((memo, column) => {
    return { ...memo, [column.dataIndex]: column };
  }, {});

  function formatValue(value, { type, format } = {}) {
    if (value == undefined) {
      return value;
    }

    if (type === "boolean") {
      if (typeof value === "boolean") {
        return value.toString();
      } else if (typeof value === "number") {
        return Boolean(value).toString();
      }

      return value;
    }

    if (type === "number" && format === "percent") {
      return [parseFloat(value).toFixed(2), "%"].join("");
    }

    return value.toString();
  }

  function format(row) {
    return Object.fromEntries(
      Object.entries(row).map(([dataIndex, value]) => {
        return [dataIndex, formatValue(value, typeByIndex[dataIndex])];
      })
    );
  }

  return data.map(format);
};

export default TableRenderer;

Following is the code for PieChart component.

// PieChart.js

import { QueryRenderer } from "@cubejs-client/react";
import { CubeContext } from '@cubejs-client/react';
import { Spin } from "antd";
import "antd/dist/antd.css";
import React, { useContext } from "react";
import {
  PieChart,
  Pie,
  Cell,
  Tooltip,
  ResponsiveContainer,
  Legend
} from "recharts";

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

const renderChart = ({
  resultSet,
  error,
  pivotConfig,
  onDrilldownRequested
}) => {
  if (error) {
    return <div>{error.toString()}</div>;
  }

  if (!resultSet) {
    return <Spin />;
  }

  return (
    <ResponsiveContainer width="100%" height={350}>
      <PieChart>
        <Pie
          isAnimationActive={true}
          data={resultSet.chartPivot()}
          nameKey="x"
          dataKey={resultSet.seriesNames()[0].key}
          fill="#8884d8"
        >
          {resultSet.chartPivot().map((e, index) => (
            <Cell key={index} fill={colors[index % colors.length]} />
          ))}
        </Pie>
        <Legend />
        <Tooltip />
      </PieChart>
    </ResponsiveContainer>
  );
};

const ChartRenderer = () => {
  const { cubejsApi } = useContext(CubeContext);
  return (
    <QueryRenderer
      query={{
        measures: ["Fraud.amount"],
        timeDimensions: [],
        order: {
          "Fraud.amount": "desc"
        },
        dimensions: ["Fraud.type"]
      }}
      cubejsApi={cubejsApi}
      resetResultSetOnChange={false}
      render={(props) =>
        renderChart({
          ...props,
          chartType: "pie",
          pivotConfig: {
            x: ["Fraud.type"],
            y: ["measures"],
            fillMissingDates: true,
            joinDateRange: false
          }
        })
      }
    />
  );
};

export default ChartRenderer;

You can find the complete code for this demo application at this link.

Where to go from here

We have only covered the basics of Cube in this tutorial. Cube comes packed with features that can help you build data-intensive apps fast. Cube supports features such as multiple database connections, multi-tenancy, GraphQL API, SQL API, and more.

You can sign up for Cube Cloud for free if you would like to play around. To learn more about how Cube can help you to build your project head over to the official documentation page.

If you have questions, or feedback, we would love to hear what you have to say! Come join our Slack community. Click here to join!

That’s all for today. Feel free to leave Cube a ⭐ on GitHub if you liked this article. Happy hacking.

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 a nivo Dashboard with Cube

Adnan Rahić — Fri, 15 Apr 2022 13:55:03 +0000

This guest post was written by Julien Bras. He's an Innovation Team Leader at @Wiiisdom. You can get in touch with him through his website or Twitter.

I’ve worked for many years as a business intelligence (BI) consultant, building dashboards and reports for big companies. It’s not easy to build a dashboard with classic BI tools, and it’s even trickier if you want to embed elements in a classic React web app. You’ll face various issues, including security, integration, and interactivity—not to mention the price tag.

This tutorial will walk you through building a custom dashboard that displays data from a PostgreSQL database using nivo and Cube Cloud. Here is the expected result:

What is Nivo?

Nivo is a React library that allows you to easily build charts and other dataviz elements. It’s built on top of the famous D3 library, but has even more visualizations and is optimized for easier, more intuitive use. Not only will you find the classic bar and pie charts, but you’ll also find new components, like Funnel, CirclePacking and Choropleth to name a few.

One of the things that makes nivo stand out is that it comes with server-side rendering capabilities, though they’re not used in this tutorial. It’s also a fully declarative library that allows users to specify how a chart should render, right down to the last pixel. Here’s a simple example for a pie chart:

const MyResponsivePie = () => {

    const data = [
    {
        "id": "php",
        "value": 425,
    },
    {
        "id": "erlang",
        "value": 276,
    },
    {
        "id": "stylus",
        "value": 62,
    },
    ...
    ];

    return (<ResponsivePie
        data={data}
        margin={{ top: 40, right: 80, bottom: 80, left: 80 }}
        innerRadius={0.5}
        padAngle={0.7}
        cornerRadius={3}
        activeOuterRadiusOffset={8}
        borderWidth={1}
    />)
}

It will generate an SVG element that looks like this:

Compared to Chart.js or Recharts, nivo has a broader selection of components, and offers full control over how your data is displayed. The downside to nivo is that, as of this writing, there’s no native integration with Cube Cloud’s Developer Playground, so building a dashboard with it requires a little more manual coding than some of the other options.

What is Cube?

Cube is a Headless BI solution that lets you expose your database through a rich API that can be consumed by your React stack and, ultimately, the nivo charts you’ll build. Cube functions as a bridge, of sorts, between data stored in MySQL, PostgreSQL, and your front-end application.

It’s a headless business intelligence engine, allowing you to manage the complexity of your data behind the scenes, and expose the information you want to show the consumer via multiple APIs. Then you just have to hook up the data to show it in the desired components. Cube also provides a Developer Playground that lets you build charts visually, further speeding up dashboard creation.

Cube is available in two versions:

an open-source version named Cube that you host and manage by yourself. It’s a good choice if you need to deploy Cube in a private cloud near your data source, or if you want to have full control over your architecture.
a full-featured service named Cube Cloud, which offers high availability and scalability, and requires little management or maintenance.

Build a Nivo Dashboard With Cube Cloud

Time to get your hands dirty. To follow along the tutorial, you’ll need a few things ahead of time:

npm and node on your laptop to build the React application that will host your dashboard
psql command-line tool to load a script on the ElephantSQL service. If you don’t already have it, you can follow this tutorial to install it.

That’s it—everything else will be covered in the tutorial. If you want to follow along in your own editor, the full tutorial is available as a GitHub repo.

Initialize Your Database

If you already have a PostgreSQL database available online, you can use your own data as you follow along. If not, head over to ElephantSQL and create a free account. ElephantSQL is a “PostgreSQL as a Service” solution with a free plan that will be very convenient for this tutorial.

After creating an account, create a new instance on the Tiny Turtle plan:

Then you can select the region, ideally somewhere close to you. After selecting that, you’ll be able to see information related to your instance:

Make a note of your server name, user name, database, and password—you’ll need those later.

Now let’s add some data to your instance. You will download a sample e-commerce database script here, and load it into your ElephantSQL instance. To form the command for this, use the following syntax:

psql -h <server> -U <user> < ecom-dump.sql

The server should be set to match the server from your instance detail page, which is likely something like drona.db.elephantsql.com (user can also be found on your instance details page, and should be changed to match your values; the password will be requested when you launch the command).

If it loads correctly, your terminal will show you a bunch of rows, indicating that the data is now loaded on the instance and available to run queries.

You can check the data directly in the ElephantSQL interface, using the Browser tab. You should be able to see the tables list using the Table queries button, and be able to run some SQL queries like SELECT * FROM "public"."users" LIMIT 100, as shown below.

That completes the first step. Now it’s time to move to Cube Cloud.

Connect Cube Cloud

As mentioned previously, Cube offers two options: Open-source Cube and Cube Cloud. In this tutorial, you’ll be using Cube Cloud, as it comes with a generous free tier to develop and test your application, and requires less setup.

Start by creating a Cube account.

Next, create a new deployment. When selecting a region, try to choose the same region you chose for your ElephantSQL instance in order to optimize data transfers.

Once you’ve gotten familiar with Cube, it’s possible to link your Cube Cloud instance to a GitHub repository, creating powerful CI integration. For now, though, select Create to start from an empty project.

It’s time to connect to your backend database. First, select PostgreSQL as the database:

Finally, enter the database credentials from the ElephantSQL instance details screen:

Server Name
Database
Username
Password

Cube will ask you to generate the data schema. This is a Cube concept that will let you associate tables and fields to API objects. In this tutorial, we will generate them using the built-in generator, but it’s also possible to customize them. Take a look at the documentation for more information about how schemas work, and how to generate your own.

Give your instance a couple of seconds to spin up, and you’ll be taken to the Overview, as shown below:

Here is a brief tour of the left menu:

Playground is the Developer Playground, which will let you play with your data, build test charts, and more. It can also help you to generate code for multiple frameworks. In this tutorial, you’ll use it to generate some of the code for your nivo dashboard.
Schema exposes the generated schema. Each JavaScript file describes a cube that is tied to a single SQL query, with specific links to other cubes and metrics.
Queries gives you a historical view of the queries handled by Cube (API side and database side)
Pre-Aggregations keeps you up to date about the batch jobs that are building pre-aggregations. It’s a practical solution to reduce the query duration when you have a large database. A good introduction on this topic is available in the documentation!

Let’s start building your first chart using the Playground. Select Users Count as a measure, Users City as a dimension, and a Bar chart type. Click Run, and Cube will display your first chart.

The Playground is the best place to play with the data and get a better idea of the best representation to show a given metric or trend. The only downside is that there’s no way to generate a nivo chart. This won’t stop you from building a nivo dashboard, though!

Build Your Nivo Dashboard

Bootstrap a new React application using Create React App:

npx create-react-app nivo-dashboard

You’ll need to add a few packages. @nivo/* will allow us to build nivo components. @cubejs-client/core is the basic cube library, and @cubejs-client/react provides components and hooks that you’ll use later.

cd nivo-dashboard/
npm add @nivo/core @nivo/bar @nivo/pie @nivo/bump @cubejs-client/core @cubejs-client/react

Start your application with the following command, and it will open a browser to http://localhost:3000 with the default start page:

npm start

Now you need to connect from React to your Cube application. Go back to the Overview, and click on the How to connect your application link in the REST API section, then select the React tab. It will give you some code to copy-paste in your application. Be aware that it’s sensitive information, as it contains an API token and the endpoint of your Cube API. Later, you can store this information in dedicated environment variables to avoid pushing it to a Git repository.

To start building your dashboard, you’ll build a bar chart, just like the one you built earlier in the Developer Playground. In a new src/charts/UsersPerCity.js file, start by creating a simple component:

import { ResponsiveBar } from "@nivo/bar";
import { useCubeQuery } from "@cubejs-client/react";

const UsersPerCity = () => {
  // use hook to grab data from Cube
  const { resultSet, isLoading, error, progress } = useCubeQuery({
    measures: ["Users.count"],
    timeDimensions: [],
    order: {
      "Users.count": "desc",
    },
    dimensions: ["Users.city"],
  });

  if (isLoading) {
    return (
      <div>
        {(progress && progress.stage && progress.stage.stage) || "loading"}
      </div>
    );
  }

  if (error) {
    return <div>{error.toString()}</div>;
  }

  if (!resultSet) {
    return null;
  }

  const dataSource = resultSet.tablePivot();

  return (
    <div style={{ height: "250px", width: "100%" }}>
      <ResponsiveBar
        data={dataSource}
        keys={["Users.count"]}
        indexBy="Users.city"
        margin={{ top: 0, bottom: 80, left: 60 }}
        colors={{ scheme: "accent" }}
        axisBottom={{
          tickSize: 5,
          tickPadding: 5,
          tickRotation: 45,
          legendOffset: 32,
        }}
      />
    </div>
  );
};

export default UsersPerCity;

This uses the hook useCubeQuery, provided by Cube, for a specific query:

{
    measures: ["Users.count"],
    timeDimensions: [],
    order: {
      "Users.count": "desc",
    },
    dimensions: ["Users.city"],
  }

You can retrieve this query from the JSON Query panel of the Playground:

The hook lets you easily handle the loading state (isLoading) and provides you a resultSet object that contains the JSON representation of the data to show on your bar chart.

With the data acquired, you’re simply using the ResponsiveBar from nivo to render the bar chart with the resultSet object:

  const dataSource = resultSet.tablePivot();

  return (
    <div style={{ height: "250px", width: "100%" }}>
      <ResponsiveBar
        data={dataSource}
        keys={["Users.count"]}
        indexBy="Users.city"
        margin={{ top: 0, bottom: 80, left: 60 }}
        colors={{ scheme: "accent" }}
        axisBottom={{
          tickSize: 5,
          tickPadding: 5,
          tickRotation: 45,
          legendOffset: 32,
        }}
      />
    </div>
  );

Now we can open src/App.js to display the chart. To do that, replace the contents of src/App.js with the following:

import UsersPerCity from "./charts/UsersPerCity";
import cubejs from "@cubejs-client/core";
import { CubeProvider } from "@cubejs-client/react";

const cubejsApi = cubejs(
  "API_TOKEN",
  {
    apiUrl:
      "API_ENDPOINT",
  }
);

function App() {
  return (
    <CubeProvider cubejsApi={cubejsApi}>
      <UsersPerCity />
    </CubeProvider>
  );
}

export default App;

This first initializes the cubejsApi with your token and API endpoint. Don’t forget to add your API token and endpoint, which you got from the Cube Overview screen.

Then you’re using CubeProvider, which will provide the Cube context to all components below. It’s possible to pass cubeJsApi directly to useCubeQuery, but doing it for all components at once is much easier when you’re working with more charts.

Finally, you use your brand new UsersPerCity component. If everything’s working correctly, http://localhost:3000 will display a chart like this:

Congratulations—you’ve made it!

At this point, you can customize your application by making it interactive. For example, if you need to add a filter to show only a specific city on the graph, you can take advantage of the filter section in the JSON query, and test it on the Developer Playground. Then you can add a new input component in your React application, and set up the useState hook to dynamically pass the input value to the Cube query. You can learn more about filtering in the query in the Cube documentation.

Now that you have the basics down, you can continue to add more charts and polish your application. For reference, the complete React app with all four charts, as shown at the beginning of the tutorial, is available in this GitHub repo.

Get Ready To Learn More

You’ve just scratched the surface of Cube in this tutorial. For example, you haven’t yet spent time customizing schemas. But when you start modifying the schema of your Cube files, Cube Cloud gives you easy tools to manage that. You can link your instance to a specific GitHub repository (to host schema files), and the Development Mode will allow you to test the modification or to deploy a custom branch.

You can learn more in the documentation about the Cube IDE.

What Have You Learned Today?

Adding Cube to the equation is a quick and easy way to enhance the user experience of your application. Instead of spending developer time to build this kind of chart from scratch, it’s much more efficient to first design them within the Developer Playground, then integrate them into your application with just a few lines of code, like you did in this tutorial.

To continue the journey, you could take a look at the promising new API available with Cube. It allows you to interact with Cube using GraphQL instead of REST. This blog post explains how this can be a game changer. You can also try out the SQL API. It allows you to use Cube for MySQL-compatible queries, enabling you to connect to any business intelligence solution that supports SQL.

Finally, don’t hesitate to ping the Cube team on Slack—they’re very responsive, and always happy to help new users.