Quick Navigation

I’ve lost count of how many times I’ve seen a perfectly constructed data cube deliver reports that made executives nod happily — while the numbers were completely misleading. The culprit? Cube metrics that were defined without understanding their additive nature. If you’ve ever stared at an OLAP cube and wondered why the average doesn’t roll up correctly or why the total seems too high, you’re not alone. Let me walk you through what cube metrics actually are, how to choose them, and the mistakes that even seasoned analysts make.

What Exactly Are Cube Metrics (And Why Most Analysts Get Them Wrong)

A cube metric is a numerical measure stored in a data cube — the core of multidimensional analysis. Think of it as the “what” you’re measuring: sales revenue, units sold, profit margin, inventory count. Sounds simple, right? Here’s where it gets tricky: not all metrics behave the same way when you slice and dice them across dimensions like time, product, or region.

Most data warehousing textbooks define three categories — additive, semi-additive, and non-additive — but they rarely explain why picking the wrong type can quietly corrupt your dashboards. Let me give you a real example from my own consulting days.

Real Story: A client had a cube tracking daily inventory levels. They wanted to see inventory by month. The developer simply summed daily inventory across all days, giving a monthly “total inventory” of 3,000 units — but the warehouse only held 100 units on any given day. The metric was semi-additive (not summable across time), and the total was meaningless. That dashboard made it into the CEO’s quarterly review before anyone caught it.

The core problem: analysts often assume every metric can be summed. Understanding the additive behavior is the first step to building reliable cubes.

The 3 Types of Cube Metrics You Must Know (With Examples)

Type Additive Across All Dimensions? Example Why It Matters
Additive Yes — sum across any dimension Sales amount, quantity sold Most straightforward; works with any aggregate
Semi-Additive No — sum across some dimensions (e.g., time) is invalid Inventory balance, bank account balance Using sum across time double-counts; use average or last value
Non-Additive No — cannot sum across any dimension Profit margin (percentage), price per unit Must recalculate from base components; never sum directly

I always tell my teams: if you’re measuring a level (like inventory at a point in time), it’s semi-additive. If you’re measuring a ratio (like percentage), it’s non-additive. Only flows (like revenue) are safe to sum.

Additive Metrics

These are your best friends. You can sum them across any combination of dimensions and the result makes sense. Common examples: sales revenue, order count, call duration (in seconds). When I designed a cube for a retail chain, all their transaction-level numbers were additive — no brainer.

Semi-Additive Metrics

Here’s where most errors happen. Take inventory: you can sum inventory across products and stores (giving total stock), but summing inventory across time is wrong. The correct aggregation for time is typically an average or the last value. I once watched a BI team report “annual inventory” by summing 12 monthly figures, resulting in a number 12 times the actual stock. To avoid this, always ask: “Does it make sense to add this over time?” If not, use AVERAGE or LAST CHILD in your MDX or SQL.

Non-Additive Metrics

Percentages, ratios, and unit prices are classic non-additive. You can’t sum profit margins across products to get a company margin — you have to sum the numerator and denominator separately, then divide. Same for average order value: summing average values across regions gives a meaningless number. I always store the base components (e.g., total revenue and order count) and compute the ratio in the reporting layer.

How to Design Cube Metrics for a Sales Data Warehouse (A Step-by-Step Case Study)

Let’s build a real cube for a company that sells software subscriptions. We have dimensions: Date, Product, Customer, Region. We need these metrics:

  • Revenue (additive)
  • Number of subscriptions sold (additive)
  • Average subscription price (non-additive)
  • Active subscribers at month end (semi-additive)

Step 1: Identify the Business Process and Grain

The grain is one row per subscription sale. That means each sale event gets a row. Revenue and subscription count are naturally additive at this grain. Active subscribers, however, require a snapshot table — one row per customer per month indicating whether they are active.

Step 2: Choose the Right Aggregation Rules

For the cube, I define:

  • Revenue → SUM (additive)
  • Subscriptions sold → SUM (additive)
  • Average price → AVG (but stored as a calculated measure: Revenue / Subscriptions sold)
  • Active subscribers → LAST NON EMPTY (semi-additive; for time aggregation use the last value in the period)

