Governance & Write-back

A SQL table and a BI report built on top of it are, structurally, two different systems: the table has its own access model (or none), and the BI tool layers its own separate security on top, usually administered by a different person or team. In MODLR, the data, the calculation logic, security and the reporting/interface layer are one governed system, so there's nothing to keep in sync by hand.

Security in a SQL + BI stack

Row-level security in a BI tool - a Power BI RLS role, for example - is defined and maintained inside the BI tool, separately from whatever grants exist (or don't) on the underlying tables. Nothing structurally guarantees the two stay aligned: someone can lose access to a table but keep the BI role that was built against it, or a new entity can be added to a hierarchy in the warehouse without being added to the RLS role that's supposed to restrict who sees it. Reconciling the two means someone auditing both systems by hand.

Security in MODLR

MODLR keeps this as one security model with two layers, both defined inside the platform rather than split across a data tool and a BI tool. Instance Security governs Read, Write or No Access to model objects - Cubes, Dimensions, Dimension Elements, Cards, Workviews and more - applied via Groups, so an Employee dimension or a manager's own department can be restricted at the object or element level. Access Tags sit on top, at the Application level, controlling which Planning Screens a user can even reach. A Collaborator only has access to the Applications they're added to, and only to what their Access Tags and Group membership allow within them; a Modeller has full administrative access to the Instance by default, though the same Instance Security Groups can also scope a Modeller down to less than full access on a given Instance if that's what the model calls for. That's a single security definition, and it applies consistently everywhere the model is accessed - a Workview, the Excel Add-in, a Card, or the RESTful API - rather than one model for the data and a second one for the report sitting on top of it.

Write-back

Most BI-over-SQL tools are read-only by design - built to visualise a warehouse, not to accept input back into it. The moment someone needs to adjust a forecast line or submit a budget from inside a report, a BI-over-SQL stack has nowhere for that to go; it means bolting on a separate mechanism - a custom form, an edited extract reloaded back in - which has to solve concurrency, validation and audit from scratch.

In MODLR, the same Cube that's being reported on accepts input directly, governed by the same Instance Security that governs reading it. Dataspreading commands - /set, /add, /subtract, /multiply, /divide - can be entered into a cell from a Workview, and the same write path is available from a Process (cube.set), the Excel Add-in (CubeSend), or an Input or Trigger component on a Card. Building the report and letting people plan against the numbers behind it are the same system, rather than two systems someone has to reconcile.

One report, reused everywhere

A large Power BI report usually ends up distributed through several different mechanisms at once - the Power BI Service for internal users, a separate embed for a customer-facing portal, a PDF export for a board pack, key visuals rebuilt again in PowerPoint for an exec deck. Each of those is a different distribution path, often maintained separately, and typically only the first one is actually covered by the RLS the team built.

In MODLR, a Card is a single object, built once, that gets reused as-is across every one of those mediums rather than rebuilt for each:

• Native web portal - added to a Screen via a Dashboard, inside an Application, governed by Instance Security and Access Tags as above.
• Embedded elsewhere - an Embed Token turns a Screen into a URL that can be dropped into an external website or a tool like Power BI via an iframe, without requiring a MODLR login. Since there's no logged-in user for Instance Security to apply to, access is instead scoped directly on the token - left open, password-protected, or restricted at the element level.
• Excel - the same Card can be linked to a cell range in the Excel Add-in and executed inside a worksheet, using the context already set by the CubeGet in that cell.
• PowerPoint - the same Card can be inserted directly onto a slide, with its context set per-card or tied to a presentation-wide Global Element, and refreshed across an entire deck with one click when the reporting period changes.
• Email and export - the same Card can also be sent in an email or exported to PDF or CSV.

Every one of those surfaces is reading the same Card, against the same governed Cube. A hierarchy change, an EBITDA rollup fix, or a security change made once is reflected everywhere that Card is used, rather than needing to be rebuilt separately in the web portal, the exported deck, and whatever's embedded on the website.

Audit trail

A SQL table's history, if it's kept at all, is usually a separate audit table or change-data-capture stream bolted on by whoever anticipated needing it - at the row level, not the individual value. In MODLR, Cube Logs can record every change made to every value, capturing the cell's full address, its value before and after, who made the change, and where it was made from - whether that's a Workview, a Card, a Process, or the Excel Add-in. It's the same log regardless of which interface the change came through.

For when a read-only SQL + BI stack is still the better fit, and when the possibility of write-back tips the balance towards building the governance in MODLR from the start, see When to Use Each.

Illustrating the improvement

• One security definition - Instance Security and Access Tags, both inside MODLR - governs a Cube everywhere it's accessed, rather than a table-level grant and a separately maintained BI role that can silently drift apart.
• Write-back is native: the report and the input surface are the same governed Cube, not a read-only visualisation with a bolted-on form to handle the cases the warehouse wasn't built for.
• A Card is built once and reused as-is across the web portal, Excel, PowerPoint, email, PDF/CSV export and iframe embeds - rather than rebuilt per surface, with security only actually enforced on whichever one someone remembered to lock down.
• Every change is logged at the value level, with who made it and where it came from, without a separate audit mechanism having to be designed and maintained alongside the tables.