Cloud & Automation
Building a Multi-Tenant Report Engine on Azure and Microsoft Fabric
Design a multi-tenant report engine on Azure and Microsoft Fabric with metadata-driven reports, constrained custom queries, caching, pagination, asynchronous exports and capacity protection.
On this page
Reporting platforms become difficult to scale when every report request is treated as a direct query.
An interactive UI may need fresh JSON for a grid or chart. External API consumers may request the same report repeatedly. Some users need CSV or XLSX exports. Others need Parquet for downstream processing. A multi-tenant platform must also guarantee that one customer cannot query another customer’s data, even when custom reporting is introduced.
At the same time, analytical fact tables may live in Microsoft Fabric, where compute capacity is shared and expensive queries can affect other workloads.
How do you build a reporting engine that gives users fresh data when needed, reuses generated results when possible, protects Fabric capacity, and supports both predefined and customer-defined reports?
A useful answer is to make the Report Engine the policy and execution boundary between consumers and the analytical platform.
- Angular UI
- Azure API Management
- Report Engine
- Microsoft Fabric
External API clients enter through the same gateway and engine. The consumer should not need to know where the data is physically stored, which execution mechanism generated the report, or whether the result came from a reusable artifact.
If the concept is new to you, start with What Is a Report Engine?. This article goes deeper: it is the multi-tenant architecture on Azure and Microsoft Fabric, from metadata model to capacity protection and asynchronous exports.
1. The core design principle
The Report Engine is an abstraction over reporting behavior.
It receives a report request and decides:
- who the caller is;
- which tenant and accounts the caller may access;
- which report definition applies;
- whether the parameters are valid;
- whether the request can be served from an existing artifact;
- whether the query is safe and affordable to execute interactively;
- whether a stored procedure or metadata-driven query should run;
- whether the request should be handled synchronously or asynchronously;
- which output format should be returned.
That keeps clients simple.
The Angular application should ask for a report, not for a Fabric table.
An external integration should request a report, not submit SQL.
A custom-report builder should select approved business fields, not physical column names.
This separation also gives the backend freedom to evolve. The source could be a Fabric Warehouse today and a different curated serving layer later without changing the public reporting contract.
2. Report Engine responsibilities
A production Report Engine is more than a query endpoint.
- Request
- Report DefinitionResolve standard or custom metadata
- Security + ValidationTenant, account, parameters, allowed fields
- Execution PlannerCache, interactive query, or async export
- Result ProcessingSort, paginate, aggregate, format
- Delivery
- JSON
- CSV
- XLSX
- Parquet
Typical responsibilities include authentication context, tenant and account authorization, report definition resolution, parameter validation, query evaluation, execution planning, cache lookup, dynamic query generation, stored procedure execution, sorting, pagination, output formatting, execution tracking, artifact generation, telemetry, and audit records. Report Engine Architecture: Request to Result traces one request through these stages in order.
3. Standard and custom reports should use the same engine
A common design mistake is to create one subsystem for standard reports and another for custom reports. That duplicates security, pagination, caching, output generation, monitoring, Fabric connectivity, and error handling.
A cleaner design treats standard and custom as definition styles.
- Report Request
- Report Definition
- predefinedStandardStored procedure or predefined query
- metadata-drivenCustomValidated dynamic query model
- Shared Report Engine
A standard report may resolve to:
Type: STANDARD
ExecutorType: STORED_PROCEDURE
ExecutorName: rpt.SalesByAccount
A custom report may resolve to:
Type: CUSTOM
ExecutorType: DYNAMIC_QUERY
DatasetId: SalesReporting
Both still use the same authorization rules, execution planner, cache, state store, pagination, sorting, artifact generation, and telemetry.
Standard versus custom should describe how a report is defined, not create two separate reporting platforms.
4. Metadata-driven reporting
Custom reporting becomes safer when users build reports from approved metadata rather than database objects.
ReportDefinition
ReportId
Name
Type: STANDARD | CUSTOM
ExecutorType: STORED_PROCEDURE | DYNAMIC_QUERY
ExecutorName
DatasetId
CacheTTL
Enabled
Version
DatasetDefinition
DatasetId
Name
Source
AllowedFields
Relationships
FieldDefinition
FieldId
DisplayName
SourceExpression
DataType
Aggregation
Filterable
Sortable
Groupable
The business-facing model might expose:
Sales
Account
Channel
Date
Revenue
Order Count
instead of:
fact_sales.sales_amount
dim_account.account_name
dim_channel.channel_name
This gives the engine control over visible fields, legal relationships, grouping, aggregation, filtering, and executable expressions while hiding physical-schema changes behind stable business metadata.
5. Microsoft Fabric as the analytical data layer
The Report Engine should query a curated reporting contract, not raw ingestion tables.
- BronzeRaw or minimally processed ingestion
- SilverClean, validated, conformed data
- GoldCurated reporting layer
- fact_sales
- fact_orders
- fact_payments
- dim_account
- dim_channel
- dim_date
How data reaches Bronze is an integration decision of its own, covered in Data Integration Patterns; the layers themselves are explained in Medallion Architecture Explained.
The exact serving surface may be a Fabric Warehouse, a Lakehouse exposed through its SQL analytics endpoint, or another supported serving surface. Microsoft Fabric Lakehouse vs Warehouse compares the two main options.
The SQL analytics endpoint provides a read-only T-SQL query surface over OneLake data and is well suited to reporting scenarios where analytical queries should be separated from operational workloads.
The important architectural rule is that the Report Engine depends on a stable reporting layer.
That gives a stable contract, better performance tuning, simpler query generation, isolation from ingestion changes, easier security review, and clearer ownership. For how workspaces, capacities and layers fit together around it, see Designing a Scalable Microsoft Fabric Architecture.
6. Query Evaluator vs Dynamic Query Executor
Custom reporting needs two distinct components.
Query Evaluator
The evaluator answers:
Can this request run?
It validates the dataset, requested fields, filters, aggregations, tenant and account scope, date range, dimensions, sorting, complexity, and expected execution path.
Its output is a validated internal query model, not SQL supplied by the caller.
{
"dataset": "SalesReporting",
"dimensions": ["date", "account"],
"metrics": ["sales_amount"],
"filters": [
{"field": "channel", "operator": "eq", "value": "Online"}
],
"dateRange": {
"start": "2026-10-01",
"end": "2026-10-31"
}
}
Dynamic Query Executor
The executor answers:
How do I execute an already-approved plan?
- Custom Request
- Query EvaluatorValidate fields, filters, scope, complexity
- Validated Query ModelInternal representation, not raw SQL
- SQL GeneratorBuild parameterized SQL from approved metadata
- Dynamic Query Executor
- Fabric
Clients never submit arbitrary SQL.
7. Query cost and Fabric capacity protection
Custom reporting creates an operational problem: users can accidentally ask expensive questions.
The engine should estimate cost before execution using signals such as date range, number of dimensions, number of metrics, filter selectivity, expected row count, aggregation complexity, and whether a cached artifact exists.
| Cost class | Example behavior |
|---|---|
| LOW | Execute interactively |
| MEDIUM | Execute interactively with tighter limits or cache preference |
| HIGH | Queue as asynchronous export |
| TOO EXPENSIVE | Reject or require narrower filters |
- Validated Request
- Cost Evaluation
- interactiveLOWExecute now
- boundedMEDIUMApply limits and prefer cache
- asyncHIGHQueue export
- rejectTOO EXPENSIVERequire narrower request
- Controlled Execution
Azure API Management can enforce rate-limit, quota, and concurrency policies before requests reach the engine. APIM protects the service from request spikes; the engine protects Fabric from expensive analytical work. Measuring API and Report Request Volume shows which signals tell you when those limits need adjusting.
8. Multi-tenant security
Multi-tenancy must be enforced by the engine, not delegated to user-provided parameters.
- Tenant
- Account
- Report
The caller’s authorized tenant and account scope should come from authenticated security context or a trusted authorization lookup.
Do not trust this merely because the client sent it:
?tenantId=123
&accountId=456
Instead resolve an authoritative scope:
tenant_id = 123
authorized_accounts = [456, 457, 480]
and inject it into every execution plan.
WHERE tenant_id = @tenantId
AND account_id IN (
SELECT value
FROM STRING_SPLIT(@authorizedAccountIds, ',')
)
Fabric Warehouse and the SQL analytics endpoint do not support user-defined table types, so a table-valued parameter is not available there. Pass the engine-resolved list as a delimited parameter, as above, or join to a trusted access-mapping table maintained by the platform.
User-defined report metadata must never be able to remove or override mandatory tenant-security predicates.
Fabric-native row-level or OneLake security can provide another control layer, but the Report Engine still needs application-level authorization because it owns the reporting contract.
9. Caching and report artifacts
Repeated report requests are a good caching opportunity when the cache key represents the actual underlying dataset. Whether a given report should be served from a stored result at all is a separate question, covered in Pre-Rendered Reports vs Live Queries.
Example request:
groupBy=Date
groupBy=Channel
groupBy=Account
startDate=2026-10-01
endDate=2026-10-31
Normalize it into:
tenant
account scope
report definition + version
dimensions
metrics
filters
date range
Then hash it.
For a reusable underlying dataset, do not include presentation-only values such as:
page
pageSize
sort
unless they materially change the generated artifact.
Parquet is a strong canonical format for reusable analytical results because it is columnar, typed, and efficient for analytical rereads.
A reusable Parquet artifact can then feed paged JSON, CSV, XLSX, or downstream analytical processing.
OneLake can be a good home for canonical analytical artifacts that should remain close to Fabric. Current OneLake capabilities also allow external applications to access data through ADLS-compatible APIs when tenant settings permit it, and short-lived user-delegated SAS tokens are supported when enabled.
However, artifact storage and artifact delivery are separate decisions.
If external file download through signed URLs is the primary requirement, Azure Blob Storage or ADLS Gen2 may be operationally simpler for the delivery layer. A design can therefore use OneLake for canonical analytical artifacts and Azure Storage for externally distributed exports.
10. Interactive UI vs export path
Interactive reporting and file export should not be forced through the same response shape. Designing a Report Engine for UI and API Consumers covers the consumer side of this split in more detail.
Interactive Angular UI
- Angular
- Report Engine
- Fabric Reporting Layer
- Server-side Page + Sort
- JSON
JSON is appropriate for grids, charts, totals, pagination, and interactive filters.
The UI should not download a huge CSV and paginate it in the browser.
Export and integration path
- Export Request
- Report Engine
- Artifact Lookup
- hitCached ArtifactReuse existing result
- missFabric QueryGenerate and persist artifact
- Requested File
- CSV
- XLSX
- Parquet
This prevents static export files from becoming the interactive database.
11. Pagination and sorting
Interactive reports need server-side pagination.
A simple implementation can start with:
ORDER BY sales_amount DESC, account_id ASC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
The tie-breaker matters because pagination needs deterministic ordering.
For very large result sets, keyset or cursor pagination can avoid the growing cost of deep offsets.
WHERE (
sales_amount < @LastSales
OR (
sales_amount = @LastSales
AND account_id > @LastAccountId
)
)
ORDER BY sales_amount DESC, account_id ASC;
The engine should own these mechanics so the UI does not need database-specific pagination logic.
12. Azure service mapping
| Responsibility | Service |
|---|---|
| UI | Angular |
| API gateway | Azure API Management |
| Report Engine | Azure App Service |
| Metadata and execution state | Azure SQL or Azure Table Storage |
| Analytical data | Microsoft Fabric |
| Canonical analytical artifacts | OneLake |
| External export delivery | OneLake or Azure Blob / ADLS Gen2, depending on access model |
| Asynchronous queue | Azure Service Bus |
| Background worker | App Service or Azure Container Apps |
| Identity | Microsoft Entra ID + managed identities |
| Monitoring | Application Insights / Azure Monitor |
Azure Table Storage vs Azure SQL
Azure Table Storage can work well for simple high-scale key-based state such as:
ExecutionId
TenantId
Status
CreatedAt
CompletedAt
ArtifactLocation
Its design depends heavily on PartitionKey and RowKey.
Azure SQL becomes more attractive when metadata has relationships, transactional changes across multiple entities, richer administrative queries, indexing needs, or versioning relationships.
A practical system may use Azure SQL for report metadata and Table Storage only for simple execution state, or use Azure SQL for both to reduce architectural complexity.
Managed identities
App Service and Azure Functions support managed identities for accessing Entra-protected Azure resources without storing application credentials. The same pattern can be used with Service Bus and other supported Azure services.
13. Asynchronous report generation
Large exports should not hold an HTTP request open indefinitely.
- Export Request
- Report Engine
- Execution RecordCreate queued job and return execution ID
- Azure Service Bus
- WorkerExecute, format, persist
- Artifact
The initial response can return:
{
"executionId": "rpt_7f13...",
"status": "queued"
}
The caller can poll a status endpoint, receive a callback if supported, or be notified when the export is ready.
Service Bus queues are useful because they buffer bursts of work and decouple the API path from background execution.
14. Example end-to-end request
GET /api/reports/sales
?groupBy=Date
&groupBy=Channel
&groupBy=Account
&startDate=2026-10-01
&endDate=2026-10-31
A possible sequence is:
- API Management applies gateway policies and forwards authenticated context.
- The Report Engine resolves tenant and authorized account scope.
- The engine loads the
ReportDefinition. - Parameters are parsed and normalized.
- The evaluator validates fields, dimensions, date range, and access.
- A deterministic cache key is generated.
- The engine checks whether a reusable artifact already exists.
- The planner estimates query cost.
- The planner selects cache reuse, synchronous Fabric execution, asynchronous export, or rejection.
- If Fabric is required, the engine executes a stored procedure or approved generated SQL against the reporting layer.
- Results are processed and tracked.
- Reusable artifacts are persisted where appropriate.
- The UI receives JSON, or the export consumer receives the requested file or delivery reference.
- APIM
- Resolve ScopeTenant + accounts
- Load Definition
- Validate + Normalize
- Cache + Cost Plan
- Execute if Needed
- Process + Persist
- Deliver
15. Evolution toward controlled custom reporting
Phase 1 — Standard reporting
- Standard reports
- API + Angular
- Fabric reporting layer
- JSON / CSV
- Basic cache
Phase 2 — Asynchronous exports
- Async exports
- Artifact reuse
- Service Bus
- Execution worker
Phase 3 — Metadata-driven custom reports
- Dataset catalog
- Field metadata
- Query Evaluator
- SQL Generator
- Dynamic Query Executor
Phase 4 — Reporting platform capabilities
Potential additions include scheduled reports, saved customer report definitions, templates, per-tenant quotas, usage-based limits, pre-generation of popular reports, cache warming, and subscriptions.
The key is that those capabilities extend the same engine rather than creating parallel reporting stacks. The Modern Report Engine project holds the complete design and implementation plan for this kind of engine.
16. Design principles
- Treat reporting as a platform, not a collection of endpoints.
- Separate report definition from report execution.
- Keep security outside customer-defined metadata.
- Query curated Fabric data, not raw tables.
- Cache expensive reusable results.
- Keep interactive delivery separate from export delivery.
- Make custom reporting constrained and metadata-driven.
- Protect Fabric capacity before adding more compute.
17. Conclusion
The goal is not to build another BI product.
The goal is to provide a governed reporting engine that exposes analytical data safely, efficiently, and consistently to both applications and customers.
Azure API Management provides an API boundary and traffic controls. The Report Engine centralizes security, metadata, planning, caching, execution, and delivery. Microsoft Fabric provides the curated analytical data layer. Service Bus and workers handle expensive asynchronous exports. OneLake or Azure Storage can persist reusable artifacts depending on how those artifacts are consumed.
Most importantly, standard and custom reports share the same platform.
That creates a reporting architecture that can evolve from predefined reports to constrained customer-defined analytics without giving up tenant isolation, operational control, or Fabric capacity.
Official references
- What is the SQL analytics endpoint for a lakehouse?
- Query the Fabric Warehouse or SQL analytics endpoint
- T-SQL surface area in Fabric Data Warehouse
- STRING_SPLIT (Transact-SQL)
- OneLake tenant settings and external access
- Microsoft Fabric permission model
- Azure API Management policies
- Azure App Service managed identities
- Azure Service Bus asynchronous messaging
- Azure Service Bus managed identity
- Azure Table Storage design guidance
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFabric Medallion ArchitectureDesign a practical Bronze, Silver, and Gold architecture in Microsoft Fabric with clear data contracts, replayability, incremental processing, quality gates, and serving responsibilities.Open cheat sheet
- ArticleDesigning a Report Engine for UI and API ConsumersDesign one governed report backend for interactive UI pages, external APIs, downloads and large asynchronous exports.Read article
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.