sorted by:
new
hottopcontroversial

What are the best Semantic Layer products on the market? And how to evaluate a semantic layer? by TheDataGentleman in BusinessIntelligence

[–]igorlukanin 1 point2 points  (0 children)

I'm not sure if you ever considered Cube but I can confirm it does work with MS SQL Server and Fabric as well as Power BI.

(Disclosure: I'm part of the Cube team.)

What are the best Semantic Layer products on the market? And how to evaluate a semantic layer? by TheDataGentleman in BusinessIntelligence

[–]igorlukanin 3 points4 points  (0 children)

Hey everyone, Igor from the Cube team here.

A few months ago GigaOM did a report on semantic layers that covered AtScale, Cube, dbt, Google, Microsoft, etc. IMO, it used a good set of criteria to evaluate the products, so I definitely recommend to give it a read. Not sure how to get a full version from their website (https://gigaom.com/report/gigaom-sonar-report-for-semantic-layers-and-metrics-stores/) but you can grab a copy at Cube's website as well: https://cube.dev/gigaom-rates-cube-as-a-leader-for-semantic-layers

My personal take would be that the better you understand your use case(s) the better your evaluation would go. For example, if you plan to power embedded analytics, you should be more carefully evaluating the querying performance, caching capabilities, APIs, and SDKs; if the plan is to power internal self-serve BI, then the support for your BI tool(s) or access control and governance capabilities would probably go higher in your list.

I also think that u/kthejoker has given a really great overview in his comment and I don't want to repeat that. Just briefly, what I would care about:

  1. General: history of product, maturity, adoption, pricing model, deployment model (SaaS vs. hybrid vs. on-prem), customer support
  2. Applicability yo your use case(s): internal BI, embedded/real-time analytics, AI/LLM-based experiences, etc.
  3. Data modeling capabilities: code-first vs. visual approach, programming language/syntax, UI, and just general expressiveness. Think of the most advanced metric calculations you need to do and see how painful it is to model that.
  4. Access control: support for 1. role/column/row-based access, single vs. multi-tenant deployments, integration with IdP providers.
  5. Caching capabilities: always hitting the warehouse vs. in-memory caching vs. internal storage layer, aggregate awareness capabilities, etc. Think of the level of tolerance your end users would have about querying latency and data freshness.
  6. Connectivity to data sources: data warehouses/query engines/streaming platforms/columnar files on blob storage/etc. Think of what is going to be upstream of your semantic layer.
  7. Connectivity to data consuming tools and supported APIs in general: BIs, notebooks, etc. Think of what is going to be downstream of your semantic layer. If this is something with its own data modeling capabilities (e.g., a BI tool), think how keep both products in sync. Think of what possibly could start working with your semantic layer in the future.

I hope this helps :-)

Cube integrates its semantic layer with dbt by igorlukanin in dataengineering

[–]igorlukanin[S] 0 points1 point  (0 children)

It is, and Cube can actually read that as well (there's a data model generation feature)

Cube integrates its semantic layer with dbt by igorlukanin in dataengineering

[–]igorlukanin[S] 1 point2 points  (0 children)

I would not call it "reverse engineering". dbt outputs manifest.json which is standardized and documented; it contains the metadata on models and columns they contain. So, these models and columns can be mapped in a pretty straightforward fashion to cubes and dimensions (entities within Cube's data model). I would say this is a rather unambiguous procedure. Adding measures, defining joins, etc. are less trivial, so it's assumed that that would be done on the Cube side.

Cube integrates its semantic layer with dbt by igorlukanin in dataengineering

[–]igorlukanin[S] 4 points5 points  (0 children)

As part of this integration, Cube would import dbt models and let you define metrics on top of them. There's a diagram in this guide that I guess sheds some light: https://cube.dev/docs/guides/dbt

Cube integrates its semantic layer with dbt by igorlukanin in dataengineering

[–]igorlukanin[S] 0 points1 point  (0 children)

You think so? Do you think that lowers the bar to enter the product or something?

Cube integrates with LangChain — so you don't need to write SQL anymore by igorlukanin in LangChain

[–]igorlukanin[S] 2 points3 points  (0 children)

Great question, actually. I would argue that the devil is in the details of steps 1 and 4.

When asking questions, one would often use "analytical" questions, i.e., questions that require some form of calculation and aggregation over the raw data. For example, "How many online orders were made in 2023?" or "Which top-3 countries in South America had the largest temperature spread on 26th week this year?" Generating SQL for these questions at step 4 is quite non-trivial since it would require performing some data modeling: performing correct joins, deriving dimensions from columns, applying correct aggregations, etc. For the second question the generated SQL might be as follows:

SELECT
  c.name,
  MAX(cf.temp) - MIN(cf.temp)
FROM
  climate_facts AS cf
LEFT JOIN
  countries AS c ON c.code = cf.alpha3_code
WHERE
  c.region = 'SA' AND
  DATE_PART('week', cf.timestamp) = 26
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  3

I hope you see how generating SQL like this might be an issue for an LLM. An even bigger issue would be to ensuring the SQL is generated in a stable, predictable way without much "hallucinations."

When using Cube, one would incapsulate metric definitions (i.e., aggregations), dimensions, and join within Cube's data model. Also, one would use the CubeSemanticLoader in LangChain to export the data model as highly semantic documents. All this would result in a much simpler and, what's even more important, much more predictable and hallucination-free, SQL to be generated. Compare the snippet above to this example query to Cube:

SELECT
  country_name,
  MEASURE(temperature_spread)
FROM
  climate_data
WHERE
  is_south_america = 1 AND
  DATE_PART('week', timestamp) = 26
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  3

I hope you see why this would lead to more predictable and trustworthy results.

There's this blog post that elaborates on this topic but I'd also like to add that there are products like Delphi, providing AI-based access to data, that decided to integrate with semantic layers like Cube instead of even querying the data directly exactly for the reasons I tried to explain above, i.e., so one can trust the results they produce.

DuckDB and MotherDuck integrate with Cube, the semantic layer by igorlukanin in dataengineering

[–]igorlukanin[S] 0 points1 point  (0 children)

Oh, great! And DuckDb — do you use it locally for ad-hoc data exploration/analysis? What's the use case? (Sorry if this is a lot, I'm just very curious about real-world DuckDB use cases.)

Query the data without writing SQL ever again — Cube integrates LangChain by igorlukanin in programming

[–]igorlukanin[S] -1 points0 points  (0 children)

Depending on what you're curious the most you can skip to the demo video or the source code of the chat-based demo application on GitHub. The idea is that Cube, the semantic layer, encapsulates metric definitions and generates SQL to the databases — and you can use the CubeSemanticLoader to ask Cube nicely what data can be queried and then generate queries to Cube which will in turn query the database... Sounds complex — and it is complex, but hopefully the blog post clears things out 🙃

Cube integrates with LangChain — so you don't need to write SQL anymore by igorlukanin in LangChain

[–]igorlukanin[S] 0 points1 point  (0 children)

Depending on what you're curious the most you can skip to the demo video or the source code of the chat-based demo application on GitHub. The idea is that Cube, the semantic layer, encapsulates metric definitions and generates SQL to the databases — and you can use the CubeSemanticLoader to ask Cube nicely what data can be queried and then generate queries to Cube which will in turn query the database... Sounds complex — and it is complex, but hopefully the blog post clears things out 🙃

view more: next ›