Identifying a Dataset’s Unit of Analysis

Hello again. In the previous lesson, you translated a broad service-desk concern into a focused question:

For resolved incident tickets in May 2025, which assignment group had the highest SLA-breach rate, and how did that rate compare with April 2025?

That question is well scoped—but it can still produce a wrong answer if you misunderstand what each dataset row represents. Before counting records, calculating rates, or building a chart, an analyst needs to identify the dataset’s unit of analysis, also called its grain.

By the end of this lesson, you will be able to state a dataset’s grain clearly, identify a likely key for that grain, and recognize when repeated values are expected rather than duplicates.

---

Grain: complete this sentence first

A dataset’s grain is the real-world thing, event, or summary that one row represents.

The simplest way to identify it is to complete this sentence:

Each row represents one ________.

For example:

• Each row represents one resolved service ticket.
• Each row represents one status update on a service ticket.
• Each row represents one agent on one calendar day.
• Each row represents one assignment group’s monthly SLA summary.
• Each row represents one product on one customer order.

“Grain,” “granularity,” “unit of analysis,” and “unit of observation” all point to the same essential question: what is a record actually about?

A row is not automatically “one customer,” “one ticket,” or “one sale.” It depends on how the data was collected and stored. A service desk may have several related datasets, each with a different grain:

Dataset	Grain statement	What repeated IDs mean
Tickets	One row per ticket	A ticket ID should normally appear once.
Ticket_Updates	One row per update made to a ticket	A ticket ID appears repeatedly because a ticket can have many updates.
Daily_Backlog	One row per assignment group per calendar day	The same group appears once for each date.
SLA_Monthly_Summary	One row per assignment group per month	The same group appears once for each reporting month.
Agents	One row per agent	An agent ID should normally appear once if this is a current-state list.

The column name alone does not determine grain. A column called ticket_id might be unique in a ticket table but repeat many times in an activity log. The surrounding fields—especially dates, timestamps, event IDs, and category combinations—tell the fuller story.

---

Why grain comes before counting

Return to the question from the previous lesson: compare SLA-breach rates across assignment groups.

A basic calculation might be:

$$\text{SLA breach rate}=\frac{\text{number of resolved tickets that breached SLA}}{\text{number of resolved tickets}}$$

That formula assumes the numerator and denominator are both counting tickets. Therefore, the underlying data must either be at ticket grain or be carefully summarized to ticket grain first.

Consider these two datasets.

Dataset A: one row per ticket

Ticket ID	Assignment Group	Resolved Date	SLA Breached
INC-1001	Network Support	2025-05-03	Yes
INC-1002	Service Desk	2025-05-03	No
INC-1003	Network Support	2025-05-04	No

Its grain is:

Each row represents one service ticket.

Here, counting rows gives the number of tickets, assuming each ticket ID appears once.

Dataset B: one row per ticket update

Update ID	Ticket ID	Update Time	Status	Assignment Group
UPD-01	INC-1001	2025-05-01 09:15	Open	Service Desk
UPD-02	INC-1001	2025-05-01 11:40	Assigned	Network Support
UPD-03	INC-1001	2025-05-03 16:10	Resolved	Network Support
UPD-04	INC-1002	2025-05-03 14:20	Resolved	Service Desk

Its grain is:

Each row represents one recorded update to a service ticket.

Now INC-1001 appears three times, and that is not automatically a duplicate problem. It is expected because one ticket went through three recorded events.

If you count rows in Dataset B, you count updates—not tickets. A report stating there were “four tickets” would be incorrect; there are only two distinct tickets. This is one of the most common analytical errors in workplace reporting: a metric is named correctly but calculated at the wrong grain.

A useful rule is:

Never assume that a row count equals the number of customers, tickets, orders, or employees. First confirm that the table has that entity’s grain.

---

Detail level and aggregation

Grain also tells you the dataset’s level of detail.

A dataset is finer-grained when it records more detailed observations. It is more aggregated when it combines many observations into one row.

For instance, a service-desk dataset could appear in any of these forms:

Grain	Example row	Detail available
Ticket update	One status change for one ticket at one time	Very detailed: process history and timestamps
Ticket	One complete service ticket	Ticket-level analysis by priority, group, or breach status
Agent-day	One agent’s activity on one date	Daily workload trends per agent
Group-month	One assignment group’s monthly totals	High-level reporting, limited investigation detail

Suppose a monthly summary table contains this:

