Context
We had recently done an investigation into potentially using a second semantic model to form the basis of a self-service Power BI report. The write up for that can be found here.
This investigation is a follow up to that and broadens the horizon: "With a clean slate, how would you architect a self-service reporting solution?"
Below is a write-up of the options investigated to achieve this:
Problem Statement
The previous investigation 2nd Model Considerations explored the feasibility of introducing a second semantic model specifically for the Analytics Builder report.
The outcome of that investigation indicated that while Perspectives could meet some of the requirements, there are significant limitations around usability, optimisation and maintainability.
This follow up spike takes a clean slate approach:
If I was designing the architecture from scratch today, what is the best way to support intuitive, reliable analytics usage for a group of non-technical users to both passively consume some reports but also have the ability to self-serve in other reports while maintaining a manageable, scalable architecture for the development team?The two core considerations for the self-serve aspect are:
- The report needs to be as simple and intuitive as possible for someone, who has low data literacy, to be able to use it.
- This simplicity needs to be balanced against the added complexity of the regular release cycles and the level of general technical debt that could be incurred.
The Users
There are three distinct groups of users who engage with our suite of reports:
| User Group | Characteristics | Primary Needs | Suitable Interface |
|---|---|---|---|
| Analysts / Power Users | Data literate, exploratory, hypothesis-driven | Full flexibility, access to the semantic model | Semantic Model (Power BI Desktop / Service) |
| Decision Makers | Time-limited, outcome-focused | Clear, curated insight aligned to strategy | Standard reporting |
| Operation / front-line users | Low data literacy, process focused | Guided user interfaces, limited choices, clear prompts | Purpose-built UI |
The Analytics Builder report is intended to be used primarily by front-line users i.e. the third group listed. However Power BI is a tool designed primarily for Analysts / Power Users, the first group in the above table.
Therefore this creates an unavoidable gap in cognitive load.
Cognitive Load Problem
Power BI requires users to:
- understand business logic in metric definitions (calculated columns, calculated tables, measures, field parameters)
- navigate tabular structures and understand relationships between them
- select the appropriate fields and filters
- assemble meaningful visuals
- avoid misinterpretation
These tasks have a technical baseline and assume a certain level of data literacy.
Changing object names, hiding tables or simplifying fields can reduce surface friction but it does not eliminate the need to understand dimensional modelling and the relationships between tables.
In other words, Power BI can be simplified but it cannot be a guided workflow tool without losing analytical value.
Architectural Options Reviewed
The possible approaches are detailed in the below table:
| Approach | Benefits | Limitations |
|---|---|---|
| Single Model with Perspectives | No duplication, consistent logic, lowest overhead | Does not solve user experience (UX) challenges for non-analysts. Requires ongoing manual Perspective maintenance. |
| Second Semantic Model | Tailored model for simplicity | Doubles measure governance. High maintenance burden on development team. Release complexity increases. |
| Auto-generated model via scripting / REST API (as per previous investigation). | Reduces manual maintenance | Technically complex, still inherits the UX limitations of Power BI for non-analysts. |
| External app layer consuming the current semantic model. | Can present structured, intuitive UX for low data literacy users. | Requires software engineering skillset and product development effort. Higher initial cost, lower ongoing cost. Highest chance of adoption. |
Proposed Direction
Short-Term
Use the approach as detailed in the previous investigation to present a simplified, curated subset of the existing semantic model for Analytics Builder.
This has the following advantages:
- Maintains a single source of truth
- Minimises technical debt
- Keeps release cycle stable
- Respects existing RLS
Long-Term
Build an application layer (a web app) on top of the semantic model that provides:
- Clear workflows with defined tasks
- Structured prompts and guardrails
- Limited and context-aware choices
- A UX designed for non data literate personas.
This would allow Analysts and Power Users to continue to interact through Power BI directly and non-analysts to interact through a web user interface. But both groups of users would be engaging with the same semantic model, the single source of truth.
This aligns with the requirements of this item:
- Reduced cognitive load / good UX for non-analysts
- Governance and metric consistency
- Avoids the technical debt of a dual model system
Why A Web Application?
In broad strokes, a web app's backend uses a mechanism to call measures or to execute DAX queries against the existing semantic model. The specific implementation (e.g. using the Power BI REST API) is a technical detail outside the scope of this investigation.
The critical point is that the logic for the measures etc. remains in one semantic model.
The new work involved is creating a mapping layer which would map the limited choices offered to the user to the technically named objects in the semantic model. So for example creating the mapping so that when a user types or selects 'Show me the number of Active Customers', the semantic model returns the output of the measure [ActiveCustomerCount].
The key is that this is normal application code and any changes would be considered just a code change to a software development team. All in a day's work.
The web app doesn't contain any business logic. Instead it contains instructions on how to ask for it. Consequently it transforms the technical debt of logic and object duplication into the more manageable and usual debt of interface mapping.
Summary
Power BI is a data analysis tool not a simplified data consumption tool. We can, unfortunately only ever have, at most, two of the following three:
- A simple, intuitive user experience.
- Low technical debt and maintenance.
- A Power BI-centric solution.
If we had a clean slate, this should not be done inside of Power BI. The user experience goal cannot be met without significant compromise. Any solution within Power BI will force us to sacrifice the user experience and/or our long-term technical health.
A purpose-built web application on top of the existing semantic model appears to offer the best path to simplicity, intuitiveness and low-to-no technical debt.
What Happened Next?
The team accepted the recommendation of the investigation and are currently implementing the short-term approach while building and prototyping a simple web application in the background.