Architecture Decision Record · Financial Reporting · Access Governance
Governed financial reporting platform replacing a manually assembled spreadsheet process with a single semantic model, controlled report catalogue and Finance-owned access governance. Directors receive self-service unit-level P&L while dynamic row-level security prevents unauthorised financial visibility.
The problem was not the spreadsheet
Financial reporting had become a manual assembly process. Data was being captured from multiple disparate reports and queries, brought together in spreadsheets, verified, and then emailed or presented to stakeholders.
Depending on the reporting cycle, this could take hours to days. The process required repeated verification and still did not provide complete visibility across the organisation.
More importantly, financial reporting was also an access-control problem. Directors needed access to their own business-unit P&L, while Finance needed confidence that one business unit could not see another's financial information.
A manually distributed spreadsheet could not provide a sufficiently strong control boundary. The risk was not only that a number might be wrong. The wrong financial information could be distributed to the wrong person.
The architecture decision
The decision was to replace departmental reporting silos with a single governed semantic model protected by dynamic row-level security.
The security model was deliberately separated from the report itself. Finance owned the organisational access decisions through a master-data process, while automation propagated those decisions into the reporting platform.
Why one semantic model?
The principal architectural alternative was to create separate reports or semantic models for each business unit.
That approach initially appears attractive because access becomes simple: build the report for the business unit and give the appropriate people access to it.
The problem is that every additional model becomes another place where definitions, calculations, transformations and security assumptions can diverge.
Financial reporting needs consistency. If two business units use subtly different definitions of the same measure, the organisation no longer has one financial reporting model. It has several interpretations that happen to share a name.
A single semantic model established one governed definition of the financial measures while allowing access to those measures to vary according to the user's organisational scope.
Separating governance from enforcement
One of the important design decisions was not to make Power BI the place where Finance maintained individual access assignments.
Finance owned three things: the authoritative data source, the final reporting asset, and the decision about who should have access to which organisational data.
The access matrix therefore became governed master data. Power BI was responsible for enforcing the resulting access rules, not for being the source of those business decisions.
Microsoft Forms
of access decision
access assignment
propagate changes
of approved scope
Access provisioning as a data pipeline
Access provisioning was treated as a controlled data flow rather than an administrator manually changing permissions whenever someone requested access.
A user submitted an access request through Microsoft Forms. Organisational restrictions limited who could submit the request, and the submission was retained as a tracked request.
Finance reviewed and approved the request. The approved assignment was then represented in the MDM file, which became the source for the organisational access matrix.
Python ETL validated the submission before incorporating the change into the MDM-driven process. The resulting access matrix was then consumed by the reporting security model.
This changed the operational question from "Who remembers to update this user's access?" to "What does the governed access data say this user should be able to see?"
Changes became data changes
The same design simplified organisational changes.
If a director moved from one business unit to another, the MDM assignment could be updated. The access matrix would then reflect the new organisational relationship without requiring individual reports to be reconfigured.
The same principle applied to removing users from the reporting scope. Removing a leaver from the MDM assignment allowed the downstream process to remove that user's authorised access.
Dynamic row-level security
Power BI provided the enforcement boundary through dynamic row-level security and multiple security roles.
The important architectural distinction was that RLS did not determine organisational policy. It enforced the policy represented by the governed access matrix.
A business-unit director could therefore access their own P&L without receiving access to other units, while Finance could operate with the broader visibility required for financial governance.
This provided a single reporting surface without requiring a separate semantic model for every business unit.
Security validation
Security was validated using a test matrix rather than relying solely on the configuration being correct.
The test matrix made the security boundary explicit and provided a repeatable way to validate it when the model or organisational mappings changed.
Reporting discovery was a separate problem
Governance was not limited to the data model. Users also needed a reliable way to find the correct reporting asset.
A report catalogue index allowed users to explore available reporting by fiscal period rather than relying on emailed links or administrator knowledge.
Where the catalogue did not contain everything required, the reporting solution could retrieve the underlying data and supplement the final Power BI report with the required measures and reporting logic.
This separated three concerns that had previously been mixed together: discovering the right report, establishing a consistent definition of the data, and enforcing who could see it.
Lineage and auditability
The reporting architecture was documented from the source ledger through transformations and semantic calculations to the final executive visual.
The purpose was not documentation for its own sake. Lineage gave Finance and auditors a way to understand where a reported number originated and how it reached the final reporting surface.
The key trade-off
The central architectural trade-off was between a single governed model and the apparent simplicity of separate departmental models.
Separate models could reduce the immediate complexity of security configuration, but they would create a larger maintenance surface and make it easier for definitions to diverge.
The single-model approach concentrated complexity into the semantic and security architecture. That required a stricter change process because a change to the financial model could have security implications and therefore required RLS validation.
The trade-off was accepted because financial consistency and controlled access were more important than making individual departmental reporting models independently simple.
Consequences
What improved
- Directors gained self-service access to their authorised P&L.
- Unauthorised cross-business-unit visibility was controlled through RLS.
- Financial definitions were centralised in one semantic model.
- Finance retained ownership of access decisions.
- Access changes became MDM updates rather than report-by-report administration.
- Access requests were submitted through a controlled and tracked workflow.
- Users gained a searchable reporting catalogue by fiscal period.
- Lineage provided visibility from financial source through to executive reporting.
What it cost
- The central model became a critical governed asset.
- Security changes required deliberate validation.
- Finance needed to maintain the authoritative MDM assignments.
- The provisioning process introduced additional governance steps.
- Model changes required consideration of their impact on RLS.
Outcome
The resulting platform replaced a manual financial-reporting process with a governed reporting model that Finance could own and operate.
Directors could self-serve their authorised financial information without relying on emailed spreadsheets, while Finance retained control over the underlying data, reporting asset and access decisions.
The broader lesson was that reporting governance is not a single feature. The semantic model, access model, provisioning workflow, master data, catalogue and lineage all form part of the reporting architecture.
The architecture separated business ownership from technical enforcement. Finance decided who should see what. MDM represented that decision. Python automated its propagation. Power BI enforced it. The semantic model provided one definition of the financial truth.