DEV Community: Artyom Keydunov

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.

What is Headless BI?

Artyom Keydunov — Thu, 24 Mar 2022 16:35:04 +0000

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

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

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

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

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

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

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

1. Data Modeling

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

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

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

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

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

2. Access Control

Since a headless BI tool is the first layer downstream of one’s data sources, it must also manage and audit access to that data. These controls can’t be decoupled from the data model, and, therefore, should be defined within it.

Why?

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

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

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

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

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

3. Caching

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

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

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

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

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

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

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

4. APIs

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

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

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

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

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

Headless BI should be open source

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

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

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

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

Headless BI: WIP

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

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

Won’t you join us?

Building Open-Source Metrics Stores with Cube

Artyom Keydunov — Thu, 18 Nov 2021 15:51:30 +0000

TL;DR: You can build a consistent and reusable metrics layer and connect it to a front-end app and a BI tool like Superset or Tableau at the same time.

Today, Cube powers analytics features inside thousands of applications where developers have leveraged Cube’s data schema as a metrics layer—a consistent, single source of truth. By powering a single repository of metrics, Cube helps developers quickly and reliably ship embedded analytics features and other data-powered applications with the peace of mind that their metrics definitions remain consistent.

Cube solves this problem very well for application developers, but it’s been unsolved for users of dashboards and business intelligence tools, and the data engineering teams that support them.

In most modern organizations, there are multiple tools that consume data from the same single warehouse but perform their own downstream metrics calculations. This leads to inconsistent calculations and disagreements between teams: How do we calculate revenue? Does it include revenue from X cohort? How do monthly payments affect annual projections?

The more tools an organization uses, the more inconsistencies and conflicts arise, and the harder it is for a business to make decisions using accurate data. To avoid this, they need a centralized, upstream location in which to build and maintain their metrics, so that every tool works from the same source of metric truth.

Today, I’m excited to share a way for Cube to function as a metrics store for any data consumer: we’re proud to announce the new Cube SQL API. With the addition of this API, Cube now functions as a headless BI layer to provide consistent metrics to any querying and visualization tool.

How it works

In the modern data stack, Cube acts as a proxy for data warehouses and translates every incoming query, whether it is JSON, GraphQL, or SQL, into native queries to the underlying data storage.

Cube translates queries by using a JSON-based data modeling layer consisting of cubes. Think of these as database views, backed by either a reference to an existing database table or a new table created by SELECT statement. Cubes contain defined measures and dimensions.

Measures are quantitative data, such as the number of units sold, unique visits, profit, and so forth. Dimensions are categorical data, such as state, gender, product name, or units of time. (You can learn more about Cube’s data schema in the docs.)

Below is an example of the cube we can use to describe metrics about leads for a sales organization.

cube(`Leads`, {
  sql: `
    SELECT
      persons.id,
      persons.created_date,
      deals.id as deal_id
    FROM persons
      LEFT JOIN deals ON deals.person_id = persons.id
  `,

  title: `Leads`,
  description: `Leads for Insides Sales`,

  measures: {
    count: {
      type: `count`
    },
    converted_to_deal_count: {
      type: `count`,
      filters: [
        { sql: `(${is_converted_to_deal}) = true` }
      ]
    },
    to_deal_conversion_rate: {
      type: `number`,
      sql: `ROUND(${converted_to_deal_count} / NULLIF(${count}, 0) * 100, 2)`,
      format: `percent`
    }
  },

  dimensions: {
    time: {
      sql: `created`,
      type: `time`
    },
    is_converted_to_deal: {
      type: `boolean`,
      sql: `deal_id IS NOT NULL`
    }
  }
})

With the above definitions of our metrics we can ask questions like "What is our monthly lead-to-deal conversion rate over the last year?"

When we query Cube’s metrics layer via the SQL API, cubes will be presented as tables and measures and dimensions as columns. To answer the above question with SQL we need to write the following query:

SELECT DATE_TRUNC('month', time),
       to_deal_conversion_rate
FROM leads
WHERE time >= '2021-01-01'
  AND time <= '2021-12-31'
GROUP BY 1

Cube will translate this query into the query for the underlying database.

SELECT
  DATETIME_TRUNC(
    DATETIME(created_date, 'America/Los_Angeles'),
    MONTH
  ) `leads__time_month`,
  ROUND(
    count(
      CASE WHEN ((deal_id IS NOT NULL) = true) THEN 1 END
    ) / NULLIF(count(*), 0) * 100,
    2
  ) `leads__to_deal_conversion_rate`
FROM
  (
    SELECT
      persons.id,
      persons.created_date,
      deals.id as deal_id
     FROM persons
      LEFT JOIN deals ON deals.person_id = persons.id
  ) AS `leads`
WHERE
  (
    created_date >= TIMESTAMP('2021-01-01T00:00:00.000Z')
    AND created_date <= TIMESTAMP('2021-12-31T23:59:99.999Z')
  )
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10000

You can query Cube with SQL from your favorite programming language, such as Python.

Most powerfully, because the Cube SQL API speaks MySQL-compatible SQL, you also can connect your favorite BI tool—like Superset, Metabase, or Tableau—directly to Cube, and let Cube generate SQL to fetch and display data. Here’s an example of using Superset with the Cube SQL API.

You can check our documentation for the full Superset integration tutorial.

Built-in relational cache

Every query can leverage Cube’s pre-aggregations layer—a database-agnostic materialization engine—in order to make dashboards and reports load in milliseconds instead of minutes.

While defining metrics, developers can specify which metrics they want to pre-aggregate. Cube will run complex calculations beforehand in the background and will create a cache table with the results. All queries will be served from this cache table, dramatically increasing dashboards’ performance.

Where we go next

We’re excited about today’s launch, but it is only the beginning of the journey! We’re committed to supporting all the major SQL operations, and improving error handling to provide more useful tips to users. Please let us know in our Slack channel if you see something that is not working as you expected.

