A current engagement calls for migrating a rather elaborate financial dashboard from Qlik to Power BI. Just when we think there is light at the end of the tunnel, another business rule is unearthed in Qlik that we need to convert. These rules range from business calculations and dummy rows in dimension tables to allocation overrides.
This brings us to an important question: Where should all this business logic live?
Over the years, I’ve been advocating for the following approach:
| Example | Destination |
| Data quality issues | ETL and star schema tables |
| Custom categories | ETL and star schema tables |
| Allocations | ETL and star schema tables |
| Calculations that can be materialized | ETL and star schema tables |
| Calculated columns | Prefer ETL; semantic model when appropriate |
| End-user refinements (metadata, hierarchies, display folders, custom sorting, etc.) | Semantic model |
| Measures | Semantic model |
| RLS and OLS | Semantic model |
In other words, everything that can be materialized and isn’t a native semantic model feature goes to ETL. That’s what ETL is for, and materialized logic is great for report performance. On the other hand, DAX measures can seldom be materialized because they depend on runtime context, so they naturally belong in the semantic model.
Sometimes the choice isn’t binary. Calculated columns, for example, can be implemented in either layer. Unless DAX is more suitable, however, my preference would still be ETL—mainly to centralize business logic and because calculated columns aren’t supported with Direct Lake tables.
What about pure reporting features, such as field parameters used by a specific dashboard? It might make sense to implement them only in that report rather than overload the shared semantic model with extra fluff. But what if other reporting clients, such as Excel, need the same functionality? In that case, putting it in the semantic model might be the better—or perhaps the only—choice.
So, there is actually a third layer to consider: the report itself. The closer a feature is to presentation and a specific user experience, the stronger the case for keeping it in the report. The more reusable it is across reports and client tools, the stronger the case for moving it into the semantic model.
Shortcuts are tempting. For convenience and faster delivery, a BI developer could heavily favor the semantic model—or Power Query—for implementing business logic. The downsides are decentralized business logic and the reengineering effort required to reverse-engineer these rules should you one day part ways with Power BI.
My rule of thumb remains simple: materialize reusable business logic as far upstream as practical, keep context-dependent analytics in the semantic model, and reserve the report layer for presentation-specific functionality.
JOIN THE CONVERSATION
Discussion