Cube Blog

the short answer: use a semantic layer. It's the cleanest way to give the right context and the best place to pull a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU. With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of SQL. We[0] use cube[1] for this. It's the best open source semantic layer, but there's a couple closed source options too. My last company wrote a post on this in 2021[2]. Looks like the acquirer stopped paying for the blog hosting, but the HN post is still up. 0 - https://www.definite.app/ 1 - https://cube.dev/ 2 - https://news.ycombinator.com/item?id=25930190

Was about to say the same. Last year I went through a huge industry comparison exercise for my former company. Spent 6 months evaluating BI tooling. Every option out there is ridiculously expensive. Looker quoted us at $1M/year, though they were using the sticker shock strategy to get us around a $200K/year price point. I came away with the impression that BI is simply not accessible outside of enterprise level orgs. So I love seeing tools like this come into the market. You should also check out https://cube.dev - they're much less out-of-the-box, but super flexible for developers.

There is a new approach of using static site generators to make BI pages feel instantly responsive. - Evidence.dev (https://evidence.dev/) - Observable Framework (https://observablehq.com/framework/) I have found that both the speed and the frontend control either of these tools gives you is pretty good (with Evidence looking better out-of-the-box just in my personal opinion). The main problem I always had with the embedded versions of Tableau, Looker, etc. is that they felt super canned (it was obvious it was a poorly/"lightly" white labeled solution -- when you see an embedded Tableau dashboard, you _know_ it is Tableau, etc) and they were slow. More on this here: https://magis.substack.com/p/an-observation-on-dashboard-speed PS -- I would add that the "headless" version of the above tools that I have seen is https://cube.dev/

I'm not too familiar with https://cube.dev/ - but my initial impression is they are focused more on providing APIs backed by SQL. They have a SQL API that emulates the PostgreSQL wire protocol, whereas Spice implements Arrow and Flight SQL natively. Their pre-aggregations are a similar concept to Spice's data accelerators. It also looks like they have their own query language, whereas Spice is native SQL as well.

We use https://cube.dev/ as intermediate layer between data warehouse database and Superset (and other "terminal" apps for BI like report generators). You define your schema (metrics, dimensions, joins, calculated metrics etc) in cube and then access them by any tool that can connect to SQL db

Yeah, similar to what you and the other commenter from Definite said, we (Delphi)[0] find semantic layers way better for this kind of work than just going straight to a database/data warehouse. One thing you really need with LLMs is consistency. Text-to-SQL kind of lets the LLM do whatever it wants - join tables that shouldn't be joined, define aggregates one way in one query and another way in the next. Because semantic layers define how tables should join, measure definitions, etc., they mean people get consistent results from one query to the next, which builds trust in the LLM. Cube (which was mentioned in another comment and has a great open-source semantic layer) has a good article about that here: https://cube.dev/blog/semantic-layer-the-backbone-of-ai-powered-data-e… [0] https://delphihq.com

It sounds like you only want to build the frontend yourselves? You could take a look at Cube[1] which will probably work fine as a backend for your in-app reporting. [1] https://cube.dev/

Here's the Retool guide (https://cube.dev/blog/building-an-internal-dashboard-with-retool-and-c… but I don't think Retool should be compared to Delphi; one is a low-code tool builder; the other one is a conversational interface for the semantic layer. Both are great for their purposes, both can be used with Cube, even at the same time :-)

Yep. Cube would automatically figure out the join path for you on top of the defined join graph in the data model using the Dijkstra algorithm. The best practice however is to use views: https://cube.dev/docs/schema/reference/view/#views. Those can be used to explicitly control join paths and get an effect similar to what Looker Explore can provide.

An alternative to Transform that's done an awesome job to integrate with BI tools is Cube.js Check their website https://cube.dev

I've added BigQuery and run the benchmark and get the results... but due to their ToS , the benchmark results cannot be published. See https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-database The benchmark instructions for BigQuery are here: https://github.com/ClickHouse/ClickBench/tree/main/bigquery If you follow them, just add the .json with the results and regenerate the HTML with the generate-results.sh script. You will get the version of the benchmark page with BigQuery included - for your own use. I wish more vendors will get rid of DeWitt Clause sooner. For example, Snowflake has removed it a few months ago (maybe due to pressure from Databricks), SingleStore also removed the clause recently.

