Almost every BI performance problem, cost surprise and refresh failure traces back to two things: how the engine stores data, and where the query runs. Neither is usually taught, which is why so much BI work is superstition — people learn that a particular chart type is slow without ever learning why.

A transactional database stores rows together, because a transaction touches whole rows: fetch this customer, update this order. An analytical engine stores columns together, because an analytical query touches few columns and many rows: sum revenue by region for the year.

Two consequences follow, and they explain the whole design:

• You only read the columns you use. Summing one column of a fifty-column table reads one fiftieth of the data. This is the single largest reason columnar engines are fast on analytical work, and it is also why the same engine is dreadful at fetching one whole row.
• A column compresses far better than a row does, because values in one column are the same type and often repeat. A row mixes a name, a date, a float and a flag, which share no structure.

• Dictionary encoding. Store each distinct value once in a dictionary and replace the column with integer references. A country column with 190 distinct values becomes a column of 8-bit integers plus a small dictionary.
• Value encoding. For numeric columns, store an offset from a base rather than the value — if every value lies between 1,000,000 and 1,000,255, one byte per row will do.
• Run-length encoding. Replace a run of identical values with the value and a count. This is why sort order matters enormously: the same column sorted well compresses several times better than the same column unsorted, and the engine chooses a sort order per segment to maximise this.
• Bit-packing. If the dictionary has 190 entries, eight bits per row is enough. Nobody stores a 64-bit integer for a value that needs eight.

Import or extract loads a compressed copy into the BI engine. Queries never touch the source, so they are fast and free. The data is as of the last refresh, the model must fit in memory, and the refresh must finish inside its window.

Direct query or live connection leaves the data where it is and translates every visual into a query against the warehouse. The data is always current. But every user interaction becomes a query — and a dashboard with eight visuals and four slicers can generate dozens of queries per page view, per user.

Hybrid or composite splits the model: recent partitions query live, historical partitions are imported. This is usually the honest answer for a large estate, and its failure mode is that the split is mis-specified and users silently get the slow path.

The most effective large-model technique is to keep a small pre-aggregated table in memory alongside a large detail table left in the warehouse. The engine answers a summary query from the aggregate — instantly — and only falls through to the detail when the user drills into something the aggregate cannot answer.

This works because of a fact about how dashboards are actually used: the overwhelming majority of queries are summaries, and only a small minority need row-level detail. You are optimising for the common case and accepting a slower path for the rare one, which is the same reasoning as any cache.

The thing to verify rather than assume: is the aggregate actually being hit? Every engine of this kind offers a way to see whether a query used the aggregation or fell through, and an aggregate that never gets hit is pure cost. A single mismatched column data type or an unexpected grain is enough to miss it silently.

The single most consequential modelling decision is star schema versus one wide table, and the answer is nearly always the star: a fact table of measures and foreign keys, surrounded by small dimension tables.

• Compression. Repeating a long product name on every one of a hundred million fact rows is expensive even after dictionary encoding, because the dictionary reference itself is stored per row. Keeping the name in a small dimension keeps the fact table narrow and low-cardinality.
• Filter propagation. These engines are built to filter a small dimension and propagate the result to a large fact table. That path is heavily optimised; filtering a large table directly on a text column is not.
• Ambiguity. A model with many-to-many relationships or bidirectional filters everywhere becomes genuinely ambiguous — the engine cannot tell which path a filter should take, and results become hard to explain. Most ‘the numbers are wrong’ reports in a mature BI estate are relationship ambiguity rather than arithmetic.
• The exception worth naming: some newer cloud-native tools are built around a flattened single-table model and query the warehouse directly, on the argument that the warehouse should do the joins. That is a coherent position, and it moves the modelling work rather than removing it.