What is Business Intelligence?
Business Intelligence (BI) refers to the set of processes, technologies, and tools that enable collecting, integrating, analyzing, and presenting business information to facilitate decision-making. Its goal: transforming raw data into actionable insights for executives, managers, and operational teams.
BI encompasses reporting (periodic dashboards), ad hoc analysis (one-off data queries), and increasingly augmented analytics (automatic suggestions, alerts, and AI-based explanations). It differs from Data Science: BI answers 'what happened?' and 'why?'; Data Science answers 'what will happen?' and 'what should we do?'
The four levels of analytics
Descriptive (what happened?), Diagnostic (why did it happen?), Predictive (what will happen?) and Prescriptive (what should we do?). Traditional BI mainly covers the first two levels. Modern BI tools increasingly integrate predictive and prescriptive capabilities through ML connectors.
BI primarily uses historical structured data and produces reports for business users. Data Science explores unstructured data, builds predictive models, and mainly addresses technical teams. In practice, the two disciplines are converging on modern platforms.
KPI: definition, construction and pitfalls
A KPI (Key Performance Indicator) is a quantifiable measure that evaluates the effectiveness of an organization, process, or individual relative to a defined objective. The key concept is in 'Key': a KPI must be a crucial indicator, not simply an available metric.
SMART criteria for a good KPI
An effective KPI is: Specific (measures one precise thing), Measurable (quantifiable with reliable data), Achievable (realistic given the context), Relevant (directly linked to strategic objectives), and Time-bound (with a clear reference period). Example: 'Increase the 90-day customer retention rate from 65% to 75% by Q4 2026' is a SMART KPI. 'Improve customer experience' is not.
Lagging KPIs vs leading KPIs
Lagging indicators measure past results: revenue, NPS, churn rate. They confirm what has happened but do not enable anticipation. Leading indicators measure predictive signals: number of scheduled demos, activation rate of new features, average time to first value (TTV). A balanced dashboard combines both.
Vanity metrics give the impression of success without being actionable. 'Number of page views' or 'Number of sign-ups' are often vanity metrics if they are not correlated with real business objectives. Always ask: 'What will we do differently if this KPI doubles?'
BI tools on the market
The BI tools market has segmented between legacy enterprise solutions, new cloud-native platforms, and modern open-source tools.
Microsoft Power BI
Power BI is the global BI market leader according to Gartner, particularly dominant in Microsoft organizations (Azure, Office 365). It offers an intuitive dashboard-building interface, a powerful analytical engine (DAX for advanced calculations), and native integration with Excel and SharePoint. Power BI Desktop is free; Power BI Service (cloud) requires a Pro license.
Tableau (Salesforce)
Tableau is renowned for its advanced dataviz capabilities and drag-and-drop interface that allows non-developers to create sophisticated visualizations. Acquired by Salesforce in 2019, it integrates natively into the CRM ecosystem. Tableau is often preferred by analytics teams that value visual exploration.
Looker (Google)
Looker stands out for its LookML language, which defines a centralized semantic layer: all metrics and dimensions are defined once and reused across all reports. Acquired by Google in 2019, it integrates into Google Cloud and BigQuery. Looker is the preferred choice for data-driven organizations wanting a single source of truth for their metrics.
Metabase and Apache Superset
Metabase is the most accessible open-source BI tool: without SQL, business users can build reports through an intuitive interface. Apache Superset is the more powerful open-source alternative, adopted by large organizations (Airbnb, Lyft), with full SQL support and advanced visualization capabilities.
Dimensional modeling: star schema and snowflake schema
Dimensional modeling is the technical foundation of analytical data warehouses. It structures data to optimize OLAP queries and facilitate understanding by business users.
The star schema
The star schema places fact tables (which contain measures: revenue, quantity, duration) at the center, surrounded by dimension tables (which contain context: date, product, customer, region). Joins are simple and direct, optimizing analytical query performance. Example: 'sales' fact table with columns for amount, quantity, and keys to date, product, and customer dimensions.
Slowly Changing Dimensions (SCD)
Slowly Changing Dimensions manage the evolution of dimensions over time. Type 1: overwrite the old value (simple, but loses history). Type 2: add a new row with validity dates (preserves history, but multiplies rows). Type 3: add a 'previous value' column (limited compromise). SCD Type 2 is the most widely used for historical analysis.
Ralph Kimball formalized dimensional modeling in 'The Data Warehouse Toolkit' (1996). His bottom-up approach (starting from business processes) contrasts with Bill Inmon's method (top-down, enterprise-first). Both methodologies coexist today.
Kimball, R., Ross, M. - The Data Warehouse Toolkit, 1996The semantic layer: a single source of truth for metrics
The semantic layer is an abstraction layer between raw data and BI tools. It centralizes business metric definitions and makes them consistently accessible to all reporting tools.
Without a semantic layer, each BI tool recalculates its own metrics: the conversion rate in Power BI may differ from the one in Looker if definitions vary. The semantic layer enforces a single, shared definition. dbt Semantic Layer, LookML (Looker), and Cube.js are the most popular solutions.
dbt Semantic Layer
dbt Semantic Layer (formerly dbt Metrics) allows defining metrics directly in dbt code, in pure SQL. These metrics are then exposed via a MetricFlow API that any compatible BI tool can query. The advantage: metrics versioned in Git, tested and documented, accessible from Tableau, Power BI, Superset, and many others.
Consolidating your BI knowledge with flashcards
BI combines business concepts (KPI, metrics, reporting) and technical ones (SQL, modeling, tools). Spaced repetition is ideal for durably anchoring these notions, which are often mixed up in practice.
Concepts to absolutely master: KPI vs metric difference, star schema vs snowflake schema, SCD Type 1/2/3, lagging vs leading KPI, DAX vs SQL vs LookML. These are the most recurring questions in BI and Data Analytics interviews.
Frequently asked questions about Business Intelligence and KPI
What is Business Intelligence?
Business Intelligence is the set of processes, technologies, and tools that enable collecting, analyzing, and presenting information to facilitate decision-making. It answers 'what happened?' (descriptive) and 'why?' (diagnostic), and differs from Data Science which answers 'what will happen?' and 'what should we do?'
What is the difference between a KPI and a metric?
A metric is any quantifiable measure (number of visits, average session duration, revenue). A KPI is a metric specifically chosen because it is crucial for measuring performance against a strategic objective. All KPIs are metrics, but not all metrics are KPIs.
Which BI tool to choose: Power BI, Tableau or Looker?
Power BI is the natural choice in Microsoft ecosystems (Azure, Office 365), with accessible licensing. Tableau is preferred for advanced visual exploration and creating sophisticated dataviz without SQL. Looker is optimal for data-driven organizations that want a centralized semantic layer (LookML) and Google Cloud integration.
What is a star schema?
A star schema is an analytical data model with a central fact table (containing measures: sales, revenue, quantities) surrounded by dimension tables (containing context: date, product, customer, region). It is called 'star' because the schema resembles a star with the fact table at the center.
What is a Slowly Changing Dimension (SCD)?
A Slowly Changing Dimension manages how a dimension evolves over time (e.g., a customer changes region). Type 1 overwrites the old value (loses history). Type 2 adds a new row with start and end validity dates (preserves complete history). Type 2 is the most widely used for historical analysis.
What is the semantic layer?
The semantic layer is an abstraction layer between raw data and BI tools. It centralizes metric definitions to ensure they are calculated the same way across all tools. dbt Semantic Layer, LookML, and Cube.js are the most popular solutions. It prevents the conversion rate in Power BI from differing from the one in Tableau.
What is DAX in Power BI?
DAX (Data Analysis Expressions) is Power BI's formula language for advanced calculations. It allows creating calculated measures, calculated columns, and tables directly in the data model. DAX is particularly powerful for time-based calculations (year-over-year, running totals) and complex contextual filtering.
Previous article: Data Governance - definition and implementation