Pro tip: Never store Average Price as a base measure. Always compute it from Revenue and Count. I once saw a cube where the analyst pre-calculated average price per product, then summed it across products to get a “company average” — that’s a weighted average problem, and the sum approach gave a completely wrong number.

Step 3: Handle Nulls and Zeroes Carefully

Nulls in fact tables can make semi-additive metrics behave unexpectedly. I always use COALESCE or ISNULL to replace nulls with 0 for additive measures, but for semi-additive metrics like inventory, a null might mean “no data” vs “zero stock.” In my practice, I set a default of 0 for sales measures, but for balances I keep null and let the cube logic decide (e.g., using ".Ignore" in SSAS or returning blank in Power BI).

Common Pitfalls When Working with Cube Metrics (From Firsthand Experience)

The “Average Trap”

Analysts love averages because they seem intuitive. But averaging an average (like average of regional average prices) is statistically wrong. I’ve seen dashboards showing “average profit margin” that was simply the arithmetic mean of product-level margins — ignoring that products contribute different revenue weights. The fix: always compute weighted averages (sum of margin dollars divided by sum of revenue).

Ignoring Currency Conversion in Global Cubes

If your company sells in multiple currencies, storing revenue in local currency and then summing across countries yields a useless number. You must convert to a common base currency (e.g., USD) using exchange rates. But exchange rates themselves are semi-additive — you can’t average them across time to get a yearly rate. I always store the rate at transaction date and apply it to each row, then sum the converted amounts.

The “Snapshot” Mistake

I’ve inherited cubes where the developer stored inventory as a daily snapshot but allowed users to sum across months — producing the dreaded “inventory = 30x actual” error. If you use snapshot fact tables, you must restrict time aggregation to average or last value, and educate users. I now add a warning as a measure comment in the cube metadata.

While the concepts are tool-agnostic, here’s what I use (and see in production) most often:

  • Microsoft SQL Server Analysis Services (SSAS) — robust, multidimensional and tabular modes. Great for enterprise cubes with complex semi-additive measures using MDX Avg or LastNonEmpty.
  • Power BI (with DirectQuery on a tabular model) — allows clear measure definitions (e.g., ActiveSubscribers = LASTNONBLANK(…) ).
  • Looker (Google Cloud) — uses liquid SQL to define measures; you specify type: sum, type: average, etc., and it handles aggregations automatically.
  • Apache Kylin / Druid — for real-time OLAP cubes with big data; careful with non-additive metrics as they may require approximation cubes.

I personally swear by SSAS Tabular for most classic BI scenarios because it gives fine-grained control over measure behavior (e.g., semi-additive with time intelligence).

Frequently Asked Questions About Cube Metrics

My cube shows different totals for the same metric when I use different dimensions. Is the cube broken?
Probably not the cube — check your metric type. If you have a semi-additive metric and you’re summing across time, you’ll get inflated numbers. Also, verify that your dimension tables have correct relationships (e.g., all products have matching IDs). I’ve debugged cases where a missing product in the dimension caused partial data. Use a trace query to compare granular data vs. cube output.
How do I handle exchange rates in a cube with multiple currencies without making it non-additive?
Store transaction amount in local currency plus the exchange rate used. Create a calculated measure that multiplies local amount by the rate to yield USD. That converted amount is additive across all dimensions. Never store only the converted amount without the rate, because you lose the ability to recalculate if rates change.
Can I have a non-additive metric in a cube if I never need to aggregate it?
Yes, but be careful. If you store a ratio like profit margin as a base measure (pre-calculated), users can still accidentally sum it in reports. I always recommend storing the numerator and denominator separately and computing the ratio in the presentation layer (e.g., as a calculated measure in MDX or DAX). That way the ratio is never summed.
What’s the best way to educate business users about semi-additive metrics?
I stopped trying to teach them MDX. Instead, I add a text note as a measure property (e.g., “This measure cannot be summed over time”). In Power BI, you can add a custom tooltip. But the most effective tactic is to restrict the default aggregation — for example, set the AggregationFunction in SSAS to LastNonEmpty — so users can’t sum even if they try.

After years of building cubes, I’ve learned that the metric design phase is where you either build trust or plant time bombs. Get the additive behavior right, document your decisions, and always test with a simple “does the slice match the source?” query. Your executives will thank you — even if they never know what a cube metric really is.