Improving Performance by Reducing Granularity
Suppose a sales model contains one row for every item on every customer order. Most report users only ask for monthly revenue by region and product category. The detailed model works, but it carries far more rows than those reports normally need.
An analyst proposes replacing the transaction-level table with a monthly summary. The model should become smaller and faster. Is that enough evidence that the proposal is correct?
Not yet. The missing question is whether anyone still needs analysis below that monthly level. A smaller model isn’t useful if it can no longer answer a required business question. Perhaps a customer service manager must investigate an individual order, or an operations analyst needs daily rather than monthly patterns. The decision has to protect those requirements, not merely produce an impressive reduction in row count.
This is why the current Power BI Data Analyst exam scope includes improving performance by reducing granularity. Granularity, sometimes called grain, describes the level of detail represented by a row. In the original sales table, a row might represent one order item. In a daily summary, a row could represent one date, product, and region. In a monthly summary, many daily records could contribute to one row for a month, product, and region.
Moving to a higher grain means keeping less detail. You might hear that described as reducing granularity, even though the resulting categories cover broader periods or groups. Either way, the practical mechanism is aggregation. Values from multiple detailed rows are combined into fewer summary rows. Amounts may be totaled at the selected grain, while the detailed rows that contributed to those totals are no longer present in that summary table.
That can dramatically reduce model size and improve performance. If thousands of order-item rows can be represented by a much smaller set of monthly category totals, Power BI has less imported data to store and less detail to process for summary questions. But aggregation has a price. Once the summary contains only month, category, region, and total sales, it can’t identify a particular order item that was removed from its grain.
The right starting point is therefore a requirement, not a button or modeling technique. Ask what one row must represent for the analysis people actually perform. Do they need totals by month, week, or day?
Must they distinguish stores, regions, products, or individual stock items? Do they need to investigate customers or orders? The lowest required level is the boundary you can’t cross without changing analytical capability.
For the sales model, imagine that executives need monthly revenue by region and category. Regional managers need daily revenue by store and product. Customer service sometimes needs individual order details.
A single monthly summary can serve the executives, but it can’t serve all three audiences. Daily store and product questions require a lower grain than month, region, and category. Individual-order investigation requires detail that neither summary preserves.
That doesn’t automatically mean the entire reporting model must remain at order-item grain. It means the analyst must document the lowest grain required for each use and decide how detail will remain available. The governed summary model might handle common management reporting while a drillthrough path or another approved detail source supports investigation. The important point is that the detail access must genuinely exist. A report label saying that users can investigate transactions doesn’t restore transaction rows that aren’t available anywhere.
Now consider where the reduction occurs. Power Query is the refresh-time transformation layer. If detailed source rows are grouped into daily or monthly summaries there, the query output delivered to the model has the higher grain. The semantic model, meaning the Power BI model containing tables, relationships, metadata, and calculations, receives that summarized result. A report visual built later can’t simply reveal detailed records that never entered the model through that query.
A report visual can also display summarized values without changing the stored grain. A chart might group transaction-level sales by month even though the underlying model still contains every order item. That changes what the user sees in that visual, but it doesn’t reduce the table’s stored granularity. The model still carries the detailed rows, so the potential model-size benefit of replacing them with a higher-grain table hasn’t occurred.
Data Analysis Expressions, or DAX, can calculate totals in the semantic model. A measure can return monthly sales when a report applies monthly filter context. That’s useful calculation behavior, but the measure doesn’t by itself remove the transaction rows from storage. It answers a question over the model it has. If the detailed data was removed earlier, a DAX calculation can total the remaining summary rows, but it can’t reconstruct order identifiers and line details that no longer exist.
The Power BI service is another adjacent layer. It governs how published content is refreshed, accessed, and consumed. Service permissions can determine whether a person may reach a detailed report or another governed resource. They don’t alter the meaning of a row in the imported summary table. Treating access configuration as though it changed data grain confuses governance with modeling.
These distinctions help you reject tempting but incomplete solutions. Hiding an order identifier column doesn’t aggregate the table. Displaying only monthly totals doesn’t prove that the underlying model is monthly.
Creating a monthly DAX measure doesn’t necessarily reduce the number of stored fact rows. Restricting access to a detail report doesn’t create a summary table. Each action belongs to a layer, and each produces a different consequence.
Return to the three sales audiences. The analyst chooses a daily summary for the main operational model because regional managers must compare stores and products by day. A monthly summary would be too coarse for that requirement. The daily table contains a row for each required combination of date, store, and product, along with the summarized business values. Executives can still view monthly results because daily values can be combined into months.
Customer service still needs individual orders, so the daily model can’t satisfy that investigation alone. The analyst preserves an approved route to the required transaction detail, such as drillthrough or alternative detail access. The precise design depends on the solution’s architecture and governance, but the decision rule stays stable. Don’t discard a level of detail until you know that no required analysis depends on it, or until you’ve preserved another valid way to reach it.
Notice the asymmetry here. Lower-grain data can usually support a broader summary question because many detailed rows can contribute to a total. A report can combine daily values into a month. A monthly summary, however, doesn’t reveal how its value was distributed across particular days unless that detail was retained. The monthly total may be correct while every question about daily variation remains unanswered.
The same issue appears with dimensions other than time. If sales are summarized by region and category, users may still calculate a company total by combining regions. They can’t reliably break that total down by an individual store or product when those identifiers aren’t part of the retained grain. Aggregation supports movement toward broader groups. It doesn’t create dimensions that were discarded.
Before accepting the design, write the grain as a plain statement. For example, one row represents sales for one date, one store, and one product. That sentence is more useful than saying the table contains summarized sales. It tells you which filters should have meaning, which detail questions the table can answer, and which ones require another path. It also gives reviewers a concrete requirement against which they can test the model.
Then inspect the query output or resulting table rather than trusting that a transformation completed successfully. Do the row-defining columns match the documented grain? If the intended grain is date, store, and product, combinations that should be represented separately must not be collapsed merely because a grouping omitted one of those columns. A successful refresh proves that processing completed. It doesn’t prove that the chosen grouping preserved the required analysis.
Measures need validation too. Compare results under filters that exercise every retained part of the grain. Test a single day, then a broader period.
Test one store and one product, then their parent groups. The purpose isn’t to assume that every total must remain unchanged under every filter. The purpose is to observe whether each supported filter produces the result expected from the documented grain.
Suppose the main report looks correct when it shows monthly revenue for all regions. A regional manager then filters to one store and gets a blank or a value that doesn’t distinguish that store. The normal case worked because company totals can be calculated from almost any regional summary. The store filter exposes the real problem. Store wasn’t retained in the summary grain, so the model can’t answer the manager’s required question from that table.
Changing chart formatting won’t fix that cause. Neither will rewriting a measure merely to ignore the missing distinction. The analyst should reproduce the failing filter, state the expected and actual result, and inspect the layer that controls the behavior. If the query grouped only by date, region, and product, but the requirement calls for stores, the grouping is too coarse. The correction must preserve store in the grain or direct that analysis to an appropriate detail source.
After correcting the model, both the broad and narrow cases need another test. The monthly company total should still behave as expected. The single-store daily result should now be observable. Customer service’s path to an individual order should also be checked in the consumer experience rather than assumed from the existence of a report page. Retesting the original case guards against fixing one symptom while damaging behavior that previously worked.
Refresh conditions matter to verification because a model can appear correct with one set of data and fail when new combinations arrive. Imagine test data containing only one store in each region. A summary grouped by region may appear to respond to a store-related report because every regional value happens to correspond to one store. Once a second store appears in the region, the omitted store grain becomes visible. Testing a meaningful edge condition exposes the false confidence created by convenient sample data.
Role and access conditions can reveal a different problem. A summary report may work for a consumer who also has access to transaction details, while another authorized consumer can see the summary but can’t use the promised detail path. The aggregated values could be correct and the model could be smaller, yet the overall requirement would still be unmet for that second consumer. Validation has to include the access state in which the intended audience uses the solution.
This doesn’t mean every report needs unrestricted transaction detail. Governance may require carefully controlled access. The point is to align the claim with reality. If the requirement says certain users must investigate an order, verify that those users can reach the approved detail under the intended permissions. If the requirement says management users need only summaries, don’t assume they also need access to every underlying row.
Storage choices don’t remove the need for this analysis. Whether a solution imports data or uses another supported connectivity approach, the analyst still has to understand the grain users need and the consequences of summarization. Connectivity, data size, latency, governance, security, and architecture can influence the wider design. They don’t make a coarse summary capable of answering a detail question whose defining columns and rows aren’t available.
A familiar feature is therefore not a universally correct answer. One organization may accept a daily sales model with governed transaction drillthrough. Another may need transaction grain in the main semantic model because its routine analysis depends on orders and line items. A third may use a monthly management model with separate operational detail access. The valid choice comes from the required questions, the relevant Power BI layer, and the evidence that users can obtain the intended result.
Performance evidence should also be connected to the change being evaluated. A reduced row set or smaller model can support the case that aggregation achieved its performance purpose. Observable report behavior under the required filters supports the case that analysis was preserved.
Neither observation replaces the other. A fast report that can’t answer the business question is incorrect. A functionally rich model that ignores an explicit performance requirement may also need more work.
Keep the comparison controlled. Evaluate the same required report interactions before and after the granularity change. Confirm that summary totals and retained dimensions behave as expected.
Check the detail route for the people who need it. Then compare relevant performance observations. If several unrelated transformations, calculations, and report changes are introduced at once, it becomes difficult to attribute improvement or failure to the granularity decision.
When results differ, classify the symptom before editing the model. If the query output lacks a required identifier, investigate the refresh-time transformation. If the columns exist but filtering behaves unexpectedly, inspect semantic-model structure and calculation context.
If the correct value exists but a report interaction doesn’t expose it, inspect that interaction. If only certain consumers fail to reach required detail, inspect published access and the consumer experience. This keeps troubleshooting focused on the layer that can actually produce the symptom.
The strongest evidence is a chain from requirement to behavior. The requirement names the lowest detail needed. The transformation or model design preserves that grain, or it provides a governed alternative for detail.
DAX results and report filters then behave correctly at supported levels. Refresh and access tests show that the design still works under realistic conditions. Performance observations confirm that the reduction delivered a worthwhile benefit.
That chain is more dependable than remembering where a setting appears. Interfaces change, and multiple features can sound relevant to performance. The underlying judgment remains the same. State what one row represents, name the questions that representation can answer, identify any detail it can’t answer, and verify the intended alternative. Only then can you say the model became more efficient without losing analysis the business actually requires.
Reducing granularity is a modeling decision with two sides. Aggregating detailed rows into a higher-grain table can reduce model size and improve performance, but any discarded detail stops being directly available from that table. The safe boundary is the lowest grain the required analysis needs, supported by a real, governed path to detail when users must investigate further.
Prove the design at the layer where each behavior occurs. Inspect the summarized query or table, test calculations and filters at broad and narrow levels, and confirm that intended consumers can reach any promised detail. When those observations match the documented requirement, improved performance and preserved analysis are both demonstrated rather than assumed.
Choose a lower granularity that preserves the questions users still need to answer. Which drill or detail request would reveal that you reduced the data too far? Use the matching episode reference as the visual and technical handoff for practice with this concept. This lesson was produced by Bare Metal Cyber dot com.