Everything described here is available in Cube’s OSS offering, licensed under Apache 2.0. In addition, we're working on cataloguing and collaboration tools for the Cube metrics layer in Cube Cloud, our fully-managed Cube service. Watch this space.

If you’re interested in learning more, please give Cube a try.

Announcing Cube Cloud: Managed hosting of Cube applications

Artyom Keydunov — Tue, 05 Oct 2021 13:55:43 +0000

TL;DR: Cube is an open-source API layer for building modern data apps. Here's a step-by-step tutorial that shows how Cube shines. Now Cube can be run on a fully managed platform — Cube Cloud. It's a small step for the Cube team, but a giant leap for the developers: Cube Cloud has a free tier so everyone can start building with Cube right away. Read more below...

Today, we’re excited to announce general availability of Cube Cloud, a fully managed service for running Cube applications in the cloud of your choice.

You can try it out for free at cube.dev/cloud.

The launch follows more than two years of collecting community feedback about running Cube in production, which started with my cofounder Pavel and me helping community members manage Cube under load. We learned a lot about different load footprints, use cases, and patterns of how to scale Cube to match different use cases, and began work on what later became Cube Cloud.

Our mission: to help developers focus on building data applications with Cube, not on running, monitoring, and scaling Cube infrastructure.

Our plan: a platform purpose-built to run Cube applications, smart enough to autoscale to accommodate usage growth, and rich with controls to trace queries, inspect load, and work with Cube pre-aggregations.

I’m excited to share what we’ve built. Let’s take a look at some important new features:

Auto-scale Cube API instances

Cube API instances are designed to be scaled horizontally, but it’s hard to say at what CPU, memory, or requests threshold you need to scale, since this depends on a variety of factors, including your schema structure and footprint, API workload patterns, and database resources.

In Cube Cloud, you don't need to worry about any of it—how many instances to run, how much memory to allocate, etc. Cube Cloud knows how to autoscale your deployment based on API workloads and a multi-tenancy structure to load-balance schemas per API instance.

Cube Cloud also auto-scales pre-aggregations of the Cube relational caching layer. Auto-scaling ingestion helps to keep cache refresh time constant even when the underlying data grows, and the same is true for the querying side: Cube Cloud takes care of bringing more Cube Store workers to process all the incoming requests.

Cache and pre-aggregations warm-up

To ensure a good user experience, we recommend pre-compiling your schema and warming up both in-memory and pre-aggregations caches before serving user requests. It usually happens during the rollout of updates to the data schema. When building multitenant applications and load balancing different schema versions across API instances, it is important to pre-compile versions of the schema per serving API instance, especially for dynamic schemas that make third-party requests.

Cube Cloud takes care of schema pre-compilations and cache warm-ups for you, including balancing multi-tenant schema versions.

GitHub integration and collaborative schema editing

Cube Cloud integrates with GitHub to streamline the process of developing and testing schema edits with your team.

First, Cube Cloud can automatically deploy changes from a connected GitHub repository. Simply commit and git push to trigger a deployment.

Additionally, if you want to edit and test your schema directly in Cube Cloud, you can do so via Development Mode, which deploys your own personal API endpoint to test out via Playground as you edit the schema. When you’re happy with your updates, you can commit and push your changes via the git integration, then share these updates with the rest of your team or push them to a production environment in Cloud.

We’re now actively working on some neat features to support staging environments and more collaborative workflows. For example, multiple branches on each deployment will soon allow you to set up multiple environments with the same backend data source and caches, while using different schema versions and resource configurations. We’ll also enable you to open pull requests and merge branches directly from Cube Cloud. Watch this space.

Monitoring

Cube Cloud provides performance monitoring for API instances and your source database. Use this tool to monitor overall load, and spot slow queries to optimize.

Additionally, we're working on integrations with third-party alerting and APM tools like Datadog and PagerDuty, so you can export metrics and logs of your Cube Cloud deployment to these tools.

Query Tracing

Query tracing provides a historical view of all your Cube API and database queries. This can be very helpful both during development and in production to find slow queries, understand the lifecycle of each query, and optimize performance and caching configuration.

Pre-aggregations management

Cube can accelerate a query by utilizing pre-aggregations—aggregated and optimized representations of source data—instead of processing raw data. In Cube Cloud, it’s easier to create and configure pre-aggregations during development and maintain them in production. We've built a control panel to view all your pre-aggregations in one place.

This tool lets you view build history, partitions, build times, refresh times, and much more. You can also build pre-aggregations manually with the Build All button, or select individual partitions to build.

To track the history of pre-aggregations over time, Cube Cloud provides a list of every build that happened.

Security

Cube Cloud is not just the fastest way to run Cube; we aim for it to be the most secure.

For starters, all communications between Cube Cloud and your databases can be encrypted. In addition, it’s possible to run Cube Cloud in an isolated, dedicated VPC that can be peered to your database's VPC.

Encryption at rest for pre-aggregations using Customer Key Management is available for dedicated VPC instances.

To make it easier to use Cube Cloud in large enterprise environments, we’re actively in the process of earning our SOC-2 certification. If you’d like to be updated on our progress with this certification, please let us know.

Starting price: $0

We’re so confident that Cube Cloud is the superior way to develop and run Cube, and proud of the features that we’ve added, that we encourage every developer to use it.

Cube Cloud is totally free for up to 1GB/month of processed data—with no limitations on access to the Cube Cloud IDE and no cap on the number of data stores you connect or API instances you create. Give it a try now. (For additional data as well as auto-scaling and high availability, pricing starts at just $99/month. Learn more about Cube Cloud pricing here.)

If your team requires dedicated infrastructure, support and training, SLAs, or other custom enterprise features, get in touch: we’re happy to help.

This is just the beginning

