What is Business Intelligence?
Business Intelligence (BI) refers to the set of processes, technologies and tools that collect, integrate, analyze and present business information to support decision-making. Its core purpose: turning raw data into actionable insights for executives, managers and operational teams — at the right granularity, at the right time, in the right format.
BI covers periodic reporting (weekly or monthly dashboards that summarize performance), ad hoc analysis (one-off queries answering a specific business question), and increasingly augmented analytics (automatic suggestions, anomaly detection, alerts and AI-generated explanations). It is clearly distinct from Data Science: BI answers descriptive questions ('what happened?') and diagnostic ones ('why?'), while Data Science answers predictive questions ('what will happen?') and prescriptive ones ('what should we do?').
The four levels of analytics
Gartner's analytics maturity model defines four progressive levels. Descriptive analytics (what happened?) is the foundation of BI: dashboards, reports, historical aggregations — most organizations stop here. Diagnostic analytics (why did it happen?) explores causes through drill-down, segmentation and cohort comparison. Predictive analytics (what will happen?) uses statistical and ML models to anticipate churn, demand or defects. Prescriptive analytics (what should we do?) recommends optimal actions: price optimization, budget allocation, personalization.
Traditional BI covers the first two levels. Modern BI platforms (Power BI, Tableau, Looker) increasingly add predictive capabilities through ML connectors, OpenAI APIs and augmented-analytics features that automatically flag anomalies and meaningful trends.
Self-service BI and AI-augmented analytics
Self-service BI is one of the most significant shifts of the past decade: giving business users the ability to query data and build their own reports without depending on the IT team. Power BI, Tableau and Metabase pioneered this democratization. The condition for self-service to actually work: a well-defined semantic layer that exposes business concepts rather than raw tables, and a data catalog that helps users find and understand the data available to them.
AI-augmented analytics is the next step: tools automatically detect meaningful correlations, explain a KPI's variation in natural language, suggest relevant visualizations, and let users query data conversationally ('Show me this quarter's sales by region compared to last year'). Power BI Copilot, Tableau Pulse and Looker's Explore Assistant are commercial implementations of this trend.
OLTP (Online Transaction Processing) systems are optimized for frequent transactional writes (your CRM, ERP, mobile app). OLAP (Online Analytical Processing, a concept formalized by Ted Codd in 1993) systems are optimized for complex analytical queries over large volumes of data. BI runs on OLAP systems (Data Warehouse, Lakehouse) fed from OLTP systems through ETL/ELT pipelines. Mixing the two inside the same system is a classic architectural mistake.
KPI: definition, construction and pitfalls to avoid
A KPI (Key Performance Indicator) is a quantifiable measure that evaluates the effectiveness of an organization, a process or an individual against a defined objective. The key word is 'Key': a KPI must be a crucial indicator tied to a strategic objective, not simply a metric that happens to be available and easy to calculate.
The SMART criteria for a good KPI
An effective KPI meets five SMART criteria: Specific (measures one precise thing, not a vague notion), Measurable (quantifiable with reliable and available data), Achievable (realistic given the context and resources), Relevant (directly tied to the organization's strategic objectives), and Time-bound (with a clear reference period and a defined measurement frequency).
Example of a SMART KPI: 'Increase 90-day customer retention from 65% to 75% by Q4 2026 through new onboarding features.' Counter-example: 'Improve customer experience' — it is neither measurable, nor specific, nor time-bound. Writing a KPI properly forces precision about what you measure, how, and to what extent.
Lagging KPIs vs leading KPIs
Lagging indicators measure past results that confirm a trend that has already played out: revenue, churn rate, Net Promoter Score (NPS), EBITDA. They are reliable and easy to measure but do not let you anticipate or act ahead of time. Leading indicators measure predictive signals that foreshadow future results: number of demos scheduled (precedes signed deals), activation rate of new features (anticipates retention), time-to-first-value (predicts churn).
A balanced strategic dashboard combines both: lagging KPIs show where you stand, leading KPIs signal the direction you're heading and let you act before the final results are locked in.
OKR vs KPI: two complementary logics
OKRs (Objectives and Key Results), popularized by Google, combine an ambitious qualitative objective (O) with 3 to 5 measurable key results that define success (KR). Example: O = 'Become the trusted reference for data teams'; KR1 = 'Reach 10,000 decks generated through the catalog'; KR2 = 'Pro user NPS above 50'. OKRs are typically quarterly, ambitious (target score of 0.7/1) and impact-focused.
KPIs are permanent and measure the ongoing performance of an existing process or activity. OKRs are temporary and measure progress toward a transformation goal. In practice: OKRs are set at the start of a quarter and feed the operational KPIs that track them. An OKR becomes a KPI once the objective is reached and turns into the new baseline to maintain.
Vanity metrics give the illusion of success without being actionable. 'Page views', 'sign-ups', 'follower count' are often vanity metrics if they are not correlated with real business objectives. The simple test: 'What would we do differently if this KPI doubled?' If the answer is 'nothing different', it is probably a vanity metric. Favor actionable metrics instead: activation rate, revenue per active user, 30/60/90-day retention rate.
The BI tools on the market
The BI tools market is split between legacy enterprise solutions, newer cloud-native platforms and modern open-source tools. The right tool depends on your cloud ecosystem, your team's skills, and your target use cases.
Microsoft Power BI and DAX
Power BI is the global BI market leader according to the Gartner Magic Quadrant, particularly dominant in Microsoft organizations (Azure, Office 365, Teams). Its drag-and-drop dashboard builder is accessible to business users; its Power Pivot analytical engine (columnar, in-memory) delivers excellent performance on tables with millions of rows. Power BI Desktop is free; Power BI Service (cloud collaboration, sharing, scheduled refreshes) requires a Pro license (around $10/user/month) or Premium.
DAX (Data Analysis Expressions) is Power BI's formula language for advanced calculations. It lets you build context-aware calculated measures — CALCULATE, SUMX, DATEADD — for complex aggregations such as a rolling 12-month total, year-over-year growth, or average basket size filtered by segment. DAX has a steep learning curve but is very powerful for time-based analysis and contextual filtering.
Tableau (Salesforce)
Tableau is the reference tool for advanced analytical visualizations. Its drag-and-drop interface lets non-developers build sophisticated visualizations (scatter plots, treemaps, interactive geographic maps, small multiples) without writing SQL. Acquired by Salesforce in 2019, Tableau integrates natively into the CRM ecosystem and Salesforce Data Cloud.
Tableau stands out for its dataviz and visual exploration capabilities: Table Calculations for time-series analysis, Level of Detail (LOD) Expressions for aggregations at variable granularity, and Tableau Pulse for automatic insight detection. It is often preferred by analytics teams and data journalists who value exploration and visual storytelling.
Looker (Google) and LookML
Looker stands apart architecturally: rather than storing data, it generates SQL on the fly and pushes it directly to the Data Warehouse. LookML, Looker's modeling language, defines the semantic layer — dimensions (columns), measures (aggregations), filter sets and explores — in YAML. This definition is centralized, versioned in Git, and every Looker query derives from this model.
Acquired by Google in 2019, Looker integrates natively into Google Cloud and BigQuery. Looker Studio (formerly Data Studio) is the free version, more accessible but without LookML. Looker is the tool of choice for data-mature organizations that want a single source of truth for all their metrics and value governance and metric consistency.
Metabase, Apache Superset and embedded BI
Metabase is the most accessible open-source BI tool: without SQL, business users build reports through a guided interface (filters, groupings, aggregations in a few clicks). Metabase Cloud is the SaaS version; the open-source version is easy to self-host. Apache Superset is the more powerful open-source alternative, adopted by companies like Airbnb, Lyft and Preset: full SQL support, advanced visualizations, plugins, and an API for embedding.
Embedded BI integrates BI dashboards directly into third-party applications via iframes or SDKs. Grafana (open-source, historically used for infrastructure monitoring) is increasingly used for embedded operational BI. Redash, Preset (Superset as a service) and Lightdash (open-source, dbt-native) are modern alternatives.
Dimensional modeling: star schema, grain and SCD
Dimensional modeling is the technical foundation of analytical Data Warehouses. Formalized by Ralph Kimball in 'The Data Warehouse Toolkit' (1996), it structures data to optimize OLAP queries and make it understandable to non-technical business users.
The star schema: fact table and dimension tables
A star schema places a fact table at the center, surrounded by dimension tables. The fact table holds measurable events (transactions, sales, clicks, calls) with their numeric measures (amount, duration, quantity) and foreign keys pointing to each dimension. Dimension tables hold the descriptive attributes that provide context: a Date dimension (day, week, month, quarter, year, holiday), a Customer dimension (name, segment, region, acquisition date), a Product dimension (category, brand, list price).
The schema is called a 'star' because dimensions radiate around the fact table like the points of a star. Joins are simple (one join per dimension), which strongly optimizes OLAP query performance — unlike normalized (3NF) schemas that require multiple joins. A business user naturally understands this schema: they think in terms of 'sales by product by month by region', which maps directly onto dimensions.
The fact table grain: the foundational decision
The 'grain' is the precise definition of what a single row in the fact table represents — it is the single most important decision in dimensional modeling. A poorly defined grain creates aggregation problems that are impossible to fix without a full rebuild. Examples: 'one row = one commercial transaction' (transactional grain), 'one row = a daily snapshot of an account's state' (periodic grain), 'one row = one click event' (event grain).
The grain dictates which dimensions are naturally available and which aggregations are valid. A transactional grain cannot directly count 'the number of active customers this month' (a deduplicated aggregation) without an intermediate aggregation step. Nailing down the grain precisely before building fact tables saves months of analytical technical debt down the line.
Slowly Changing Dimensions (SCD): tracking dimension history
Dimensions change over time: a customer moves to a new region, an employee changes department, a product changes category. Slowly Changing Dimensions (SCD) define how to handle these changes. SCD Type 1: overwrite the old value with the new one — simple but loses history (a report on sales 'by customer region' will show the current region, not the region at the time of the sale). SCD Type 2: add a new row with effective_date and expiry_date columns (or an is_current boolean) — preserves complete history at the cost of row multiplication.
SCD Type 3 adds a 'previous value' column (current_region + previous_region) — a limited compromise that only tracks the most recent change. SCD Type 4 uses a separate history table. In practice, SCD Type 2 is used in about 80% of cases where history matters. dbt offers the dbt_utils package and Snapshots to implement SCD Type 2 automatically based on the delta between two runs.
Conformed dimensions and Kimball's bus architecture
Conformed dimensions are dimension tables shared across multiple fact tables and data marts. The Date dimension is the canonical example: the same date table is used by sales facts, production facts, and customer support facts. This lets you combine facts from different domains into a single report (sales + support tickets by month) because they share the same dimension key.
Kimball's 'bus architecture' is a matrix listing data marts in rows and dimensions in columns — a checkmark shows which dimensions are conformed across which data marts. It is the planning tool that guarantees interoperability between the different analytical areas of a Data Warehouse.
Ralph Kimball formalized dimensional modeling in 'The Data Warehouse Toolkit' (1996, 3rd edition 2013). His bottom-up approach (starting from business processes) contrasts with Bill Inmon's top-down method (a normalized enterprise model first). Both methodologies coexist today: Kimball dominates pragmatic new projects, Inmon remains present in large organizations with strong consistency requirements.
Kimball, R., Ross, M. - The Data Warehouse Toolkit, 3rd Edition, 2013The semantic layer: a single source of truth for metrics
The semantic layer is an abstraction layer between raw data (Data Warehouse, Lakehouse) and BI tools. It centralizes business metric definitions, calculation logic and access rights, and exposes them consistently to every reporting and analysis tool.
The problem it solves is fundamental: without a semantic layer, every BI tool or analyst recalculates their own metrics using their own logic. The conversion rate in Power BI differs from the one in Tableau because teams have subtly different definitions of the denominator ('sessions with at least one product page view' vs. 'total sessions'). Multiply that by ten tools and analysts, and metric chaos becomes a major governance problem.
dbt Semantic Layer and MetricFlow
The dbt Semantic Layer (formerly dbt Metrics, built on MetricFlow) lets you define metrics directly in your dbt project, in YAML. You define the metric once — its name, type (simple, ratio, derived), time grain, and breakdown dimensions — and MetricFlow generates the optimal SQL for each consuming tool. These metrics are versioned in Git, documented through dbt Docs, and tested with the same frameworks used for SQL transformations.
dbt Semantic Layer compatibility extends to Tableau, Power BI, Hex, Mode and any JDBC/API-compatible tool. The key advantage over Looker: metrics stay inside the dbt project most organizations already rely on for their transformations, avoiding an extra layer.
LookML and Cube.js: two approaches to the semantic layer
LookML (Looker) is the market's most mature solution: a YAML language that defines dimensions, measures, joins and explores. Every Looker user goes through LookML — it is impossible to build an ad hoc query without going through the semantic layer, which guarantees total consistency. The downside: LookML is exclusive to Looker and creates strong lock-in to the Google ecosystem.
Cube.js is the open-source semantic layer solution that is independent of any BI tool. It exposes a REST/GraphQL/SQL API that any tool can consume — you define cubes (analytical entities), their dimensions and measures in JavaScript or YAML, and Cube generates optimized SQL for each source (BigQuery, Snowflake, Postgres, ClickHouse). It is the choice for organizations that want a semantic layer without being tied to a specific BI tool.
The semantic layer is not a luxury — it is metric governance infrastructure. Without it, a growing number of analysts, tools and dashboards inevitably fragments definitions and erodes trust in data. When the CFO and CMO present different growth numbers to the board, each backed by a source that is 'official', the problem is not technical — it is the absence of a semantic layer.
Data visualization and data storytelling: building effective dashboards
Data visualization is not about making data pretty — it is about making it understandable and actionable. A good dashboard guides the user's attention to the most important information and helps them make a decision or ask a more precise question. A bad dashboard drowns key information in visual noise.
Choosing the right chart type
The right chart depends on the type of comparison you want to communicate. Comparing values across categories: a bar chart (horizontal for long labels) or column chart (vertical for short time series). Change over time: a line chart for continuous trends, an area chart to show volume. Proportions: a pie chart for at most 3-4 categories (beyond that, use a sorted horizontal bar chart). Correlations between two variables: a scatter plot. Distributions: a histogram or box plot.
Three common mistakes to avoid: pie charts with more than 5 segments (unreadable), Y axes that do not start at zero (distorting the perceived magnitude of changes), and 3D charts (depth creates perceptual distortion). Simple rule of thumb: if a chart needs a legend to be understood, it is probably too complex.
Principles of effective dashboard design
An effective dashboard respects information hierarchy: the most important KPI in the top left (Western eyes naturally start scanning there), context and detail below and to the right. Limit dashboards to 5-7 KPIs — beyond that, attention fragments and no single KPI is truly monitored. Every KPI needs a reference baseline (target, prior period, benchmark), without which the raw value is hard to interpret.
Data storytelling structures insights as a narrative: context (what's happening?), complication (what's the problem or gap?) and resolution (what action do you recommend?). The best dashboards guide the user from observation to action, not just from question to data.
A proliferation of dashboards across an organization is a symptom of poor BI governance. When every team builds its own dashboards with its own definitions, decision-makers no longer know which number to trust. A regular (semiannual) audit of active dashboards — view counts, last access, semantic duplicates — helps identify and deprecate unused dashboards and consolidate fragmented definitions.
Consolidating your BI knowledge with spaced repetition
BI blends business concepts (KPI, metrics, OKR, vanity metrics) and technical ones (star schema, SCD, DAX, LookML, semantic layer). These notions get mixed up easily in practice — the line between a KPI and a metric, between a leading and a lagging indicator, between a star schema and a snowflake schema, are typical interview questions for Data Analyst and BI Engineer roles.
Memia's flashcards cover the fundamental concepts of BI and KPIs with cards designed to test understanding (not rote memorization): comparative framings, practical scenarios that force a choice between two approaches, and open questions about trade-offs.
The most recurring themes in Data Analyst and BI interviews: KPI vs metric (and how to defend a KPI choice), star schema vs snowflake schema (trade-offs), SCD Type 1 vs Type 2 (and when to choose which), lagging vs leading KPIs (with concrete examples), DAX vs SQL vs LookML (when do you use which?), and the role of the semantic layer. Mastering these comparisons is more useful than memorizing isolated definitions.
Explore the Data & AI cluster
Frequently asked questions about Business Intelligence and KPIs
What is Business Intelligence?
Business Intelligence is the set of processes, technologies and tools that collect, analyze and present information to support decision-making. It answers descriptive questions ('what happened?') and diagnostic ones ('why?'), and differs from Data Science, which answers predictive questions ('what will happen?') and prescriptive ones ('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. Simple test: if doubling this metric would not change the team's decisions, it is a metric, not a KPI.
What is the difference between a lagging KPI and a leading KPI?
Lagging KPIs measure past results: revenue, NPS, churn rate. They confirm a trend that has already played out. Leading KPIs measure predictive signals that foreshadow future results: number of demos scheduled, activation rate, time-to-first-value. A good dashboard combines both: lagging KPIs say where you stand, leading KPIs signal where you're heading.
Which BI tool should you choose: Power BI, Tableau or Looker?
Power BI is the natural choice in Microsoft ecosystems (Azure, Office 365), with accessible licensing and a powerful DAX engine. Tableau is preferred for advanced visual exploration and sophisticated dataviz, particularly in large analytics organizations. Looker is optimal for data-driven organizations that want a centralized semantic layer (LookML) and Google Cloud/BigQuery integration.
What is a star schema?
A star schema is an analytical data model with a central fact table (containing quantitative measures: sales, revenue, quantities) surrounded by dimension tables (containing descriptive context: date, product, customer, region). Joins are simple and OLAP performance is excellent. It is called a 'star' because the schema forms a star shape with the fact table at the center.
What is a Slowly Changing Dimension (SCD)?
An SCD manages how a dimension changes over time (e.g., a customer moves to a new region). Type 1 overwrites the old value (simple, but loses history — the current region replaces the region at the time of sale). Type 2 adds a new row with effective_date/expiry_date validity dates — preserves complete history. Type 2 is the most widely used when historical analysis matters.
What is the semantic layer?
The semantic layer is an abstraction layer between raw data and BI tools. It centralizes metric definitions to guarantee they are calculated the same way across every tool. Without it, the conversion rate in Power BI can differ from the one in Tableau depending on each analyst's logic. dbt Semantic Layer, LookML (Looker) and Cube.js are the leading solutions.
What is DAX in Power BI?
DAX (Data Analysis Expressions) is Power BI's formula language for advanced calculations. It lets you build context-aware calculated measures (CALCULATE, SUMX, DATEADD) for complex aggregations: a rolling 12-month total, year-over-year growth, average basket size filtered by segment. DAX has a steep learning curve but is very powerful for time-based analysis and complex contextual filtering.
What is the difference between OKR and KPI?
KPIs (Key Performance Indicators) measure the ongoing performance of an existing process and are permanent. OKRs (Objectives and Key Results) combine an ambitious, temporary objective with measurable key results for the quarter. In practice: OKRs are set at the start of a quarter and define which KPIs to move. An OKR becomes a KPI once the objective is reached and turns into the new baseline to maintain.
What is self-service BI?
Self-service BI gives business users the ability to build their own reports and query data without depending on the IT team. It requires a well-defined semantic layer (to expose business concepts rather than raw tables) and a data catalog (to help users find and understand the data). Power BI, Tableau and Metabase are the most widely used tools for self-service BI.
Previous article: Data Governance - definition and implementation