4 ms·

For an article about LLM+OLAP, it doesn't spend much time on that part. Specifically it seems like their strategy is around using an LLM to generate a DSL query

by d_watt 3y ago

For an article about LLM+OLAP, it doesn't spend much time on that part. Specifically it seems like their strategy is around using an LLM to generate a DSL query for an unnamed semantic layer, then everything downstream of that is normal warehousing, with the semantic layer handling actual SQL creation. I wish it spent time on talking about how they trained their LLM to reliably generate parsable queries for the semantic layer, and what the accuracy rate of what the user intended vs what they got. I do think the only way a LLM based analytics tool can succeed is via a semantic layer rather than direct SQL, since database schemas fail to encode a lot of information about the data (EG a warehouse might not even know user.customer_id = customer.id). Malloy could be an interesting target here.

hobs 3y ago
Eh, many of them have some way to provide markup even when its informational only, because a data catalog or dictionary is required to use most large olap products. eg Snowflake lets you declare all the foreign keys you want, but does nothing with that info except let you use it.
- d_watt 3y ago
  Sure, some OLAP databases let you add the same metadata that a OLTP database gives you as constraints, especially enterprise ones. A lot still don't, like Clickhouse, afaik. No OLAP database I know of would let you encode other semantic layer things like aggregations or metrics. EG defining a DAU/MAU metric as "The distinct number of users logged in that day vs the distinct number of users in the 28 days before that day." Those types of definitions usually live in the semantic layer or bi layer, which a LLM analysis tool would need to solve for.
paddy_m 3y ago
It looks like https://github.com/tencentmusic/supersonic https://github.com/tencentmusic/supersonic is a component. I'm trying to figure out what they are doing too.
paddy_m 3y ago
Ibis could also be a target. It compiles queries written in python to multiple dataframe libraries, and SQL targets. https://ibis-project.org/ https://ibis-project.org/
mritchie712 3y ago
Agreed, that's exactly what we're doing with Definite[0]. We spin up Cube[1] for all our customers and the results vs. directly generating SQL are much better. Cube has some other really nice out of the box features too (e.g. caching). 0 - https://www.definite.app/ https://www.definite.app/ 1 - https://cube.dev/ https://cube.dev/
- random3 3y ago
  Is your SQL generation and cache layer open-source?
mjirv 3y ago
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-experiences https://cube.dev/blog/semantic-layer-the-backbone-of-ai-powe.... [0] https://delphihq.com https://delphihq.com
- bgorman 3y ago
  What is an example of a "semantic layer" in this context.
  - mjirv 3y ago
    Cube (https://cube.dev https://cube.dev) is a good one. Others include AtScale[0], dbt's MetricFlow[1], Google's Looker[2] (also a BI tool but powered by a semantic layer), and Propel[3]. [0] https://atscale.com https://atscale.com [1] https://www.getdbt.com/product/semantic-layer https://www.getdbt.com/product/semantic-layer [2] https://cloud.google.com/blog/products/data-analytics/introducing-looker-modeler https://cloud.google.com/blog/products/data-analytics/introd... [3] https://www.propeldata.com https://www.propeldata.com They're kind of an updated version of OLAP cubes if you're familiar with those. Typically semantic layers sit on top of a data warehouse, let you define metrics using code or a UI, and provide APIs or SQL connectors so that you can query them.

by d_watt 3y ago

hobs 3y ago

Eh, many of them have some way to provide markup even when its informational only, because a data catalog or dictionary is required to use most large olap products. eg Snowflake lets you declare all the foreign keys you want, but does nothing with that info except let you use it.

d_watt 3y ago
Sure, some OLAP databases let you add the same metadata that a OLTP database gives you as constraints, especially enterprise ones. A lot still don't, like Clickhouse, afaik. No OLAP database I know of would let you encode other semantic layer things like aggregations or metrics. EG defining a DAU/MAU metric as "The distinct number of users logged in that day vs the distinct number of users in the 28 days before that day." Those types of definitions usually live in the semantic layer or bi layer, which a LLM analysis tool would need to solve for.

paddy_m 3y ago

It looks like https://github.com/tencentmusic/supersonic https://github.com/tencentmusic/supersonic is a component. I'm trying to figure out what they are doing too.

paddy_m 3y ago

Ibis could also be a target. It compiles queries written in python to multiple dataframe libraries, and SQL targets. https://ibis-project.org/ https://ibis-project.org/

mritchie712 3y ago

Agreed, that's exactly what we're doing with Definite[0]. We spin up Cube[1] for all our customers and the results vs. directly generating SQL are much better. Cube has some other really nice out of the box features too (e.g. caching). 0 - https://www.definite.app/ https://www.definite.app/ 1 - https://cube.dev/ https://cube.dev/

random3 3y ago
Is your SQL generation and cache layer open-source?

mjirv 3y ago

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-experiences https://cube.dev/blog/semantic-layer-the-backbone-of-ai-powe.... [0] https://delphihq.com https://delphihq.com

bgorman 3y ago
What is an example of a "semantic layer" in this context.
- mjirv 3y ago
  Cube (https://cube.dev https://cube.dev) is a good one. Others include AtScale[0], dbt's MetricFlow[1], Google's Looker[2] (also a BI tool but powered by a semantic layer), and Propel[3]. [0] https://atscale.com https://atscale.com [1] https://www.getdbt.com/product/semantic-layer https://www.getdbt.com/product/semantic-layer [2] https://cloud.google.com/blog/products/data-analytics/introducing-looker-modeler https://cloud.google.com/blog/products/data-analytics/introd... [3] https://www.propeldata.com https://www.propeldata.com They're kind of an updated version of OLAP cubes if you're familiar with those. Typically semantic layers sit on top of a data warehouse, let you define metrics using code or a UI, and provide APIs or SQL connectors so that you can query them.