More is definitely in store! We will continue to build more tools in Cube Cloud to ensure smooth development, staging, and production for Cube-powered data applications. We also hope you’ll share your feedback and feature requests by dropping us an email, finding us on Twitter, or participating in our Slack community.

We raised Series A to build headless data analytics

Artyom Keydunov — Mon, 19 Jul 2021 15:14:17 +0000

Less than a year ago, I was writing a blog post announcing our seed fundraising. I didn't expect to write a successor post so soon! But today we are overjoyed to announce that Cube Dev has raised $15.5M in Series A funding, led by Decibel.

It’s been a busy eleven months. Since last August, there are 5 times more servers running Cube, and as many new members in our Slack community. The past months have also seen the rollout of powerful new features including Cube Store, and the emergence of new community features and educational resources.

In announcing this funding round, I want to share what we’ve learned over that time, and explain how we will use the new funding and what we’ve learned to more quickly build the tech stack that powers data applications.

The new architecture for data applications

The database and cloud data warehouse industry is booming. We indeed have more data than ever. From the Cube community, I know a lot of seed-stage startups dealing with hundreds of millions and even billions of records.

The good news is that the technology now exists to process all of this data. Remember that less than a decade ago, only a privileged few big tech firms could afford the budget needed to run and manage Hadoop clusters on a scale, but since then, cloud data warehouses emerged as Ford's Model T for data. Now, even small companies can afford to store and analyze big data. This enables everyone to build data applications and embed intelligence into their products.

That being said, we still have to build fundamental infrastructure to make it possible—to bring data to life inside the applications. If data is the new oil, we need the tools to refine it into rocket fuel.

Developers need a new architecture to deliver data from warehouses and data lakes to applications at scale, and there are hard problems to solve in designing this architecture:

How do we abstract metrics definitions and make them consistent, testable, and version controllable?
How do we ensure that our API layer supports a large number of users and high volumes of data without growing in complexity?
How do we manage complex multi-tenancy scenarios and per-end-user custom metrics?
How do we aggregate and cache the data to process hundreds and even thousands of queries per second?

We’ve spent more than two years tackling these questions with all of you in our amazing Cube community, and we’ve all learned a lot.

Over two years ago, when Pavel and I were playing ping-pong and chatting about open-sourcing Cube.js, I couldn't have imagined where it would lead us. Today’s Cube is so different from what we initially released. Community not only contributed features but pioneered many fundamental architecture decisions we have now in the Cube.

Now, with new funding, we're excited to start a new chapter and to build more and faster.

Where we go from here

Cube’s mission is to empower developers to build data applications. To pursue this mission, here is some of what we have planned:

Hiring. We plan to build a team of 30 people by the end of the year and double our open-source engineering team. This allows us to fix bugs and ship new features faster.
Extend Cube Store. It's been less than 3 months since we released Cube Store, but it's already been adopted by more than half of Cube users. As many of you have noticed, Cube Store provides a significant boost for latency and concurrency on analytics queries like Top-K. We intend to further improve its performance and the ease of configuring and deploying Cube Store and work to make its immense power accessible to every user.
New features. I always enjoy hours spent chatting with the community about what we need to build next. There are many things I know you are excited about. Two frequent requests are real-time pre-aggregations from Kafka streams and databases, and BI connectors. I'm happy to share that we've already started to work on these, and plan to ship previews later this year. Please DM me on Slack if you are interested in running Cube on top of real-time and streaming data, or connecting Cube to your BI tool.
Launch Cube Cloud. We're also hard at work building Cube Cloud, our fully managed Cube service coming later this year too. Managing infrastructure opens so many new opportunities to build features we’ve always wanted to add to Cube but couldn’t without access to the underlying infrastructure. I can't wait to share it with you all. Cube Cloud will launch to general availability in the autumn, but first we’re working with design partners who can test Cube Cloud with their data, influence feature development, and work hands-on with the Cube Dev team in advance of general availability. Can you help? Don’t miss this opportunity to shape Cube’s next chapter. Drop us a line to learn more.

Building an Open Source Web Analytics Platform. Part 1: Overview and Analytics Backend

Artyom Keydunov — Thu, 09 Jul 2020 14:43:39 +0000

This is the first part of a guide on building an open source web analytics platform with Cube.js You can find the full guide here.

Also, below you can see the demo of the final application. Online demo is availble here.

Building your own analytics engine, like the one behind Google Analytics, sounds like a very sophisticated engineering problem. And it truly is. Back then, it would require years of engineering time to ship such a piece of software. But as data landscape changes, now we have a lot of tools which solve different part of this problem extremely well: data collection, storage, aggregations, and query engine. By breaking the problem into smaller pieces and solving them one-by-one by using existing open-source tools, we will be able to build our own web analytics engine.

If you’re familiar with Google Analytics (GA), you probably already know that every web page tracked by GA contains a GA tracking code. It loads an async script that assigns a tracking cookie to a user if it isn’t set yet. It also sends an XHR for every user interaction, like a page load. These XHR requests are then processed, and raw event data is stored and scheduled for aggregation processing. Depending on the total amount of incoming requests, the data will also be sampled.

Even though this is a high-level overview of Google Analytics essentials, it’s enough to reproduce most of the functionality.

Architecture overview

Below you can see the architecture of the application we are going to build. We'll use Snowplow for data collection, Athena as the main data warehouse, MySQL to store pre-aggregations, and Cube.js as the aggregation and querying engine. The frontend will be built with React, Material UI, and Recharts. Although the schema below shows some AWS services, they can be partially or fully substituted by open-source alternatives: Kafka, MinIO, and PrestoDB instead of Kinesis, S3, and Athena, respectively.

We'll start with data collection and gradually build the whole application, including the frontend. If you have any questions while going through this guide, please feel free to join this Slack community and post your question there.

