BI Analytics
Fundamentals

Star Schema: the structure that makes BI fast

Before any DAX measure or SQL query can work correctly, the data model has to be right. Most BI performance problems (slow reports, wrong totals, measures that behave unexpectedly) trace back to a poorly designed model. Get the model right and everything else becomes easier.

The dominant pattern in BI is the Star Schema. It organises data into two types of tables:

The "star" shape emerges naturally: the fact table sits at the centre, with dimension tables radiating outward. Each foreign key in the fact table connects to the primary key of one dimension. A sales fact table might have keys to Date, Customer, Product, and Region, four dimension tables, four points of the star.

The alternative is Snowflake Schema, where dimensions are normalised into sub-dimensions. It saves storage but forces Power BI to perform more joins, slows down queries, and makes DAX significantly more complex to write. For most BI workloads, Star Schema beats Snowflake. The trade-off of slightly more storage for dramatically simpler measures and faster performance is almost always worth it.

DAX: the measure language you need to understand

DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services, and Excel's Power Pivot. It looks like Excel formulas but operates on tables and columns, not cells. Understanding one concept separates beginners from practitioners:

Beyond those three, the patterns that appear in almost every production BI solution are:

• Measures vs calculated columns: Measures are computed at query time using the current filter context. Calculated columns are computed at refresh time and stored in the model. Use measures for aggregations, calculated columns only when you need a value to filter or group by.
• DIVIDE() instead of the / operator: handles divide-by-zero gracefully, returning a blank (or a specified alternative) instead of crashing the visual.
• VAR / RETURN: store intermediate results to avoid recalculating the same expression multiple times in a complex measure. Improves both readability and performance.
• ALL() and ALLEXCEPT(): remove filters from the context, used inside CALCULATE to compute totals, percentages, or rankings that ignore slicers.

SQL patterns every BI analyst uses

SQL is the language of the data warehouse, the layer where raw source data is transformed into clean, modeled tables that Power BI can query. Beyond basic SELECT/WHERE/GROUP BY, BI analytics relies heavily on a specific set of patterns.

One pattern worth highlighting in detail: the period-over-period comparison. In SQL, this means joining a table to itself (or using LAG with a window function) to get the previous period's value in the same row as the current period's value, so you can compute the difference in a single SELECT.

The KPIs that business stakeholders actually ask for

Technical BI skills without domain knowledge produce dashboards nobody uses. The metrics that appear on executive reports and board packs follow recognisable patterns across most industries. Finance and HR are the two most common domains for BI analysts, and each has its own vocabulary.

The most common mistake in finance dashboards: confusing Budget vs Actual with Forecast vs Actual. Budget is fixed at the start of the year and measures against the original plan. Forecast updates throughout the year and reflects the best current estimate of where the year will end. A good FP&A dashboard tracks both: the budget variance tells you where you are vs. the plan; the forecast tells you where you're going.

In HR analytics, headcount sounds simple but has three valid definitions: point-in-time (employees active on a specific date), average (mean headcount over a period), and FTE-adjusted (weighted for part-time). When stakeholders say "headcount," always clarify which definition they mean before building the measure.

The modern BI stack: where the data goes before Power BI

Power BI is the last mile, the visualisation layer that executives interact with. Behind it is a stack that ingests, stores, and transforms data before it ever reaches a dashboard. Understanding the stack helps you debug problems, communicate with data engineers, and design solutions that scale.

The data warehouse is typically organised in layers: a raw layer that stores source data exactly as received, a staging layer where data is cleaned and standardised, and a mart layer where domain-specific star schemas live: the Finance mart, the HR mart, the Sales mart. Power BI connects to the mart layer.

The transformation layer (the SQL logic that moves data from raw to mart) is increasingly built with dbt (data build tool), which lets you write SQL transformations as version-controlled, tested, documented models. On the cloud side, the common combinations are Snowflake + dbt + Fivetran, or Azure Synapse + Power BI in a Microsoft-native stack.

As a BI analyst, you don't need to build the ingestion pipeline, but you need to know it exists, understand how data arrives in the warehouse, and be able to trace a wrong number backwards through the layers to find where it went wrong.