Month	Assignment Group	Tickets Resolved	SLA Breaches
May 2025	Network Support	200	36
May 2025	Service Desk	500	20

Its grain is:

Each row represents one assignment group for one month.

The table has only two rows, but it summarizes 700 resolved tickets. Counting rows would tell you there are two group-month summaries, not 700 tickets.

You can use this table to calculate monthly breach rates:

$$\text{Network Support breach rate}=\frac{36}{200}=18\%$$

But you could not use it to investigate which individual ticket priorities, issue types, or escalation paths contributed to the breaches—those details have already been aggregated away.

The analytical question determines how much detail you need. Monthly group summaries may be enough for a manager deciding where to focus attention. To understand why that group performed poorly, you will usually need ticket-level or event-level data.

---

Grain is often defined by a combination of fields

Sometimes one field uniquely identifies each record. In a ticket table, ticket_id may be enough:

Ticket ID	Priority	Resolved Date
INC-1001	P2	2025-05-03
INC-1002	P3	2025-05-03

Here, ticket_id is a plausible key, and the grain is one ticket.

But a single field is often not enough. Consider a daily backlog summary:

Date	Assignment Group	Open Ticket Count
2025-05-01	Network Support	42
2025-05-02	Network Support	38
2025-05-01	Service Desk	61

Neither Date nor Assignment Group is unique by itself. The combination of:

$$\text{Date}+\text{Assignment Group}$$

should identify one row, assuming the dataset is intended to contain one daily summary for each group.

Its grain statement is not merely “one assignment group.” It is:

Each row represents one assignment group’s open-ticket count on one calendar date.

The time component matters. Without it, you would fail to describe why the same assignment group legitimately appears on multiple rows.

A key is the field, or combination of fields, expected to distinguish one row from another at the table’s stated grain.

This leads to an important distinction:

• A repeated value is not necessarily a duplicate. It may be expected at the dataset’s grain.
• A duplicate record is a repeated row, or repeated key combination, when the table’s intended grain says there should be only one.

For example, in a one-row-per-ticket table, two rows with INC-1001 are suspicious. In a one-row-per-ticket-update table, multiple rows with INC-1001 are normal; duplicate update_id values would be more concerning.

A system-generated row identifier can help identify physical records, but it does not automatically prove the business grain. If every imported row has a unique row_id, that only tells you the extraction gave each row a label. You still need to determine whether the rows represent tickets, updates, daily snapshots, or something else.

---

A repeatable way to identify grain

When you first open a dataset, do not begin with a chart or formula. Use this short investigation routine.

1. Read the table name and available documentation

Names such as incident, ticket_activity, daily_sla_summary, and agent_history are clues, not guarantees. Read any data export description, report title, worksheet name, or available field definitions.

A table named Tickets could contain one current row per ticket—or it could contain one row per ticket per day from a historical snapshot. Confirm rather than infer.

2. Look for the entity, event, and time fields

Ask:

• What business entity appears in the data: ticket, customer, employee, order, product?
• Is each row a single entity, an action involving that entity, or a summary?
• Does the table contain an event timestamp, a reporting date, or both?
• Which fields naturally distinguish records from one another?

For a service-desk table, these fields are particularly informative:

Field pattern	What it may suggest
ticket_id plus current status and resolution fields	One row per ticket
ticket_id, update_id, and update timestamp	One row per ticket update
ticket_id plus snapshot date	One row per ticket per snapshot date
assignment group plus month plus totals	One row per assignment group per month
agent ID plus effective start and end dates	One row per agent history period

3. State the grain in ordinary language

Do not write only “ticket grain” if the time or event detail matters. Prefer a full statement:

• “Each row represents one incident ticket.”
• “Each row represents one status-change event for one incident ticket.”
• “Each row represents one incident ticket as observed at the end of one calendar day.”
• “Each row represents one assignment group’s performance for one month.”

If you cannot complete that sentence confidently, pause the analysis. You may need documentation or a clarification from the data owner.

4. Identify the expected key

Translate the grain into one likely unique identifier:

Grain statement	Expected key
One row per ticket	ticket_id
One row per ticket update	update_id, or possibly ticket_id plus update timestamp
One row per ticket per day	ticket_id plus snapshot date
One row per agent per day	agent_id plus date
One row per group per month	assignment group plus month

This is a hypothesis to test—not a guarantee.

5. Compare the key expectation with the actual rows

Look for whether the candidate key repeats. If a supposed ticket-level table contains 10,000 rows but only 9,200 distinct ticket IDs, investigate before counting tickets.