We're going to use Snowplow for data collection, S3 for storage, and Athena to query the data in S3.

Data Collection with Snowplow

Snowplow is an analytics platform to collect, enrich, and store data. We'll use the Snowplow Javascript tracker on our website, which generates event-data and send it to the Snowplow Collector to load to S3.

Before loading the data, we'll use Enricher to turn IP addresses into coordinates. We'll use AWS Kinesis to manage data streams for collection, enrichment, and then finally loading into S3. The schema below illustrates the whole process.

Let's start by setting up the tracker. Adding Snowplow's tracker to the website is the same, as adding Google Analytics or Mixpanel tracker. You need to add the asynchronous Javascript code, which loads the tracker itself.

<!-- Snowplow starts plowing -->
<script type="text/javascript">
;(function(p,l,o,w,i,n,g){if(!p[i]){p.GlobalSnowplowNamespace=p.GlobalSnowplowNamespace||[];
p.GlobalSnowplowNamespace.push(i);p[i]=function(){(p[i].q=p[i].q||[]).push(arguments)
};p[i].q=p[i].q||[];n=l.createElement(o);g=l.getElementsByTagName(o)[0];n.async=1;
n.src=w;g.parentNode.insertBefore(n,g)}}(window,document,"script","//d1fc8wv8zag5ca.cloudfront.net/2.10.2/sp.js","snowplow"));

window.snowplow('newTracker', 'cf', '{{MY-COLLECTOR-URI}}', { // Initialise a tracker
  appId: '{{MY-SITE-ID}}',
  cookieDomain: '{{MY-COOKIE-DOMAIN}}'
});

window.snowplow('trackPageView');
</script>
<!-- Snowplow stops plowing -->

