Lakshya

Analytics & BI Tools · Chapter 1 of 8

The analytics stack, and what each tool is for

Four layers. Most tool arguments are really arguments about which layer owns the metric definition.

3 min read1 diagramAll 8 chapters

Analytics tooling looks crowded because several products span several layers and market themselves as covering all of them. Separate the layers first and the choices become tractable.

THE STACK, AND WHICH LAYER EACH TOOL OWNS VISUALISE dashboards, reports Power BI · Tableau · Looker · Qlik · Superset · Metabase where business users actually live MODEL the semantic layer Looker (LookML) · Power BI datasets · dbt semantic layer · Cube where one definition of a metric is enforced — or is not PREPARE clean, join, shape Alteryx · Power Query · dbt · Matillion · Fivetran the layer that quietly becomes the whole pipeline STORE the warehouse or lakehouse Snowflake · Databricks · BigQuery · Redshift see the Databricks and Snowflake book Most tool arguments are really arguments about which layer should own the metric definition. If it lives in the dashboard, you will have six definitions. That is the whole story of BI governance.
Four layers, and the tools that own each. The recurring failure is that the metric definition ends up in the visualisation layer, where every dashboard author can redefine it — which is how an organisation ends up with six numbers for revenue.

What each layer is actually for

  • Store — the warehouse or lakehouse. Covered in its own book here.
  • Prepare — cleaning, joining, shaping. Historically ETL tools; now mostly SQL transformation in the warehouse, with visual tools where the users are analysts rather than engineers.
  • Model — the semantic layer, where a business concept gets one definition. The most under-appreciated layer and the one that decides whether your numbers agree.
  • Visualise — where business users live. Highly visible, and the least consequential of the four for whether the numbers are right.

The question that cuts through every tool comparison

“Where is active customer defined, and can a dashboard author override it?”

If the answer is that anyone can redefine it in a report, you will have as many definitions as you have reports, and no tool choice fixes that. If it is defined once in a modelled layer that reports must consume, most governance problems disappear regardless of which visualisation tool sits on top.

← All chaptersPower BI, in practice →