Simon Späti

Deep Dives

Universal Semantic Layer: Capabilities, Integrations, and Enterprise Benefits

MVC is an interesting concept from the late 70s that separates the View (presentation) from the Controller via the Model. It has been used in designing web applications and is still heavily used, for example, in Ruby on Rails or Laravel, a popular PHP framework.

This design pattern got me thinking: Wouldn't it be convenient to separate the presentation from the storage through a data modeling layer, similar to the model layer? A place where we can declaratively define our metrics, measures, and business KPIs so that we can update them confidently and version them.

Today, we have the beginning of such a separation of concerns with a [Semantic Layer](https://cube.dev/use-cases/semantic-layer). Let's explore the parallels between MVC and data.

## What Is an Active Record and How Does It Fit In?
To understand how this parallel works, let's start with [Active Record](https://guides.rubyonrails.org/active_record_basics.html), the intermediary, and concrete implementation of the Model layer in Ruby on Rails. An Active Record is the "M" in MVC (Model View Controller) and maps database tables to Ruby classes. It facilitates the creation and use of business objects whose data requires persistent storage in a database, also __called [ORM (Object-Relational Mapping)](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping)__.

A data modeling language such as __[LookML](https://cloud.google.com/looker/docs/what-is-lookml)__ __could be the ORM to a semantic layer__, mapping raw data sources to business metrics or existing defined tables within the semantic layer, also called [Views](https://cube.dev/blog/introducing-views).

The Active Record provides methods to perform CRUD operations without writing SQL. Semantic layers typically don't update or change data but provide extensive API components that abstract complex consumer queries and __data operations__. The Active Record also represents relationships between models where a semantic layer's data modeling component defines __relationships__ between various data entities and metrics.

An Active Record __validates__ data before persisting in the database; the same goes for the semantic layer, with its access control and declarative data modeling, which allows parsing definitions and ensures __data integrity__ and access rights.

## Web Development's MVC Applied to Data
We can see similar analogies if we take a step back and look at the [MVC model](https://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller).

In MVC, the *View* represents the visual presentation of information, such as charts, diagrams, or tables, and the *Model* represents the application's dynamic data structure. And we use the *Controller* to combine the two.
![Image of the MVC and how they interact.](https://ucarecdn.com/b276118b-d342-493c-bf42-8e85f8a202d4/)
*Image of the MVC and how they interact.*

The Controller usually accepts user input and converts it into commands for the Model or View. In Rails, client requests are sent to a router, which directs them to specific controller methods. The controller then interacts with models and prepares responses using views.

This solved the problem of __maintainability__ and, most importantly, __separation of concerns__. If we contrast that with the data world, where data transformations are made within data pipelines, dbt transformations, in your business intelligence tools, or anywhere along the data warehouse chain, we have the same problems that key metrics are __mixed__ with technical implementation logic, usually defined multiple times in several BI tools, data apps or dashboards.

Traditional data access is done through SQL queries via [ODBC](https://en.wikipedia.org/wiki/Open_Database_Connectivity) connection or a data pipeline. In the context of newer data architecture, such as a data lake or data lakehouse, we duplicate metrics definitions on top of this extracted data. Often, creating another dbt model is much easier than using an existing one.

This duplication of definitions obscures the maintainability of key KPI declarations, creating inconsistency, making them harder to apply, and affecting data governance.

Think of the Model<sup>1</sup> from the model-view-controller pattern and how it could solve these issues with a __central, consistent, and declarative__ way of defining our metrics. This is what a semantic layer is created for.

> 📝 Still Want to Know More About MVC or Active Record?
> 
> For a deeper understanding of these fundamental patterns with detailed explanations of concepts we're borrowing for our data architecture patterns:
> 
> - [Ruby on Rails Guide on Active Record](https://guides.rubyonrails.org/active_record_basics.html): Learn about the complete ORM implementation in Rails
> - [Wikipedia's MVC article](https://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller): Explore the pattern's history from Xerox PARC to modern web frameworks

## The MVC for Data as a Semantic Layer
If we apply the semantic layer to the MVC model: Can we map it to one of the components, the model or the controller? Let's check.

Like the __Controller__, which accepts user input and converts it into actions on the Model or View, the semantic layer acts as an intermediary between data consumers (like BI tools or data apps) and data sources. It receives queries or requests from users and translates them through the APIs (SQL, MDX, REST, GraphQL, AI, etc.) into executable commands against the data sources. It routes user intentions to the correct data retrieval or manipulation, abstracting the underlying, potentially complex, architecture.

At the same time, a semantic layer also overlaps and has traits from the __Model__. The Model from MVC holds the application's data and business logic, similar to a semantic layer that holds the data modeling and key business metrics, this aligns closely with the Model's responsibility for data representation and business rules. Secondly, the Model ensures data integrity and validation logic. Similarly, the semantic layer enforces best practices data governance rules with access controls and, with it, data model language that checks the validity of rules almost like a compiler.

So, can the semantic layer be seen as a Controller? Partially, yes: It acts as a mediator between the user's requests and the data, but not exclusively: It also embodies characteristics of the Model by defining data structures, business metrics, and relationships.

That could look something like this:
![](https://ucarecdn.com/4c246ad9-4c82-4a9e-ba57-bbf31b7d2a1e/)
*The image shows the semantic layer MVC, comparing the traditional MVC above.*

Breaking it down - looking only at the query side (not updating the data as seen with dotted lines) - we can put the semantic layer in the middle, replacing the Controller, and at the same time, the View component goes through the semantic layer as an abstraction:

- __Semantic Layer ↔ Model__: The semantic layer applies business logic and abstracts complex data access, governing the Model from direct querying by users and views.
- __Semantic Layer ↔ View__: The View uses the semantic layer as an abstraction to access data via APIs, ensuring metrics are only defined once and are consistent.
- __Semantic Layer ↔ User__: The user interacts with the semantic layer to update business metrics and retrieve data, maybe checking the lineage of a given KPI. The layer ensures metrics are updated consistently and aligned with the defined business rules.

The connections are left with dotted lines, as they might still occur, but not in the lens of the semantic layer:

- __Semantic Layer ↔ Controller__: The controller is only involved in direct CRUD operations on the Model, which would bypass the semantic layer.
- __Model ↔ View__: For example, clicking a button that causes a database entry to be updated. It is not a use case a semantic layer supports.

### A More Semantic Layer-Centric View
As we have seen, the semantic layer acts mostly as an intermediary between the raw data (Model) and the analytics tools or applications (View/Controller). If we recall the __key components__ of a semantic layer:

- __Data modeling__: Unified, consistent metric definition in a declarative manner.
- __APIs__ (GraphQL, REST, SQL, MDX, AI): Integrate with any endpoint and deliver trusted data.
- __Access control__: Centralize and fine-grain governance and security policies.
- __Caching__: Query optimization, pre-aggregations; deliver faster more cost-efficient results.

We can update and draw another image by interacting with all the sources (Model) and have all the visualizations with data apps, BI tools, spreadsheets, and AI models (View). In the below adapted MVC model for a semantic layer, we represent the raw data sources as the Model.

![](https://ucarecdn.com/891e7ea7-93ab-4a5d-a987-d5c1c14a7485/)
*The image represents the MVC pattern for a Semantic Layer, interacting with various ways data is consumed and visualized.*

The semantic layer essentially absorbs some Model-like responsibilities (data modeling) while primarily functioning as an advanced Controller that manages how data is accessed, processed, and delivered to the View components.

## What Are the Benefits of the MVC in Data?
How does the MVC pattern improve our lives within the data engineering lifecycle?

In summary, a semantic layer achieves a __separation of concerns__, which benefits complex organizations. With its declarative nature, we get __flexibility__ and more straightforward modification and extension of individual metrics, as well as __reusability__ through models that can be reused across different views (dashboards, data apps, etc.), which is instrumental in following the DRY principles for your most precious Key Performance Indicator.

Ultimately, it serves much better __data governance__, maintainability, a single source of definition, and simplifies metrics in large organizations with a declarative way of defining metrics.

> 💡 Related Architectural Patterns
>
> There are also other related Patterns we didn't get into. [Repository Pattern](https://martinfowler.com/eaaCatalog/repository.html) and [Facade Pattern](https://en.wikipedia.org/wiki/Facade_pattern). They use different terms but serve similar purposes to achieve __separation of concerns__. A simplified view and comparison:
> 
> - __Repository Pattern__: Client → Repository → Domain Objects Person
> - __MVC__: View → Controller → Model
> - __Facade Pattern__: Client → Facade → Complex Subsystems

## What's Next
Organizations like Jobber have demonstrated the practical benefits of evolving from traditional patterns like Active Record to modern semantic layers. Their [transition](https://cube.dev/blog/jobber-runs-on-cubejs) from Active Record to Cube shows how semantic layers can benefit organizations directly.

As we have seen, a semantic layer can benefit MVC and Active Records. It ensures a central, consistent, and declarative way of defining metrics, improving maintainability and governance in complex data systems.

In Part 2, we'll examine the benefits of having a semantic layer and focus on its strengths and advantages above and beyond those discussed in Part 1, as well as how it fits into today's data architecture.

---

<sup>1</sup> Note: We will explore a bit of a nuanced definition of a semantic layer as a mix of controller and model.

Exploring the Semantic Layer Through the Lens of MVC

The shift to cloud data storage has been a transformative move for enterprises, promising scalability, security, and accessibility, but the journey is not without obstacles. One common and critical challenge that many organizations face post-migration is the disruption of live queries from spreadsheets—a staple in business operations. For technical decision-makers, this presents a significant hurdle. 

Fortunately, Cube Cloud’s universal semantic layer not only enables but also enhances the spreadsheet experience. In this blog, learn why Cube Cloud and spreadsheets are a powerful combination to enable live queries in Microsoft Excel and Google Sheets.

## Losing Live Query Capabilities in Spreadsheets
Despite advancements in modern BI and data visualization platforms, spreadsheets remain an indispensable part of the business productivity suite. Their flexibility, ease of use, and ubiquity make them an irreplaceable tool for many users. From finance departments running complex calculations to sales teams tracking targets, spreadsheets are deeply ingrained in daily workflows in multiple lines of business.

While the migration to cloud data platforms like Databricks, Snowflake, BigQuery, or Redshift is a strategic move towards a more modern, scalable infrastructure, an unintended consequence is the inability to live query cloud data from spreadsheets. This issue arises because traditional spreadsheet tools like Microsoft Excel and Google Sheets aren’t natively designed to interact seamlessly with most cloud data platforms. 

## Why Cube Cloud and Spreadsheets Are Better Together
Instead of forcing users to export data manually, which is time-consuming and error-prone, there is a better way forward with Cube Cloud. It offers an enhanced solution to the spreadsheet dilemma with the universal semantic layer. 

Cube Cloud seamlessly integrates with your existing cloud data infrastructure, improving your data and analytics workflows. The platform supports a wide range of data sources, BI tools, and spreadsheets, making it a versatile addition to your data stack. It’s the ideal choice for enterprises seeking to bridge the gap between their cloud data platforms and spreadsheets.

### Unify Data Models and Metrics
Cube Cloud acts as an intermediary between your cloud data platform and spreadsheets. It provides a unified access point that standardizes queries and ensures consistent data definitions across all tools. This means that whether a user is working in Microsoft Excel, Google Sheets, or a modern BI platform, they’re accessing the same, consistent data.

### Centralize Governance and Security
One of the major concerns with data exports is the potential for data breaches and loss of data integrity from manual manipulations. Cube Cloud addresses this by centralizing data governance and security. With the universal semantic layer, you can enforce access controls and permissions, ensuring that only authorized users can access sensitive information. This mitigates the risk associated with data sprawl and unauthorized data exports.

### Optimize Performance
Live querying from spreadsheets to cloud data platforms can be costly, resource-intensive, and slow if not managed properly. Cube Cloud optimizes query performance through pre-aggregation caching. This ensures that your users can run complex queries directly from their spreadsheets without experiencing significant lag or performance issues.

## How Cube Cloud Enhances Spreadsheet Analytics
By integrating Cube Cloud, you can restore the ability for direct data interactions within spreadsheets. Users can refresh their data, apply filters, and run analyses on the latest information without needing to export and re-import data. This real-time capability is crucial for making informed business decisions quickly and accurately.

You can extend the benefits of the universal semantic layer by allowing anyone to connect, analyze, and reuse trusted data assets with Microsoft Excel and Google Sheets using a live connection, without exports or snapshots. This integration eliminates the need for pre-processing, ensures real-time data access, and streamlines complex data transformations, allowing analysts to focus on deriving insights and storytelling.

### Modern Cloud OLAP in Microsoft Excel
The native MDX API in Cube Cloud leverages Excel’s native Analysis Services connection. It integrates the universal semantic layer with Excel so that users can perform multidimensional analysis and leverage Excel's powerful data analysis features while maintaining a connection to Cube Cloud. Now, you can leave legacy systems behind, and step into the future to support [Modern Cloud OLAP](https://cube.dev/use-cases/modern-cloud-olap).

By migrating from legacy OLAP platforms, organizations can stop supporting the older systems, providing license and maintenance savings and replacing them with more powerful, modern cloud data platforms. With the MDX API, Excel directly queries large datasets through the universal semantic layer. The MDX queries are translated into optimized SQL, processed by Cube, and the results are returned to Excel. 

![](https://ucarecdn.com/0b221539-69c7-4ffa-b201-5f2755f0b5ee/-/quality/smart/-/format/auto/)

### Perform Live Queries in Google Sheets
Cube Cloud for Sheets is a Google Sheets Add-on. It can be found in the [Google Workspace Marketplace](https://workspace.google.com/marketplace) and installed via the browser, or added from Google Sheets directly from Extensions > Add-ons. Once installed, you can access a Cube Cloud sidebar. With Cube Cloud for Sheets, customers can search data assets and create pivot tables from modeled data in Cube. 

Cube Cloud for Sheets integrates the universal semantic layer with Google Sheets so that users can perform live queries on governed data with Google Sheets, while maintaining a connection to Cube. With the add-on, Google Sheets can directly query and aggregate any data modeled in Cube Cloud through the universal semantic layer, opening up data access beyond BigQuery and Looker, the default connection options.

![](https://ucarecdn.com/dcbce8da-e711-41ab-93e1-f87adc243f2c/-/quality/smart/-/format/auto/)

## Enable Live Query Capabilities with Spreadsheets and Cube Cloud
The future of enterprise data is in the cloud, but that doesn’t mean we should leave behind the tools that have proven their worth over decades. Spreadsheets are here to stay, and with Cube Cloud’s universal semantic layer, you can ensure they remain powerful and governed tools for everyone in your organization. 

By restoring live query capabilities, enhancing data governance, and optimizing performance, Cube Cloud bridges the gap between your cloud data warehouse and the familiar spreadsheet environment, empowering your teams to work smarter and more efficiently.

Embrace the best of both worlds—modern cloud infrastructure and the timeless utility of spreadsheets—with Cube Cloud. [Contact sales](https://cube.dev/contact) to learn more about live queries in Microsoft Excel and Google Sheets.

Michael Hetrick

How Cube Improves Spreadsheet Analytics

Enterprises are constantly seeking ways to streamline and optimize their data infrastructure. In fact, 95% of global executives agree on the necessity of new data architectures and strategies to effectively manage significant changes in their organizations’ data environments, according to Accenture, which names the need for a semantic layer. 

The shift toward a universal semantic layer promises transformative benefits for organizations. The technical solution to unifying disparate, built-in semantic layers is relatively straightforward with Cube Cloud’s universal semantic layer, which enables unified, governed, optimized, and integrated data models that can be reused across all endpoints. 

While the technical aspects of this change are important, the real challenge lies in successfully managing the changes that must accompany this transformation. Change management is not just a complementary aspect of the transition; it is the cornerstone of success. Without a robust change management strategy, even the best technical solutions can fail to deliver business value. 

In this blog, we’ll explore why change management should be at the forefront of your transition plan and provide prescriptive guidance on how to manage the shift from multiple semantic layers across the modern data stack and within data consumers to a single, universal semantic layer. By focusing on key organizational change factors, enterprises can fully realize the potential of a universal semantic layer and avoid the pitfalls that often come with large-scale data and analytics initiatives.

## 1. Establish a Clear Vision and Communicate It
At the outset of any significant organizational change, it’s essential to establish a clear and compelling vision for the future. This is particularly true when transitioning to a universal semantic layer, as the move can affect multiple departments and systems across the enterprise. 

Leaders must articulate not only the technical advantages but also the business outcomes, such as improved data consistency, enhanced analytics capabilities, and faster time-to-insight. Focus on what is gained—your data with consistency, context, and trust. When employees across all levels understand why the change is happening and how it will improve their day-to-day work, they’re more likely to buy into the process and contribute positively to its success. 

Equally important is how this vision is communicated. A one-time announcement won’t suffice. Instead, ongoing and transparent communication is crucial throughout the transition process. This includes hosting regular meetings, sending out newsletters, and providing avenues for employees to provide feedback, ask questions, and express concerns. By fostering an environment of open communication, the leadership team can keep employees engaged and aligned with the overall vision of moving toward a universal semantic layer.

## 2. Secure Executive Sponsorship
No large-scale organizational change succeeds without the visible and active involvement of senior leadership. Executive sponsorship provides the necessary authority, resources, and momentum to overcome potential resistance and roadblocks along the way. Without strong executive backing, even the most technically sound solutions can languish due to lack of support. For the transition to a universal semantic layer, having an executive sponsor who understands the strategic importance of the initiative and is committed to its success is vital.

Executive sponsors need to be active advocates. They should actively participate in the process by championing the initiative in meetings, addressing concerns from stakeholders, and ensuring that the transition is adequately resourced. Their involvement signals to the entire organization that this is a high-priority initiative and that success hinges on collective effort. Additionally, their continued oversight can help navigate competing priorities and ensure that focus remains on driving the universal semantic layer forward.

## 3. Assemble a Cross-Functional Team
A successful transition to a universal semantic layer requires collaboration across multiple teams. Data infrastructure touches virtually every aspect of the business, from IT and data engineering to business intelligence and individual departments relying on analytics to make data-driven decisions. Assembling a cross-functional team that represents these various stakeholders is essential. This team will be tasked with guiding the transition, troubleshooting issues, and ensuring the solution meets the needs of the broader organization.

This cross-functional team should include both technical experts, data analysts, and business users. The technical side will understand how to integrate systems, ensure data flows are smooth, and maintain security. Meanwhile, analysts and business users can offer invaluable insight into how they interact with data and what they need from the new deployment. This collaboration ensures that the transition addresses both operational efficiency and user needs, making it more likely to be adopted across the organization.

## 4. Conduct a Thorough Needs Assessment
Before diving into implementation, conducting a thorough needs assessment is critical. A universal semantic layer will unify your data, but it’s essential to understand the existing environment and use cases first. Document how data is managed and consumed across AI, BI, spreadsheets, and embedded analytics. This assessment will identify pain points in the current state, such as data silos, inconsistent metrics, and inefficient workflows. Your goal is to map out how the isolated semantic layers within specific use cases are currently created and managed, as well as how they can be centralized and optimized within the universal semantic layer.

In this stage, gathering input from key stakeholders across the organization is invaluable. Data engineers will have insights into technical bottlenecks, while business intelligence teams can provide feedback on how the current approach to semantics serves—or hinders—their analytical needs. With this information in hand, you can create a roadmap that addresses existing pain points while strategically introducing the new universal semantic layer. The assessment not only helps shape the technical implementation but also provides a baseline for measuring the success of the transition.

## 5. Develop a Comprehensive Training Program
A new universal semantic layer won’t succeed unless users are empowered to utilize it effectively. A comprehensive training program is key to ensuring that employees, regardless of their role, are prepared for the changes. Training should be tailored to different user groups, as data engineers will need more in-depth technical knowledge, while business users will need to understand how the universal semantic layer improves their workflows and analytics capabilities. 

In addition to formal training sessions, consider creating a repository of resources that employees can reference post-implementation, such as online tutorials, user guides, and FAQs. This kind of continuous enablement allows for a smoother transition, as employees can learn at their own pace and troubleshoot issues on their own before reaching out for support. Ongoing education is critical to maintaining high adoption rates and ensuring that the universal semantic layer is leveraged to its full potential.

## 6. Implement Incrementally and Iterate
For a project as comprehensive as implementing a universal semantic layer, a phased, incremental approach is often the most effective. Instead of rolling-out all at once, start with a single use case. Choose from AI, BI, spreadsheets, or embedded analytics for a department or a subset of data sources that will serve as the initial use case. This pilot allows your organization to address technical issues, refine workflows, and gather feedback before expanding the rollout.

An iterative approach provides the flexibility to make adjustments and optimize the solution based on real-world usage. For example, if users struggle with certain aspects, you can modify training or even update data definitions in response. This method not only helps to minimize disruptions but also gives stakeholders a sense of ownership as they see the universal semantic layer evolving to meet their needs. When the time comes for broader deployment and expanding to support other use cases, your teams will be better prepared, having learned valuable lessons from the pilot phase.

## 7. Monitor Progress and Measure Success
Transitioning to a universal semantic layer isn’t a one-time event; it’s a continuous process that requires ongoing monitoring and management. It’s important to establish clear metrics for success and track these throughout the transition. These metrics could include data consistency, time to insight, adoption rates, and overall user satisfaction. By setting these KPIs from the outset, you’ll be able to measure progress and quickly identify areas where further adjustments are needed.

In addition to tracking quantitative metrics, gathering qualitative feedback from users is equally important. Regular check-ins with key stakeholders and end-users can provide insights into how the universal semantic layer is functioning in practice. By maintaining open communication channels and continuously measuring both technical and user-focused KPIs, you can ensure the transition is delivering on its promised benefits and make course corrections as necessary.

## Conclusion
The transition to a universal semantic layer with Cube Cloud is an exciting opportunity to unify, govern, optimize, and integrate your data with any endpoint. But the success of this transformation is contingent not just on the technology itself, but on how well your organization manages the change process. 

Remember, the technology may be the easy part—managing the change across the organization is where the real work lies. Tackle the challenge of change management, and you’ll unlock the full potential of a universal semantic layer, setting your organization up for data success today and as your stack evolves. Contact sales to learn more about how Cube Cloud can unify data across AI, BI, spreadsheets, and embedded analytics.


Why Change Management is the Key to Success with a Universal Semantic Layer

One frequently asked question our Sales Engineers at Cube encounter is: **"Why do we need a semantic layer if we already have a great data model built in dbt core or another tool?"** While a solid data model is fundamental, it alone isn't sufficient for optimal data access and utilization. There are several reasons [why adding a semantic layer](https://cube.dev/use-cases/semantic-layer) becomes crucial.

## Explicit metrics, not implied
Firstly, let's discuss consistency and governance. A [data model organizes and simplifies data](https://cube.dev/product/data-modeling), making it easier to manage and query. However, it still demands that data analysts write SQL queries to access the data. This process, while effective, is susceptible to errors. Even skilled analysts can make mistakes, leading to inconsistencies and incorrect data retrieval. In a complex data ecosystem, it's easy to miss nuances in how metrics are calculated, which can lead to significant discrepancies. A semantic layer mitigates these issues by making the definitions of metrics explicit rather than implied. For instance, without a semantic layer, an analyst might mistakenly count order lines instead of considering the quantity field, producing inaccurate results that could pass a superficial quality check. Semantic layers encode these rules, ensuring that every data query follows the same logic and metrics definitions, eliminating the need for manual checks and reducing errors.

## Abstraction reduces complexity
Beyond just consistency, the semantic layer offers a more intuitive interface for data retrieval. While data models require SQL proficiency to query data, [semantic layers abstract this complexity away](https://cube.dev/blog/why-api-based-data-access-is-essential-for-modern-data-management). This is particularly beneficial for engineers who might not be adept at writing SQL but are familiar with other interfaces like REST APIs. By compiling simple requests into complex, consistent SQL, semantic layers allow users to interact with data systems without needing in-depth SQL knowledge. For instance, instead of writing detailed SQL queries, a user can simply make a request for "order lines by month" or "order quantity by customer," and the semantic layer handles the intricacies. This abstraction not only speeds up development but also reduces the risk of bugs in applications that rely on data retrieval.

## Complex queries in plain language
AI preparedness is another compelling reason to adopt a semantic layer. These layers provide the context benefits of a knowledge graph and add the constraints and compiler needed to execute queries. This makes it easier for AI systems to interact with and analyze data accurately. The [semantic layer allows complex queries to be expressed in plain language](https://cube.dev/use-cases/llm-and-ai-semantic-layer), making it easier for AI to understand and process them. For example, SQL queries could be simplified to something like to "order lines by month" and "order quantity by customer." The semantic layer handles the complex SQL transformation, making the data easier to access and analyze for AI agents. This not only improves AI's accuracy but also makes the entire system more user-friendly.

## Clearly defined user access
Security is another critical aspect where semantic layers shine. Because these layers are well-defined and minimal, they make it easier to [enforce security policies](https://cube.dev/product/semantic-layer-security). The clear and obvious entities within a semantic layer provide a perfect checkpoint for security measures, ensuring that only authorized users can access specific data. This is much harder to achieve with raw SQL queries where securing each query individually is cumbersome and error-prone.

## Optimized cost and latency
Performance and cost efficiencies are significant benefits of adopting semantic layers. The consistent SQL generated by a semantic layer increases the likelihood of hitting the data warehouse cache, reducing both the cost and latency of data queries. Humans writing SQL can easily introduce slight variations in their queries for the same goal, decreasing cache hit probabilities. Moreover, [semantic layers can enable sophisticated caching](https://cube.dev/product/caching-and-data-performance) mechanisms, including pre-aggregation of known query patterns. These caches are more functional than typical data warehouse caches, providing higher cache hit ratios and even partial hits where only very recent data needs to be fetched. This is especially crucial for companies offering data as a product to their customers, where optimizing cost and latency is essential. Cloud data warehouse vendors, whose revenue is often tied to compute usage, have little incentive to invest in advanced caching. Hence, the semantic layer fills this gap, ensuring that your investment in a data model yields the desired performance and cost benefits.

In conclusion, while good data modeling is fundamental, stopping there would mean missing out on substantial benefits. The semantic layer offers enhanced consistency, a more intuitive interface, improved AI readiness, better security, and significant performance and cost advantages. These benefits make the semantic layer an indispensable component for any data-driven organization looking to maximize the utility and efficiency of their data assets.


David Jayatillake 

Data Modeling

Where should a semantic layer be built?

The way organizations access and manage their data can significantly impact their efficiency, security, and overall success. Traditionally, many organizations have relied on direct database connections to interact with their data warehouses. 

As data environments become more complex and security needs grow, API-based data access to cloud data warehouses is emerging as a superior alternative. This blog explores why transitioning from direct database access to API-based access is not just beneficial but necessary for modern data management and how you can move to API-based access with Cube Cloud.

## What’s wrong with direct database connections?

While direct database connections have been the norm for years, they come with several drawbacks:

- Connecting directly to a database requires knowledge of the database schema, relationships between tables, and calculations, increasing the risk of inconsistencies in business logic.
- Duplication of direct database connections across analytics tools becomes extremely likely and highly unmanageable when changes are needed.
- Every direct connection reduces flexibility when the time comes to replatform the data or analytics technology, increasing the effort needed to migrate.
- Direct connections can expose databases to unauthorized access and potential breaches, especially if access controls are not consistently managed. 
- Handling high volumes of concurrent queries and data requests can strain the database, leading to performance bottlenecks. 

![](https://ucarecdn.com/c9063aa6-04c2-4599-ab7d-778a581ab509/-/quality/smart/-/format/auto/)

## Why is API-based data access better?

API-based access provides a more secure, scalable, and user-friendly approach to data management. APIs offer robust authentication and authorization mechanisms, such as OAuth and API keys, providing an additional layer of security. Fine-grained data access controls can be enforced, ensuring that users and applications have only the necessary permissions, reducing the risk of unauthorized access. 

APIs abstract the complexities of the underlying data structure, offering a simplified and consistent interface for developers, data analysts, and business users. This abstraction reduces the need for deep database knowledge, allowing developers to focus on building applications and data analysts and business users to focus on insights, rather than managing data access intricacies. In addition, APIs facilitate easier integration with various platforms and services, promoting interoperability within the data ecosystem.

APIs are designed to handle high concurrency and large volumes of requests efficiently. They can optimize queries and data retrieval processes, ensuring better performance compared to direct database queries. Techniques such as caching further enhance performance and user experience. API usage can be monitored and logged, providing valuable insights into data access patterns, performance metrics, and potential security issues. This monitoring enables better governance and management of data access, allowing organizations to identify and address problems proactively.

APIs can ensure consistency and reusability across different applications and use cases. This reduces the risk of errors and inconsistencies that might arise from direct access and ad-hoc queries. APIs can help manage and control costs by limiting the number of queries, reducing data transfer volumes, and optimizing resource usage of cloud data sources. Better visibility and management of data access costs are particularly important in cloud environments where data egress and query execution can incur significant charges.

![](https://ucarecdn.com/d083b993-64ff-400f-930e-6e2458a82d4f/-/quality/smart/-/format/auto/)

## Connect to data with Cube Cloud’s Data APIs
Cube Cloud provides extensive data API coverage for a variety of use cases, whether it’s AI, BI, spreadsheets, or embedded apps. [Data APIs](https://cube.dev/product/data-apis) are used to run queries against the data models you define. Despite using various transports and query formats, all data APIs share common [querying concepts](https://cube.dev/docs/product/apis-integrations/queries).

- __[SQL API](https://cube.dev/docs/product/apis-integrations/sql-api)__ - Delivers data over the [Postgres-compatible protocol](https://www.postgresql.org/docs/current/protocol.html) to BI tools. 
- __[REST API](https://cube.dev/docs/product/apis-integrations/rest-api)__ - Delivers data over the HTTP protocol to embedded analytics applications. 
- __[GraphQL API](https://cube.dev/docs/product/apis-integrations/graphql-api)__ - Delivers data over the HTTP protocol to [GraphQL](https://graphql.org/)-enabled data applications. 
- __[MDX API](https://cube.dev/docs/product/apis-integrations/mdx-api)__ - Provides a native interface for Microsoft Excel connections via the XMLA standard.  
- __[AI API](https://cube.dev/docs/product/apis-integrations/ai-api)__ - Provides a standard interface for interacting with large language models (LLMs) as a turnkey solution for text-to-semantic layer queries.

![](https://ucarecdn.com/196989ec-b515-4be1-bbfd-96945c04ffd4/-/quality/smart/-/format/auto/)

## How to Transition to API-based Data Access with Cube Cloud

Transitioning from direct database access to API-based access is a strategic move that requires careful planning, execution, and change management. Use the step-by-step guide for data leaders to manage this transition effectively:

1. __Assess the Current State__ - Conduct a thorough assessment of the existing data architecture and connections. Identify the systems and applications that rely on direct database access. Understand the specific requirements and use cases for Cube Cloud as the new integration point.
2. __Define Clear Objectives__ - Establish clear objectives for the transition, such as delivering consistent data definitions, centralizing data governance, and enhancing scalability. Align these objectives with the organization’s broader strategic goals.
3. __Engage Stakeholders__ - Involve key stakeholders early in the process, including developers, database administrators, data analysts, business users, and security teams. Communicate the benefits of moving to Cube Cloud API-based access and address any concerns or resistance to change.
4. __Plan the Transition__ - Develop a comprehensive transition plan with timelines, milestones, and resource allocation. Consider a phased approach, starting with less critical systems to minimize risk.
5. __Create Data Models__ - Unify fragmented business definitions with Cube Cloud’s code-first approach to [data modeling](https://cube.dev/docs/product/data-modeling/overview). Build data models for ease of use and understandability by data analysts, business users, and AI.
6. __Implement Data Access Controls__ - Centralize and enforce fine-grained [data access controls](https://cube.dev/docs/product/auth) for governance and security. Use logging for monitoring and auditing.
7. __Design for Performance and Scalability__ - Design for high performance and scale, using optimization techniques, such as [pre-aggregation caching](https://cube.dev/docs/product/caching), to improve performance and save on cloud data usage costs.
8. __Provide Documentation and Training__ - Develop comprehensive documentation for connecting with Cube Cloud, including usage guides, examples, and best practices. Offer training sessions to help developers and users transition smoothly to API-based access.
9. __Monitor and Optimize__ - Implement monitoring and analytics with real-time diagnostics to track Cube Cloud usage, performance, and potential issues. Continuously optimize data models based on feedback and usage patterns to improve efficiency and user experience.
10. __Ensure Compliance__ - Make sure your implementation complies with relevant data protection regulations and industry standards. Regularly review and update security and compliance measures to address evolving threats and requirements.

## Make the Move to Cube Cloud

Transitioning from direct database access to API-based access is a key step for organizations looking to modernize their data management practices. Cube Cloud’s data APIs offer numerous benefits, including enhanced security, simplified development, improved performance, and better cost management. 

By carefully planning and executing this transition, data leaders can unlock the full potential of their data, driving greater efficiency, security, and innovation within their organizations. [Contact sales](https://cube.dev/contact) to learn more about how Cube Cloud can be used to unify, govern, optimize, and integrate every data experience with API-based data access. 

APIs

Why API-Based Data Access is Essential for Modern Data Management

The ability to access, understand, and utilize data efficiently is critical for every organization. Within companies of every size, data leaders are acutely aware of the challenges posed by having multiple semantic layers and business logic spread across cloud data warehouses, transformations, and applications.

The complexity, inconsistency, and inefficiency of such a fragmented approach can significantly impede your organization’s success with data. What if you could have one place to capture all of your business logic and connect to it from anywhere? The good news is that you can do this with a universal semantic layer. 

In this blog, we’ll explore why implementing a universal semantic layer is transformational for how data is managed and consumed, helping you finally achieve the single source of truth for everyone.

## Establish Consistent Data Definitions, Metrics, and Governance
The primary pain point with business logic fragmentation is the lack of consistency. Yes, different teams already have access to data in their preferred analytics tool, but they might define the same metric in various ways or make incorrect joins between tables, leading to conflicting reports and business decisions. The universal semantic layer addresses this by providing a single source of truth for all your data definitions and metrics. 

- __Abstraction:__ Eliminate data complexity and the need to understand how to define relationships, dimensions, measures, and calculations.
- __Consistency:__ With a universal semantic layer, your organization can ensure that everyone is on the same page, using the same definitions and metrics.
- __Governance:__ Policies and standards can be enforced regardless of data consumer, ensuring data security, compliance, and integrity.

By centralizing data definitions and governance in Cube Cloud’s universal semantic layer, you eliminate discrepancies, reduce errors, and enhance trust in your data. For data leaders, this means more accurate and reliable analytics, streamlined regulatory compliance, and a solid foundation for data-driven strategic planning.

![](https://ucarecdn.com/84f259ee-4269-4670-a6ab-58d033ef0889/-/quality/smart/-/format/auto/)
*Once data is modeled in Cube Cloud, data engineers can explore a visual representation of cubes, views, and relationships with Data Graph.*

### How to Establish Consistency
- __Assess Current Data Landscape:__ Conduct a thorough audit of existing data definitions, metrics, and governance policies across applications.
- __Define Standardized Metrics:__ Collaborate with key stakeholders to agree on standardized data definitions and metrics.
- __Apply Data Access Controls:__ Govern data access in Cube Cloud with roles, policies, and procedures, to enforce consistency and compliance.
- __Train Users:__ Provide comprehensive training to ensure all users understand the new data definitions and governance policies.

## Reuse Modeled Data Across AI, BI, Spreadsheets, and Apps
Every data and analytics platform has some type of semantic layer that is tightly coupled with its presentation layer. Disparate semantic layers, such as data models built in Tableau and in PowerBI, trap data within specific platforms, making it difficult to connect to and reuse the same data across various applications. The universal semantic layer frees your data from single-use applications, allowing it to be reused across all AI, BI, spreadsheets, and custom-built data apps.

- __Versatility:__ Modeled data can be defined once and delivered across multiple use cases with APIs, instead of direct database queries.
- __Efficiency:__ Save time and resources by eliminating the need to recreate models for different tools.
- __Innovation:__ Empower data teams to innovate with consistent, high-quality data that can be easily integrated into any endpoint.

This level of data reuse ensures that your organization can maximize the value extracted from your data investments with Cube Cloud’s universal semantic layer. For data leaders, this translates into faster time-to-insight, more innovative applications, and the ability to drive more comprehensive and strategic analytics initiatives across the organization.

![](https://ucarecdn.com/a5262e32-8d3d-4a20-b557-ef047227812d/-/quality/smart/-/format/auto/)
*Data analysts and business users can search a unified view of connected data assets and view lineage with Semantic Catalog instead of creating a duplicate model scratch.*

### How to Reuse Modeled Data
- __Inventory Data Models:__ Evaluate existing data models, and identify redundancies and gaps among data consumers.
- __Standardize Data Models:__ Develop a set of standardized data models that can be reused across various applications.
- __Integrate with Cube Cloud:__ Migrate existing data models into Cube Cloud’s universal semantic layer and connect via data APIs.
- __Monitor and Iterate:__ Continuously monitor data model usage and iterate based on data analyst and business user feedback.

## Achieve Lightning-Fast Query Performance and Cloud Cost Savings
High-speed performance is key when it comes to data and analytics. Slow queries not only frustrate users but also hinder timely decision-making. The universal semantic layer is designed to optimize query performance, delivering lightning-fast results while also driving down cloud costs.

- __Performance:__ By centralizing and optimizing your semantic layer, you benefit from enhanced query performance across all data consumers.
- __Cost Savings:__ Reduced data duplication and efficient query execution translate into significant cloud cost savings.
- __Scalability:__ Handle large-scale data queries with ease, ensuring your analytics can grow with your business needs.

With Cube Cloud, you can achieve the dual benefits of speed and cost efficiency, ensuring your data and analytics infrastructure is both powerful and economical. For data leaders, this means being able to deliver timely and actionable insights to the business, optimize resource allocation, and support a scalable and sustainable data architecture.

![](https://ucarecdn.com/9b3a5d28-7626-4096-a3f8-b591c8730412/-/quality/smart/-/format/auto/)
*Cube Cloud allows data engineers to define pre-aggregations to achieve faster, more cost-efficient results with caching in Cube Store.*

### How to Optimize for Speed and Savings
- __Evaluate Current Performance:__ Assess the performance of existing query executions and identify bottlenecks.
- __Consolidate Data Sources:__ Streamline data sources to reduce duplication and redundancy.
- __Optimize Queries:__ Reconfigure queries to leverage Cube Cloud’s pre-aggregation caching using Rollup Designer.
- __Implement Cost Savings Practices:__ Monitor direct cloud data warehouse usage and use Cube Store to deliver results from the cache.

## Simplify Data Access and Exploration for Everyone
Data democratization is a key goal for modern companies. However, multiple semantic layers often act as barriers, making data discovery and exploration complex and confusing to data analysts and business users. The universal semantic layer simplifies data access for all users, regardless of their technical expertise, as well as providing context to generative AI.

- __Accessibility:__ Intuitive natural language interfaces and centralized data make it easier for all users to find and use the data they need.
- __Exploration:__ Empower users to explore data freely, fostering a culture of data-driven decision-making.
- __Collaboration:__ Facilitate better collaboration across teams by providing a unified view of data in a shared workspace.

By making data more accessible, Cube Cloud’s universal semantic layer ensures that your entire organization can find and leverage data insights with generative AI and natural language, driving better business outcomes. For data leaders, this means fostering a data-centric culture, improving cross-functional collaboration, and enabling more informed and strategic decision-making across all levels of the organization.

![](https://ucarecdn.com/83424229-4ac8-4f3c-8c4d-54798663c148/-/quality/smart/-/format/auto/)
*Cube Cloud provides a tailored interface for data analysts and business users to interact with data in natural language and search connected data assets.*

### How to Simplify Data Exploration and Access
- __Assess User Needs:__ Conduct a survey to understand the data access and exploration needs of different user groups.
- __Enable Access to a Tailored Interface:__ Grant access to Cube Cloud’s AI Assistant, Semantic Catalog, and Playground for data analysts and business users.
- __Provide Training and Support:__ Offer training sessions and resources to help users navigate and utilize Cube Cloud effectively.
- __Encourage Collaboration:__ Foster a collaborative environment where users can share insights and best practices.

## Conclusion
Cube Cloud’s universal semantic layer offers a compelling solution to the challenges posed by multiple semantic layers and fragmented business logic spread across cloud data warehouses, transformations, AI, BI, spreadsheets, and apps. By establishing consistent data definitions, enabling data reuse, enhancing performance, and simplifying access, Cube Cloud empowers your organization to unlock the full potential of its data. 

It is easy to start and scale Cube Cloud, moving beyond fragmented approaches to a unified, governed, optimized, and integrated single source of truth. For data leaders, this transition means gaining a competitive edge through better data management, ensuring strategic alignment, and driving the organization towards a more innovative and data-driven future. 

Are you ready to transform your modern data stack? [Contact sales](https://cube.dev/contact) to learn more about the future of data management with Cube Cloud’s universal semantic layer today.

Transform How Data is Managed and Consumed with a Universal Semantic Layer

Choosing the right infrastructure option is critical to align your business needs, scalability goals, and security and compliance requirements. Cube Cloud is designed to fit, not dictate, your modern data stack architecture and technology choices. The flexibility in infrastructure options allows organizations to tailor their infrastructure to meet specific technical and operational requirements, while optimizing costs. 

In this blog, we’ll explore Cube Cloud’s infrastructure options: shared infrastructure, dedicated infrastructure, dedicated infrastructure with customer-supplied pre-aggregation storage (CSPS), and bring your own cloud (BYOC) to help you choose the right configuration.

## Shared Infrastructure
With shared infrastructure, Cube provides a fully-managed solution, taking care of everything—hosting, security, scaling, maintenance, and updates. As the most popular infrastructure option, it is ideal for organizations who want to minimize IT overhead and focus on their core competencies. 

Shared infrastructure is a cost-effective starting point if you do not have to immediately address security and compliance requirements that are available in dedicated infrastructure. In this scenario, everything is deployed on the Cube Cloud infrastructure in one of our shared VPCs. Cube takes care of creating, scaling, and monitoring your deployments, as well as managing Cube Store and persisting pre-aggregated data. This option requires the least effort to set up and is available on all [pricing plans](https://cube.dev/pricing).

![](https://ucarecdn.com/1f7e7205-2a7e-4dd4-ba6a-3d27b4dfb1c4/-/quality/smart/-/format/auto/)

Choose shared infrastructure if you are seeking: 
- __Ease of Use:__ With everything managed by Cube, organizations can quickly get up and running without worrying about infrastructure configuration and maintenance.
- __Lower Cost:__ Control costs with the simplicity of a fully-managed SaaS configuration.
- __Scalability:__ Cube handles scaling, ensuring performance as user demand increases.

Shared infrastructure may not be for you if you are concerned about:
- __Less Control:__ Enterprise features, such as VPC peering or PrivateLink and external monitoring integrations are not available on the shared infrastructure.
- __Resource Contention:__ May occur from when the majority of platform resources are in use, also known as the “noisy neighbor” problem.

## Dedicated Infrastructure
Dedicated infrastructure is a great fit for most typical enterprise use cases. With dedicated infrastructure, Cube Cloud is hosted on a dedicated application cluster allocated exclusively to one customer within Cube’s account. 

The option offers isolation of managed services. Providing isolated resources, this ensures better performance and additional layers of security. It is available on the [Enterprise](https://cube.dev/pricing) plan and above.

![](https://ucarecdn.com/33fcfd83-5ebd-4b04-bdc1-d2193bdc2ca6/-/quality/smart/-/format/auto/)

Choose dedicated infrastructure if you are seeking:
- __Enhanced Performance:__ Dedicated resources mean more consistent performance without the variability of shared environments.
- __Increased Security:__ Dedicated environments can be more secure, meeting compliance and regulatory standards.
- __Enterprise Capabilities:__ Increased SLAs, more features, and more data source connectivity to meet your unique requirements are included in the Enterprise tiers. 

Dedicated infrastructure may not be for you if you are concerned about:
- __Higher Costs:__ Dedicated resources come at a premium compared to shared environments.
- __Data Residency:__ Having the ability to control where data is stored and processed.

## Dedicated Infrastructure with Customer-Supplied Pre-aggregation Storage (CSPS)
Dedicated infrastructure with CSPS combines elements of both Cube-managed and customer-managed resources. Cube manages the application, while the storage components are managed by the customer. 

Cube Store is the only component of Cube that is stateful, and it requires an object store to persist pre-aggregated data. In the previous scenarios, storage is provided and managed by Cube Cloud, but with CSPS you get an option to move all the data at-rest to your own bucket for greater security and better compliance. 

On the [Enterprise Premier](https://cube.dev/pricing) plan, organizations can move all data at rest to the customer’s infrastructure. In this scenario, all Cube components reside on the Cube Cloud side. Cube Store uses a customer-provided object store, on the cloud provider of your choice, for reading and persisting pre-aggregated data. This provides additional peace of mind when processing highly critical business or personal information.

![](https://ucarecdn.com/ac333673-8ef8-4b1f-ba48-b40e8b7396d0/-/quality/smart/-/format/auto/)

Choose dedicated infrastructure with CSPS if you are seeking:
- __Flexibility:__ Allows organizations to leverage Cube’s expertise for application management while the customer maintains control over sensitive data.
- __Optimized Operations:__ Potential for operational cost savings by choosing to manage only data at rest within a customer-provided object store, compared to BYOC.
- __Greater Security & Compliance:__ Ensure data residency, security, and compliance for data at rest, according to your requirements.

Dedicated infrastructure with CSPS may not be for you if you are concerned about:
- __More Complex Integration:__ Integrating and managing a hybrid environment can be more complex, requiring coordination between the Cube and the customer.
- __Maintenance Overhead:__ While less than a fully self-managed environment, hybrid models still demand some level of IT involvement from the customer.

## Bring Your Own Cloud (BYOC)
BYOC allows organizations to host Cube Cloud components on their own cloud infrastructure, either on AWS, Azure, or GCP. With BYOC, all the components interacting with private data are deployed on the customer infrastructure. The components are managed by the Cube Cloud Control Plane via the Cube Cloud Operator, which is on Cube’s infrastructure. It is available on the [Enterprise Premier](https://cube.dev/pricing) plan only.

![](https://ucarecdn.com/6a8c4629-ad70-470c-a3fe-3affc8734abe/-/quality/smart/-/format/auto/)

Choose BYOC if you are seeking:
- __Unmatched Security and Compliance:__ BYOC environments are the most secure option, meeting stringent compliance and regulatory standards, compared to previous scenarios.
- __Flexibility:__ Allows for all the components interacting with private data to be deployed on the customer infrastructure.
- __Consolidated Cloud Cost:__ Potential to optimize costs based on volume of your existing cloud agreements or usage patterns.

BYOC may not be for you if you are concerned about:
- __Technical Expertise Required:__ Significant in-house expertise needed to operate the infrastructure.
- __Increased Responsibilities:__ Customers must maintain the underlying kubernetes clusters and other infrastructure components, which can be resource-intensive.

## Conclusion
Selecting the right infrastructure option depends on a thorough understanding of your business requirements, IT capabilities, and long-term goals. Fully-managed, shared infrastructure is perfect for those seeking simplicity and reliability, while dedicated infrastructure suits businesses needing more predictable performance and security. Dedicated infrastructure with CSPS combines elements of both Cube-managed and customer-managed resources with data at rest on the customer’s infrastructure. BYOC provides unmatched security and compliance for technically proficient teams. 

Carefully evaluating these options will ensure your Cube Cloud deployment aligns with your strategic and operational objectives, delivering on your security and compliance requirements. Contact sales to explore Cube Cloud which infrastructure option is right for your unique requirements.

All Things Cube

Exploring Cube Cloud Infrastructure Options to Fit Your Requirements

## Introduction
Often at the beginning of an analytical application's lifecycle–when there is a smaller dataset that queries execute over–the application works well and delivers responses within acceptable thresholds. As the size of the dataset and number of users grow, the time-to-response from a user's perspective can often suffer quite heavily. This is true of both application and purpose-built cloud data warehousing solutions.

This leaves us with a chicken-and-egg problem. Application databases can deliver low-latency responses with small-to-large datasets, but struggle with massive analytical datasets. Cloud data warehousing solutions usually make no guarantees except to deliver a response, which means latency can vary wildly on a query-to-query basis.

Cube Cloud optimizes query performance from redundant queries with in-memory caching, queue management, and configurable pre-aggregation caching in Cube Store. In this blog, we’ll provide an overview of Cube Cloud’s caching capabilities and load testing to compare Cube Store to a cloud data warehouse.

## Caching in Cube Cloud
Cube Cloud provides a two-level caching system. The first level is an [in-memory cache](https://cube.dev/docs/product/caching#in-memory-cache "in-memory cache") and is active by default. Cube Cloud's in-memory cache acts as a buffer for your database when there's a burst of requests hitting the same data from multiple concurrent users. The second level of caching is called [pre-aggregations](https://cube.dev/docs/product/caching#pre-aggregations "pre-aggregations"), and requires explicit configuration to activate. 

Cube Cloud caches the results of executed queries using in-memory cache. The cache key is a generated SQL statement with any existing query-dependent pre-aggregations. Upon receiving an incoming request, Cube Cloud first checks the cache using this key. If nothing is found in the cache, the query is executed in the database and the result set is returned as well as updating the cache.

If an existing value is present in the cache and the ```refresh_key``` value for the query hasn't changed, the cached value will be returned. Otherwise, the SQL query will be executed against either the pre-aggregations storage or the source database to populate the cache with the results and return them.

Pre-aggregations are defined in the data model and stored in a layer of the aggregated data built and refreshed by Cube Cloud. You can learn more about defining pre-aggregations in [data modeling reference](https://cube.dev/docs/reference/data-model/pre-aggregations "data modeling reference"). It can dramatically improve the query performance and provide a higher concurrency. Pre-aggregations are designed to provide the right balance between time to insight and querying performance.

![](https://ucarecdn.com/4151b6b0-2136-488e-9bfb-3403a0ce5f60/)

## Load Testing
### Objective
The following tests measure query performance and peak Requests Per Second (RPS) across distinct scenarios.

### Hypothesis 
Cube Store can deliver better performance than a cloud data warehouse in a real-world analytics use case.

### Methodology
Load tests were structured into three scenarios using a similar size/cost configuration and <a href="https://www.tpc.org/tpch/" target="_blank" rel="noopener">TPC-H</a> with a scaling factor of 100. The testing framework parameters operate in terms of virtual users (VU): 1 VU is 1 concurrent request. The test duration was set to 10 seconds. The parameters of the test were done purposefully so that both components could achieve a 100% success rate, instead of overwhelming the components to failure. 

Each load test is designed to evaluate the platforms' performance under different conditions:
1. __Run Case 1:__ This scenario involved running identical queries against both Cube Store and the cloud data warehouse to gauge baseline performance metrics.
2. __Run Case 2:__ Here, we introduced a measure, a dimension, and a random filter into the queries, simulating a more realistic analytical workload.
3. __Run Case 3:__ This scenario added complexity by incorporating random dates and granularity alongside a measure and a dimension, reflecting common real-world analytics use cases.

| Cube Cloud Configuration    | Cloud Data Warehouse Configuration     |
| ---------- | ---------- |
|  Tier: Enterprise with Dedicated Infrastructure. | Cluster size: Medium 
API instances: 10 (min and max range set to 10).
Cube Store workers: 8 | 

__NOTE:__ When conducting small and quick tests on query speeds and then trying to extrapolate those results to real-world use cases, the methodology usually fails to take into account query variability and the multiple layers of caching a modern data stack has. It is important to test with your data and workloads. Your results may vary.

## Results
### Run Case 1
In this scenario, where identical queries were executed on both Cube Store and the cloud data warehouse, we observed the following results:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 766       | 691       |
| Peak RPS       | 46.67       | 46       |
| p(95) ms       | 253       | 303       |

![](https://ucarecdn.com/02ca1190-d182-4493-8e1c-5d08211d2867/)

![](https://ucarecdn.com/78da4f06-36b4-44b7-b2e3-ad8ee8f35932/)

The first run case is similar to a very rigid and unrealistic test. In both tests, we are just measuring the performance of Cube Store and the cloud data warehouse first caching layers. Both perform similarly and even in a real-world use case where the same query is executed multiple times by different actors, the real performance would be affected not by the query itself but by the size of the payload the caching layer is serving.

### Run Case 2
Introducing a measure, a dimension, and a random filter provided deeper insights into the platforms' performance under a more realistic workload:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 814       | 731       |
| Peak RPS       | 47       | 46.67       |
| p(95) ms       | 226       | 290       |

![](https://ucarecdn.com/ae58c0f9-b5d9-4782-b3ee-061d38fb41eb/)

![](https://ucarecdn.com/1205e2f0-8c85-487d-bcf4-6864b8323a13/)

The second run case tests a scenario where every query is different from each other, but the possible set of queries being executed is small. This means that the first few queries will be slow, but as the cloud data warehouse caching layers are warmed up, the subsequent queries are as fast as possible. Cube Cloud’s caching layer is already warmed up since it’s serving queries from pre-aggregations; that’s the reason it can achieve maximum performance since the beginning of the test.

### Run Case 3
The inclusion of random dates and granularity alongside other query elements further tested the platforms' capabilities:

| 10 VUs, 20 s     | Cube Store     | Cloud Data Warehouse     |
| ---------- | ---------- | ---------- |
| Success Rate       | 100       | 100       |
| Total reqs       | 880       | 85       |
| Peak RPS       | 46.67       | 5.67       |
| p(95) ms       | 250       | 4201       |

![](https://ucarecdn.com/67ea8fd3-198b-48de-ae0a-2206241f0359/)

![](https://ucarecdn.com/917fdb7d-5cdd-4a89-a94e-6dee649566f7/)

The third run case is a more realistic scenario. All queries are different, so all queries get executed by the respective query engine. In this scenario, Cube Store's speed is more noticeable, and for the cloud data warehouse to achieve the same performance, it would require using a much bigger cluster size.

## Conclusion
Cube Cloud’s caching capabilities prove to be a powerful solution for improving query performance. With its in-memory caching, queue management, and configurable pre-aggregation caching in Cube Store, Cube Cloud addresses the latency issues that often arise as dataset and user numbers grow. This is particularly beneficial since application databases tend to struggle with massive analytical datasets, and cloud data warehousing solutions can be inconsistent in their response times. Load testing Cube Cloud’s caching capabilities has demonstrated its effectiveness compared to cloud data warehouse solutions.

Nurzhan Ospanov

Comparative Analysis of Load Tests on Cube Store and Cloud Data Warehouse

Working with Databricks and Cube is a great way to ensure your data engineering team can build and maintain a [unified semantic layer](https://cube.dev/use-cases/semantic-layer) for your organization. At a higher level, it is a better means to make cloud data accessible and consistent to every data consumer in and outside of your company. 

By combining Databricks’ unified analytics and data store with Cube’s data modeling, access control, caching, and APIs, it’s now possible to deliver consistent metrics securely to business intelligence tools, embedded analytics use cases, and AI agents.

## Why a Semantic Layer?

A semantic layer functions as a middleware component between all of your data sources, including cloud-based data lakehouses, such as Databricks, and downstream applications. Its primary role is to abstract complicated physical data models and present them in a more user-friendly manner with improved field labeling, field descriptions, and dynamically calculated metrics. It serves as a contextual bridge between the data sources and the diverse analytical tools businesses employ for data manipulation, slicing, dicing, and analysis.

![](https://ucarecdn.com/5bcadadb-60ee-4698-bc6e-1f1e2ad8aa71/)

## Why Cube and Databricks?

**Unify your data stack**. Databricks established itself as a high-performance Lakehouse for a **modern data stack**, unifying all data types. Combined with Cube, you will have consistently modeled metrics and performant queries for all your downstream data consumers. 

**Modern data stack** follows several foundational principles and architecture designs: 
* Using a Lakehouse like Databricks as a central data platform
* ELT vs. [ETL](https://www.databricks.com/glossary/extract-transform-load)
* Applying software engineering best practices to data management 

The Cube platform embodies software engineering best practices, empowering data engineers to [handle data models through code](https://cube.dev/docs/product/getting-started/databricks/create-data-model). This capability unlocks the potential to harness version control systems for secure data model evolution management, fostering collaborative efforts and streamlined code reviews. Additionally, it facilitates the integration of a semantic layer into the CI/CD pipeline and the creation of distinct development, staging, and production environments.

**Harness the capabilities and scalability offered by Databricks with Cube.** Cube seamlessly integrates with Databricks, enabling direct queries without the need for data extraction. This ensures that your data remains within the Databricks environment. Cube optimizes performance by leveraging the computing power of your Databricks instance, fully maximizing the scalability of your cloud data lakehouse. Additionally, you have the option to create [pre-aggregations](https://cube.dev/docs/product/caching/getting-started-pre-aggregations) within Cube, reducing the need for small, repetitive queries on your Databricks instance and allowing for background aggregate updates instead. Our aggregate-aware pre-aggregations are fundamentally different from common table or view materializing strategies often employed for performance reasons. With Cube pre-aggregations, users do not need to be aware of objects materialized for performance reasons; instead, they simply issue their queries, and Cube automatically chooses the optimal way to answer, from cache, from a pre-aggregate, or the source.

**Use your Databricks Lakehouse data in any application.** Cube supports your current and future use cases with first-class application programming interfaces (APIs). In addition to our SQL API, Cube also speaks REST and GraphQL, which are often preferred by application developers who are working with common front-end frameworks to develop embedded analytic applications. With Cube, you can take your investment in your Databricks ecosystem and build anything you can dream of.

## A step-by-step guide to building semantic layer with Cube and Databricks

In the below section, we’ll cover a quickstart step-by-step tutorial on how to start with Cube and Databricks. Remember: Cube Cloud is free to start (there is a sign-up button, up and to the right^). No credit card or calls with salespeople are required! :) 

Find a detailed getting started with [Cube Cloud and Databricks](https://cube.dev/docs/product/getting-started/databricks) guide in Cube documentation. 

### Create Databricks and Cube accounts
First, create Cube Cloud and Databricks accounts.

If you don’t have a dataset that you would like to use, you can follow [this guide](https://cube.dev/docs/product/getting-started/databricks/load-data) to create a test dataset in your Databricks instance.

### Connect Databricks to Cube

Please follow these steps to create your first deployment in Cube Cloud and connect it to your Databricks instance.

1. After you [sign in to your Cube Cloud account](https://cubecloud.dev/auth), click “Create Deployment”.

2. Give the deployment a name, select the cloud provider and region of your choice, and click “Next”.

3. Next, click Create to create a new project from scratch.

4. Select Databricks from the list of supported data sources and enter your Databricks credentials to connect to it. Get a [free Databricks account](https://www.databricks.com/try-databricks?utm=cube#account)

5. Once Databricks is connected, Cube can generate a basic data model from your data warehouse schema, which helps you get started with data modeling faster. We'll inspect these generated files in the next section and start changing them.

### Create your data model
Cube follows a dataset-oriented data modeling approach, which is inspired by and expands upon dimensional modeling. 

When building a [data mode](https://cube.dev/docs/product/getting-started/databricks/create-data-model) in Cube, you work with two dataset-centric objects: cubes and views. Cubes usually represent business entities such as customers, line items, and orders. In cubes, you define all the calculations within the measures and dimensions of these entities. 

To begin building your data model, click on “Enter Development Mode” in Cube Cloud. This will take you to your personal developer space.

Once you are in developer mode, navigate to the “Data Model” and click on any file located in the left sidebar to open it.

You can follow [this detailed guide](https://cube.dev/docs/product/getting-started/databricks/create-data-model) on how to make changes to your data model and build new measures and dimensions.

When you are ready to test updates to your data model, you can navigate to [Cube’s Playground](https://cube.dev/docs/product/workspace/playground). The Playground is a web-based tool that allows you to query your data without connecting any tools or writing any code.

### Query your data
You can query Cube using a BI or visualization tool through the Cube SQL API. For a better experience, we recommend mapping the BI's data model to Cube's semantic layer. This can be done automatically with Semantic Layer Sync or manually.

**Semantic Layer Sync**
[Semantic Layer Sync](https://cube.dev/docs/product/getting-started/databricks/query-from-bi) will synchronize all public cubes and views with connected BI tools.
1. Create a new sync by navigating to the “Semantic Layer Sync” tab on the “BI Integrations” page and clicking “+ Create Sync”. 
2. Follow the steps in the wizard to create a sync
3. Replace the fields for user, password, and URL with your BI tool credentials, then click on “Save All”. You can now go to the “BI Integrations” page and trigger the synchronization of your newly created semantic layer.
4. After running the sync, navigate to your BI tool instance. You should see the orders_view dataset that was created in the tool. Cube automatically maps all metrics and dimensions in the BI tool to measures and dimensions in the Cube data model.

**Manual setup**
Alternatively, you can connect to Cube and create all the mappings manually. 
1. You can find the credentials to connect to Cube on the “BI Integrations” page under the “SQL API Connection” tab.
2. After connecting, create a new dataset in the BI tool and select "orders_view" as a table.
3. Now you can map the BI tool metrics and columns to Cube's measures and dimensions.

That was a high level preview. You can create both you Databricks and Cube instances for free to try it out in your own environment.

Great work!

### More resources

Recorded workshop [Getting Started with Cube on Databricks](https://cube.dev/events/unlock-data-cube-snowflake)

Detail on [connecting to Databricks](https://cube.dev/docs/product/getting-started/databricks)


Artyom Keydunov

Getting Started with a Databricks Semantic Layer

## Utilize the power and scalability of Snowflake.

We’ve long seen that the data experiences landscape has been very diverse. From dozens of general-purpose BIs to hundreds of vertical purpose-built analytical solutions, data consumers have many, many options to visualize and consume data.

The evolution of front-end technologies made it possible to embed quality analytics experiences directly into many software products, further accelerating the proliferation of data products and experiences. 

Cube works on top of Snowflake with direct queries and doesn’t require you to run extracts hence your data always remains in Snowflake. 

## What is a Semantic Layer?

A semantic layer is a middleware between your cloud data warehouse, such as Snowflake, and downstream applications. It abstracts raw data and presents it in a meaningful way to the end-user and serves as a contextual filter between the data warehouse and the various analytical tools that businesses use to slice, dice, and analyze their data.

![](https://ucarecdn.com/8d945f29-e9eb-4e7f-9e9e-29ae7074e195/)

## Why Cube and Snowflake?

**Future-proof data stack**. Snowflake established itself as the center of gravity in a **modern data stack**, a new approach to data engineering that enables teams to pursue higher-value activities. 

**Modern data stack** follows several foundational principles and architecture designs: 
* Using a cloud data warehouse like Snowflake as a central data platform
* [ELT vs. ETL](https://www.snowflake.com/guides/etl-vs-elt)
* Applying software engineering best practices to data management 

Cube embraces software engineering best practices and allows data engineers to [manage data model in code](https://cube.dev/docs/product/getting-started/cloud/create-data-model). This enables leveraging the version control systems to manage data model evolution safely and facilitate collaboration and code reviews. It also makes it possible to have a semantic layer as a part of the CI/CD process and have isolated environments for development, staging, and production.

**Utilize the power and scalability of Snowflake**. Cube works on top of Snowflake with direct queries and doesn’t require you to run extracts hence your data always remains in Snowflake. Cube pushes compute down to your Snowflake instance, fully utilizing the power and scalability of your cloud data warehouse. You can optionally create [pre-aggregations](https://cube.dev/docs/product/caching/getting-started-pre-aggregations) in Cube and enable aggregate awareness to not query your Snowflake instance with small repeated queries but rather update aggregates in the background.

## A step-by-step guide to building semantic layer with Cube and Snowflake

In the below section, we’ll cover a quickstart step-by-step tutorial on how to start with Cube and Snowflake. Cube Cloud is free to start, and you can sign up anytime. No credit card or calls with salespeople are required!

You can find a detailed getting started guide in Cube documentation. 

### Create Snowflake and Cube accounts
First, create Cube Cloud and Snowflake accounts.

If you don’t have a dataset that you would like to use, you can follow [this guide](https://cube.dev/docs/product/getting-started/cloud/load-data) to create a test dataset in your Snowflake instance.

### Connect Snowflake to Cube

Please follow these steps to create your first deployment in Cube Cloud and connect it to your Snowflake instance.

1. After you [sign in to your Cube Cloud account](https://cubecloud.dev/auth), click “Create Deployment”.

2. Give the deployment a name, select the cloud provider and region of your choice, and click “Next”.

3. Next, click Create to create a new project from scratch.
4. Select Snowflake from the list of supported data sources and enter your Snowflake credentials to connect to it. 
5. Once Snowflake is connected, Cube can generate a basic data model from your data warehouse schema, which helps you get started with data modeling faster. We'll inspect these generated files in the next section and start changing them.

### Create your data model
Cube follows a dataset-oriented data modeling approach, which is inspired by and expands upon dimensional modeling. 

When building a [data mode](https://cube.dev/docs/product/getting-started/cloud/create-data-model) in Cube, you work with two dataset-centric objects: cubes and views. Cubes usually represent business entities such as customers, line items, and orders. In cubes, you define all the calculations within the measures and dimensions of these entities. 

To begin building your data model, click on “Enter Development Mode” in Cube Cloud. This will take you to your personal developer space.

Once you are in developer mode, navigate to the “Data Model” and click on any file located in the left sidebar to open it.

You can follow [this detailed guide](https://cube.dev/docs/product/getting-started/cloud/create-data-model) on how to make changes to your data model and build new measures and dimensions.

When you are ready to test updates to your data model, you can navigate to [Cube’s Playground](https://cube.dev/docs/product/workspace/playground). The Playground is a web-based tool that allows you to query your data without connecting any tools or writing any code.

### Query your data
You can query Cube using a BI or visualization tool through the Cube SQL API. For a better experience, we recommend mapping the BI's data model to Cube's semantic layer. This can be done automatically with Semantic Layer Sync or manually.

**Semantic Layer Sync**
[Semantic Layer Sync](https://cube.dev/docs/product/getting-started/cloud/query-from-bi#semantic-layer-sync) will synchronize all public cubes and views with connected BI tools.
1. Create a new sync by navigating to the “Semantic Layer Sync” tab on the “BI Integrations” page and clicking “+ Create Sync”. 
2. Follow the steps in the wizard to create a sync
3. Replace the fields for user, password, and URL with your BI tool credentials, then click on “Save All”. You can now go to the “BI Integrations” page and trigger the synchronization of your newly created semantic layer.
4. After running the sync, navigate to your BI tool instance. You should see the orders_view dataset that was created in the tool. Cube automatically maps all metrics and dimensions in the BI tool to measures and dimensions in the Cube data model.

**Manual setup**
Alternatively, you can connect to Cube and create all the mappings manually. 
1. You can find the credentials to connect to Cube on the “BI Integrations” page under the “SQL API Connection” tab.
2. After connecting, create a new dataset in the BI tool and select "orders_view" as a table.
3. Now you can map the BI tool metrics and columns to Cube's measures and dimensions.

That was a high level preview. You can create both you Snowflake and Cube instance for free to try it out in your own environment.

### ...and you are ready to go...

The combination of Cube and Snowflake help to untangle the complexities of data modeling and empowers organizations to streamline their analytics efforts. Through step-by-step guidance, the blog has illuminated the path to creating a powerful semantic layer, enabling users to harness the true potential of their data and drive insightful decision-making. 

### More resources

Recorded workshop [Getting Started with Cube on Snowflake](https://cube.dev/events/unlock-data-cube-snowflake)

Detail on [connecting to Snowflake](https://cube.dev/docs/product/configuration/data-sources/snowflake)


Getting Started with a Snowflake Semantic Layer 

## The AI-powered data experience

We’ve long seen that the data experiences landscape has been very diverse. From dozens of general-purpose BIs to hundreds of vertical purpose-built analytical solutions, data consumers have many, many options to visualize and consume data.

The evolution of front-end technologies made it possible to embed quality analytics experiences directly into many software products, further accelerating the proliferation of data products and experiences. 

And now, with the arrival of large language models, we are living through another step change in technology that will enable many new features and even in the advent of an entirely new class of products across multiple use cases and domains—_including_ data. 

LLMs are taking the data consumption layer to the next level with **AI-powered data experiences** ranging from chatbots answering questions about your business data to AI agents making actions based on the signals and anomalies in data. 

![](https://ucarecdn.com/5c8bd295-4317-4820-beea-532f8c9ba391/)

## Semantic layer gives context to LLMs

LLMs are indeed a step change, but inevitably, as with every technology, it comes with its own limitations. **[LLMs hallucinate](https://en.wikipedia.org/wiki/Hallucination_(artificial_intelligence)); the [garbage in, garbage out](https://en.wikipedia.org/wiki/Garbage_in,_garbage_out) problem has never been more of a problem.** Let’s think about it like this: when it’s hard for humans to comprehend inconsistent and disorganized data, LLM will simply compound on that confusion to produce wrong answers.  

We can’t feed LLM with database schema and expect it to generate the correct SQL. To operate correctly and execute trustworthy actions, __it needs to have enough context and semantics__ about the data it consumes; it must understand the metrics, dimensions, entities, and relational aspects of the data by which it's powered. Basically—LLM needs a semantic layer.

The semantic layer organizes data into meaningful business definitions and then allows for querying these definitions—rather than querying the database directly. 

The ‘querying’ application is equally important as that of ‘definitions’ because it enforces LLM to query data through the semantic layer, ensuring the correctness of the queries and returned data. With that, the semantic layer becomes a solution for the LLM hallucination problem.

![](https://ucarecdn.com/c79d8e5a-ad67-43b3-ac4f-b3d323c0fb65/)

Moreover, the combination of LLMs and semantic layers can enable a new generation of AI-powered data experiences. At Cube, we’ve already witnessed many organizations build custom in-house LLM-powered applications, and startups, like [Delphi](https://cube.dev/blog/conversational-interface-for-semantic-layer), build out-of-the-box solutions on top of Cube’s semantic layer ([demo here](https://cube.dev/events/unleash-the-power-of-ai)). 

On the edge of this developmental forefront, we see Cube being an integral part of the modern AI tech stack as it sits on top of data warehouses, providing context to AI agents and acting as an interface to query data.

![](https://ucarecdn.com/ea4d11af-b3f9-43b9-b02e-b8fa8056c4b1/)

Cube’s data model provides structure and definitions that are used as a context for LLM to understand data and generate correct queries. LLM doesn’t need to navigate complex joins and metrics calculations because Cube abstracts those and provides a simple interface that operates on the business-level terminology, instead of SQL table and column names. This simplification helps LLM to be less error-prone and avoid hallucinations. 

For example, an AI-based application would first read Cube’s meta API endpoint, downloading all the definitions of the semantic layer and storing them as embeddings in a vector database. Later, when a user sends a query, these embeddings would be used in the prompt to LLM to provide additional context. LLM would then respond with a generated query to Cube, and the application would execute it. This process can be chained and repeated multiple times to answer complicated questions or create summary reports. 

### Performance

In regards to response times—when working on complicated queries and tasks, the AI system may need to query the semantic layer multiple times, applying different filters. 

So, to ensure reasonable performance, these queries must be cached and not always pushed down to the underlying data warehouses. Cube provides a relational cache engine to build pre-aggregations on top of raw data and implements aggregate awareness to route queries to these aggregates when possible.

### Security

And, finally, security and access control should never be an afterthought when building AI-based applications. As we mentioned above, generating raw SQL and executing it in a data warehouse may lead to wrong results. 

However, AI poses an additional risk here: since it cannot be controlled and may generate arbitrary SQL, direct access between AI and raw data stores can also be a significant security vulnerability. Instead, generating SQL through the semantic layer can ensure granular access control policies are in place. 

### And more...

We have a lot of exciting integrations with the AI ecosystem in store and can’t wait to share them with you. Meanwhile, if you are working on an AI-powered application with Cube – [let’s talk](https://cube.dev/contact)! 


Artificial Intelligence

—and the antidote to AI Hallucinations.

Semantic Layer: The Backbone of AI-powered Data Experiences

Embedded analytics offers the seamless integration of rich data experiences into users' natural workflows. Let me walk you through the technical requirements of a modern embedded analytics data stack,  highlighting the role of a data warehouse-centric architecture and a robust semantic layer. The evolution towards decoupling BI from the frontend not only ensures total consistency of data across applications but also opens the door to exciting developments and cost-savings.

## What is embedded analytics?

Embedded analytics means much more than using iframes to place charts in a dashboard. The real promise of embedded analytics is to _bring rich data experiences to users where they already are_. As [Gartner][3] puts it, “data analysis occurs within a user’s natural workflow, without the need to toggle to another application.”

With embedded analytics, financial analysis can be built right into a personal finance application. Personal health metrics can be displayed alongside one’s medical history. Every member of a team can see the business’s KPIs and trends without logging into a BI tool. 

For businesses, embedded analytics affords full control over what data is available and how it’s presented. For data consumers, embedded analytics means finding useful insights within the sites or apps they already use—without toggling between unfriendly, third-party dashboards. [A Harvard Business Review study found that switching between applications costs employees time equivalent of up to five weeks a year](https://hbr.org/2022/08/how-much-time-and-energy-do-we-waste-toggling-between-applications).

At Cube, we’ve been on the front lines of embedded analytics for years. Here are some recent technological and behavioral shifts we’ve observed.

## The rise of the cloud data warehouse

In the past, collecting and processing massive data sets was so technically complex and expensive that only a few companies did so. Now, cloud data warehouses make it possible for organizations in every industry and of every size to easily and affordably collect and process more data—which is often the most uniquely competitive asset a company has. This newfound accessibility has led to the [explosion in adoption](https://www.alliedmarketresearch.com/data-warehousing-market) of cloud data warehouses.

But it's not enough to just collect lots of data. Businesses need to _use_ the data, too—which has increased the need for customizable, powerful data experiences. **As a result, companies’ adoption of cloud data warehouses has led to greater interest and investment in embedded analytics, too.**

## Data consumers are more diverse

As more and more businesses invest in embedded analytics, **there is also a quickly growing variety of data consumers**. Business intelligence was once the purview of specialized engineers and analysts—but now, every member of an organization is expected to use and understand data to perform their job.

So, who is the modern data consumer? For starters, they may be less technical. The modern data consumer [may not be familiar with data engineering concepts like OLAP][5]—and they shouldn’t have to be. Moreover, the modern data consumer may not even be familiar with legacy BI tools—and, again, they shouldn’t need to be.

In order to make data accessible and actionable for these data consumers, companies need to build customized user interfaces that move beyond mere iframed graphs. And so, to match the pace of increasing diversity in data consumers, embedded analytics applications have grown more polished and interactive. 

## Front-end development is easier

**A major change in the world of embedded analytics is the rise of modern front-end development tools**. In the past decade, there have been incredible advances in powerful, simple front-end technologies—particularly [React][7]. It’s now easier than ever to design tailored, streamlined user experiences.

This innovation hasn’t just made it easier to develop custom data experiences; it also has altered users’ expectations to make such experiences essentially mandatory. Both internal data consumers and end users have new, higher standards for usability, richness, and responsiveness.

Since these expectations can’t be met with generic dashboards or out-of-the-box BI platforms, more companies are building customizable and performant embedded analytics features using modern front-end tools.

## There are new tools

It’s become easier for front-end development teams to build highly custom native UIs, but these aren’t necessary for every project. Sometimes, standard visualizations are enough and development speed is key. 

**A new generation of collaborative data tools**, including [Hex][6], [Observable][8], and [Streamlit][9], make it possible for data analysts to quickly select the appropriate chart for a dataset and share reports both throughout a company and to end customers.

Organizations need the freedom to choose the right tool for each job—whether it’s a custom application built by front-end teams or shareable dashboards built by data teams. 

## Rethinking the data stack

![modern-embedded-analytics-data-stack][30]

So, as we think about these developments, what are the technical requirements of a modern embedded analytics data stack?

## Data warehouse-centric architecture

As [others][10] [have][11] [noticed][12], the cloud data warehouse is a place not only store and process data, but also make it interactive. To actually be a backend for data analytics applications, including embedded analytics, the data warehouse needs to be at the core of your [data stack](https://cube.dev/product/data-apis).

Having data in a single place significantly reduces operational overhead. On one end, ELT is replacing ETL, on the other end, we see a trend away from moving data into “serving” databases and towards powering applications with data directly from warehouses. This architecture is easier to orchestrate and less error-prone because it removes additional pipelines and storage layers. 

Warehouses still predominantly work with batch data, but there are a lot of recent exciting developments in supporting real-time data. [Firebolt][13], [Clickhouse][14] and [Materialize][15] are actively innovating in the space. 

Finally, this architecture also opens an opportunity for [bring-your-own-data-warehouse applications][16]. This may be beneficial for both vendors and consumers because it simplifies the security architecture and streamlines new software onboarding.

## Semantic Layer in the middle

[We’ve previously described the four essential components of a semantic layer][17]. To specifically support embedded analytics, a semantic layer solution must have these attributes:

### First-class support for cloud data warehouses

A major piece of a semantic layer is **[access control](https://cube.dev/product/data-access-control)** integrated with the data warehouse’s security controls, because embedded analytics always require multitenancy. A second piece is **[advanced caching,](https://cube.dev/product/caching-and-data-performance)** because although the data warehouse is a great candidate for a backend, it doesn't does not support high-concurrency small queries with the low latency that modern data consumers expect.

### Data modeling

Across the wide variety of new data consumers, everyone should consume the same data: “net sales” in your dashboard should mean the same thing as “net sales” in my mobile CRM. To achieve this consistency, [data modeling and defining metrics](https://cube.dev/product/data-modeling) should be handled once, and this must be up-stack from every application or dashboard.

### Diverse APIs

Different data consumers have different expectations and requirements, so it’s inevitable that a company will end up supporting a whole class of data apps. For all of these applications to share the same metrics, a [semantic layer must be accessible via multiple APIs](https://cube.dev/product/data-apis), e.g., SQL, GraphQL, and REST.

## A hybrid presentation layer

To support different data consumers, use cases, and teams, an embedded analytics presentation layer should be universally open and compatible with every frontend architecture; organizations should be able to granularly curate their stacks. 

When high customization is required and front-end teams are looped in, different charting libraries can be used, ranging from [D3][18] to [Chart.js][19] and [Highcharts][20]. These most likely will be natively integrated with frontend application frameworks like React or [Angular][21].

When requirements are less custom, tools like Observable or [Dash][23] come into the picture. These tools can be natively integrated into an app and combined with a more custom interface, while making it faster and cheaper to build tailored applications.

And on the least customizable—but lowest barrier to entry end—data analysts and engineers can quickly build appealing data interfaces with modern BI tools like [Superset][24] and [Metabase][25]. These tools don't require front-end knowledge, which usually enables even faster building and iteration. They too can be combined with other presentation layers within a single application. 

Finally, there is the emerging category of no-code/low-code tools for internal tooling. This segment includes [Appsmith][26] and [Retool][27] which can also be used to build analytics interfaces.

## Conclusion

We’ve moved far past the era when embedded analytics was considered a part of a single integrated BI solution. The latest generation of BI vendors have already innovated to run “live” queries directly on top of data warehouses, which allows for cloud data warehouse-centric architectures.

The next evolutionary step—one that’s already rapidly taking place—is decoupling  BI from the frontend. By doing so, organizations are empowered to facilitate total consistency of the same data across all data applications, as well as AI agents and chatbots. 

This architecture will be enabled by several products, including Cube. And, as the category matures, and more enterprises adopt this architecture, we’ll see exciting, exponentially compounding developments. So, buckle up.

[Get started with Cube for free today][28], or [get in touch][29] to discuss your next embedded analytics project!

[1]: https://cube.dev/blog/headless-bi
[2]: https://cube.dev/blog/open-source-data-modeling
[3]: https://www.gartner.com/en/information-technology/glossary/embedded-analytics
[4]: https://www.alliedmarketresearch.com/data-warehousing-market
[5]: https://benn.substack.com/p/ghosts-in-the-data-stack?s=r
[6]: https://hex.tech
[7]: https://www.youtube.com/watch?v=7LMsR30p1xM
[8]: http://observablehq.com
[9]: http://streamlit.io
[10]: https://medium.com/snowflake/how-to-build-a-data-app-on-snowflake-db80ac459767
[11]: https://pchase.substack.com/p/thenewbackend?s=r
[12]: https://www.youtube.com/watch?v=PEFP5fLx1dY
[13]: https://www.firebolt.io
[14]: https://clickhouse.com
[15]: https://materialize.com
[16]: https://pchase.substack.com/p/thenewbackend?s=r
[17]: https://cube.dev/blog/headless-bi
[18]: https://d3js.org
[19]: https://www.chartjs.org
[20]: https://www.highcharts.com
[21]: https://angularjs.org
[22]: https://observablehq.com
[23]: https://plotly.com/dash/
[24]: https://superset.apache.org
[25]: https://www.metabase.com
[26]: https://www.appsmith.com
[27]: https://retool.com
[28]: https://cube.dev/cloud
[29]: https://cube.dev/contact
[30]: https://ucarecdn.com/43cba8de-9051-4064-a408-7278eab16218/

Here’s how the embedded analytics data stack has changed.

The Modern Data Stack for Embedded Analytics

With the [explosive growth of cloud data warehouses][1], an entire [ecosystem of new data tools][2] 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][3], 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][4] [have][5] [observed][6].

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][7]\]

In response, we’ve begun to see so-called [“headless” BI][8]. 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.

![data-modeling.png][data-modeling]
## 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][9] 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.][10]

![access-control.png][access-control]
## 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…

![caching.png][caching]
## 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.

![api-layer.png][api-layer]
## 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 must be available to everyone
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][11]. Over 200 contributors have enriched our tool on [our GitHub repo][12], 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][14] to tools like legacy BI applications and gathering feedback in [our Slack][13]. Our [Twitter DMs][15] are open and [our community meets][16] every month. And, with this momentum, [we’re hiring][17] 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?

[1]: https://www.theregister.com/2021/11/15/rise_of_the_cloud_data_warehouse/
[2]: https://www.moderndatastack.xyz
[3]: https://645ventures.com/voices/articles/the-commoditization-of-business-intelligence-tools-the-rise-of-embedded-intelligence-and-our-investment-in-cube-dev
[4]: https://benn.substack.com/p/metrics-layer?s=r
[5]: https://basecase.vc/blog/headless-bi
[6]: https://towardsdatascience.com/a-brief-history-of-the-metrics-store-28208ec8f6f1
[7]: https://basecase.vc/blog/headless-bi
[8]: https://betterprogramming.pub/headless-bi-metric-standardization-in-action-afb2ac7e89b6
[9]: https://www.mckinsey.com/business-functions/mckinsey-analytics/our-insights/the-data-driven-enterprise-of-2025
[10]: https://cube.dev/blog/dbt-metrics-meet-cube/
[11]: https://github.com/cube-js/cube.js/releases/tag/v0.3.5-alpha.0
[12]: https://github.com/cube-js/cube.js
[13]: https://slack.cube.dev
[14]: https://github.com/cube-js/cube.js/issues/3906
[15]: https://twitter.com/messages/compose?recipient_id=179985200
[16]: https://cube.dev/community-call
[17]: https://cube.dev/careers/

[data-modeling]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/473518f5-dc4f-449e-b598-09029c36b3c4.png
[access-control]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9b11b846-f021-42f8-881e-6dd6807d84c6.png
[caching]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c03dc534-f19d-4550-9534-3f1d649439ba.png
[api-layer]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/211a28aa-ab08-4c8d-b0a0-42850acd6eb6.png

The four essential components of a headless BI tool

What is Headless BI?

With this article, we start a multi-part series that will cover the spectrum of reasons to use Cube Store, a purpose-built distributed, fast, and reliable data store, as a part of your Cube deployments.

With [more than 2 years](https://cube.dev/blog/introducing-cubestore) in active development, Cube Store accounts for half of Cube’s [Rust code base](https://github.com/cube-js/cube/tree/master/rust) and supports some of the most critical features: in-memory cache, query queue management, and pre-aggregations. Thanks to these features, Cube provides fantastic support for [embedded analytics](https://cube.dev/use-cases/embedded-analytics) and [real-time analytics](https://cube.dev/use-cases/real-time-analytics) use cases, processing client queries to cloud data warehouses and streaming databases with sub-second latency and high concurrency while safeguarding these data sources from load bursts and overspending.

In this article, we’ll focus on the in-memory cache and query queue features. Last year, we [announced](https://cube.dev/blog/replacing-redis-with-cube-store) plans to update these features to use Cube Store and, consequently, remove Redis®* from Cube deployments. [Much work](https://github.com/cube-js/cube.js/issues/5042) has been done on GitHub, and we’ve also run a [webinar](https://cube.dev/events/migrating-from-redis) covering these changes recently. Now, let’s recap and dig deeper.

_&ast; Redis is a registered trademark of Redis Ltd. Any rights therein are reserved to Redis Ltd. Any use by Cube is for referential purposes only and does not indicate any sponsorship, endorsement or affiliation between Redis and Cube._

## In-memory cache in Cube

As the semantic layer for data applications, Cube is designed to sit between data sources (e.g., cloud data warehouses and traditional databases) and data consumers (e.g., BI tools, data notebooks, and front-end apps). It receives queries from these data consumers via any of the supported APIs (i.e., SQL API, REST API, and GraphQL API) and fulfills them in a timely manner.

![Semantic layer](https://ucarecdn.com/0e3e2588-ec07-44bf-a4ee-856f793132be/)

In the most trivial scenario, to fulfill a query, Cube would analyze the query, apply access control rules, transform it using the data model, generate a new SQL query, run this new query against the upstream data source, and deliver the result set back to the data consumer. It takes time, and querying the upstream data source is the most time-consuming..

You can expect the latency to be up to a few seconds when using cloud data warehouses such as Snowflake or BigQuery; it can grow indefinitely when using traditional databases such as Postgres. It depends on multiple factors, including but not limited to the following: the complexity of the query, the volume of data to be processed, the type of data storage (row-based vs. columnar), the database architecture (single-node vs. distributed), and implemented optimizations.

It means that, to ensure low query latency and excellent user experience at the data consumer level, __running queries against upstream data sources should be avoided at all costs, when possible__. Let’s consider one of these possibilities.

Often, multiple identical queries would hit Cube within a short time frame. It usually happens when multiple users open the same dashboard in a BI tool or the same page of a front-end application; it also happens when a single user refreshes a page or navigates back to it. In many cases, a result of a query that was run 5 seconds ago should still be relevant for the same query that has just arrived; moreover, query results often don't depend on the query time at all. For example, the revenue generated by the Oregon branch of your company last week or the volume of deposits withdrawn from Silicon Valley Bank on the week of March 5, 2023, don’t change as time passes.

### Query deduplication

Cube deduplicates identical queries to its APIs.

Let’s unpack how it works:

- When the first-of-a-kind query arrives, it will be fulfilled by querying the upstream data source. The result would also be put in the in-memory cache that Cube maintains.
- Next, when a query identical to the first one arrives, it would be fulfilled from the in-memory cache—almost instantly. We’re talking about milliseconds vs. seconds in the case of upstream data sources.
- As more and more identical queries hit Cube, almost counterintuitively, the overall performance would improve. Indeed, median and 95th percentile latencies for the query would naturally tend to the in-memory cache latency, not that of the upstream data source.

Please watch the following 10-minute demo to see how query deduplication works and how it affects query latencies:

<iframe width="560" height="315" src="https://www.youtube.com/embed/ULVkdUlKpUc?start=2389" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>

Here’s the most insightful part of the demo above:

```bash
% k6 run scripts/1.js | grep iteration
     iterations.....................: 2950   588.92/s
     iteration_duration.............: min=2.46ms med=10.33ms p(95)=23.13ms max=1563.71ms
```

When thousands of queries are being run (specifically, 2950), the maximum latency is well over a second because one query has to hit the upstream data source; the median and p(95) latencies are two orders of magnitude lower, in the range of 10–23 milliseconds. __Cube effectively employs its in-memory cache to deduplicate identical queries and provide the lowest query latency possible.__

It’s worth noting that the in-memory cache is, naturally, a cache, so it’s subject to one of the [two hardest things](https://skeptics.stackexchange.com/questions/19836/has-phil-karlton-ever-said-there-are-only-two-hard-things-in-computer-science) in computer science: cache invalidation. By default, Cube would invalidate the entries in the in-memory cache after a [time interval](https://cube.dev/docs/schema/reference/cube#parameters-refresh-key) that depends on the upstream data source: every 2 minutes for BigQuery, Athena, Snowflake, and Presto; every 10 seconds for all other databases. It makes sense to deduplicate queries against data warehouses more aggressively since running them costs money and consumes limited resource quotas.

### API idempotence and scalability

Cube’s architecture allows scaling its API instances from one to many. Regardless of which API instance was hit by an incoming query, Cube will check if its unified in-memory cache has a relevant cached result.

Such architecture makes Cube’s APIs idempotent. You can distribute the load between API instances however you want (a simple round-robin strategy works excellently), and no sticky behavior is needed. You can also implement more sophisticated load-balancing strategies, e.g., when running [massive multitenant deployments](https://cube.dev/docs/cloud/configuration/deployment-types#production-multi-cluster) in production. It means __you can freely and quickly scale API instances up and down__, no cache warm-up is needed for new instances.

Production deployments in Cube Cloud are able to [auto-scale the number of API instances](https://cube.dev/docs/cloud/configuration/deployment-types#cube-api-instance) from 2 to 10 (and above), so you can process dozens and hundreds of requests per second (RPS). [Talk to us](https://cube.dev/contact) if you’d like to discuss your use case and performance requirements.

## Query queue in Cube

When Cube needs to run a query against the upstream data source, it goes through the following steps:

- First, the query is added to the query queue. Cube maintains a separate query queue for every upstream [data source](https://cube.dev/docs/schema/reference/cube#data-source) and your data model can surely work with [multiple data sources](https://cube.dev/docs/config/multiple-data-sources) at once.
- Second, Cube will pick a query from the queue and execute it against the data source. The maximum number of simultaneously executed queries is defined by the [`CUBEJS_CONCURRENCY` config option](https://cube.dev/docs/reference/environment-variables/#cubejs-concurrency) and depends on the upstream data source: 2 for Postgres, 4 for Redshift, 5 for Athena and Snowflake, 10 for BigQuery, etc. These sane defaults prevent the data source from overloading and data warehouse resource quotas from depleting.
- Lastly, when the query execution is completed, Cube will put its results in the in-memory cache. Immediately thereafter, all API requests that were waiting for the results of this query will be fulfilled.

Please watch the following 3-minute demo to see how the query queue works and how it allows limiting query concurrency on the data source side:

<iframe width="560" height="315" src="https://www.youtube.com/embed/ULVkdUlKpUc?start=3098" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>

Queuing queries instead of running them directly against the upstream data source brings substantial benefits. __Query queue safeguards data sources from excess queries in case of burst load;__ it also allows servicing long-running interactive queries and canceling queries initiated by dropped API connections.

## In-memory cache and query queue implementation

As evident from the previous sections, in-memory cache and query queue are essential parts of Cube that have been present from day one.

### Initial architecture

Initially, they were implemented using [Redis](https://redis.io), an open-source in-memory data store, to keep the cache and queue entries. Cube API instances and refresh workers interacted with Redis to maintain the query queue and use the in-memory cache. The following architecture was used:

![Architecture with Redis](https://ucarecdn.com/18f3c514-1856-49b2-bd4d-1d48c8a043f7/)

It worked really well for many use cases. However, we collected enough evidence over time to decide that Cube needed to move on from Redis.

### Reasons to move on from Redis 

Please read the [blog post](https://cube.dev/blog/replacing-redis-with-cube-store) with detailed reasoning by Pavel Tiunov, CTO at Cube.

Alternatively, refer to the following summary:

- __Operations and reliability considerations.__ Redis requires a complex setup for [scalability](https://redis.io/docs/management/scaling/) and [high availability](https://redis.io/docs/management/sentinel/). To maintain a Redis cluster, you must run at least three Redis and Redis Sentinel nodes. Moreover, cluster coordination consumes a lot of resources under high load.
Use case considerations. Redis works well as a data store for the in-memory cache. However, it was never designed for large columnar payloads and low bandwidth consumption. Also, Redis is less than optimally suited as a data store for the query queue given the [complexity of data flow](https://github.com/cube-js/cube.js/blob/master/packages/cubejs-query-orchestrator/DEVELOPMENT.md).
- __Client stability and code complexity considerations.__ Since the first days of Cube, Redis has required substantial effort to provide stable support on the client side. We had to introduce the support for multiple client libraries (node-redis and ioredis) and the related [`CUBEJS_REDIS_USE_IOREDIS` config option](https://cube.dev/docs/reference/environment-variables#cubejs-redis-use-ioredis). Moreover, we had to maintain complex and error-prone code on the client side to manage the query queue (see examples: [one](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L93,L101), [two](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L130,L137), [three](https://github.com/cube-js/cube.js/blob/02a8e027c1f414c50cb49f257ce68c01425400ec/packages/cubejs-query-orchestrator/src/orchestrator/RedisQueueDriver.js#L209,L218)).

One more deciding factor was that many Cube deployments—in addition to Redis—include Cube Store, a purpose-built distributed, fast, and reliable data store. So, we updated the implementation of the in-memory cache and query queue to use Cube Store.

### Up-to-date architecture

Up-to-date Cube deployments use Cube Store as the all-in-one data store: for in-memory cache, query queue, and pre-aggregations. Redis is not needed.

![Architecture with Cube Store](https://ucarecdn.com/c97e77a5-1ebf-4542-951c-480a7ccc2509/)

If you still have Redis as a part of Cube deployment, read on for the migration guide.

## How to migrate to Cube Store

Cube supports using Cube Store for in-memory cache and query queue starting from version 0.31.60. To migrate, you will need to upgrade Cube to this version and check the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver).

Migration scenarios slightly differ depending on where you run Cube: in Cube Cloud, in a self-hosted environment, or locally on your computer.

### Migration in Cube Cloud

In Cube Cloud, the migration from Redis is seamless. Every [production cluster](https://cube.dev/docs/cloud/configuration/deployment-types#production-cluster) in Cube Cloud includes a fully managed Cube Store with high availability.

Proceed through the following steps:

1. Pick a deployment, navigate to <Btn>Settings</Btn> / <Btn>General</Btn>, and ensure the Cube version is at least 0.31.60. Choose either a <Btn>Stable</Btn> (preferred) or the <Btn>Latest</Btn> channel:

    ![Versions](https://ucarecdn.com/7798b767-b917-4258-9fd8-54d5f87c9046/)

2. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, go to <Btn>Settings</Btn> / <Btn>Configuration</Btn> and ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore`.

    ![Environment variables](https://ucarecdn.com/3eeb9e89-a8c2-4a73-ab4a-56de840429eb/)

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

### Migration when self-hosting

In a self-hosted environment, the migration is really easy if you already have Cube Store. If you don’t run it yet and don’t want to complicate your setup, consider [using Cube Cloud](https://cubecloud.dev/auth/signup) which provides a seamless migration experience.

Proceed through the following steps:

1. If you don’t run Cube Store yet, you should run at least a Cube Store router. Please refer to the Cube Store [architecture overview](https://cube.dev/docs/caching/running-in-production#architecture) and example Docker configuration.
2. Ensure the version is at least 0.31.60 for all Cube nodes: API instances, refresh workers, Cube Store routers, and Cube Store workers.
3. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore` for all API instances and refresh workers.
4. Remove all [`CUBEJS_REDIS_*` config options](https://cube.dev/docs/reference/environment-variables/#cubejs-redis-url) from all API instances and refresh workers.
5. Remove Redis from your architecture.

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

### Migration on a local computer

If you run a single instance of Cube locally on your computer in the [development mode](https://cube.dev/docs/configuration/overview#development-mode), the migration is really easy because you already have Cube Store running in-process.

Proceed through the following steps:

1. Ensure the Cube version is at least 0.31.60.
2. Starting from 0.32.0, no additional configuration is needed. If you’ve chosen a version between 0.31.60 and 0.32.0, ensure you have the [`CUBEJS_CACHE_AND_QUEUE_DRIVER` config option](https://cube.dev/docs/reference/environment-variables#cubejs-cache-and-queue-driver) set to `cubestore`.

That is all. Now you run Cube with Cube Store for the in-memory cache and query queue.

## What’s next?

Run Cube Store as part of your Cube deployments. [Get started today in Cube Cloud](https://cubecloud.dev/auth/signup) to try a fully managed Cube experience.

Enjoy the benefits of using Cube Store for the in-memory cache and query queue, including low query latency for identical queries and safeguarding data sources from burst load.

Please don’t hesitate to [talk to us](https://cube.dev/contact) if you have any questions. Also, join our Slack community of 8,000 data practitioners at [slack.cube.dev](https://slack.cube.dev) to discuss your experience with Cube Store.

Igor Lukanin

Moving on from Redis to a better in-memory cache and query queue

How you win by using Cube Store: Part 1

When I was reading Pedram’s [deep dive on metrics layers](https://pedram.substack.com/p/what-is-the-metrics-layer) a few weeks ago, I couldn’t help but question the tool selection. One can obviously blame my employee-employer affiliation which is hard to deny. However, the real reason for that was this [summary statement](https://pedram.substack.com/p/what-is-the-metrics-layer):

> All three tools have different trade-offs, and their strengths and weaknesses tell of the challenges a metrics layer faces.
> 

While this is a true statement, it dismisses the fact that there’s an ergonomic, feature-complete, and accessible solution on the market that doesn’t suffer from highlighted pitfalls. With that, I’m putting Cube in the spotlight and kindly requesting your ~~blowback~~ [feedback on social media](https://twitter.com/thecubejs/status/1578049949811589123).

## In previous episodes

If you haven’t read the original [deep dive on semantic layers](https://pedram.substack.com/p/what-is-the-metrics-layer), please do. I promise it’s gonna be a rewarding experience.

There, Pedram mentions some prior thought leadership on the topic and introduces a challenge for three tools: Looker, dbt Metrics, and Lightdash. They have to model and provide access to the *Activation Metric*, a crucial characteristic of an imaginary B2B SaaS product with users and workspaces. A workspace is considered active if users perform some activation event, so we want to report on the activation rate daily, weekly, or monthly and how it changes over time. Later, Pedram shows how Looker, dbt Metrics, and Lightdash solve the data modeling and elaborates on their strengths and pitfalls.

Here’s the `workspace_details` table matching the imaginary dataset that was used by all tools to calculate the metrics. I’ve generated 20 million rows and put them into Postgres, so we have something to work with:

![dataset](https://ucarecdn.com/1f7f0a20-b331-4afa-9e63-7313a7e96c05/)

Now, let’s talk about Cube.

## Cube, the semantic layer

Cube is the [semantic layer](https://cube.dev) for building data applications, created to make data consistent and accessible. It solves the many-to-many problem many data teams have by serving as a source of truth for metrics definitions, access control rules, and caching settings.

![Cube in the pipeline](https://ucarecdn.com/dfb299b9-7e27-47f0-a362-0a14c643c740/)

Cube connects to numerous [data sources](https://cube.dev/docs/config/databases), from cloud data warehouses to [streaming platforms](https://cube.dev/blog/headless-bi-with-streaming-data), and delivers data to all kinds of [data consumers](https://cube.dev/docs/config/downstream) via multiple APIs, including the [SQL API](https://cube.dev/blog/expanded-bi-support). It is deliberately visualization-agnostic and delegates data presentation and visualization to BI tools, data notebooks, front-end apps, etc.

Cube is open source and there's also [Cube Cloud](https://cube.dev/cloud) that provides fully managed experience and additional tooling on Free, Standard, and Enterprise tiers (see [pricing](https://cube.dev/pricing)).

## Data modeling

In Cube, we define metrics in [data model](https://cube.dev/blog/open-source-data-modeling) files that seem to be heavily inspired by LookML.

At the top level, everything is grouped in “cubes” that are usually modeled over tables or views in the data warehouse but can also be modeled over pretty complex queries. Cubes include definitions for measures (quantitative features), dimensions (qualitative features), segments (reusable filters), and pre-aggregations (declarative definitions of rollups that drastically speed up queries, more on them later).

Here’s the data model for our activation metric:

```jsx
cube(`WorkspaceActivation`, {
  sql: `SELECT * FROM public.active_workspace_details`,

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

    n_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
    },

    n_active_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
      filters: [ {
        sql: `${is_active_workspace} IS TRUE`
      } ],
    },

    activation_rate: {
      sql: `ROUND(100 * (1.0 * ${n_active_workspaces} / ${n_workspaces}), 2)`,
      type: `number`
    }
  },

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

    reporting_day: {
      sql: `reporting_day`,
      type: `time`
    },

    is_active_workspace: {
      sql: `is_active`,
      type: `boolean`
    }
  },
});
```

A few observations here:

- Similarly to Looker, we define measures as formulas, not as fully-formed SQL tables.
- While we can use “formatting” functions like `ROUND` in measure definitions, formatting options are deliberately limited. Cube puts the burden of data presentation on downstream tools.
- By default, we don’t have to specify how our reporting date should be broken down. We’ll be able to provide desired time granularity at query time.
- Needless to say, we can define other cubes and [joins](https://cube.dev/docs/schema/reference/joins#joins) between them.

## Query syntax

Cube provides a set of APIs to fetch data: [SQL API](https://cube.dev/docs/backend/sql) (mostly for BI tools and data notebooks), [REST API](https://cube.dev/docs/rest-api), and [GraphQL API](https://cube.dev/docs/backend/graphql) (mostly for front-end applications). A simple query to the SQL API:

```jsx
SELECT reporting_day, activation_rate
FROM WorkspaceActivation;
```

Of course, we can run a more complex query, too:

```jsx
SELECT
  DATE_TRUNC('WEEK', reporting_day) AS reporting_week,
  activation_rate
FROM WorkspaceActivation
ORDER BY 1 DESC;
```

Queries would turn into JSON when expressed in the REST API syntax. First one:

```json
{
  "measures": [
    "WorkspaceActivation.activation_rate"
  ],
  "timeDimensions": [ {
    "dimension": "WorkspaceActivation.reporting_day"
  } ]
}
```

Second one:

```json
{
  "measures": [
    "WorkspaceActivation.activation_rate"
  ],
  "timeDimensions": [ {
    "dimension": "WorkspaceActivation.reporting_day",
    "granularity": "week"
  } ],
  "order": {
    "WorkspaceActivation.reporting_day": "desc"
  }
}
```

Since we haven’t connected a downstream tool yet, we can run queries and explore the metrics in Cube’s [Playground](https://cube.dev/docs/dev-tools/dev-playground) with drop-down inputs for measures and dimensions:

![Playground](https://ucarecdn.com/0a0e01d2-cb3a-4cbe-8f2f-a87b257324bb/)

We can instantly get the JSON queries for the REST API here:

![JSON query](https://ucarecdn.com/51228e1d-33b6-46d7-8bf6-637e04261e2c/)

More conveniently, we can also look up the SQL statement that would be generated by Cube and run against the data warehouse when the query is executed. No surprise here: Cube uses the provided query and data model definitions to compose the final query: 

![Generated SQL query](https://ucarecdn.com/742514b5-ee4e-43dd-8c1b-d03b3284dbcc/)

## Query acceleration

Obviously, queries can be expensive: costly to run, too complex to wait for, or both.

In that case, we can define a pre-aggregation (actually, the recommendation is always to use pre-aggregations in production). Here’s what we need to append to the data model:

```jsx
  preAggregations: {
    main: {
      measures: [ activation_rate ],
      timeDimension: reporting_day,
      granularity: `week`,
      refreshKey: {
        every: `1 day`
      }
    }
  }
```

It instructs Cube to build a rollup and use it to serve queries that contain any subset of provided measures and dimensions (namely, `activation_rate` and `reporting_day` with weekly granularity). Usually, a single pre-aggregation speeds up multiple queries. We can also configure freshness: a pre-aggregation update can be triggered on schedule (e.g., every day) or when an arbitrary SQL statement yields a new result. 

Now, the last query would return the same result, but a completely different query would be executed against the data warehouse:

![Accelerated SQL query](https://ucarecdn.com/786f7cc1-791d-4122-b13a-b3660405f439/)

Truth be told, it wouldn’t be executed against the data warehouse at all. The `prod_pre_aggregations.workspace_activation_main` table is transparently created and maintained in [Cube Store](https://cube.dev/blog/introducing-cubestore), a custom-built massively distributed columnar storage for pre-aggregation data.

There’s an option to skip using Cube Store and store pre-aggregations in a data warehouse, but that’s not the most reasonable thing to do. With pre-aggregations in Cube Store, Cube can serve practically any query with sub-second latency (expect something between 50 ms and 500 ms) and allow for a decent concurrency (say, 100 QPS or maybe 1000 QPS).

Okay, but what would Cube do if we need to query for other granularity? Since it’s aware that queried measures are defined with distinct counts, it won’t be using the rollup and will transparently fall back to a query to the data warehouse (there’s an [option to disallow that](https://cube.dev/docs/caching/using-pre-aggregations#rollup-only-mode) at all times, if we need to).

![Non-accelerated SQL query](https://ucarecdn.com/a0d3bd13-889d-4a92-925d-984ee2dd9a4d/)

## Data consumers

Now, we’re ready to connect downstream tools. We can pick anything that supports Cube’s SQL API, and that’s practically any tool. Cube’s SQL API uses Postgres dialect and wire protocol, so if something works with Postgres as an upstream source, it works with Cube: 

![Supported data sources](https://ucarecdn.com/ba240ef2-11fc-477d-843d-39b0b9fe2649/)

We have numerous options here. Out of respect for the first tech company ever where the [first triple-name-tie is Claire](https://twitter.com/clairebcarroll/status/1570194384980971522), let’s connect Cube to [Hex](https://hex.tech):

![Connection to Hex](https://ucarecdn.com/7c89eb8a-d7dd-41b9-bcdb-c1889b70eba5/)

Indeed, [Hex works with Cube](https://cube.dev/blog/hex-integration-collaborative-data-notebooks):

![Hex notebook](https://ucarecdn.com/3761be9c-722c-461e-a1ba-7dcb01e1441b/)

In case we’re building a front-end app, we can directly `fetch` from the REST API endpoint or use the provided JavaScript client library:

```jsx
import cubejs from '@cubejs-client/core';

const cubejsApi = cubejs.CubejsApi(
  process.env.CUBE_TOKEN,
  { apiUrl: process.env.CUBE_API_URL }
);

const query = {
  measures: [ 'WorkspaceActivation.activation_rate' ],
  timeDimensions: [ {
    dimension: 'WorkspaceActivation.reporting_day',
    granularity: 'week'
  } ],
  order: {
    'WorkspaceActivation.reporting_day': 'desc'
  }
};

const resultSet = await cubejsApi.load(query);

console.log(resultSet.tablePivot());
```

Now you can plot the data with a [charting library](https://awesome.cube.dev/?tools=charts) of your choice:

![Data in the terminal](https://ucarecdn.com/dee1b9f9-74ff-4e06-8522-335922f2afd1/)

## Wrapping up

Hopefully, this write-up provides some insight into how Cube works and helps compare it to Looker, dbt Metrics, and Lightdash from the original [deep dive](https://pedram.substack.com/p/what-is-the-metrics-layer).

Also, if you’re using dbt Metrics, please keep in mind that you can leverage Cube’s [integration with dbt](https://cube.dev/blog/dbt-metrics-meet-cube) that transparently reads metrics definitions from dbt and automagically creates a data model from them.

Currently, Cube uses JSON-like markup for data model definitions and Looker-like metrics organization into cubes. Later in 2022, Cube is likely to [introduce YAML markup](https://forms.gle/4CZRRKK8nD4PvtxZA) for data models and an alternative approach to metrics.

With that, I do have an answer to the final question from Pedram’s article:

> Can a metrics layer be universal enough to gain applicability across the data stack yet still be designed in such a way to be relevant to BI tools?
> 

Yes. Try Cube.

The easiest way to get started with Cube is [Cube Cloud](https://cube.dev/cloud). It provides managed hosting and convenient tools for Cube, including the Playground, the data model editor with syntax highlighting, and—more importantly—the ability to instantly fork data models and query private versions of APIs while the production version stays intact and serves queries.

[Sign up for Cube Cloud](https://cubecloud.dev/auth/signup) on the free tier today and experience that yourself. Feel free to connect your production dataset or tinker with the Postgres instance used in this blog post:

```
Host:      demo-db-examples.cube.dev
Database:  ecom
User:      cube
Password:  12345
```

Cube in the spotlight alongside Looker, dbt Metrics, and Lightdash.

Deep dive: What the heck is the Semantic Layer

The ongoing story of the digital age is still about [taking products apart](https://hbr.org/2014/06/how-to-succeed-in-business-by-bundling-and-unbundling). The wave of [bundling](https://towardsdatascience.com/the-great-data-debate-unbundling-or-bundling-7d7721ee8514), [unbundling](https://blog.fal.ai/the-unbundling-of-airflow-2/), and [rebundling](https://dagster.io/blog/rebundling-the-data-platform) happening in the data space has subsided, but the point remains. Generations of tools in the modern data stack, whatever it is, succeed each other primarily for two reasons:

- Emerging tools provide more flexibility and a lower entry barrier for practitioners.
- Alternatively, teams escape into a safe harbor of a well-known integrated solution that promises big returns on even bigger investments. Indeed, has anyone ever got fired for buying Looker?

Today, we’re going to talk about Cube and Apache Superset—a bundle of tools representing a finer alternative to Looker in many use cases.

> We've had a webinar on replacing Looker with Cube and Superset. [Check the recording and slides.](https://cube.dev/events/replacing-looker-with-cube-and-superset)

## Free as in “open source”

[Cube](https://cube.dev) and [Superset](https://superset.apache.org) belong to different categories in the data stack. The former is the [headless business intelligence](https://cube.dev/blog/headless-bi) platform, and the latter serves as the data visualization and exploration platform. However, being open source and standing on the shoulders of giants are among many things that they have in common.

Both tools are licensed under Apache License 2.0 and actively developed on GitHub. Over the last 3 years, Cube’s [repository](https://github.com/cube-js/cube.js) has acquired almost 14,000 stars (an unmatched [leader in the category](https://twitter.com/sspaeti/status/1570044606338990081)). Written mostly in Rust, Cube’s data processing and storage are based on the [Arrow DataFusion](https://github.com/apache/arrow-datafusion) query execution framework which uses [Apache Arrow](https://arrow.apache.org/) as its in-memory format. Superset has a longer history (born at Airbnb in 2015 where it replaced Tableau, joined the Apache Incubator program in 2017), has a [repository](https://github.com/apache/superset) with 48,000 stars, and is powered by [Apache ECharts](https://echarts.apache.org/en/index.html), one of the most mature and popular charting libraries. 

![Cube and Superset](https://ucarecdn.com/5f3f2313-c055-4b9e-b345-56568c53aca2/)

You’re definitely free to take and use Cube and Superset at your own discretion, including self-hosting them in your private or public cloud. You’re also free to explore and get started with [Cube Cloud](https://cube.dev/cloud) and [Preset Cloud](https://preset.io/product/), fully managed platforms with freemium pricing models and plenty of quality of life features on top of open source products. Compare that to Looker’s “[request a demo](https://cloud.google.com/resources/looker-demo/?_ga=2.157282873.347220352.1663670208-344442256.1663670208)” paywall that would cost your team a few weeks of qualification calls on Google Meet before you start building.

## Simple as in “separation of concerns”

Cube is intentionally [headless](https://cube.dev/blog/headless-bi) and agnostic to the data presentation layer. However, it does a number of things and does them well:

- connects to a plethora of [data sources](https://cube.dev/docs/config/databases), from cloud data warehouses like BigQuery, Snowflake, and Redshift to streaming platforms like Kafka and Redpanda;
- provides a [data modeling](https://cube.dev/docs/schema/getting-started) layer as the centralized source of truth for metrics definitions;
- secures data with its [access control](https://cube.dev/docs/security) layer;
- makes sure any query runs well under a second with its [caching](https://cube.dev/docs/caching) layer;
- delivers data to any downstream tool, including BI tools like Superset, via its API layer that provides [SQL API, REST API, and GraphQL API](https://cube.dev/docs/config/downstream#ap-is-references).

Superset works by connecting to a data source and querying the data where it lives. The ideal way to use Superset is with a [dataset-centric approach](https://preset.io/blog/dataset-centric-visualization/) to modeling and visualization. 

A dataset-centric approach implies that a well-normalized dataset is used to “derive” richer datasets with extra semantics, making them valuable for analysis and visualization. By creating derived datasets that incorporate aggregate metric calculations earlier in the ETL/ELT process *before* surfacing for visualization, many of the issues with change management, maintenance, logic reuse within dashboards, etc. are significantly reduced for a better experience. 

Cube complements Superset in this way, and this allows for Superset-generated SQL queries to work with no issues when connected to a Cube environment (which is treated as a PostgreSQL database connection). 

![Looker vs Cube + Superset](https://ucarecdn.com/59031c52-890f-402f-9400-0d0d7034c887/)

You’re free to configure and fine-tune each tool and each layer within every tool when building with Cube and Superset. Want to build and schedule a report? Check Superset dashboards. Need to expose new metrics in that report? Cube’s data modeling layer is at your service.

## Compatible as in “no vendor lock-in”

Speaking of data modeling, Cube provides a [data modeling language](https://cube.dev/blog/open-source-data-modeling) that is not tied to any specific interface, visualization, or user experience. Unlike proprietary LookML definitions that require Looker IDE for syntax highlighting and linting, Cube’s data model is written in JavaScript (and [soon-to-be-written](https://forms.gle/4CZRRKK8nD4PvtxZA) in YAML) and feels native in your editor of choice. On top of that, you are able to generate a data model dynamically or even fetch it from a remote endpoint.

Consider an excerpt of Looker view from the recent [deep dive](https://pedram.substack.com/p/what-is-the-metrics-layer) by Pedram:

```yaml
view: workspace_activation {
  sql_table_name: "METRICS"."WORKSPACE_ACTIVATION" ;;

  dimension_group: date {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}."REPORTING_DATE" ;;
  }

  dimension: is_active_workspace {
    type: yesno
    sql: ${TABLE}."IS_ACTIVE_WORKSPACE" ;;
  }

  dimension: workspace_id {
    type: string
    primary_key: yes
    sql: ${TABLE}."WORKSPACE_ID" ;;
  }

  measure: count_workspaces {
    type: count_distinct
    description: "# of Workspaces"
    sql: ${workspace_id} ;;
  }

  measure: count_active_workspaces {
    type: count_distinct
    description: "# of Unique Workspaces Active within 1 Day"
    sql: ${workspace_id} ;;
    filters: [is_active_workspace: "yes"]
  }

  measure: activation_rate {
    type: number
    sql: ${count_active_workspaces} / ${count_workspaces} ;;
    value_format_name: percent_1
  }
}
```

Compare with the equivalent Cube data model (way less of `;;`, to say the least):

```javascript
cube(`WorkspaceActivation`, {
  sql: `SELECT * FROM public.active_workspace_details`,

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

    n_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
    },

    n_active_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
      filters: [ {
        sql: `${is_active_workspace} IS TRUE`
      } ],
    },

    activation_rate: {
      sql: `ROUND(100 * (1.0 * ${n_active_workspaces} / ${n_workspaces}), 2)`,
      type: `number`
    }
  },

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

    reporting_day: {
      sql: `reporting_day`,
      type: `time`
    },

    is_active_workspace: {
      sql: `is_active`,
      type: `boolean`
    }
  },

  preAggregations: {
    main: {
      measures: [ activation_rate ],
      timeDimension: reporting_day,
      granularity: `week`,
      refreshKey: {
        every: `1 day`
      }
    }
  }
});
```

In a similar fashion, Cube’s API layer is compatible with practically any data consumer. When the time comes, and you realize that, alongside business users using dashboards and reports in Superset, you’d like to have data analysts crunching numbers and seeking insights in data notebooks, Cube will be there to help liberate your data.

![Data consumers](https://ucarecdn.com/b35f44ef-12a5-42d7-a618-74637fc3e0cd/)

You can expect any SQL query generated by your data exploration tool like Superset to work with Cube’s SQL API. Needless to say, equivalent queries to Cube’s REST API and GraphQL API will yield the same data:

```sql
SELECT
  DATE_TRUNC('WEEK', reporting_day) AS reporting_week,
  activation_rate
FROM WorkspaceActivation
ORDER BY 1 DESC;
```

Let’s have a bird’s-eye view here.

The generation of BI tools like Looker encouraged end-users to invest heavily in building out tons of LookML models to populate the semantic layer. While this established Looker as a source of truth in an organization for business metrics, it also created an immense amount of lock-in in your BI tool. You couldn’t take your LookML models with you and use them with another tool if you decided to switch BI tools. Google’s acquisition of Looker accelerated organizations’ anxieties around this lock-in, to the point that Google was actually [forced to integrate LookML with Tableau](https://roundup.getdbt.com/p/look-ml-tableau-metrics-layer). This way, organizations could use LookML for transformation (Looker’s strength) and Tableau for visualization (Tableau’s strength).

The future however is looking to be very different from the past:

- Open-source headless BI platforms like Cube provide unified semantic layers to sit between the database and BI later, eliminating the need, or providing alternate implementations, for expensive solutions like Looker that lock you in.
- Open-source BI tools like Superset with an intentionally thin semantic layer complement Cube and enable *last-mile data transformation* for the explicit purpose of data visualization in your BI tool.

When the need for a complex yet flexible metrics/semantic layer arises, Cube and Superset is a winning open source-based combo!

## Ready-to-go as in “today”

Now, you’re very much welcome to try Cube and Superset today and see them in action. Please join the Slack communities of [Cube](https://slack.cube.dev) and [Superset](https://join.slack.com/t/apache-superset/shared_invite/zt-16jvzmoi8-sI7jKWp~xc2zYRe~NqiY9Q) to share your feedback.

Please tune in to the [webinar on October 4](https://cube.dev/events/replacing-looker-with-cube-and-superset) with [Shreesham Mukherjee](https://www.linkedin.com/in/shreesham/), Developer Relations Engineer at Preset, and [Igor Lukanin](https://www.linkedin.com/in/igorlukanin/), Head of Developer Relations at Cube. We’ll discuss Cube and Superset as an open source alternative to Looker in popular use cases, demo both tools, and take your questions.

See you!

Shreesham Mukherjee

Looker

Respectfully unbundling the elephant in the BI room.

Open source Looker alternative with Cube and Apache Superset

Previously, we’ve [described the parts of headless BI](https://cube.dev/blog/headless-bi), taken an [in-depth look at the data modeling layer](https://cube.dev/blog/open-source-data-modeling), and explored one use case for headless BI: [embedded analytics](https://cube.dev/blog/what-is-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.

![](https://ucarecdn.com/f3b2607c-8d11-489e-a09e-df9f94c1384c/)

## 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](https://snowflake.com) or a real-time tool like [Firebolt](https://www.firebolt.io/), [ClickHouse](https://clickhouse.com/), or [Materialize](https://materialize.com/).

#### Headless BI layer

A crucial component of a data app is the [headless BI layer](https://cube.dev/blog/headless-bi). 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](https://d3js.org/) to [Chart.js](https://www.chartjs.org/) and [Highcharts](https://www.highcharts.com/). These most likely will be natively integrated with frontend application frameworks like [React](http://react) or [Angular](https://angularjs.org/).

### 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](https://www.appsmith.com/) and [Retool](https://retool.com/), which can be used to quickly build analytics interfaces.

There also are data application frameworks that are helpful here: tools like [Plotly Dash](https://plotly.com/dash/) and [Streamlit](https://streamlit.io) 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.


Jonathan E Cowperthwait

Application Development

Moving beyond the dashboard.

What are data apps?

Previously, we’ve discussed the recent emergence of headless BI, and reviewed [headless BI’s four essential components][1]. Now, let’s take a more in-depth look at one of those layers: data modeling. Here is an introduction to open source data modeling in Cube’s headless BI platform.

How data is modeled is the first fundamental element of the business intelligence stack, because it organizes and streamlines querying a data source. Additionally, the data model defines the relationships between each data entity, which forms the basis of all analysis of that data.

Data modeling is a vital component of the BI stack and must be consistent and universally accessible. And so, its logic should be defined only once, and should be defined in code. Furthermore, this code should be governed by version control, to enable safe iteration as an organization grows.

## A data modeling language for Headless BI

![open source data modeling language for headless BI][image01]

Cube’s entire headless BI platform is open source—and this includes our open source data modeling language. This language is used to describe measures, dimensions, and relationships in a data source. Then, Cube servers use the model written in this language to generate SQL queries against data sources when responding to requests made to our REST, GraphQL, and SQL APIs.

Unlike [other data modeling languages][7], Cube’s data modeling language is not tied to any specific interface, visualization, or user experience. This decoupling makes it possible to use the same model definitions across every application. Each data consumer can have a custom-tailored experience in the data application of their choice while still working with consistent and standardized definitions.

## The design of Cube’s data modeling language

**Cube’s data model is a relational graph of the entities that contain measures and dimensions.** 

In Cube, entities are called “cubes”—inspired by OLAP cubes. In essence, they are data tables with a semantic meta layer that describes both the cube’s measures and dimensions and its relationships to other cubes. 


![open source data modeling - Cube][image02]

**Cube operates on the foundational primitives of *dimensions* and *measures*.** Users can define metrics by combining these primitives.  

***Dimensions***  are properties of entity objects, e.g., a user’s country or a product’s category. 

```javascript
cube(`Products`, {
  dimensions: {
    category: {
      sql: `category`,
      type: `string`
    }
  }
});
```

***Measures*** are aggregations, typically defined as SQL expressions or algebraic operations over other measures:


```javascript
cube(`Orders`, {
  measures: {
    totalValue: {
      sql: `value`,
      type: `sum`
    }
  }
});
```

Finally, cubes are connected to each other and constitute a data graph.


```javascript
cube(`Orders`, {
  joins: {
    Products: {
      relationship: `belongsTo`
      sql: `${Orders}.product_id = ${Products}.id`,
    }
  }
});
```

When users construct metrics, the dimensions and measures they query don’t have to belong to the same cube. They can be located in different places on the data graph. Cube will generate appropriate SQL, including taking care of [chasm / fan-outs][8], to join the underlying tables to respond to the request. 

![open source data modeling - Cube][image03]

**Cube’s approach is different from a query-oriented approach** in which metrics are defined by a custom query and a bunch of attributes are assigned to that query. That approach essentially results in single cubes, each with a single measure and a subset of dimensions and filters. Because these cubes aren’t connected to one another, we can’t reuse dimensions, filters, joins, access controls, caching configuration, etc. 

If we were to continue answering more questions and building more metrics, we would have to build more queries, with duplicated definitions and different join paths. At best, this would be repetitive; at worst, it would lead to inconsistent definitions and a combinatorial explosion of metrics and dimensions. 

Instead, we’re focused on letting users define relationships and reuse definitions to build maintainable and scalable data models.

**Cube’s data modeling language is based on JavaScript.** This may come as a surprise, given that many data modeling frameworks and languages are based on YAML. However, as a markup language, YAML has no runtime and only limited capacity for code organization. This is a big limitation for complex and dynamic data models.

By running a data model in a JavaScript virtual machine, Cube can mutate the model at runtime both reactively and proactively, which enables dynamic data definitions, caching, and access control rules.

**Cube’s data modeling language also manages caching.** Users can define a set of measures and dimensions that are commonly used together, and materialize them as rollup tables. Since the caching rules are defined in code, it’s possible to manage them using best practices including version control, code reviews, and tests in isolated environments.

![open source data modeling - Cube][image06]

**In Cube, data processing and storage are written in Rust**, based on the [Datafusion][9] query execution framework, which uses [Apache Arrow][10] as its in-memory format.

## A data modeling example

Let’s take a look at how to use Cube’s data modeling language.

We’ll model data for an imaginary e-commerce website. Assume the data warehouse has tables like `orders` and `users` with information about transactions and who placed them.

### Orders

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the order</td>
  </tr>
  <tr>
    <td>
      <code>user_id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>value</code>
    </td>
    <td>Value of the order in USD</td>
  </tr>
  <tr>
    <td>
      <code>status</code>
    </td>
    <td>Status of the order, either “processing” or “completed”</td>
  </tr>
</table>

### Users

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>country</code>
    </td>
    <td>Country of the user</td>
  </tr>
</table>

We can represent these two tables in Cube’s modeling language as cubes with this configuration:


```javascript
cube(`Orders`, {
 sql: `SELECT * FROM orders`,
 measures: {
   totalValue: {
     sql: `value`,
     type: `sum`,
     description: 'Revenue from completed orders placed on our website.',
   }
 },
 dimensions: {
   id: {
     sql: `id`,
     type: `number`,
     primaryKey: true
   },
  
   status: {
     sql: `status`,
     description: 'Status of the order, either processing or completed',
     type: `string`
   },
  
   time: {
     sql: `timestamp`,
     type: `time`
   }
 }
});
```

```javascript
cube(`Users`, {
  sql: `select * from users`,
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    country: {
      sql: `country`,
      type: `string`,
      description: `User's country of residence`
    }
  }
});
```

Let’s look at the major elements of the model.

The `sql` property describes the table of data that will be used in cube, which references the underlying table within the data warehouse. It also can support arbitrary SQL queries in complex cases.

The cubes include defined **_measures_** and **_dimensions_**:

 - ***Measures:*** quantitative data, such as the number of units sold, number of unique visits, revenue, and so forth
 - ***Dimensions:*** properties of data entities, such as state, gender, product name, or units of time
 
To build data relationships, we define the `joins` property on one of the cubes:

```javascript
cube(`Orders`, {
  joins: {
    Users: {
      relationship: `belongsTo`
      sql: `${Orders}.user_id = ${Users}.id`,
    }
  }
});
```

## Querying the data model

With the above data model, it’s now possible to query Cube’s APIs to learn total value grouped by country, or a specific company‘s total value from completed orders.

To find out the total value of orders by country, we’d use the following JSON query:

```json
{
  measures: [Orders.totalValue],
  dimensions: [Users.country]
}
```

You also can explore the data model from within a BI tool:

![querying a Cube data model using Superset][image07]

These examples are relatively simple, but a data model and queries can get very complex, with many measures, dimensions, and relationships.

By creating a data model foundation and exposing metrics via many APIs, Cube ensures that data is accessible and consistent for every downstream application.

 - *Want to explore Cube’s data modeling for yourself? [Create a free Cube Cloud account now][2].*
 - *Have questions or input? [Drop us a line][3], [join our Slack][4], or [view our headless BI project on GitHub][5].*
 - *Want updates like these in your inbox? [Subscribe to our newsletter][6].*

[1]: https://cube.dev/blog/headless-bi
[2]: https://cube.dev/cloud
[3]: https://cube.dev/contact
[4]: https://slack.cube.dev
[5]: https://github.com/cube-js/
[6]: https://cube.dev/#email
[7]: https://www.looker.com/platform/data-modeling/
[8]: https://biguru.wordpress.com/2008/05/01/its-all-in-the-universe-handling-chasm-and-fan-traps/
[9]: https://github.com/apache/arrow-datafusion
[10]: https://arrow.apache.org/
[image01]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/713a8abb-a7d2-4c83-b1f7-6b39bcbbd748.png
[image02]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/26a0660e-b04a-4eaf-b863-7a4c7a72c87f.png
[image03]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/86a88814-86a5-4236-9edf-208d18215ea4.png
[image04]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d4290ab8-3c0d-401b-ae7c-4828b784280f.png
[image05]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d701d0de-1b86-450b-9d2e-b302a5aae7e6.png
[image06]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8b3a6449-c759-4263-9198-87bd29e2b44d.png
[image07]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/688c2958-0016-4d3b-8699-18477d524f8b.png

An introduction to open source data modeling in Cube’s headless BI platform.

Cube’s Open Source Data Modeling Language

Cube has been an [open-source project](https://github.com/cube-js/cube.js) since 2018. We try our best to listen to the community and our users to make it the best analytics API server on the market today.

We really appreciate all the help and sincere dedication our lovely community has done to provide feedback, submit pull requests, and feature ideas to improve Cube even more. We hope the soon-to-be 12,000 stars on GitHub are a representation of our dedication to making our community happy.

In this article, I want to walk you through creating a new Cube App deployment on Cube Cloud and connecting it to your database.

If you prefer watching a video instead, have a look at this short 5 minute tutorial on our YouTube channel.

<div><div style={{position: 'relative', paddingTop: '56.25%'}}><iframe src="https://www.youtube.com/embed/HVZ4ytDEjqc" style={{position: 'absolute', top: 0, left: 0, width: '100%', height: '100%'}} frameBorder="0" allowFullScreen></iframe></div></div>

Before moving on I want to share a bit about the benefits of using Cube Cloud vs. self-hosting Cube yourself.

## Why use Cube Cloud?

We [launched Cube Cloud this October](https://cube.dev/blog/announcing-cube-cloud/) with the vision of making Cube accessible to everyone, no matter the scale and DevOps experience level.

You already know Cube is an open-source project with almost 12,000 stars on GitHub. It's packed full of cool features, such as auto-generating schemas, auto-generating dashboards, a developer playground, and much more. It's easy to work with and developer-friendly.

Self-hosting Cube can come with its own set of complications due and pitfalls. Let me explain.

### Managed Infrastructure

[Cube requires several pieces of infrastructure](https://cube.dev/docs/deployment/production-checklist) to work reliably at scale. To get both high concurrency and high data size you need:

- Compute resources
- Redis
- Cube Store
- An API gateway
- A refresh worker
- A load balancer
- optionally, a VPC configuration

You can mitigate this by using infrastructure as code tools like AWS CloudFormation, Pulumi, or Terraform, and using Kubernetes as a container orchestrator.

![https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png)

### Monitoring and Scaling

Maintaining application up-time is a serious responsibility. To achieve this with self-hosting, you have to make sure that monitoring is configured for Cube and that you can scale Cube up if there's a spike in traffic.

With Cube Cloud, both monitoring and auto-scaling are available out-of-the-box.

You also get query monitoring in the Queries tab in the sidebar. The entire query history is available, including metrics like query result size and time spent, helping you dig into why some queries take longer than others.

![frame_chrome_mac_light_(72).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0844659b-c006-4d75-98b8-7984a82fee81.png)

You can see application performance metrics in the Metrics tab.

![frame_chrome_mac_light_(73).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b6d12fca-de08-46f8-b736-930969e651a4.png)

### Caching

A major problem when scaling Cube apps is optimizing pre-aggregation build times. Similarly, when trying to determine the status of your pre-aggregations; you need to know whether they're built or not and when they were built.

In Cube Cloud, we can check out the Pre-Aggregations tab to see the status of all pre-aggregations in your Cube deployment, including whether they're building or not and even which of the partitions, if any, are being refreshed.

![frame_chrome_mac_light_(74).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7f43d038-6d95-4e91-bd31-90b8a6feca2c.png)

In production, these pre-aggregations are kept up-to-date automatically for you.

### Development Tools

You get GitHub integration with Cube Cloud. Your deployment can watch a GitHub repository for changes and deploy updates. Connect a deployment to GitHub from the Settings page and you're set.

![frame_chrome_mac_light_(75).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3b1bf669-7c95-4746-baa0-951bfe2e6f60.png)

You can also make changes directly in the Cube Cloud console by entering Development Mode.

![frame_chrome_mac_light_(76).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/3c74ca7f-58b7-44c4-9e88-d719ab93b325.png)

You get a dedicated Git branch, complete with a personal API URL and separate pre-aggregations.

### Support

If you have paying customers, having paid support is a lifesaver. If something breaks, and you need help fixing it, having someone to call is a lifesaver. Cube Cloud comes with support on the Enterprise plan, so you can ping us whenever you hit a snag!

## Sign Up to Cube Cloud

Signing up is simple and secure because you can also choose to sign up with your GitHub account. Here's how you sign up.

Jump over to the [sign-up page](https://cubecloud.dev/auth/signup) and enter your email.

![frame_chrome_mac_light_(57).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/651bf8cb-fedf-4f21-bac4-6879ff91633b.png)

Once you click `Sign Up`, you'll receive an email from us to set a password.

![frame_chrome_mac_light_(58).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/376a65f0-d828-4c10-a4b3-c56f8a86e5a9.png)

Next, you select a subdomain for your Cube Cloud account.

![frame_chrome_mac_light_(59).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/eab870f8-a430-4048-84d4-38e0c7238e08.png)

With all this out of the way, you can finally start creating Cube Deployments.

![frame_chrome_mac_light_(60).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4c4c987d-dfa1-4591-8464-a3ec76a2f820.png)

Let me explain how to create a deployment.

## Create a Deployment in Cube Cloud

Creating a deployment consists of four main steps.

1. Create Deployment
2. Set Up the Cube App
3. Set Up the Database Connection
4. Generate the Data Schema

Let me break it down into even more detail.

## Step 1 - Create Deployment

The first step of creating a Cube App from scratch in Cube Cloud is to create a deployment.

Once you click the `create deployment` button it'll open up the first step in the deployment creation UI flow.

![frame_chrome_mac_light_(61).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8edd42ca-a308-4cc5-a358-fb6324a28298.png)

Here you choose what name to give your deployment and pick the cloud provider and region. The reason why to choose either AWS or GCP is based on where you're hosting your own infrastructure and apps.

Let me give you an example. Let's say you have a metrics dashboard that needs to consume data on `us-east-1` on AWS. Using the same AWS region to host the Cube deployment as where you are hosting this metrics dashboard will reduce the network delay and latency between Cube and your app.

Which database you're using is also important when choosing a cloud provider and region. If you have data in BigQuery then you should not choose AWS. If you're using AWS Redshift then vice-versa applies.

### Need a Dedicated VPC?

A reason for using a VPC is if you expect high throughput of up to and above millions of requests per day. Using a VPC will give you a dedicated managed infrastructure that is ready to serve such high numbers of users.

Additionally, if your company required security compliance like SOC2, using a VPC is mandatory.

The best part is that a VPC is readily available for you if you need it. All you need to do is to reach out to us and ask for it. Super simple!

![frame_chrome_mac_light_(62).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/972709c6-6911-4c9e-910c-f316120698d7.png)

But, back to business. Let's get back to creating the Cube app deployment. After selecting a cloud provider and region, add a name for your deployment and click next.

![frame_chrome_mac_light_(63).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f2455031-14b9-49ca-b449-eb113a8deda8.png)

## Step 2 - Set Up the Cube App

Next up, the second step in creating a Cube App from scratch in Cube Cloud is to click the `+ create` button.

![frame_chrome_mac_light_(64).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0fe2ae4e-d596-499d-b26b-5f62f5780683.png)

But, before moving on, I want to ask if you already have a Cube app? If you do, please jump over to my co-worker Hassan's [guide on migrating a Cube app](https://cube.dev/blog/migrating-from-self-hosted-to-cube-cloud/) to Cube Cloud.

In this tutorial, I want to stick to creating a Cube App from scratch, so let me continue where I left off.

## Step 3 - Set Up the Database Connection

After selecting the `+ Create` button and proceeding to create a Cube app from scratch, you'll be prompted to select a database type. This lets you choose from dozens of the most popular databases available on the market today.

![frame_chrome_mac_light_(67).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/81442713-0261-424c-bb09-17b1601c10e0.png)

I'll proceed with selecting PostgreSQL to hook up a sample e-commerce database to show you how simple it really is. The values I'll use are:

```json
Hostname
demo-db.cube.dev

Port
5432

Database
ecom

Username
cube

Password
12345
```

In the UI it'll look exactly like the image below.

![frame_chrome_mac_light_(68).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a0c30616-0a8e-4c85-8f79-4f9ba849eb63.png)

Once you click the `Apply` button, Cube Cloud will test if the connection was successful, and proceed to the next step.

If you run into issues here, make sure to allow the Cube Cloud IPs to access your database. This means you need to open up your firewall to these IPs. In the case of AWS, this would mean adding a security group with allowed IPs.

## Step 4 - Generate the Data Schema

Step four in this case consists of generating a data schema. Start by selecting the database tables to generate the data schema from, then hit `generate`.

![frame_chrome_mac_light_(69).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a959643f-4e0f-4f62-9dc9-4d48a0405002.png)

Cube Cloud will generate the data schema and spin up your Cube deployment. With this, you're done. You've created a Cube deployment, configured a database connection, and generated a data schema!

![frame_chrome_mac_light_(70).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4cd2de24-098f-465e-b7ee-dbadd3e82ab9.png)

You're ready to run queries in the Playground.

## Running Queries in Cube Cloud's Playground

By clicking on the Playground in the left nav you'll open the Playground UI.

Here you can test queries by selecting from all the measures and dimensions in the data schema. This lets you test different dashboards and visualizations for when you want to build your own metrics dashboards.

The image below showcases a bar chart with an **`Orders Count` measure**, an `**Orders Status` dimension**, and an `**Orders Created At` time dimension** **grouped by day for the current month**.

![frame_chrome_mac_light_(71).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/56faf279-2d8c-4d98-97d3-cb960ea5413a.png)

You can use the playground as a testing ground for all queries you want to run in your own dashboards. The playground will also display how long a query took, showing you the response time above the chart itself.

That's it! You've finished creating a Cube Deployment on Cube Cloud.

### What About Query Acceleration?

Congrats! You've configured a Cube app in Cube Cloud. You're ready to add pre-aggregations and run the deployment in cluster mode. We have detailed tutorials explaining these concepts for you to read [here](https://cube.dev/blog/cube-cloud-deep-dive-mastering-pre-aggregations/). You can follow along with the tutorial to add pre-aggregations to your data yourself.

We at Cube also hosted a [workshop about pre-aggregations](https://www.youtube.com/watch?v=1_BP3B_wbec) that you can take a look at to learn more about query acceleration.

## Conclusion

Deploying a Cube App on Cube Cloud consists of four steps. First, select the cloud provider, region, and name. Second, select creating a Cube app from scratch, Third, connect a database. Fourth, generate a data schema.

You should keep in mind there are three ways of deploying Cube apps in Cube Cloud. You can create one from scratch, with the Cube CLI, or with the GitHub integration.

If you choose to use Cube Cloud, you'll get the added benefit of getting managed infrastructure that auto-scales to your needs. Cube Cloud also has some added features compared to self-hosting Cube, including query performance analysis, query response times, APM, and pre-aggregations enabled by default.

I hope you end up trying out Cube Cloud because you'll save time and energy by not managing infra and just focusing on building product value.

[Sign up to Cube Cloud here](https://cube.dev/cloud) to try it out for yourself.

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Adnan Rahic

Cube Cloud Deep Dive: Starting a New Cube App

A few weeks ago, we've launched [Cube Cloud](https://cube.dev/cloud). We built Cube Cloud to help address some of the most common issues we heard from our users, and in today's blog post, I'd like to show you how and why to migrate to Cube Cloud. With Cube Cloud, you get convenient developer tools and fully managed infrastructure for Cube configured with best practices in mind.

## Why should I use Cube Cloud?

First, I want to show you some of the responsibilities you take on when running a self-hosted instance of Cube versus letting Cube Cloud manage the infrastructure for you.

As a busy application developer, time spent on setting up and maintaining various parts of infrastructure is often time taken away from implementing application bug fixes and features. Let's go over some of the reasons benefits Cube Cloud has over a self-hosted Cube installation.

## Infrastructure

While Cube is fairly easy to set up, as application developers, we're more interested in managing our application's complexity as opposed to its operational complexity. [Cube requires several pieces of infrastructure](https://cube.dev/docs/deployment/production-checklist) to work reliably at high scales of both concurrency and data size. This includes:

- Compute resources
- Redis
- Cube Store
- An API gateway
- A refresh worker
- A load balancer
- optionally, a VPC configuration for the above resources

![Cube architecture diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/783e7e2c-5205-4914-a96d-acfe3a136e65.png)

With so many moving parts, you might reduce the deployment complexity by defining your cloud infrastructure as code (e.g., using AWS CDK, Pulumi, or Terraform) and employing a workload orchestrator (e.g., Kubernetes or Nomad). If you have multiple developers working on the project, infrastructure as code would also helps provide them with separate development environments as well as a staging environment before production.

### Monitoring and Scaling

At scale, [Murphy's Law](https://en.wikipedia.org/wiki/Murphy%27s_law) rings truer than ever, and achieving and maintaining application uptime becomes a serious responsibility. To achieve this with self-hosting, we must ensure that monitoring is configured for Cube, and that we can scale Cube up if there's a spike in traffic.

With Cube Cloud, both monitoring and autoscaling are available out of the box. From a developer perspective, it feels like a serverless experience: nothing to spin up and you're only charged for usage. Cube Cloud is able to determine when a particular resource (e.g., the number of API instances) needs to be scaled, and no fiddling around with instance types and security groups is required, unlike on other cloud platforms.

Monitoring queries sent to your Cube Cloud cluster is easy, just click Queries from the sidebar and et voilà! The entire query history is available, including important metrics such as query result size and time spent, helping you dig into why some queries take longer than others.

![Recent queries made to Cube Cloud on the Queries page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/16526c40-abf5-4fdb-b356-a3be560cc2de.png)

Application performance metrics can be found on the Metrics page:

![Metrics for Cube on the Cube Cloud Metrics page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e66c97b4-fef5-4908-9810-dc034243379c.png)

![Metrics for database connections on the Cube Cloud Metrics page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4e488685-1492-4ef5-aab8-1bcb86d1c6d3.png)

Scaling is just as simple; just make sure the deployment is in Cluster mode and that's it - Cube Cloud will scale up or down based on how many requests are coming in. This is **[huge](https://c.tenor.com/17pCTXlqaHMAAAAM/donald-trump-yuge.gif)** for applications with growing datasets or variable traffic.

### Caching

Another scale problem is trying to optimise pre-aggregation build times. Trying to determine the status of configured pre-aggregations, whether they were built or not, when they were built etc. is not usually a one-click operation when self-hosting Cube.

In Cube Cloud, we can check out the Pre-Aggregations page to see the status of all pre-aggregations in our Cube deployment, including whether they're building or not and even which of the partitions, if any, are being refreshed. In production, these pre-aggregations are kept up-to-date automatically for you.

![Cube Cloud Pre-Aggregations page](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/32a47d34-c1e0-4fbe-b87b-b3e36457784a.png)

### Development Tools

Cube Cloud can watch a GitHub repository for changes and deploy. Connect a deployment to GitHub from the Settings page and you're off to the races.

![Cube Cloud Build and Deploy settings](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/a42597dc-98c8-48fa-b6a1-445cfd252c53.png)

You can make one-off changes directly in the Cube Cloud console by going to the Schema page and entering Development Mode:

![Entering Development Mode in Cube Cloud](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b0580a29-f724-4052-a10a-0ff984b18f5f.png)

Developers even get their own personal Git branches, complete with a personal API URL and separate pre-aggregations:

![Personal Git branch in Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c3d12fba-4d30-4682-817e-c090beecb0b0.png)

### Support

For an application with paying customers, having paid support is a lifesaver: if something isn't working properly, developers need to be able to communicate with someone. Cube Cloud comes with support on the Enterprise plan, so you can reach out with an email:

![Email to Cube Cloud support](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d587f935-9e71-4e16-96fb-166c48825be0.png)

## How do I migrate to Cube Cloud?

Now that we've recapped how Cube Cloud compares to self-hosting, let's have a quick look at how we can move a project to Cube Cloud.

### GitHub

Cube Cloud has first-class support for repositories hosted on GitHub; start by signing in to your account (if you don't have one, [sign up here](https://cube.dev/cloud)) and create a new deployment:

![Creating a new deployment on Cube Cloud](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f2786006-a4f2-433f-b77f-109b4704fd2d.png)

Choose the 'Import from a GitHub repository' option and connect your GitHub account to Cube Cloud:

> Using a monorepo? Remember to fill in the "Project Directory (for monorepos)" field so Cube Cloud can locate your Cube project inside the Git repository.

![Connecting Cube Cloud to a GitHub repository](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f5bde911-1072-46ec-839c-cb6c5820aa3e.png)

On the next page, choose your database and enter its credentials:

> Is your database within a VPC? Contact us by clicking on the VPCs button in the top-right.

![Choosing a database for a new Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/054e726a-6ff9-44fe-8cb9-05b3f55a8527.png)

![Configuring database credentials for a new Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/07302615-c905-4ce2-b87e-57c873571cb8.png)

### Git

Not using GitHub to host your repositories? Not a problem; Cube Cloud deployments are de-facto Git repositories. Make sure your deployment is configured to use Git:

![Configuring Cube Cloud as a Git remote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/bbc7dc5d-7f77-4008-a7f2-9b888cb39d5b.png)

Copy the CLI commands and run them in your local copy of the Cube project to add Cube Cloud as an origin. You'll need to generate Git credentials for authentication; you can also do that from the same page.

### Authentication

Cube Cloud supports the same JWK-based authentication that's available in open-source Cube; we just need to set a few environment variables first. Let's go to our deployment's settings page, and add the necessary configuration:

![Configuring JWK authentication in a Cube Clpud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/0399eab3-9bcb-4dc9-9782-f56f7a1bb3bc.png)

### Custom domains

Cube Cloud has support for custom domains, too. Add a new `CNAME` record to your DNS pointing at the host part of your deployment's API URL. In the screenshot below, the `CNAME` value would be `strong-elk.gcp.us-central1.cubecloudapp.dev`:

![Configuring custom domain on a Cube Clpud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/85aa1066-37ee-4e42-b20b-50b2c8b1d8b4.png)

Then come back to Cube Cloud and enter your custom domain, then click the 'Add' button.

### Custom NPM packages

> Custom packages are disabled by default. Shoot us an email at support@cube.dev to get it enabled for your account
>

Some Cube projects make use of third-party code from NPM; some projects may use custom drivers for example, or an error logging library such as Sentry. Cube Cloud fully supports installing dependencies from the NPM registry, simply ensure that the required dependencies are listed under the `dependencies` section of the Cube project's `package.json`.

### Multiple databases

Cube Cloud also supports connecting to multiple databases, just as in the open-source version of Cube. When creating a new deployment, simply skip the database connection wizard and go to the Environment Variables tab on the Settings page. There, simply add the required connection details as variables for each additional database:

![Configuring environment variables for multiple databases for a Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/98cf2c85-9d1f-4ea9-a744-0bdc0858c8aa.png)

Make sure the same environment variables are being used in your `cube.js` configuration file:

![Configuring driverFactory() for multiple databases for a Cube Cloud deployment](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ed8debbb-f384-44ca-a114-272476643c46.png)

Just like the open-source version of Cube, Cube Cloud can now communicate with however many databases your application needs. 💯

## Wrapping up

In an ideal world, I would get to focus solely on the needs of my users, not my infrastructure. Cube Cloud takes care of the infrastructure and provides me with convenient developer tools, letting me put my efforts into creating business value. Cube Cloud has a [free tier](https://cube.dev/pricing) for development projects and proof-of-concepts, and provides autoscaling, cache storage, and pre-aggregation warm-up along with other features out-of-the-box.

This post aimed to provide some perspectives into why migrating to Cube Cloud is a good investment for your application, as well as demonstrating the ease of moving a self-hosted project to Cube Cloud. It's really easy to deploy and grows with your application, all while minimising your operational responsibilities so you can focus on what matters; your users. Cube Cloud is now open for registration, sign up today by [clicking here](https://cube.dev/cloud).

Hope you all enjoyed reading this as much as I enjoyed writing it. Until next time 👋

Hassan Khan

Migrating from self-hosted to Cube Cloud

With datasets becoming enormous, developers need to find new ways of making data apps fast, responsive, and cost-effective. [Pre-aggregations](https://cube.dev/docs/schema/reference/pre-aggregations), one of the most powerful features of Cube, simplify this monumental feat significantly.

Today, I want to talk about using and managing pre-aggregations with [Cube](https://github.com/cube-js/cube.js), an open-source API layer for data apps, and [Cube Cloud](https://cube.dev/cloud), a fully managed platform that runs and scales Cube apps in production.

I want to give you a detailed insight into what pre-aggregations are, which scenarios you might encounter while working with them, and how both self-hosted Cube and Cube Cloud can help in solving them.

### What are Pre-Aggregations

[Pre-aggregations](https://cube.dev/docs/caching/using-pre-aggregations) are condensed versions of the source data. They are materialized ahead of time and persisted as tables separately from the raw data. Querying pre-aggregations reduces the execution time, as the queried data is orders of magnitude smaller than the raw data.

![pre-agg-1.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/705caebb-6a3f-4309-a235-f4e5a8c0ff79.png)

Pre-aggregations are highly recommended to be used in virtually any Cube app in [production](https://cube.dev/docs/deployment/production-checklist#set-up-refresh-worker). Let's talk about popular scenarios you might encounter while working with pre-aggregations.

### How to Store Pre-Aggregations

Pre-aggregation data should be stored separately from the raw data. The recommended way is to store [pre-aggregations in Cube Store](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage), a purposefully built and performant storage layer. How do you set up and run Cube Store?

**Self-hosted Cube.** When developing Cube apps locally and running Cube in [dev mode](https://cube.dev/docs/configuration/overview#development-mode), you don't need to set up anything. Cube Store will run automatically within the Cube process, and pre-aggregation data will be stored as files under the `.cubestore` directory.

Running Cube in production will require setting up more complex infrastructure. You need an API instance, a refresh worker, a Redis instance as well as a [Cube Store cluster](https://cube.dev/docs/caching/running-in-production) that will consist of a single router node and at least a couple of worker nodes. If needed, you'll scale Cube Store by adding more worker nodes.

![https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4f6d753e-77d2-4d46-964f-8d2512ff9a18.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4f6d753e-77d2-4d46-964f-8d2512ff9a18.png)

**Cube Cloud.** When developing and running Cube apps in Cube Cloud, you can use readily available managed infrastructure that includes a Cube Store cluster. Cube Cloud will automatically scale the Cube Store cluster as needed and allocate other services such as a refresh worker once you select "Cluster mode" in Settings.

![frame_chrome_mac_light_(22).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e48c2422-47ee-4ecd-b4c2-a904d8e08e76.png)

### How to Create a Pre-Aggregation

Queries won't be accelerated until pre-aggregations are defined and built.

**Self-hosted Cube**. You can write the pre-aggregation definition by hand. Please see the [docs on pre-aggregation definitions](https://cube.dev/docs/schema/reference/pre-aggregations) in the data schema where every parameter is explained.

However, what I like doing is to run the query I want to accelerate first. Then, use the Rollup Designer by clicking on the `Query was not accelerated with pre-aggregation →` link to configure a pre-aggregation definition.

![frame_chrome_mac_light_(26).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4ab0f53d-2b62-4941-b412-a41bad298a62.png)

In the `Members` section you see the measures, dimensions, and time dimensions the pre-aggregation will be configured to accelerate. These need to match the query in order for it to be accelerated. You can make sure to get a match in the `Query Compatibility` tab.

![frame_chrome_mac_light_(27).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/64a0d150-d43d-4418-b784-ace8f161c26e.png)

You can make further improvements to the pre-aggregation's performance with partitions, refreshing, and incremental builds.

![frame_chrome_mac_light_(28).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b2adf968-879b-4a2d-a3d4-379aad60d16d.png)

Once you're happy with the configuration in the Rollup Designer, click the `Add to the Data Schema` button to add the pre-aggregation to the schema file.

![frame_chrome_mac_light_(29).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/6b8d0d96-1b7e-4323-8507-73bf0f00d740.png)

**Cube Cloud**. The process is exactly the same for Cube Cloud, with one added convenience. You can also use Rollup Designer on the Schema page when you're working with the data schema.

![frame_chrome_mac_light_(47).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8f5f86b5-70e4-4ff9-b8c1-99b348912739.png)

### How to Build Pre-Aggregations

Pre-aggregations are built on-demand or based on a schedule. The build process always takes time.

**Self-hosted Cube.** Pre-aggregation builds can be triggered in two ways, either by a Cube refresh worker or on-demand when the Cube API instance gets a query that matches a pre-aggregation definition.

In the latter case, the API will trigger a build of the pre-aggregation and be stuck waiting for it to finish in order to return a value. You **do not want this to happen**, ever!

A Cube refresh worker will trigger pre-aggregation builds periodically and make sure Cube Store always has pre-aggregations ready and waiting to be queried. 

**Cube Cloud.** Enabling the warm-up of pre-aggregations before deploying the API will make sure to first **build all** pre-aggregations before deploying a new version of the API. You do this under Settings → Resources by enabling the `Warm-up pre-aggregations before deploy API` toggle.

![frame_chrome_mac_light_(23).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/96a7e0d3-b5d8-40d4-a636-79e9b41a456f.png)

You can also build pre-aggregations manually with the `Build All` button, or select individual partitions to build. This is helpful when you explicitly need to re-build pre-aggregations. It might be because you deployed a new version of your Cube app or you edited an existing pre-aggregation definition. Another option might be if you decide to disable warming up the pre-aggregations before deploying the API.

![frame_chrome_mac_light_(32).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e669724e-85ec-4ed2-a02a-a4b0859d71ce.png)

### How to Optimize Pre-Aggregation Build Times

The time it takes to build pre-aggregations can vary based on the size of the partitions. The bigger a partition is, the longer it will take to build. Additionally, a major pitfall is not having detailed insight into when pre-aggregation builds are triggered and how long they took.

**Self-hosted Cube**. The two key factors that improve pre-aggregation build times are the `partitionGranularity` and `refreshKey` parameters.

The `partitionGranularity` [defines a partition](https://cube.dev/docs/caching/using-pre-aggregations#partitioning) based on a time dimension. A partition is a shard of data. The process of partitioning will shard data into multiple tables. If you set the `partitionGranularity: 'month'`, it will partition the data into one table per month.

Partitioning is a huge performance improvement for pre-aggregation build and refresh times. This is improved further by incrementally refreshing only the last set of partitions. It leads to less data being re-built, meaning faster builds with reduced cost.

The `refreshKey` parameter is, [by default, set to every `1 hour`](https://cube.dev/docs/caching/using-pre-aggregations#refresh-strategy). We set it to every 1 hour by default as it fits most generic use cases. Having a `refreshKey` with an `every` field defines the frequency with which the pre-aggregation is rebuilt. Every 1 hour would mean on the hour, every hour. Meaning at 12:00 AM, 1:00 AM, 2:00 AM, and so on.

Adding the `incremental: true` parameter will refresh the most recent partition, which is incredibly useful if you are working with historical data that won't change.

Lastly, adding a `updateWindow: '7 day'` means going back for a specified amount of time and refreshing the partitions that fit into that time frame.

**Cube Cloud**. Building pre-aggregations without `partitionGranularity` takes longer than building them as partitions.

Using a pre-aggregation definition *with* partitioning, versus *without* partitioning does not look very different. In the sample below you can see that adding partitioning only requires three lines of code.

```jsx
countCreatedAtByDay: {
  measures: [Orders.count],
  dimensions: [Orders.status],
  refreshKey: {
    every: `1 hour`,
    updateWindow: `7 day`, // refresh partitions in this timeframe
    incremental: true // only refresh the most recent partition
  },
  partitionGranularity: `month`, // adds partitioning by month
  timeDimension: Orders.createdAt,
  granularity: `day`
},
```

However, the build times speak for themselves.

![frame_chrome_mac_light_(48).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d3b29fb4-1b3b-4325-924c-316d308648c8.png)

Additionally, you get detailed insight into when pre-aggregations were last built and exactly how long the builds took.

### How to Check the Status of Pre-Aggregations and Partitions

After adding pre-aggregation definitions to your schema, you need to be aware if pre-aggregations have been successfully built. Not knowing pre-aggregation build history, if partitions were built at all and when, how long the builds took, can all negatively impact user experience.

If you have multiple pre-aggregations with different or complex `refreshKey`s, you need to know how up-to-date the pre-aggregated data is and when the pre-aggregation is going to be rebuilt.

This will make sure your users are seeing up-to-date data with peak performance at all times.

**Self-hosted Cube**. The Cube refresh worker and Cube Store routers log the state of pre-aggregations. You can see when the execution, loading, or saving of a pre-aggregation happens. This is what a sample log from the Cube Store routers looks like.

```yaml
Executing Load Pre Aggregation SQL: scheduler-40686894-bd8f-417c-a341-af4c1e4e6bf6 
--
  SELECT
      "orders".status "orders__status", date_trunc('day', ("orders".created_at::timestamptz AT TIME ZONE 'UTC')) "orders__created_at_day", count("orders".id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".created_at >= '2023-11-01T00:00:00.000Z'::timestamptz AND "orders".created_at <= '2023-11-30T23:59:59.999Z'::timestamptz) GROUP BY 1, 2
--
2021-09-08 08:44:34,959 INFO  [cubestore::cluster] <pid:1> Running job completed (12.55392ms): IdRow { id: 535, row: Job { row_reference: Table(Tables, 535), job_type: TableImportCSV("temp://dev_pre_aggregations.orders_main20231101_z034ipqc_sntekhp5_1gjgtvi.csv.gz"), last_heart_beat: 2021-09-08T08:44:34.958894076Z, status: Completed } }
```

Cube Store supports a subset of MySQL protocol. It exposes port `3306`. You can use the [standard MySQL CLI client](https://www.configserverfirewall.com/ubuntu-linux/ubuntu-install-mysql-client/) to connect to Cube Store, or any other client compatible with the MySQL protocol.

You connect to Cube Store with the MySQL CLI client like this:

```
mysql -h <CUBESTORE_IP> --user=cubestore -pcubestore
```

Once connected to the MySQL CLI, run this SQL command to see which tables are stored in Cube Store.

```yaml
mysql> SELECT * FROM information_schema.tables;
+----------------------+-----------------------------------------------+
| table_schema         | table_name                                    |
+----------------------+-----------------------------------------------+
| dev_pre_aggregations | orders_main20210101_5qjmf21t_nm2ig41s_1gjgraf |
| dev_pre_aggregations | orders_main20210201_uobzqlem_sz20hm0u_1gjgrag |
| dev_pre_aggregations | orders_main20210301_wslzhgsp_qyh1uclp_1gjgrah |
| dev_pre_aggregations | orders_main20210401_3saakqgn_gsq0jl30_1gjgrai |
| dev_pre_aggregations | orders_main20210501_u0u2qipc_eserpdse_1gjgrai |
| dev_pre_aggregations | orders_main20210601_recoxqse_pwslyqod_1gjgraj |
| dev_pre_aggregations | orders_main20210701_yfs1mnjz_q5l4tjwz_1gjgraj |
| dev_pre_aggregations | orders_main20210801_frhjq5yg_hevt225i_1gjgrak |
| dev_pre_aggregations | orders_main20210901_zceilsss_saxibkj0_1gjgral |
| dev_pre_aggregations | orders_main20211001_bykzagbx_frt1vmhn_1gjgral |
| dev_pre_aggregations | orders_main20211101_ooxvqjhl_rikgvl0q_1gjgram |
| dev_pre_aggregations | orders_main20211201_4lz4evnw_tuyd1tz_1gjgran  |
| dev_pre_aggregations | orders_main20211001_2qjkvn24_apusbfpo_1gjgrjb |
| dev_pre_aggregations | orders_main20210601_irjg1lj1_vhtdn2q_1gjgrjb  |
| dev_pre_aggregations | orders_main20210401_sb5f5vwh_jbkn43cn_1gjgrjb |
| dev_pre_aggregations | orders_main20210901_wzxpxmyl_qs3yhswd_1gjgrjb |
| dev_pre_aggregations | orders_main20210501_0zj2jntl_c0ku1ixv_1gjgrjb |
| dev_pre_aggregations | orders_main20211101_ekkyobcj_uld514mr_1gjgrjb |
| dev_pre_aggregations | orders_main20210301_2cbcp1up_tmvran0t_1gjgrjb |
| dev_pre_aggregations | orders_main20210701_gmqrnxo2_igl1uczp_1gjgrjb |
| dev_pre_aggregations | orders_main20210101_j3kmnnak_msfmdqko_1gjgrjb |
| dev_pre_aggregations | orders_main20210201_vfcrnd42_5gpbrwfj_1gjgrjb |
| dev_pre_aggregations | orders_main20210801_rozcl2rd_t5nefwhv_1gjgrjb |
| dev_pre_aggregations | orders_main20211201_255cpz4u_e43gsbpt_1gjgrjb |
+----------------------+-----------------------------------------------+
24 rows in set (0.00 sec)
```

**Cube Cloud** goes a step further and simplifies the use of pre-aggregations significantly. If you select the pre-aggregations tab, you can view all your pre-aggregations in one place. This lets you view build history, partitions, build times, refresh times, indices, and much more. 

![frame_chrome_mac_light_(33).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/276f41bd-8b1c-4876-b995-2edd4c259fcd.png)

### How to Track Pre-Aggregation Build History Over Time

Having a detailed history of when pre-aggregation builds happen can help determine issues with stale data or even failing builds.

**Self-hosted Cube**. When a Cube Refresh Worker triggers a pre-aggregation build, and Cube Store runs the actual build, it will always log a message with a timestamp. But, these logs are ephemeral, and can often get lost in the `stdout` stream.

You can tail these logs and track build history over time. However, a much better option would be to use a custom logger like [Winston](https://github.com/winstonjs/winston) or dedicated log monitoring agent like [Fluentbit](https://fluentbit.io/) to forward these logs to a central location like [Elasticsearch](https://www.elastic.co/log-monitoring) or any [Log Management tool on the market](https://www.g2.com/categories/log-monitoring?utf8=%E2%9C%93&order=g2_score).

Here's a guide from our [docs showing how to forward logs to Loggly](https://cube.dev/docs/product/data-modeling/advanced/code-reusability-extending-cubes).

**Cube Store** simplifies this process by giving you a Build History tab to get a list of when every pre-aggregation build happened.

![frame_chrome_mac_light_(34).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5f1ae86e-19a9-490a-86ef-74aeafec2d76.png)

You can also see the anatomy of pre-aggregation names on hover. 

### How to Preview Pre-Aggregation Data

Even though you know pre-aggregation builds are successful, you still don't know if the data is valid. You need a way to preview the tables and their content in Cube Store to be absolutely sure.

**Self-hosted Cube**. You can preview the pre-aggregation tables the same way you looked into the state of pre-aggregations, by connecting to Cube Store with the MySQL CLI client, or any other client you prefer.

Use a `SELECT` command to see the content of a pre-aggregation table.

```yaml
mysql> select * from dev_pre_aggregations.orders_main20231101_ayw2ads4_sntekhp5_1gjk5dh;
+----------------+--------------------------+---------------+
| orders__status | orders__created_at_day   | orders__count |
+----------------+--------------------------+---------------+
| completed      | 2023-11-02T00:00:00.000Z |             4 |
| completed      | 2023-11-03T00:00:00.000Z |             2 |
| completed      | 2023-11-04T00:00:00.000Z |             2 |
| processing     | 2023-11-02T00:00:00.000Z |             4 |
| shipped        | 2023-11-01T00:00:00.000Z |             1 |
| shipped        | 2023-11-02T00:00:00.000Z |             1 |
| shipped        | 2023-11-03T00:00:00.000Z |             4 |
| shipped        | 2023-11-04T00:00:00.000Z |             1 |
| shipped        | 2023-11-05T00:00:00.000Z |             1 |
+----------------+--------------------------+---------------+
9 rows in set (0.01 sec)
```

In the example above, the pre-aggregation table is stored in Cube Store. Alternatively, you can also [store pre-aggregation data in cloud storage](https://cube.dev/docs/caching/using-pre-aggregations#optimizing-pre-aggregation-build-times-export-bucket) like AWS S3 or GCS. To view the data, access the S3 or GCS bucket directly through your cloud provider.

Sadly, previewing pre-aggregation tables in Cube Store is rarely done when using self-hosted Cube simply because there are a few hoops to jump through.

**Cube Cloud** makes it much easier with a dedicated `Preview` tab that's visible after drilling down to a single pre-aggregation.

![frame_chrome_mac_light_(35).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/dfa7e141-af06-49a3-8ee4-75444f9b412a.png)

Can you see the similarity to the sample drawing from the beginning of the article? You can clearly see how rows are grouped similarly to how a `GROUP BY` statement works in SQL.

### How to Serve Requests Only from Pre-Aggregations

Serving requests solely from pre-aggregations is a great way to keep query latency low, but there is a significant overhead you need to be aware of in order to enable it.

You need to have a Cube cluster configured with a Cube refresh worker and a Cube Store cluster. This ensures pre-aggregations are **always** available for queries. It's not before then you can set the Cube API instance to only return data from pre-aggregations.

**Self-hosted Cube**. You use the `CUBEJS_ROLLUP_ONLY` environment variable and set it to `true` in the Cube API instance. This prevents serving data from the source database and building pre-aggregations on-demand.

**Cube Cloud**. You enable the `Rollup Only Mode` toggle in the settings.

![frame_chrome_mac_light_(36).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/67876d9b-d1e6-431b-8aa5-61cd5efaa915.png)

### Conclusion

Self-hosted Cube deployments are equipped with a decent set of tools and options to work with pre-aggregations. However, Cube Cloud provides a few additional tools as well as the managed infrastructure to make working with pre-aggregations hassle-free.

You can [register for Cube Cloud](https://cube.dev/cloud) right away!

I'd love to hear your feedback about Cube Cloud in the Cube Community Slack. [Click here to join](https://slack.cube.dev/)!

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Cube Cloud Deep Dive: Mastering Pre-Aggregations

Sometimes in our daily data visualization, we need to merge several similar data sources so that we can manipulate everything as one solid bunch of data. 

For example, we may have an omnichannel shop where online and offline sales are stored in two tables. Or, we may have similar data sources that have only a single common dimension: time. How can we calculate summary metrics for a period? Joining by time is the wrong way because we can’t apply granularity to get the summary data correctly. 

Furthermore, how can we find seasonal patterns from summarized metrics? And how can we get and process data synchronously to track correlations between channels?

Well, the new data blending functionality in version 0.20.0 of Cube.js takes care of all these cases.

>If you’re not familiar with Cube.js yet, please have a look at this [guide](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide). It will show you how to set up the database, start a Cube.js server, and get information about data schemes and analytical cubes.

Please, keep in mind that we used here another dataset:
```js
$ curl http://cube.dev/downloads/ecom2-dump.sql > ecom2-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom2-dump.sql
```

Now let’s dive into the metrics for an example shop and visualize sales by channel and as a summary.

[Here](https://github.com/cube-js/cube.js/tree/master/examples/data-blending) is the full source and [live](https://data-blending-demo.cube.dev/) demo of the example.

I used React to implement this example, but querying in Cube.js works the same way as in Angular, Vue, and vanilla JS.

Our schema has two cubes:

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

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

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

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

and OrdersOffline.js
```js
cube(`OrdersOffline`, {
 sql: `SELECT * FROM public.orders_offline`,

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

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

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

The existence of at least one-time dimension in each cube is a core requirement for merging the data properly. In other words, the data are suitable for blending only if you can present the data on a timeline. Sales statistics or two lists of users that both have an account created date are appropriate datasets for data blending. However, two lists of countries with only a population value can’t be united this way.

## A Special Query Format for Data Blending
A simple and minimalistic approach is to apply data blending to a query object when we retrieve data from our frontend application. The schema and backend don’t need to be changed.
```js
const { resultSet } = useCubeQuery([
   {
     measures: ['Orders.count'],
     timeDimensions: [
       {
         dimension: 'Orders.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
   {
     measures: ['OrdersOffline.count'],
     timeDimensions: [
       {
         dimension: 'OrdersOffline.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
 ]);
```

The blended data is an array of query objects, so we just combine regular Cube.js query objects into an array with a defined dateRange and [granularity](https://cube.dev/docs/query-format#time-dimensions-format?utm_source=blog&utm_medium=post&utm_campaign=data-blending).

As a result, Cube.js returns an array of regular resultSet objects.

![data-blending.png](https://media.graphcms.com/On8dZ6BKRPOx4gyhXels)

But what if we want to do calculations over blended data sources or create custom metrics? For example, how can we define ratios calculated using data from two sources? How can we apply formulas that depend on data from multiple sources?

In this case, we can use another data blending function. We start by setting up a new cube.

## Data Blending Implementation within a Schema

Let’s create AllSales.js inside the schema folder:

```js
cube(`AllSales`, {
 sql: `
 select id, created_at, 'OrdersOffline' row_type from ${OrdersOffline.sql()}
 UNION ALL
 select id, created_at, 'Orders' row_type from ${Orders.sql()}
 `,

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

   onlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'Orders'` }],
   },

   offlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'OrdersOffline'` }],
   },

   onlineRevenuePercentage: {
     sql: `(${onlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   offlineRevenuePercentage: {
     sql: `(${offlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   commonPercentage: {
     sql: `${onlineRevenuePercentage} + ${offlineRevenuePercentage}`,
     type: `number`,
   },
 },

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

   revenueType: {
     sql: `row_type`,
     type: `string`,
   },
 },
});

```

Here we’ve applied a UNION statement to blend data from two tables, but it’s possible to combine even more.

Using this approach, we can easily define and combine values from several blended data sources. We can even use [calculated values](https://cube.dev/docs/measures#calculated-measures?utm_source=blog&utm_medium=post&utm_campaign=data-blending) and SQL formulas.

We can retrieve data from frontend applications and process the results in the usual way:
```js
const { resultSet: result } = useCubeQuery({
   measures: [
     'AllSales.onlineRevenuePercentage',
     'AllSales.offlineRevenuePercentage',
     'AllSales.commonPercentage',
   ],
   timeDimensions: [
     {
       dimension: 'AllSales.createdAt',
       dateRange: ['2022-01-01', '2022-12-31'],
       granularity: 'month',
     },
   ],
 });
```
![data-blending2.png](https://media.graphcms.com/zZxdzysVTjmRlMoSXjVm)

## Conclusion
If we need to visualize data from several sources and apply time granularity to the data, then with data blending, we need to write less code and we can simplify the application logic.

We looked at two ways to implement data blending:
We retrieved data as an array of query objects from a frontend application. This is simple to do and the schema doesn’t need to be modified. We can even merge data from several databases. Furthermore, we can retrieve and process independent data synchronously so that we can visualize it on a timeline.
We blended data by defining a special cube in a schema. This approach allows us to apply aggregate functions to all sources simultaneously and we can define calculated values.

We hope this tutorial will help you to write less code and help to build more creative visualizations. If you have any questions or feedback or you want to share your projects, please use our [Slack](https://slack.cube.dev/) channel or mention us at [Twitter](https://twitter.com/thecubejs).

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

Yulia Kotova

Introducing Data Blending API in Cube

Comparing a single metric over different time periods is a quite common procedure in data analytics. You can evaluate your company’s performance by comparing this year’s revenue with the previous year’s. You can understand which are the low and high sales months in your market, and furthermore, you can predict customer behavior and figure out seasonal patterns.

Analytical systems like Google Analytics, Youtube Studio, and others provide tools for comparing a single metric over different periods of time.

![screen_compare.png](https://media.graphcms.com/HVU1KJcBSPmoRJEoO5sb)

Let’s see how you can implement such analytical visualization with Cube.js.

[Here](https://compare-date-range-demo.cube.dev/) you can see the live demo and the full [source code](https://github.com/cube-js/cube.js/tree/master/examples/compare-date-range) for this example. 

We will explore a sample e-commerce dataset and compare the numbers of orders in the three most recent years. To do this, we need to run a Cube.js server and a simple dashboard application. 

>If you’re not yet familiar with Cube.js, please follow our [guide](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide) to setting up the database, starting a Cube.js server, and getting information about data schemes and analytical cubes.

Now, let’s create the `Orders` cube inside the `schema` folder with the following code:

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

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

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});
```

Here the [`count`](https://cube.dev/docs/types-and-formats#measures-types-count?utm_source=blog&utm_medium=post&utm_campaign=compare-date-range) measure is used to calculate the number of orders. The `createdAt` field is used to retrieve an order’s timestamp.

In the next sections, we will implement the example with React, but you can use the same approach with Vue, Angular, and vanilla JS.

## Multiple Queries for a Single Chart
Here’s how we retrieve the data for each year and parse result sets into the format that is accepted by our charting library:
```js
import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';
import * as moment from 'moment';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result22 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2022-01-01', '2022-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result21 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2021-01-01', '2021-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result20 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2020-01-01', '2020-12-31'],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    const parseResultSet = (resultSet) => {
      return {
        name: moment(
          resultSet.tablePivot()[0]['Orders.createdAt.month']
        ).format('YYYY'),
        data: resultSet
          .tablePivot()
          .map((item) => parseInt(item['Orders.count'])),
      };
    };

    const temp = [
      result22 ? parseResultSet(result22) : [],
      result21 ? parseResultSet(result21) : [],
      result20 ? parseResultSet(result20) : [],
    ];

    setData(temp);
  }, [result22, result21, result20]);

  return <Line data={data} title={'multiple queries'} />;
};
```
Once the data is prepared, we send it to the `<Line />` rendering component and, behold, we create an informative chart:

![compare-date-range visualization](https://media.graphcms.com/Sb7W0PirRte3EO0TmsiK)

The procedure is quite simple, isn’t it? However, as you can see, the code is a little bit messy and repetitive. More importantly, it’s not easy to adapt the code to a different amount of years or even to adjust it dynamically in response to users’ actions or application states.

How do we solve this problem? Let’s try another approach.

## A Single Query for a Single Chart

With the 0.20.0 release of Cube.js, it’s possible to retrieve data for different time periods using a single query. 

The [`compareDateRange`](https://cube.dev/docs/query-format#time-dimensions-format?utm_source=blog&utm_medium=post&utm_campaign=compare-date-range) property accepts an array of two or more different time periods and returns data for all of them in one request. 

We can rewrite our previous code in a simpler way:
```js
import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        compareDateRange: [
          ['2022-01-01', '2022-12-31'],
          ['2021-01-01', '2021-12-31'],
          ['2020-01-01', '2020-12-31'],
        ],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    if (result) {
      const temp = result.series().map((data) => {
        return {
          name: data.key.substring(0, 4),
          data: data.series.map((item) => item.value),
        };
      });
      setData(temp);
    }
  }, [result]);

  return <Line data={data} title={'the single query'} />;
};
```

You can see that this code is much shorter. Furthermore, now we’re able to set the time periods via `compareDateRange` dynamically while keeping our code clean.

Please note that we didn’t make any changes in the `<Line />` rendering component, so the chart will look essentially the same.

I hope that this will help you create useful data visualizations using substantially less code.

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

Comparing Data over Different Time Periods

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, I highly recommend beginning with this [Cube 101 tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) and then coming back here. Also, if you have any questions, don't hesitate to ask them in our [Slack community](http://slack.cube.dev/).

You can check [the source code is available on GitHub.](https://github.com/cube-js/cube.js/tree/master/examples/drill-downs) The short video below shows you what it will look like once complete. And of cource, you can [find other examples at:](https://cube.dev/examples) 

![drill-down-demo.gif](https://media.graphcms.com/KzGwEwQLSEuDtfHiJSZe)

Let's start hacking! 💻

## Defining a Drill Down in the Data Schema

Let's start by setting up a new project with Cube and configuring drill down support in the data schema. We'll use [PostgresQL](https://www.postgresql.org/) and our example e-commerce dataset for this tutorial. You can download and import it by running the following commands.

```bash
$ 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.

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

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

```bash
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 **localhost:4000**.

```bash
$ 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.

![Screen_Shot_2020-06-19_at_7.42.27_PM.png](https://media.graphcms.com/3zOXy0SRyWpBUPHNJEQI)

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.

```js
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.

```js
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](https://cube.dev/docs/@cubejs-client-vue), [Angular](https://cube.dev/docs/@cubejs-client-ngx), or [Vanilla JS](https://cube.dev/docs/reference/frontend/cubejs-client-core).

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.

```jsx
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:

```jsx
{
  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()](https://cube.dev/docs/reference/frontend/cubejs-client-core#drilldown) method. If we run it with the above values, like this:

```js
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.

```json
{
  "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()](https://cube.dev/docs/reference/frontend/cubejs-client-core#chartpivot) and [ResultSet#seriesNames()](https://cube.dev/docs/reference/frontend/cubejs-client-core#seriesnames) methods. [chartPivot()](https://cube.dev/docs/reference/frontend/cubejs-client-core#chartpivot) returns `xValues` for every data row, and [seriesNames(](https://cube.dev/docs/reference/frontend/cubejs-client-core#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.

```js
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:

```diff
-{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.

```js
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.

![Screen_Shot_2020-06-22_at_8.12.55_PM.png](https://media.graphcms.com/EDmUly6kQNGqtm5cKGMF)

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](http://slack.cube.dev/).