In essence, you may want Hasura/GraphQL if you have no idea how the end-users' query format will look, whereas Cube is well-suited for providing performant results for somewhat predictable sets of queries. Disclaimer: I work on Cube. My colleague Igor explains it better: Hasura is for CRUD with reasonable performance, Cube is for fetching analytical (quantitative) data with sub-second latency and high concurrency. Hasura is great as a general-purpose data backend, Cube is great as an API for analytics. Obviously, they can be used in the same app, e.g., a blog can serve posts and comments from Hasura and show an upvotes/downvotes chart or a rating of most productive commenters via Cube. And here are a few user stories: https://cube.dev/blog/category/user-stories/

Found the term: https://cloud.google.com/terms/service-terms > 7. Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if (a) the public disclosure includes all necessary information to replicate the Tests, and (b) Customer allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test or (ii) disclose the results of any such Test. It looks like that is less restrictive than it used to be. I found a blog post from last year mentioning an additional requirement to obtain Google's prior written consent prior to publishing (for all customers, not just fellow cloud providers) which no longer is included. https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-database

How is this different from something like https://cube.dev/

Also have a look at cube, I saw some similarity, I much prefer your clean approach https://cube.dev/docs/product/data-modeling/overview

Given that the architecture has a semantic layer, you just need to pick one that integrates with DuckDB, e.g. Cube [1]. About distributed DuckDB, have you checked Boiling Data? [2] 1- https://cube.dev/blog/introducing-duckdb-and-motherduck-integrations 2- https://boilingdata.medium.com/lightning-fast-aggregations-by-distribu…

Beware of their quite well hidden opt-out telemetry collection: https://cube.dev/docs/config#options-reference-telemetry It's really quite improper to not have this clearly mentioned anywhere obvious.

Come on. The three relevant ways to access the system via API (Rest, graphql, SQL) + available frontend integrations are top level entries in the documentation (https://cube.dev/docs).

It only works if you are just running queries in console. Any serious work with sql requires all sorts of weird stuff built around it like dbt, cubejs[1]. 1. https://cube.dev/docs/

There is a small list of vendors that do not forbid to run benchmarks with their systems. https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-database That is why there is a small subset of vendors that are being 'attacked' by this comparisons.

> If you're confused by "headless BI", there's a blog post to get you covered (https://cube.dev/blog/headless-bi), but the most concise and developer-friendly way to put it would be that Cube can connect to any data source that speaks SQL (from Postgres to BigQuery to ClickHouse to Materialize) and expose your data, conveniently presented as high-level metrics rather than in terms of tables and columns, via a set of APIs, including REST, GraphQL, and SQL API. I had to scroll halfway down that article to find that "BI" stands for "Business Intelligence"

From the article: > There's a lot of information out there, including from myself about the history and rise [2022], comparing it to an MVC-like approach, or explaining its capabilities. That's why in this article I focus on the why and showcase how to use it in a practical example in the next chapter. [1] https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ [2] https://cube.dev/blog/exploring-the-semantic-layer-through-the-lens-of… [3] https://cube.dev/blog/universal-semantic-layer-capabilities-integratio… My one line definition that I use atm: > A semantic layer acts as an intermediary, translating complex data into understandable user business concepts. It bridges the gap between raw data in databases (such as sales data with various attributes) and actionable insights (such as revenue per store or popular brands). This layer helps business users access and interpret data using familiar terms without needing deep technical knowledge. https://www.ssp.sh/brain/semantic-layer#semantic-layer-definition Edit: I'm the OP.

Nope, those are the primary components of a semantic layer. Most other semantic layer products have three main components: input data sources -- typically SQL queries or table names, configuration -- the semantic layer describing all the things you mention above, relative to the input data sources, and the access layer -- usually a non-SQL API that consumers must use to consume data that has been modeled by the semantic layer. Check out the docs for Cube [1] for an example of this. Cube also has a SQL API, but it's not fully fleshed out yet. What makes Malloy shine is that all 3 of these things are integrated in the same language, so users don't have to jump between different tools to model and explore their data. You can query/explore data in Malloy, iterate on functions to express your business logic, and immediately view the results. Doing this in something like Cube would require you to: (1) write SQL queries to prototype the function, (2) update the Cube configuration files with your changes, and (3) hit the REST API with a request to view results. In Malloy, it's all just writing and running Malloy queries. [1]: https://cube.dev/docs/query-format