BI Fundamentals
Beginner 14 min read BI Fundamentals

The BI Stack

The parts, and which problems each one owns

A BI stack spans data storage, transformation, semantic models, reporting tools and delivery. Understanding each layer tells you where problems and costs actually live.

BI stackwarehousesemantic layerreporting toolsETLdelivery
Prerequisites: What is Business Intelligence?
Before we start

What you will learn

  • Name the layers of a modern BI stack and their responsibilities
  • Trace a dashboard number back through every layer
  • Choose tooling per layer based on workload, not vendor momentum
Introduction

What is this?

The BI stack is the collection of systems that turn raw data into decisions: sources, pipelines, storage (warehouse or lake), a semantic/modeling layer, reporting tools, and a delivery layer (server, refresh, security, embeds). Each layer has a different failure mode and a different owner — and most BI bugs are layer-confusion bugs.

Motivation

Why do you need it?

  • You cannot buy 'one tool that does everything' well; each layer has specialized strengths.
  • Knowing the layers tells you who to page when a dashboard is wrong (pipeline vs model vs report).
  • Costs concentrate in specific layers; visibility lets you optimize the right one.
  • Modern, interchangeable open formats let you swap a layer without rebuilding the stack.
Think about it

Mental model

Imagine

A restaurant's kitchen-to-table pipeline

Think of

The BI stack

Ingredient sourcing (sources), prep (pipelines), cold storage (warehouse), plating (reporting) and service (delivery) are different jobs with different owners. A bad plate is not a sourcing problem, and a good plate can't fix spoiled ingredients.

Mechanics

How it works

  • Sources: app databases, SaaS exports, event streams and files emit raw data.
  • Pipelines (ETL/ELT): ingest, dedupe, type and model data into analytical shapes.
  • Storage: a warehouse/lake holds modeled facts, dimensions and raw copies.
  • Semantic layer: defines business metrics once (names, aggregations, joins, access).
  • Reporting tools: Power BI, Tableau, Looker, Superset and others connect and visualize.
  • Delivery: scheduled refresh, dashboards-as-a-service, security/RLS, embedding, alerts and versioned publishing.

Layers a dashboard number travels through.

Sources
Pipelines
Warehouse / lake
Semantic layer
Reporting tool
Delivery
Real world

In production

  • A quarterly revenue report flows: CRM exports → dbt models in Snowflake → a semantic view of revenue → a certified Power BI report in a governed workspace → embedded in the exec portal.
  • A field-ops team monitors daily delivery SLA from event streams → warehouse → Tableau published to the server with row-level security.
  • A product org runs A/B instrumentation through a lake, then serves self-service dashboards to PMs from one semantic model.
Guidelines

Best practices

  • Pick storage and formats that are interchangeable; avoid vendor lock at the serving layer.
  • Define the semantic model before building reports, not after the first 40 dashboards disagree.
  • Keep pipelines and reporting decoupled so a report change never requires a pipeline redeploy.
  • Set refresh SLAs per dataset and alert on staleness, not just failures.
Watch out

Common mistakes

  • Choosing the whole stack from one marketing page and living with its weakest layer.
  • Building reports directly on raw tables and re-defining metrics per dashboard.
  • Ignoring the delivery layer; fresh data behind stale, out-of-sync published artifacts.
  • No owner per layer, so every incident becomes a five-team war room.
Root cause analysis

Common issues

Error
Report is fresh but numbers changed without any deploy
Root cause

An upstream source or pipeline changed semantics

Fix

Check lineage and the raw-to-model diff; freeze semantic changes behind tests.

Error
Query is slow in the BI tool but fast in SQL
Root cause

The report is doing client-side filtering or the semantic layer isn't used

Fix

Push filters and aggregations into the model/database, not the visual.

When things break

Debugging tips

  • Trace the number layer by layer: source → warehouse → model → report; the wrong layer is where it breaks.
  • Compare refresh timestamps across layers to find where data age is introduced.
  • If visuals are correct but slow, the problem is the model layer; if wrong, it is a definitions problem upstream.
  • Use lineage to map each dashboard to its tables — then a bad number has one suspect list.
Interviews

Interview checkpoint

Practice

Hands-on lab

Trace a Dashboard Number End-to-End
40 min · beginner
In progress
Objectives
  • Build every layer of a miniature BI stack with SQLite
  • Introduce a defect at one layer and find it by tracing
  • Prove that one metric definition stays consistent across 'reports'

Trace a Dashboard Number End-to-End

40 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which layer is responsible for defining 'revenue' consistently for every report?

2

A BI stack is measured by how many dashboards it can render.

Go deeper

Official documentation

Modern data stack (dbt glossary)
docs
Power BI service architecture
docs