A Good Report Starts Before the First Chart
The data model is the layer between source systems and the report. It defines how a company understands a customer, product, order, revenue, margin, or fulfillment date. When these definitions are inconsistent, even an attractive dashboard can lead to poor decisions. Microsoft notes that Power BI optimization covers data sources, the semantic model, visualizations, and the environment in which the solution operates. For this reason, improving performance should begin with organizing the data, relationships, and calculations.
Before building the model, it is worth determining:
- which decisions the report should support,
- what level of detail is required,
- which systems will serve as data sources,
- how often the data should be refreshed,
- who should be able to access it.
These assumptions help prevent the model from loading information that no one will ultimately use.
A Star Schema Simplifies Analysis
The star schema is the most commonly recommended structure. At its center is a fact table, such as sales, costs, or service tickets, surrounded by dimension tables describing customers, products, dates, sales representatives, and locations. Fact tables store events and numerical values, while dimensions provide the context needed for filtering and grouping. This structure supports the performance and usability of Microsoft Power BI semantic models. Users can find fields more easily, while designers retain greater control over filter flow.
A sales table may contain customer, product, and date identifiers, as well as quantity, revenue, and cost. The customer name, segment, and region should be stored in the Customer dimension, while brand and category should be placed in the Product dimension. Descriptive information is then no longer repeated for every transaction, and the model can be expanded more easily to include new channels or markets
Relationships Must Reflect Business Logic
Microsoft Power BI automatically detects some relationships, but the model designer should verify their cardinality, filter direction, and active or inactive status. One-to-many relationships dominate in a star schema: one customer may have many transactions, and one product may appear in many sales line items. Poorly designed relationships can return blank values or duplicate results. It is also important to remember that correct relationships are necessary to calculate and present data from multiple tables.
The following areas require particular attention:
- many-to-many relationships,
- bidirectional filtering,
- multiple date types within one table,
- direct connections between fact tables,
- the absence of a unique key on the “one” side of a relationship.
Many-to-many scenarios often require a bridge table. Microsoft also recommends using a star schema instead of directly connecting two fact tables.
A Smaller Model Usually Performs Better
Every loaded column consumes memory, while fields with a high number of unique values can significantly increase the size of the model. This applies, for example, to long descriptions, technical identifiers, and precise timestamps. Columns that are not used in relationships, calculations, or reports should be removed. Data loading should also be disabled for Power Query queries that are used solely to prepare other tables.
For very large tables, aggregation may be worth considering when users do not need to analyze every individual transaction. Pre-aggregating data is one of the most effective ways to reduce the size of an imported model.
Executives may need to analyze daily results by product and region, while the complaints department may require document-level detail. The two teams do not have to use the same level of granularity, but they should work with the same KPI definitions.
Import, DirectQuery, or a Composite Model?
The data storage mode should reflect business requirements. Import mode generally provides a highly interactive experience because the data is stored in the model’s memory. DirectQuery leaves the data in the source system and sends queries while the user is working with the report. As a result, performance also depends on the database, network, and query design.
Composite models combine different storage modes, but they are more complex to manage. In DirectQuery solutions, query folding is particularly important because it allows as many transformations as possible to be performed by the source system.
In simplified terms:
- Import is a good choice when analysis speed is the priority,
- DirectQuery is useful when data must remain in the source system or needs to be highly current,
- a composite model can combine historical data stored in Import mode with current data that is queried more frequently.
For large transactional tables, incremental refresh is also worth implementing. Power BI then updates only the most recent data range instead of reloading the entire history.
Measures Create a Common Business Language
Net revenue, margin, the number of active customers, and target achievement should not be calculated separately on every report page. A better approach is to create centralized DAX measures that can be used by all visualizations and reports connected to the model. Measures respond to user filters and calculate results within the current context.
Calculated columns work differently. They create a value for every row, and those values are stored in the model. For this reason, the calculation type should be selected according to the specific use case rather than automatically creating additional columns.
The model should also be convenient for report authors. The following practices help:
- clear names for measures and columns,
- descriptions of key KPIs,
- hidden technical keys,
- folders that organize measures,
- designated owners of business definitions,
- regular validation of results with the finance team or process owner.
This allows users to work with one consistent set of concepts and metrics.
Scalability and Reliability Must Be Planned
A model is scalable when it can support additional reports without duplicating data and formulas. One semantic layer can power an executive dashboard, sales analysis, and regional reporting, provided that it has a stable structure and consistent measures.
Access permissions must also be planned. Row-level security allows rows to be filtered according to a user’s role. For example, a regional manager may see only data for their own region. However, row-level security is not designed to hide entire tables, columns, or measures.
Before publication, the results, refresh process, and performance of common user interactions should be tested. It is worth checking for missing keys, unusual values, large date ranges, and security roles. This reduces the risk that an incorrect definition will be propagated across multiple reports.
A Good Model Remains Invisible to the Use
Report users should not have to think about cardinality, relationships, or storage modes. They should be able to select the scope of their analysis and quickly receive a reliable answer.
A star schema, reduced data volume, correctly designed relationships, centralized measures, and an appropriate refresh strategy form the foundation of this experience. Together, they help the solution maintain its performance as the number of records, reports, and users grows. A well-designed semantic model becomes a shared information layer that the company can confidently use as a basis for decision-making.