Data Modelling in Power BI: Choosing the Right Approach

When working with Power BI, the quality of your data model is just as important as the reports and dashboards you eventually build on top of it. Power BI is flexible in terms of the data sources it can connect to, whether that is a relational transactional database (OLTP), a data warehouse designed with a star or snowflake schema (OLAP), or even modern lakehouse architectures. The choice of source and design, however, has a direct impact on both performance and usability.

Is there a preferred design in Power BI?

Power BI is optimised for analytical models rather than transactional structures. A star schema is generally considered best practice for Power BI because it reduces complexity, improves query performance, and makes DAX expressions simpler to write and maintain.

Snowflake schemas are also supported but tend to introduce more relationships and joins, which can negatively impact performance, especially at scale. In practice, the closer your model resembles a star schema, the smoother your report experience will be.

Lakehouse architectures, particularly when paired with Microsoft Fabric, allow data engineers to organise both structured and semi-structured data into a single source. In this setup, you can create curated tables that resemble a star schema before bringing them into Power BI, offering flexibility while maintaining performance.

Can we remodel a database inside Power BI?

A common scenario is sourcing data directly from a relational database, such as Adventure Works. In its raw state, the database is structured for transactions and not for analytics, so the schema is highly normalised.

Using Power Query in Power BI, you can reshape this into a star schema by creating fact and dimension tables. For example, Adventure Works stores product data across multiple related tables. By flattening these into a single Product dimension table, and connecting it to the Sales fact table, you build a star schema optimised for reporting.

In this sense, Power BI acts as a lightweight version of SQL Server Analysis Services (SSAS). However, the key question is cost-effectiveness. Should you build a robust semantic layer in SSAS (or Azure Analysis Services) before loading into Power BI, or should you rely on Power BI alone?

For smaller or mid-sized models, reshaping within Power BI is usually sufficient. For large enterprise-scale solutions, where reusability, governance, and centralised management are critical, SSAS or its modern replacement (Fabric’s semantic models) might be more effective.

Take the Adventure Works database as an example. The OLTP version contains tables such as SalesOrderHeader, SalesOrderDetail, Product, ProductSubcategory, and ProductCategory.

In Power BI, you can transform this structure into a star schema:

• Fact Table: Combine SalesOrderHeader and SalesOrderDetail into a single fact table, for example FactSales.
• Dimension Tables: Create dimension tables such as DimProduct (joined with ProductSubcategory and ProductCategory), DimCustomer, and DimDate.

The result is a star schema inside the Power BI data model. This process essentially turns Power BI into a modelling tool, where you restructure the data before analysis.

Does Power BI act as SSAS in this case?

In many ways, yes. Power BI uses the same VertiPaq engine as SQL Server Analysis Services (SSAS) Tabular. Once data is loaded into Power BI, it is compressed and optimised for fast querying. This means you are effectively building a semantic model, similar to what you would do in SSAS Tabular.

However, there are differences in scale and governance. SSAS can handle larger datasets, offers centralised management, and allows multiple Power BI reports to connect to a single semantic model. Power BI, on the other hand, is more lightweight and often sufficient for small to medium datasets.

Cost effectiveness: SSAS vs Power BI

The decision comes down to scale and organisational needs.

• If your datasets are relatively small or medium sized and your requirements are limited to reporting in Power BI, then reshaping the data model directly in Power BI is usually the most cost effective approach.
• If you are working with very large datasets, need enterprise-scale governance, or want a single semantic model reused across many applications, then SSAS (or Azure Analysis Services) is often more cost effective in the long run.

In practice, many organisations start by modelling directly in Power BI, and only move to SSAS when the scale or complexity requires it.

The role of Dataflows in data modelling

A key feature of Power BI is the ability to create Dataflows, which act as a transformation layer before data reaches the dataset. Dataflows allow you to centralise and reuse transformation logic across multiple reports.

It is worth clarifying that there are two types of Dataflows today:

• Power BI Dataflows (classic): These have been around for several years in the Power BI Service. They use the familiar Power Query experience to clean and reshape data, storing the results in a managed Azure Data Lake. They are particularly useful for reusing common entities such as a DimDate table across multiple datasets, ensuring consistency and reducing duplication of effort.
• Fabric Dataflows (Gen2): Introduced as part of the Fabric platform, these are the modern evolution of Dataflows. They still use Power Query, but are tightly integrated with Fabric’s OneLake storage and can land results directly into Lakehouses or Warehouses. This makes them far more scalable and powerful, especially in environments where Fabric is adopted as the enterprise data platform.

Both types of Dataflows help separate data preparation from modelling, but Fabric Dataflows provide the additional advantage of scalability, integration with other workloads, and future-proofing as organisations adopt Fabric more widely.

From a data modelling perspective, Dataflows (in either form) are extremely valuable when:

• You want to remodel OLTP data into a star schema before loading into Power BI datasets.
• You need to apply the same business logic consistently across multiple reports.
• You prefer to offload transformation work from the dataset layer and make it reusable.

Lakehouse integration

Modern BI architectures increasingly use a lakehouse as a unified storage and processing layer. Power BI integrates seamlessly with lakehouses, whether through Fabric Lakehouse or external technologies such as Databricks.

The star schema principle still applies in this environment: you want to curate data in the lakehouse into fact and dimension structures before exposing it to Power BI. The advantage here is scalability, as lakehouses can handle massive datasets and mixed data types while still serving optimised tables for reporting.

Incremental refresh

As models grow, refresh performance becomes a challenge. Incremental refresh in Power BI addresses this by refreshing only recent partitions of fact tables instead of reloading the entire dataset.

For example, in the Adventure Works FactSales table, historical sales data can remain static while only the last few months are refreshed. This greatly improves refresh times and reduces resource consumption, making large models far more manageable.

Putting It All Together: When to Use Which Approach

• Small or departmental models: Directly reshape data inside Power BI using Power Query, targeting a star schema.
• Reusable transformations across multiple reports: Use Power BI Dataflows to centralise logic.
• Enterprise-scale, cross-service reuse: Adopt Fabric Dataflows Gen2 and store curated data in OneLake for broader integration.
• Very large datasets: Implement incremental refresh to optimise performance.
• Enterprise semantic layer: Consider SSAS or Fabric semantic models when governance, reusability, and centralised management outweigh simplicity.

Final Thoughts

Data modelling in Power BI is not about choosing one tool or design in isolation. It is about understanding the trade-offs between simplicity and scalability, between reshaping on the fly and investing in upstream modelling layers.

A star schema remains the gold standard for Power BI performance, but new technologies like Fabric Dataflows and lakehouse integration are reshaping how organisations approach modelling at scale. The key is knowing when to rely on Power BI’s built-in capabilities and when to step up to more formal solutions like Fabric or SSAS.