Lightning Posts

BI for the semantic layer

October 14, 2022
BI for the semantic layer
Oliver Laslett
CTO & Co-founder
BI for the semantic layer

BI for the semantic layer

Semantic BI is a BI platform built around a semantic layer (I guess you saw that coming). There’s been a lot of hype around the semantic layer and you can check out some great recent write ups:

but it feels like something is missing. The discussion has missed the main benefit of adopting a semantic layer! The real power of the semantic layer is the experience it unlocks for BI. With the semantic layer, data teams go beyond their transformations (defining tables with SQL) to create small building blocks of SQL (metrics and dimensions) on top of their tables. Anybody can then mix and match these dimensions and metrics through a BI interface to answer their own questions.

This overcomes a well-known problem for analytics engineers: how much should you aggregate your transformed tables? Roll everything up to a daily level and you lose flexibility; leave it unaggregated and your end users need to grok SQL to aggregate it themselves. In contrast, Semantic BI allows you to define all possible aggregations (metrics). This empowers anybody to answer their questions because:

  • A small number of metrics and dimensions combine to answer a much larger space of possible questions. 5 metrics with 10 dimensions can serve over 30,000 distinct questions!
  • They can ask questions using business concepts like “revenue per week” and “top 10 products” without needing to know the details (<insert complex sql for revenue here>)

As well as this improvement to self-serve, having metric definitions as code brings improved governance and standardisation of metrics across the org. Everyone is reading from the same page!

Jaffles in the semantic layer

I want to bring this to life with an example. So, naturally, we’re going to talk about jaffles. All you need to know is that a jaffle is a toasted sandwich that looks like this:

Real-life Jaffle

In this example, our protagonist Claire[1] has the difficult task of feeding a jaffle to everybody at HungryCorp. Claire has all the raw ingredients she needs and she’s an expert sandwich maker.

Two failed experiments

On Day 1, Claire makes every jaffle to order so that each one is perfectly executed and suits each customer exactly. But each jaffle takes so much time (baking the bread, finding the right ingredients, taking the order, toasting it) there are huge queues and much frustration at HungryCorp. This approach isn’t going to scale.

On Day 2, Claire throws her hands in the air and declares that people simply make their own jaffles. In theory, customers can still get their perfect jaffle, they have all the raw ingredients and equipment available to them. In reality, the workers of HungryCorp can’t find the right ingredients, they don’t know how to bake bread (I know what bread is but I don’t know how to make it), and they’ve managed to spill cheese all over the toastie machine. It’s a disaster.

The JaffleStation aka Semantic BI

On Day 3, Claire decides to build a JaffleStation, a buffet that allows the workers to make their own jaffle the way they like it but instead of providing the raw ingredients, Claire pre-prepares some of the ingredients. They’re organised into sections of vegetarian/carnivorous products, there’s pre-made bread, the toasters have better guidance and cheese is given out in sensible portions.

In other words, the JaffleStation gives the workers of HungryCorp flexibility to mix and match ingredients but abstracts away the expert work (making bread, choosing the correct quantities, determining what is vegetarian) and gives them building blocks they understand (bread, cheese, pickles, …) to construct the end result they want.

Semantic BI serves your organization in the same way as that the JaffleStation served HungryCorp. In a Semantic BI tool, users can interact with entities they recognise like users and revenue. They understand what those things are and how to use them but don’t need to know how exactly they’re made from raw data. Once users have those definitions they can cut revenue by geography, or week, or quarter. They can make their own jaffles just how they like them!

Semantic BI removes the bottleneck for  Analytics Engineering

Analytics engineering is taking a set of raw tables and transforming and documenting them into analysis-ready tables. But, the end result has zero flexibility for end-users of the data. If they need some other information, they have to go back to the data team and get them to create new tables or update old tables.

We’ve heard the famous words “now I want this by week, actually by state, although can I have both?” there are always more questions than we can answer. We need to reduce the number of times these requests trigger a change to the underlying data models. This is the root cause of our data bottleneck.

With Semantic BI, anybody can start with revenue per week, then segment by state, change to month, and show active users alongside revenue. In short, they can answer their own questions! The best part is that we can do that without having to change the underlying data model. This gives analytics engineers leverage by allowing them to answer more questions with fewer data models. Imagine that! Now you’ve got more time to start thinking about the next model to go into your semantic layer, or what new raw data you could capture. Rather than serving endless requests to tweak your tables.

The Lightdash Way

Semantic BI is at the heart of the Lightdash Way, focusing on how to empower users to self-serve by providing the tools that modern data teams need to democratise access to data effectively. It’s what makes Lightdash tick and enables you to scale BI to enterprise levels with a smaller more productive (and we think happier) data team. Ready to put your data models and semantic layer to the test? You can signup for the Lightdash Cloud beta or start self-hosting Lightdash yourself.


  1. The story, all names, characters, and incidents portrayed in this production are fictitious. No identification with actual persons (living or deceased), places, buildings, and products is intended or should be inferred)

arrow_back  More lightning posts