Vocabulary for BI and Analytics Engineers

Business intelligence and analytics engineering sit at the junction of data engineering, statistics, and business communication. BI engineers build the data models, dashboards, and reports that help organisations understand their performance. The vocabulary spans both technical concepts (OLAP, star schema, DAX) and business communication terms (KPI, drill-down, slice-and-dice).

Data Modelling Vocabulary

Star Schema

A star schema is a database schema design used in data warehouses. It consists of a central fact table surrounded by dimension tables, forming a shape resembling a star.

“Our data warehouse uses a star schema: the central fact table stores sales transactions, and the dimension tables contain information about customers, products, dates, and stores.” “The star schema is optimised for query performance — most analytical queries can be answered by joining the fact table to one or two dimension tables.”

Fact Table

A fact table contains the measurable, quantitative data — the “facts” of the business process being analysed.

“The sales fact table contains one row per transaction, with columns for quantity, revenue, discount, and cost.”

Dimension Table

A dimension table contains descriptive attributes that provide context for the facts.

“The customer dimension includes customer name, segment, region, acquisition channel, and account creation date.”

Snowflake Schema

A snowflake schema is a variation of the star schema where dimension tables are further normalised into sub-dimension tables.

“We chose a star schema over a snowflake schema for the reporting layer because query simplicity matters more than storage normalisation for our use case.”

Data Mart

A data mart is a subset of a data warehouse focused on a specific business function or department.

“The finance team has their own data mart with pre-aggregated revenue, cost, and margin data — they don’t need access to the full transaction-level data warehouse.”

OLAP and Query Concepts

OLAP (Online Analytical Processing)

OLAP is a technology for analysing multidimensional data. OLAP systems allow users to query large datasets along multiple dimensions quickly.

“The OLAP cube lets the sales team analyse revenue by product, region, and time period simultaneously — without running slow ad-hoc SQL queries.”

Drill-Down

Drill-down is the action of navigating from a summary view to a more detailed level of data.

“I can drill down from total annual revenue to monthly revenue, then to weekly, then to individual transactions if needed.” “The report supports drill-down: clicking on a country shows revenue by city; clicking on a city shows revenue by store.”

Roll-Up

Roll-up is the opposite of drill-down — aggregating detailed data to a higher level of summary.

“We roll up daily sales figures into weekly and monthly summaries for the executive dashboard.”

Slice and Dice

Slice means filtering data along one dimension. Dice means filtering along multiple dimensions simultaneously.

“I sliced the data by the ‘Enterprise’ customer segment to see how that cohort is performing.” “Dicing the data by region, product category, and quarter gives the sales leadership the view they need for the QBR.”

Pivot

Pivoting means rotating data so that rows become columns (or vice versa), to present it in a different orientation.

“We pivoted the monthly revenue by product category — what was a long, narrow table became a wide report showing each category as its own column.”

Metrics and KPIs

KPI (Key Performance Indicator)

A KPI is a measurable value that indicates how effectively an organisation or team is achieving its objectives.

“Our primary growth KPI is Monthly Active Users (MAU). Our secondary KPIs are average session duration and feature adoption rate.” “The dashboard surfaces five KPIs: conversion rate, average order value, customer acquisition cost, churn rate, and NPS.”

Leading vs. Lagging Indicators

• Leading indicators predict future performance (e.g. number of trials started)
• Lagging indicators measure past performance (e.g. monthly revenue)

“Churn rate is a lagging indicator — by the time it rises, customers have already left. We want to track leading indicators that predict churn before it happens.”

Query Languages

DAX (Data Analysis Expressions)

DAX is the formula language used in Microsoft Power BI, Excel Power Pivot, and Analysis Services. It is used to create calculated columns, measures, and tables.

“I wrote a DAX measure to calculate rolling 12-month revenue: it uses DATESINPERIOD and CALCULATE to aggregate across the date dimension.” “DAX context is one of the most important concepts to understand — the same measure can return different values depending on the filter context applied by a visual.”

MDX (Multidimensional Expressions)

MDX is a query language for OLAP databases (such as SQL Server Analysis Services).

“The legacy reporting system uses MDX queries against the SSAS cube. We’re migrating those reports to DAX-based models in Power BI.”

Data Warehouse Architecture

ETL vs. ELT

• ETL (Extract, Transform, Load) — data is transformed before loading into the warehouse
• ELT (Extract, Load, Transform) — raw data is loaded first, then transformed inside the warehouse

“We switched from ETL to ELT — raw data now lands in the warehouse first, and transformations happen in dbt. This gives us full lineage and makes it easier to reprocess historical data.”

dbt (data build tool)

dbt is a transformation tool that allows analytics engineers to write SQL models, test them, and document them as part of a version-controlled workflow.

“All our data transformations are defined as dbt models. Each model is tested for null values and referential integrity before it’s promoted to the reporting layer.”

Practical Phrases for BI Engineers

• “The fact table needs a grain definition — what does one row represent?”
• “This KPI is a lagging indicator. We should add a leading indicator that predicts it.”
• “Let me drill down into the Q3 numbers — I see the total is off but I need to find which region is driving the variance.”
• “The OLAP cube aggregates this dimension automatically — the drill-down is handled by the cube, not by a custom query.”
• “The DAX measure calculates the month-over-month variance using PREVIOUSMONTH.”

BI and analytics vocabulary bridges the gap between raw data and business decision-making. Using these terms precisely helps you communicate with both the data engineering team that builds the pipelines and the business stakeholders who rely on the reports.