Possible explanations include:

• genuine duplicate extraction records;
• multiple records per ticket after all;
• ticket history rather than current ticket data;
• reopened tickets represented by multiple rows;
• missing or malformed ticket IDs;
• an intended grain that requires another field, such as date or update ID.

At this stage, do not delete anything merely because a value repeats. First decide whether repetition violates the intended grain.

---

Grain determines what calculations mean

For any potential calculation, ask this question:

What does one value in this column represent at this table’s grain?

The answer determines the appropriate aggregation.

Table grain	Field	Meaning of SUM(field)	Potential mistake
One row per ticket	resolution_minutes	Total elapsed resolution minutes across tickets	Calling this total “agent work hours” without evidence that elapsed time equals effort
One row per ticket	sla_breached coded as 1 or 0	Number of breached tickets	Correct only if each ticket is represented once
One row per group per day	tickets_resolved	Total tickets resolved across selected days	Counting rows instead would count group-days
One row per group per month	sla_breach_rate	Usually not an overall breach rate	Averaging group rates without considering different ticket volumes
One row per ticket update	ticket_id	Not a ticket count	Counting rows measures updates, not tickets

The fourth row deserves attention. Suppose two groups have monthly breach rates:

Group	Resolved Tickets	Breach Rate
Network Support	200	18%
Service Desk	500	4%

A simple average of the two rates is:

$$\frac{18\%+4\%}{2}=11\%$$

But that treats a group with 200 tickets as equally important to a group with 500 tickets. The overall breach rate should use the underlying counts:

$$\frac{36+20}{200+500}=8\%$$

This is another reason grain matters: a percentage summarized at one level cannot always be safely summarized again by averaging it.

---

Grain and the data model you will build later

Later in the course, you will use Power BI to connect related tables. The star-schema image below previews why grain will remain important.

In a typical analytical model:

• A fact table stores events or measurable business activity, such as ticket updates, sales transactions, or daily workload records.
• A dimension table stores descriptive attributes, such as product details, agent details, assignment groups, or promotion details.

For a ticketing model, you might have:

Table	Possible grain
Fact_Tickets	One row per service ticket
Dim_Agent	One row per agent
Dim_Assignment_Group	One row per assignment group
Dim_Date	One row per calendar date

If Dim_Agent truly has one row per agent, it can enrich many ticket rows without increasing the number of tickets. But if an agent table actually has multiple historical rows per agent and you join it without accounting for those periods, one ticket can match multiple agent records. Your ticket counts and totals may multiply.

You do not need to master joins or Power BI relationships yet. The immediate lesson is this: before combining tables, know the grain of both tables and how many rows you expect to match.

---

A grain note for your analyst workflow

For every dataset used in an analysis, add a small note like this:

Table: Ticket_Updates
Grain: One row per recorded update to one service ticket.
Expected key: update_id
Repeated ticket_id values: Expected; a ticket can have multiple updates.
Safe row count interpretation: Number of update events, not number of tickets.
Main caution: Use distinct ticket IDs or summarize to one row per ticket before calculating ticket-level KPIs.

This note helps you make calculations correctly, but it also makes your work reviewable. A teammate should be able to see why you used a row count, a distinct count, a sum, or a different summary table.

For the SLA question introduced last lesson, an appropriate grain note might be:

Table: Resolved_Incidents
Grain: One row per resolved incident ticket.
Expected key: ticket_id
Metric use: Count rows or ticket IDs to calculate resolved-ticket volume,
provided the ticket_id is unique.

If the source table is instead an update log, then that KPI calculation needs an extra preparation step to get one record per ticket. The question did not change; the grain changed what work is required to answer it reliably.

---

Key takeaways

The grain or unit of analysis is what each row of a dataset represents. State it explicitly using the sentence:

Each row represents one ________.

A dataset can be at ticket, ticket-update, ticket-per-day, agent-day, or group-month grain—even when several tables contain the same ticket or agent IDs. Repeated identifiers are only duplicates when they violate the dataset’s intended grain.

Before calculating a KPI, identify:

1. the row’s real-world meaning;
2. the candidate key or key combination;
3. whether repeated values are expected;
4. what a row count and each numeric field actually measure.

Next, you will learn to classify the fields within a dataset as identifiers, dimensions, measures, or dates. Once you know both the row grain and the role of each column, you can begin selecting the right fields for a trustworthy analysis.