Dimensions, Measures, and Identifiers in Business Data

Hello! Last time, you established what one row represents. Now we’ll look across the columns in those rows. In a storefront export, OrderID, ProductCategory, and LineRevenue may sit side by side, but they serve different analytical purposes.

By the end of this lesson, you should be able to label a field as an identifier, a dimension, or a measure, explain your choice, and spot a total that would be misleading. Allow about 40 minutes, including the short resource study and two work-alongs.

What a column is for matters more than how it looks

Suppose a stakeholder asks, “How much revenue did each product category generate?” The question has two parts. Revenue is the amount to calculate; product category determines how to split that amount. In analytics terminology, revenue is a measure and category is a dimension.

An identifier has a more specific job: it labels an entity or record so you can tell it apart from others. An order ID might contain only digits, but adding those digits would not produce a meaningful business total. Identifiers are commonly treated as dimensions in BI tools, yet it is useful to name their identifying role separately. OrderID distinguishes orders; ProductCategory describes and groups them.

Watch Adam Finer’s What Are Metrics & Dimensions? for a quick visual introduction. The video uses metric where this lesson usually uses measure. Pay particular attention to why a numeric order ID is not an amount.

The practical test is: What would I do with this field to answer the business question?

• An identifier distinguishes something: OrderID, CustomerID, or EventID.
• A dimension describes, groups, or filters: ProductCategory, Country, Channel, or OrderDate.
• A measure records a quantity you may calculate with: Units, LineRevenue, or DurationSeconds.

These roles are not determined by data type alone. A date is useful for grouping orders by day or month, not for summing dates. A postal code may look numeric but functions as a location label. Conversely, a duration recorded in seconds is a number for which an average may be meaningful.

Read the corresponding explanations in Metabase Learn’s Measures and dimensions. Its comparison between a date and a duration is especially useful for fields that do not fit a simple “text versus number” rule.

That last distinction matters. LineRevenue may be summed to find sales, but summing unit prices does not find sales. An average price could be useful for a different question. “Measure” tells you a field holds a quantity; it does not choose the right calculation for you.

Work-along: label a storefront export

Take about 10 minutes with this small export. Its grain is one row per product line on an order. Before looking at the interpretation, copy the column names onto paper or into a sheet and mark each one identifier, dimension, or measure. For an identifier, also note what it identifies.

Start with the three IDs. LineID identifies an individual product line and is unique in these four rows. OrderID identifies an order, so O101 correctly appears on two lines. CustomerID identifies a customer; C7 appears on lines belonging to two different orders. None of these codes is an amount to sum. Their usefulness lies in distinguishing records or entities. For example, counting the distinct OrderID values gives three orders represented here, whereas counting rows gives four product lines.

OrderDate, Country, and ProductCategory are descriptive dimensions. They let you ask for sales by date, in the UK, or by category. Units, UnitPrice, and LineRevenue are measures because they quantify the transaction lines. Their suitable calculations differ:

• Adding Units across these lines gives 7 units sold.
• Adding LineRevenue gives 100 in revenue.
• Adding the four UnitPrice values gives 60, but 60 is not revenue. A unit price is a rate per unit, not the money earned on the line.

Notice how the prior lesson’s grain check supports this classification. Because each row is a product line, the line revenue is recorded once for each line and can be added across these rows. The order and customer IDs repeat because several lines can belong to the same order or customer—not because those IDs are quantities.

An identifier can also be used to form a very detailed breakdown, such as revenue by OrderID. That makes it behave like a dimension in the report, but its underlying purpose remains identifying orders. Keeping the separate label helps you remember to count distinct IDs when counting orders, rather than summing IDs or assuming every line is a new order.

Put a measure and a dimension together

Now spend about 8 minutes following the stakeholder’s question: “How much revenue did each product category generate?” Keep ProductCategory as the grouping field and add LineRevenue within each group.

The dimension supplies the labels Shirts and Hats; the measure supplies the amounts. The category totals reconcile to the overall total of 100. If the question instead asked for units sold by country, you would keep the same dataset but group by Country and sum Units.

The supplied Tableau image shows this pairing in a reporting interface: Category is placed on rows and SUM(Sales) is used to show a sales total for each category.

You do not need Tableau for the principle. The same “measure by dimension” question can guide a spreadsheet summary now and a SQL or Power BI report later.

Two checks before trusting a number

Field labels help you plan an analysis, but they do not guarantee that a calculation is valid. Use these final checks when inspecting an unfamiliar export.

First, check what level each value describes. Imagine adding an OrderTotal column to the product-line table. O101’s order total is 50. If that 50 appears on both L1 and L2, summing the OrderTotal column would count O101 twice. OrderTotal is a numeric measure, but it describes a whole order, while each row describes a product line. A field can be correctly identified as a measure and still be unsafe to add across the available rows.

Second, check the field’s job in this particular question. Units usually acts as a measure when you total units sold. It could instead act as a grouping field if the question were, “How does revenue differ between lines containing one unit, two units, and three units?” You would group by the recorded Units values and calculate revenue within those groups. Its data type has not changed; its analytical role has.

For a quick 5-minute audit of any business dataset, state its grain, then annotate each column with its meaning:

Identifies what? Describes or groups by what? Measures how much or how many?

For every numeric-looking field, add one more note: which calculation, if any, makes business sense at this table’s grain. This prevents both common errors: treating an ID as a quantity and treating every quantity as something to sum.

Key takeaways

Identifiers distinguish entities or records; dimensions provide descriptive context; measures provide quantities to calculate. Numeric appearance alone cannot tell you which role a field plays. Combine this column-level check with the previous lesson’s row-grain check before reporting totals or counts.

Next, you’ll import a CSV into Excel and turn it into a structured table. These annotations will help you inspect its columns rather than accepting every imported field at face value.