The above snippet references a Snowplow Analytics hosted version of the Snowplow JavaScript tracker v2.10.2 (//d1fc8wv8zag5ca.cloudfront.net/2.10.2/sp.js). Snowplow Analytics no longer hosts the latest versions of the Snowplow JavaScript tracker. It is recommended to self-host sp.js by following the Self-hosting Snowplow.js guide.

For more details about setting up the tracker, please refer to the official Snowplow Javascript Tracker Setup guide.

To collect the data from the tracker, we need to setup Snowplow Collector. We'll use Scala Stream Collector. Here the detailed guide on how to install and configure it. This repository with the Docker images for the Snowplow components is very helpful if you plan to deploy Snowplow with Docker.

Next, we need to install Snowplow Stream Enrich. Same as for collector, I
recommend following the official guide here and use these Docker images.

Finally, we need to have S3 Loader installed and configured to consume records from AWS Kinesis and writes them to S3. You can follow [this guide (https://github.com/snowplow/snowplow/wiki/snowplow-s3-loader-setup) to set it up it.

Query S3 with Athena

Once we have data in S3 we can query it with AWS Athena or Presto. We’ll use Athena in our guide, but you can easily find a lot of materials online on how to set up an alternative configuration.

To query S3 data with Athena, we need to create a table for Snowplow events. Copy and paste the following DDL statement into the Athena console. Modify the LOCATION for the S3 bucket that stores your enriched Snowplow events.

CREATE EXTERNAL TABLE snowplow_events (
  app_id STRING,
  platform STRING,
  etl_tstamp TIMESTAMP,
  collector_tstamp TIMESTAMP,
  dvce_tstamp TIMESTAMP,
  event STRING,
  event_id STRING,
  txn_id INT,
  name_tracker STRING,
  v_tracker STRING,
  v_collector STRING,
  v_etl STRING,
  user_id STRING,
  user_ipaddress STRING,
  user_fingerprint STRING,
  domain_userid STRING,
  domain_sessionidx INT,
  network_userid STRING,
  geo_country STRING,
  geo_region STRING,
  geo_city STRING,
  geo_zipcode STRING,
  geo_latitude STRING,
  geo_longitude STRING,
  geo_region_name STRING,
  ip_isp STRING,
  ip_organization STRING,
  ip_domain STRING,
  ip_netspeed STRING,
  page_url STRING,
  page_title STRING,
  page_referrer STRING,
  page_urlscheme STRING,
  page_urlhost STRING,
  page_urlport INT,
  page_urlpath STRING,
  page_urlquery STRING,
  page_urlfragment STRING,
  refr_urlscheme STRING,
  refr_urlhost STRING,
  refr_urlport INT,
  refr_urlpath STRING,
  refr_urlquery STRING,
  refr_urlfragment STRING,
  refr_medium STRING,
  refr_source STRING,
  refr_term STRING,
  mkt_medium STRING,
  mkt_source STRING,
  mkt_term STRING,
  mkt_content STRING,
  mkt_campaign STRING,
  contexts STRING,
  se_category STRING,
  se_action STRING,
  se_label STRING,
  se_property STRING,
  se_value STRING,
  unstruct_event STRING,
  tr_orderid STRING,
  tr_affiliation STRING,
  tr_total STRING,
  tr_tax STRING,
  tr_shipping STRING,
  tr_city STRING,
  tr_state STRING,
  tr_country STRING,
  ti_orderid STRING,
  ti_sku STRING,
  ti_name STRING,
  ti_category STRING,
  ti_price STRING,
  ti_quantity INT,
  pp_xoffset_min INT,
  pp_xoffset_max INT,
  pp_yoffset_min INT,
  pp_yoffset_max INT,
  useragent STRING,
  br_name STRING,
  br_family STRING,
  br_version STRING,
  br_type STRING,
  br_renderengine STRING,
  br_lang STRING,
  br_features_pdf STRING,
  br_features_flash STRING,
  br_features_java STRING,
  br_features_director STRING,
  br_features_quicktime STRING,
  br_features_realplayer STRING,
  br_features_windowsmedia STRING,
  br_features_gears STRING,
  br_features_silverlight STRING,
  br_cookies STRING,
  br_colordepth STRING,
  br_viewwidth INT,
  br_viewheight INT,
  os_name STRING,
  os_family STRING,
  os_manufacturer STRING,
  os_timezone STRING,
  dvce_type STRING,
  dvce_ismobile STRING,
  dvce_screenwidth INT,
  dvce_screenheight INT,
  doc_charset STRING,
  doc_width INT,
  doc_height INT,
  tr_currency STRING,
  tr_total_base STRING,
  tr_tax_base STRING,
  tr_shipping_base STRING,
  ti_currency STRING,
  ti_price_base STRING,
  base_currency STRING,
  geo_timezone STRING,
  mkt_clickid STRING,
  mkt_network STRING,
  etl_tags STRING,
  dvce_sent_tstamp TIMESTAMP,
  refr_domain_userid STRING,
  refr_dvce_tstamp TIMESTAMP,
  derived_contexts STRING,
  domain_sessionid STRING,
  derived_tstamp TIMESTAMP
)
PARTITIONED BY(run STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\\t'
STORED AS TEXTFILE
LOCATION 's3://bucket-name/path/to/enriched/good';

Now, we're ready to connect Cube.js to Athena and start building our application.

Analytics API with Cube.js

We'll build our analytics API on top of the Athena with Cube.js. Cube.js is an open-source framework for building analytical web applications. It creates an analytics API on top of the database and handles things like SQL organization, caching, security, authentication, and much more.

Let's install Cube.js CLI and use it to create our application. Run the following commands in your terminal:

$ npm install -g cubejs-cli
$ cubejs create react-dashboard -d athena

Once run, the create command will create a new project directory that contains the scaffolding for your new Cube.js project. Cube.js uses environment variables starting with CUBEJS_ for configuration. To configure the connection to Athena, we need to specify the AWS access and secret keys with the access necessary to run Athena queries, and the target AWS region and S3 output location where query results are stored.

CUBEJS_DB_TYPE=athena
CUBEJS_AWS_KEY=<YOUR ATHENA AWS KEY HERE>
CUBEJS_AWS_SECRET=<YOUR ATHENA SECRET KEY HERE>
CUBEJS_AWS_REGION=<AWS REGION STRING, e.g. us-east-1>
# You can find the Athena S3 Output location here: https://docs.aws.amazon.com/athena/latest/ug/querying.html
CUBEJS_AWS_S3_OUTPUT_LOCATION=<S3 OUTPUT LOCATION>

Next, let's create a sample data schema for our events. Cube.js uses the data schema to generate SQL code, which will be executed in the database. The data schema is not a replacement for SQL. It is designed to make SQL reusable and give it a structure while preserving all of its power. We can build complex data models with Cube.js data schema. You can learn more about Cube.js data schema here.

Create a schema/Events.js file with the following content.

cube(`Events`, {
  sql: `
    SELECT
      event_id,
      event,
      platform,
      derived_tstamp,
      domain_sessionidx,
      domain_sessionid,
      domain_userid,
      ROW_NUMBER() OVER (PARTITION BY domain_sessionid ORDER BY derived_tstamp) AS event_in_session_index
    FROM
       analytics.snowplow_events
  `,

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

  dimensions: {
    timestamp: {
      type: `time`,
      sql: `derived_tstamp`
    },

    id: {
      sql: `event_id`,
      type: `string`,
      primaryKey: true
    }
  }
})

Please, note that we query snowplow_events table from analytics database.
Your database and table name may be different

Now, we can start Cube.js server and open http://localhost:4000. In development mode, Cube.js run Playground. It is an application to help you explore the data schema and send test queries.

Let's test our newly created data schema!

Cube.js accepts queries as JSON objects in the specific query format. Playground lets you visually build and explore queries. For example, we can construct the test query to load all the events over time. Also, you can always inspect the underlying JSON query by clicking JSON Query button.

You can explore other queries as well, test different charting libraries used to
visualize results and explore the frontend javascript code. If you are just starting with Cube.js I recommend checking this tutorial as well.

In the next part, we'll start working on the frontend application and will
steadily build out our data schema.

Introducing a Drill Down Table API in Cube.js

Artyom Keydunov — Thu, 25 Jun 2020 17:05:25 +0000

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

In this blog post, I'll show you how to define drill downs in the data schema and build an interface to let users explore the underlying chart's data. If you're just starting with Cube.js, I highly recommend beginning with this Cube.js 101 tutorial and then coming back here. Also, if you have any questions, don't hesitate to ask them in our Slack community.

You can check the online demo of the example here, and the source code is available on GitHub.

Let's start hacking! 💻

Defining a Drill Down in the Data Schema

Let's start by setting up a new project with Cube.js and configuring drill down support in the data schema. We'll use PostgresQL and our example e-commerce dataset for this tutorial. You can download and import it by running the following commands.

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

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

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

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

CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=ecom

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

$ npm run dev

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

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

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

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

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

Let's add more dimensions to the drillMembers property.

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

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

Building the Drill Down UI

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

Please note: although we use React in this example, you can build the same drill down in Vue.js, Angular, or Vanilla JS.

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

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

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

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

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

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

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

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

export default DrillDownExample;

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

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

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

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

To generate a query that returns data for a drill down table, we need to use the ResultSet#drillDown() method. If we run it with the above values, like this:

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

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

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

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

To get the values for xValues and yValues properties, we will use the ResultSet#chartPivot() and ResultSet#seriesNames() methods. chartPivot() returns xValues for every data row, and seriesNames() returns yValues per series. We're going to use these methods to pass xValues and yValues to the Recharts to make sure we have them in the onClick handler.

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

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

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

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

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

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

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

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

If you have any questions, please don't hesitate to reach out to me in Cube.js Slack community.

D3 Dashboard Tutorial with Cube.js

Artyom Keydunov — Thu, 16 Jan 2020 17:13:36 +0000

In this tutorial, I’ll cover building a basic dashboard application with Cube.js and the most popular library for visualizing data—D3.js. Although Cube.js doesn’t provide a visualization layer itself, it is very easy to integrate with any existing charting library. Additionally, you can use Cube.js Templates to scaffold a frontend application with your favorite charting library, frontend framework, and UI kit. The scaffolding engine will wire it all together and configure it to work with the Cube.js backend.

You can check the online demo of this dashboard here and the complete source code of the example app is available on Github.

We are going to use Postgres to store our data. Cube.js will connect to it and act as a middleware between the database and the client, providing API, abstraction, caching, and a lot more. On the frontend, we’ll have React with Material UI and D3 for chart rendering. Below, you can find a schema of the whole architecture of the example app.

If you have any questions while going through this guide, please feel free to join this Slack community and post your question there.

Happy Hacking! 💻

Setting up a Database and Cube.js

The first thing we need to have in place is a database. We’ll use Postgres for this tutorial. However, you can use your favorite SQL (or Mongo) database. Please refer to the Cube.js documentation on how to connect to different databases.

If you don’t have any data for the dashboard, you can load our sample e-commerce Postgres dataset.

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

Now, as we have data in the database, we’re ready to create the Cube.js Backend service. Run the following commands in your terminal:

$ npm install -g cubejs-cli
$ cubejs create d3-dashboard -d postgres

The commands above install Cube.js CLI and create a new service, configured to work with a Postgres database.

Cube.js uses environment variables for configuration. It uses environment variables starting with CUBEJS_. To configure the connection to our database, we need to specify the DB type and name. In the Cube.js project folder, replace the contents of .env with the following:

CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=ecom
CUBEJS_WEB_SOCKETS=true

Now let’s start the server and open the developer playground at http://localhost:4000.

$ npm run dev

The next step is to create a Cube.js data schema. Cube.js uses the data schema to generate an SQL code, which will be executed in your database. Cube.js Playground can generate simple schemas based on the database’s tables. Let’s navigate to the Schema page and generate the schemas we need for our dashboard. Select the line_items, orders, products, product_categories, and users tables and click Generate Schema.

Let’s test our newly generated schema. Go to the Build page and select a measure in the dropdown. You should be able to see a simple line chart. You can choose D3 from the charting library dropdown to see an example of D3 visualization. Note that it is just an example and you can always customize and expand it.

Now, let’s make some updates to our schema. The schema generation makes it easy to get started and test the dataset, but for real-world use cases, we almost always need to make manual changes.

In the schema, we define measures and dimensions and how they map into SQL queries. You can find extensive documentation about data schema here. We’re going to add a priceRange dimension to the Orders cube. It will indicate whether the total price of the order falls into one of the buckets: “$0 - $100”, “$100 - $200”, “$200+”.

To do this, we first need to define a price dimension for the order. In our database, orders don’t have a price column, but we can calculate it based on the total price of the line_items inside the order. Our schema has already automatically indicated and defined a relationship between the Orders and LineTimes cubes. You can read more about joins here.

// You can check the belongsTo join
// to the Orders cube inside the LineItems cube
joins: {
  Orders: {
    sql: `${CUBE}.order_id = ${Orders}.id`,
    relationship: `belongsTo`
  }
}

The LineItems cube has price measure with a sum type. We can reference this measure from the Orders cube as a dimension and it will give us the sum of all the line items that belong to that order. It’s called a subQuery dimension; you can learn more about it here.

// Add the following dimension to the Orders cube
price: {
  sql: `${LineItems.price}`,
  subQuery: true,
  type: `number`,
  format: `currency`
}

Now, based on this dimension, we can create a priceRange dimension. We’ll use a case statement to define a conditional logic for our price buckets.

// Add the following dimension to the Orders cube
priceRange: {
  type: `string`,
  case: {
    when: [
      { sql: `${price} < 101`, label: `$0 - $100` },
      { sql: `${price} < 201`, label: `$100 - $200` }
    ],
    else: {
      label: `$200+`
    }
  }
}

Let’s try our newly created dimension! Go to the Build page in the playground, select the Orders count measure with the Orders price range dimension. You can always check the generated SQL by clicking the SQL button on the control bar.

That’s it for the backend! In the next part, we’ll look closer at how to render the results of our queries with D3.

Rendering Chart with D3.js

Now, as we can build our first chart, let’s inspect the example code playground uses to render it with the D3. Before that, we need to understand how Cube.js accepts and processes a query and returns the result back.

A Cube.js query is a simple JSON object containing several properties. The main properties of the query are measures, dimensions, timeDimensions, and filters. You can learn more about the Cube.js JSON query format and its properties here. You can always inspect the JSON query in the playground by clicking the JSON Query button next to the chart selector.

Cube.js backend accepts this query and then uses it and the schema we created earlier to generate an SQL query. This SQL query will be executed in our database and the result will be sent back to the client.

Although Cube.js can be queried via plain HTTP REST API, we’re going to use the Cube.js JavaScript client library. Among other things it provides useful tools to process the data after it has been returned from the backend.

Once the data is loaded, the Cube.js client creates a ResultSet object, which provides a set of methods to access and manipulate the data. We’re going to use two of them now: ResultSet.series and ResultSet.chartPivot. You can learn about all the features of the Cube.js client library in the docs.

The ResultSet.series method returns an array of data series with key, title, and series data. The method accepts one argument—pivotConfig. It is an object, containing rules about how the data should be pivoted; we’ll talk about it a bit. In a line chart, each series is usually represented by a separate line. This method is useful for preparing data in the format expected by D3.

// For query
{
  measures: ['Stories.count'],
  timeDimensions: [{
    dimension: 'Stories.time',
    dateRange: ['2015-01-01', '2015-12-31'],
    granularity: 'month'
  }]
}

// ResultSet.series() will return
[
  {
    "key":"Stories.count",
    "title": "Stories Count",
    "series": [
      { "x":"2015-01-01T00:00:00", "value": 27120 },
      { "x":"2015-02-01T00:00:00", "value": 25861 },
      { "x": "2015-03-01T00:00:00", "value": 29661 },
      //...
    ]
  }
]

The next method we need is ResultSet.chartPivot. It accepts the same pivotConfig argument and returns an array of data with values for the X-axis and for every series we have.

// For query
{
  measures: ['Stories.count'],
  timeDimensions: [{
    dimension: 'Stories.time',
    dateRange: ['2015-01-01', '2015-12-31'],
    granularity: 'month'
  }]
}

// ResultSet.chartPivot() will return
[
  { "x":"2015-01-01T00:00:00", "Stories.count": 27120 },
  { "x":"2015-02-01T00:00:00", "Stories.count": 25861 },
  { "x": "2015-03-01T00:00:00", "Stories.count": 29661 },
  //...
]

As mentioned above, the pivotConfig argument is an object for controlling how to transform, or pivot, data. The object has two properties: x and y, both are arrays. By adding measures or dimensions to one of them, you can control what goes to the X-axis and what goes to the Y-axis. For a query with one measure and one timeDimension, pivotConfig has the following default value:

{
   x: `CubeName.myTimeDimension.granularity`,
   y: `measures`
}

Here, ‘measures’ is a special value, meaning that all the measures should go to the Y-axis. In most cases, the default value of the pivotConfig should work fine. In the next part, I’ll show you when and how we need to change it.

Now, let’s look at the frontend code playground generates when we select a D3 chart. Select a measure in the playground and change the visualization type to the D3. Next, click the Code to inspect the frontend code to render the chart.

Here is the full source code from that page.

import React from 'react';
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import { Spin } from 'antd';

import * as d3 from 'd3';
const COLORS_SERIES = ['#FF6492', '#141446', '#7A77FF'];

const draw = (node, resultSet, chartType) => {
  // Set the dimensions and margins of the graph
  const margin = {top: 10, right: 30, bottom: 30, left: 60},
    width = node.clientWidth - margin.left - margin.right,
    height = 400 - margin.top - margin.bottom;

  d3.select(node).html("");
  const svg = d3.select(node)
  .append("svg")
    .attr("width", width + margin.left + margin.right)
    .attr("height", height + margin.top + margin.bottom)
  .append("g")
    .attr("transform",
          "translate(" + margin.left + "," + margin.top + ")");

  // Prepare data in D3 format
  const data = resultSet.series().map((series) => ({
    key: series.title, values: series.series
  }));

  // color palette
  const color = d3.scaleOrdinal()
    .domain(data.map(d => d.key ))
    .range(COLORS_SERIES)

  // Add X axis
  const x = d3.scaleTime()
    .domain(d3.extent(resultSet.chartPivot(), c => d3.isoParse(c.x)))
    .range([ 0, width ]);
  svg.append("g")
    .attr("transform", "translate(0," + height + ")")
    .call(d3.axisBottom(x));

  // Add Y axis
  const y = d3.scaleLinear()
    .domain([0, d3.max(data.map((s) => d3.max(s.values, (i) => i.value)))])
    .range([ height, 0 ]);
  svg.append("g")
    .call(d3.axisLeft(y));

  // Draw the lines
  svg.selectAll(".line")
    .data(data)
    .enter()
    .append("path")
      .attr("fill", "none")
      .attr("stroke", d => color(d.key))
      .attr("stroke-width", 1.5)
      .attr("d", (d) => {
        return d3.line()
          .x(d => x(d3.isoParse(d.x)))
          .y(d => y(+d.value))
          (d.values)
      })

}

const lineRender = ({ resultSet }) => (
  <div ref={el => el && draw(el, resultSet, 'line')} />
)


const API_URL = "http://localhost:4000"; // change to your actual endpoint

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NzkwMjU0ODcsImV4cCI6MTU3OTExMTg4N30.nUyJ4AEsNk9ks9C8OwGPCHrcTXyJtqJxm02df7RGnQU",
  { apiUrl: API_URL + "/cubejs-api/v1" }
);

const renderChart = (Component) => ({ resultSet, error }) => (
  (resultSet && <Component resultSet={resultSet} />) ||
  (error && error.toString()) ||
  (<Spin />)
)

const ChartRenderer = () => <QueryRenderer
  query={{
    "measures": [
      "Orders.count"
    ],
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
        "granularity": "month"
      }
    ],
    "filters": []
  }}
  cubejsApi={cubejsApi}
  render={renderChart(lineRender)}
/>;

export default ChartRenderer;

The React component that renders the chart is just a single line wrapping a draw function, which does the entire job.

const lineRender = ({ resultSet }) => (
  <div ref={el => el && draw(el, resultSet, 'line')} />
)

There is a lot going on in this draw function. Although it renders a chart already, think about it as an example and a good starting point for customization. As we’ll work on our own dashboard in the next part, I’ll show you how to do it.

Feel free to click the Edit button and play around with the code in Code Sandbox.

Building a Frontend Dashboard

Now we are ready to build our frontend application. We’re going to use Cube.js Templates, which is a scaffolding engine for quickly creating frontend applications configured to work with the Cube.js backend. It provides a selection of different frontend frameworks, UI kits, and charting libraries to mix together. We’ll pick React, Material UI, and D3.js. Let’s navigate to the Dashboard App tab and create a new dashboard application.

It could take several minutes to generate an app and install all the dependencies. Once it is done, you will have a dashboard-app folder inside your Cube.js project folder. To start a frontend application, either go to the “Dashboard App” tab in the playground and hit the “Start” button, or run the following command inside the dashboard-app folder:

$ npm start

Make sure the Cube.js backend process is up and running since our frontend application uses its API. The frontend application is running on http://localhost:3000. If you open it in your browser, you should be able to see an empty dashboard.

To add a chart to the dashboard, we can either build it in the playground and click the “add to dashboard” button or edit the src/pages/DashboardPage.js file in the dashboard-app folder. Let’s go with the latter option. Among other things, this file declares the DashboardItems variable, which is an array of queries for charts.

Edit dashboard-app/src/pages/DashboardPage.js to add charts to the dashboard.

-const DashboardItems = [];
+const DashboardItems = [
+  {
+    id: 0,
+    name: "Orders last 14 days",
+    vizState: {
+      query: {
+        measures: ["Orders.count"],
+        timeDimensions: [
+          {
+            dimension: "Orders.createdAt",
+            granularity: "day",
+            dateRange: "last 14 days"
+          }
+        ],
+        filters: []
+      },
+      chartType: "line"
+    }
+  },
+  {
+    id: 1,
+    name: "Orders Status by Customers City",
+    vizState: {
+      query: {
+        measures: ["Orders.count"],
+        dimensions: ["Users.city", "Orders.status"],
+        timeDimensions: [
+          {
+            dimension: "Orders.createdAt",
+            dateRange: "last year"
+          }
+        ]
+      },
+      chartType: "bar",
+      pivotConfig: {
+        x: ["Users.city"],
+        y: ["Orders.status", "measures"]
+      }
+    }
+  },
+  {
+    id: 3,
+    name: "Orders by Product Categories Over Time",
+    vizState: {
+      query: {
+        measures: ["Orders.count"],
+        timeDimensions: [
+          {
+            dimension: "Orders.createdAt",
+            granularity: "month",
+            dateRange: "last year"
+          }
+        ],
+        dimensions: ["ProductCategories.name"]
+      },
+      chartType: "area"
+    }
+  },
+  {
+    id: 3,
+    name: "Orders by Price Range",
+    vizState: {
+      query: {
+        measures: ["Orders.count"],
+        filters: [
+          {
+            "dimension": "Orders.price",
+            "operator": "set"
+          }
+        ],
+        dimensions: ["Orders.priceRange"]
+      },
+      chartType: "pie"
+    }
+  }
+];

As you can see above, we’ve just added an array of Cube.js query objects.

If you refresh the dashboard, you should be able to see your charts!

You can notice that one of our queries has the pivotConfig defined as the following.

  pivotConfig: {
    x: ["Users.city"],
    y: ["Orders.status", "measures"]
  }

As I mentioned in the previous part the default value for the pivotConfig usually works fine, but in some cases like this one, we need to adjust it to get the desired result. We want to plot a bar chart here with the cities on the X-Axis and the number of orders on the Y-Axis grouped by the orders' statuses. That is exactly what we are passing here in the pivotConfig: Users.city to the X-Axis and measures with Orders.status to the Y-axis to get the grouped result.

To customize the rendering of the charts, you can edit the dashboard-app/src/pages/ChartRenderer.js file. It should look familiar to what we saw in the previous part.

You can check the online demo of this dashboard here and the complete source code of the example app is available on Github.

Congratulations on completing this guide! 🎉

I’d love to hear from you about your experience following this guide. Please send any comments or feedback you might have here in the comments or in this Slack Community. Thank you and I hope you found this guide helpful!

Launching Cube.js Templates 📊 - the easiest way to build analytics dashboards and applications

Artyom Keydunov — Thu, 21 Nov 2019 15:18:25 +0000

Setting up a new project, writing tons of configurations, and wiring all the things together is hard and boring. It's fun to write actual application code, not Webpack config. That's why Create React App is so extremely popular.

Same for analytics apps and dashboards. Although Cube.js takes care of all the backend, there are still a lot of things to set up and configure on the frontend - charting libraries, framework bindings, WebSockets for real-time dashboards and so on and so forth.

Cube.js Templates to the rescue! Templates are open-source, ready-to-use frontend analytics apps. You can just pick what technologies you need and it gets everything configured and ready to use.

React with WebSockets, Chart.js and Material UI? You got it. Template wires it all together and configure to work with the Cube.js backend.

Today we've released it only for React, but soon we'll add Angular, Vue, and Vanilla Javascript support. And it is open-sourced, same as Cube.js. Contributions are very welcomed! ❤️

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications

5 Minute Tutorial

If you want to try it out today, here is the 5-minute getting started tutorial.

Install Cube.js CLI

$ npm isntall cubejs-cli -g

Create New Project and Connect your database

Cube.js CLI has create command to setup new project. We also need to pass a database type with -d option. Here is the list of supported databases.

$ cubejs create hello-world -d postgres

Once created, cd into your new project and edit .env file to configure the database.

CUBEJS_DB_NAME=my_database
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=SUPER_SECRET

Now, run the following command to start a dev server.

$ npm run dev

And navigate to the Cube.js Playground at http://localhost:4000

Generate Schema

Cube.js uses schema to know how to query your database. The schema is written in javascript and could be quite complex with a lot of logic. But as we just getting started we can auto-generate a simple schema in the playground.

Use Cube.js Templates to create a frontend app

As we already have a Cube.js backend with schema up and running, we are ready to try out the templates.

Navigate to the "Dashboard App" tab in the playground. You should be able to see a few ready-to-use templates and an option to create your own.

Feel free to click select whatever template works for you. Or you can mix different options and create your own template.

Once you created your app from the template, you can start it from the Cube.js playground. You can edit it later in the dashboard-app folder inside the project.

That's it! Now, you have a full working both backend and frontend for your dashboard. You can follow React Dashboard Guide or Real-Time Dashboard Guide to learn how to customize the dashboard app and deploy it to production 🚀

Please feel free to share your feedback or ask any questions in the comments below or in this Slack community.