March 2019 Summaries

Cube is an open-source framework for building analytical web applications, designed to be simple to start but scalable in features, complexity, and data volume. It follows a modern approach of single-page application with a frontend separated from the API, which is also split into multiple services following a microservice architecture. Analytics starts with data residing in a database, typically using popular databases like Postgres or MySQL for simple analytical workloads. Cube can be integrated with various front-end frameworks such as React and vanilla JavaScript to build dashboards and analytics features. The framework uses a data schema to generate SQL code, which is executed in the database, making it reusable and giving power while preserving its structure.

Starting with version 0.4, the React Cube client includes the `<QueryBuilder />` component, designed to facilitate the creation of interactive analytics query builders by abstracting state management and API calls to the Cube Backend. Utilizing render props, it offers flexibility for custom UI development without rendering elements itself. Developers can build their own query builders by setting up a Cube Backend, configuring environments, and using Cube.js schema to generate a dev server, accessible via Cube.js Playground, which serves as a development environment providing scaffolding for charts and a built-in query builder. The `<QueryBuilder />` acts as a data provider, and requires the `cubejsApi` prop, expecting an instance of the cube.js API client, while other properties like `measures`, `availableMeasures`, and `updateMeasures` help manage the state related to measures, dimensions, segments, time, filters, and chart types, allowing for a dynamic and interactive query building experience. This tutorial provides a comprehensive guide to creating a simple but functional query builder, with the option to explore more complex examples through the Cube Playground's source code on GitHub.

The tutorial explains how to create a basic analytics dashboard using Node, Express, and Cube.js with MongoDB as the database. The final dashboard displays key performance indicators (KPIs) in a pie chart and bar chart, and provides real-time data updates. The tutorial covers the setup of an Express application, embedding Cube.js into it, creating a schema for a tweets table, and building the analytics dashboard using Bootstrap, Chart.js, and Cube.js client. It also discusses alternative setups, such as running Cube.js in serverless mode, and highlights the benefits of using Cube.js over hitting MongoDB directly with SQL queries.

This tutorial series is about building an analytical web application with Cube.js, a data analysis and visualization platform. The second part of the tutorial covers adding Funnel Analysis to the application, which helps analyze behavior across the customer journey by tracking user progress through a series of events or "funnels". To build funnels, the authors use Cube.js's Funnels package, which allows for dynamic generation of SQL code and pre-aggregations to improve query performance. The authors also discuss visualizing funnel data using Recharts library and generating a cube with conversions and steps measures. The next part of the tutorial will cover building a dashboard and dynamic query builder, while the final part will focus on deploying the application in serverless mode to AWS Lambda.

Serverless RDBMSs, such as AWS Aurora, offer a significant advancement in handling analytics on production RDBMS instances by decoupling processing power from storage, enabling scalable infrastructure and simultaneous OLTP and OLAP workloads. In a benchmark test, an unoptimized dataset of 100 million rows queried on Serverless MySQL Aurora took 176 seconds, with potential reductions to sub-200ms using multi-stage querying. The test involved generating a large dataset from the Sakila Sample Database, utilizing Cube.js for caching and pre-aggregation to improve query performance, and optimizing with indexing and pre-aggregation strategies that effectively reduced query processing times. While Serverless Aurora MySQL can manage extensive workloads, limitations in workload routing algorithms pose challenges, suggesting that a provisioned Aurora MySQL with read replicas or Parallel Query might be more suitable for production environments to efficiently handle OLTP and OLAP workloads.