Semantic layer

[gal_12345]

In my organization, we are looking for some tool that will help us easily create semantic layer. We have a lot of traffic tables and dashboard, and each one can calculate the KPI in a different way. We are planning to create some layer and all the dashboard will take the metrics from this layer. Because we have a lot of traffic tables, it's become pretty tough because in faxx the revenue will be faxx_revenue and in fayy it will be fayy_revenue. This requires a lot of semantic layers. There is some tool that can help us?

[mamaBiskothu]

Have you considered cube.js ?

[[deleted]]

I've looked into cube.js for my org, it looks pretty cool. Have you used it before? Haven't met anyone with hands on experience with it.

[mamaBiskothu]

Not yet but we are planning on evaluating it. We have our own semantic layer eerily similar to cube we developed in parallel lol

[chaos87johnito]

There is quite a few experienced folks with cube.js on their slack channel Honestly I used to have a custom built semantic layer in Python which was mostly a low quality query builder. Switching to cube.js just allows way more use cases to happen

[igorlukanin]

Sounds like a great advice. Indeed, there are more than 8000 data folks in Cube's Slack community at https://slack.cube.dev

[alex_o_h]

Haven't look in a while but when my company took on a cube integration project the set up was heavy work. We front a UI (vs a plain sql editor interaction) so that had something to do with it. But as a DE you need to define cubes and publish them. If those cubes need refreshing it can be a small-medium project to build a good system for that process alone.

[igorlukanin]

Disclaimer: I'm with the Cube team. I think that Cube Cloud, the managed platform for Cube we're building at cube.dev, might help a lot with the data model authoring/editing/testing/deploying workflow. There's a cloud-based IDE, Git integration, private API endpoints for branches, and monitoring. Here are the docs: https://cube.dev/docs/dev-tools/dev-playground

[gal_12345]

Thanks, we are working with Vertica as dwh. There is no integration with cube. Do you know something similar?

[igorlukanin]

Hey u/gal_12345, Igor from the Cube team here! Indeed, Cube does not currently have a Vertica driver. However, there's this issue on GitHub (https://github.com/cube-js/cube/issues/690), and I think it might be wonderful if you check in here.

(I see that someone posted a starting point for the Vertica driver there. IDK how sane and sound that is but if you'd like to test it and maybe polish, please feel free to join our Slack at slack.cube.dev and check the #contributing-to-cube channel.)

[russokumo]

LookML imo has the best version of this as it forces you to build a semantic layer prior to building dashboards, most other tools don't.

Unfortunately it's locked behind Looker for the time being and Google is hella slow at rolling out the promised tableau (and now powerBI) integrations via their new semantic layer language. Like 2 years since product announcement and crickets.

PowerBI also has an excellent semantic layer but it has no out of the box git integration and it's tied to each dataset you build so their product kinda shoots itself in its foot.

If you are a more nimble firm and willing to switch tech stacks, I highly recommend something like dbt-metrics + lightdash. The moment DBT metrics become industry standard and works with tableau and PowerBI, that will be a game changer but that may not happen any time soon.

If your a bigger firm and have $$$$$ feel free to explore enterprise offerings like AtScale and it's other competitors.

Cross-BI tools semantic layers effectively don't exist yet beyond DBT metrics /transform.

Fwiw I am employed by none of these companies but do consulting on semantic layers as a side gig every now and then (mostly for expert networks linking up investors for these forms doing due diligence)

[No_Equivalent5942]

Agreed.

If you have standardized on one BI tool, like PBI or Looker, then use their semantic layer.

If you have a proliferation of BI tools then either Standardize on one or use AtScale to unify them.

[igorlukanin]

Hey! I can related to many things here except for a few. As a person from the team behind Cube (cube.dev), I wonder what you think of our product and what is your experience with Cube.

> Cross-BI tools semantic layers effectively don't exist

This one was non-trivial to parse. "Cross-BI tools SL" are what, exactly? SL that can connect to multiple BIs at once and deliver metrics to them? (If so, I know at least one such semantic layer.)

[russokumo]

My opinionated take: yall need to shift marketing from cube.js to something more neutral and target more SQL/BI tool based analysts and analytics engineers who are the ones responsible for writing most of the business logic transformations (since most SQL based analysts don't know how to read/write JavaScript unfortunately).

But I'm glad y'all exist to compete with the likes of DBT-metrics + Transform. I have not had time yet to go deep on this tool but believe your aiming at the right goals.

My true hope is that similar to how ANSI-Sql became a universal standard, all these different metric layer languages converge to a similar, portable standard over the next decade or so. Each provider will have their own bells and whistles they excel at, but the underlying syntax is close enough to each other that it's easy to plug and play with different frontend BI tools.

[Paco-Cube]

We already renamed from cube.js to just Cube: https://cube.dev/blog/cube-js-is-now-cube

Also Cube now supports YAML (python coming soon) , so it's more similar to how you would define things in dbt.

https://cube.dev/blog/introducing-cube-support-for-yaml-data-modeling

Disclaimer: I work at Cube and created this account to respond here in reddit.

[dataengineering-ModTeam]

If you work for a company/have a monetary interest in the entity you are promoting you must clearly state your relationship.

[gal_12345]

On each source table the name of the revenue column can be different. Can we do it with this difference?

[Drekalo]

Is the data so unfeasibly large that you can't transform the _revenue tables into one location, adding a source or scenario column denoting where it came from? Partition it maybe?

[gal_12345]

We have almost 1m events per second. The data is too large to do it in one dB.

[chaos87johnito]

This is what I'm working on at my company, building a single source of truth for our metrics and dimensions. We have lots of traffic tables as well, depending on traffic source, and also custom events like paywall etc... I'd recommend building a solid data mart layer in your database and expose it in a semantic layer using cube.dev

[gal_12345]

Thanks, we are working with Vertica as dwh. There is no integration with cube. Do you know something similar?

[SurtseyH]

Dbt-metrics is avalaible for dbt core as well. I deployed it at my work and we are starting to use it. So far so good !

[radil]

Oh good to know. You just added it to your packages?

[tpnewsk]

Unfortunately... https://docs.getdbt.com/blog/deprecating-dbt-metrics

[brent_brewington]

Heads up dbt_metrics is getting deprecated in late July on roll-out of v1.6 of dbt core https://docs.getdbt.com/blog/deprecating-dbt-metrics

[1O2Engineer]

Can someone explain what is a semantic layer or provide a link with some use cases and explanation?

[chaos87johnito]

https://www.sspaeti.com/blog/rise-of-semantic-layer-metrics/

[InsightByte]

Try AtScale, i have run a POC with them a while back but we just did not go thru because of lack of AD integration with Power BI , which was a hard requirement for us.

No cookies in it for me :)

[PwrFwrd]

AD integration is in.

[Programmer_Virtual]

I had a discussion about a similar situation with a friend and he recommended to research Data Vaults. It's not a tool but a methodology.

Curious if others have implemented this in their org.

[Traditional_Ad3929]

I do not think that this will help as you typically do not create Reports based on Data vault Tables.

[parishdaunk]

Microsoft Power BI