OData Exposure — Power BI / Excel / Tableau feed

A finance manager asks for “the Invoices table in Power BI”. The default move is to give the BI tool read-only SQL access to a replica. Two weeks later, a custom view forgets WHERE tenant_id = … and one tenant reads another tenant’s data. Or someone gives Tableau direct connection strings and the audit trail goes dark. Or you build a custom OData controller per module and reinvent paging, filtering, expansion limits, and rate-limiting from scratch.

Granit.Http.ODataExposure is the sanctioned bridge: it turns the QueryDefinition<TEntity> you already wrote for the application’s grid endpoints into a full OData v4 EntitySet. Power BI Desktop’s Get Data → OData feed connector discovers it natively. Every request flows through the same filter pipeline as the application — tenant filtering, soft delete, per-EntitySet permissions — so a BI tool can never see rows the user’s own UI couldn’t see.

Pain	OData Exposure’s answer
BI team wants direct SQL → cross-tenant leaks waiting to happen	Same filter pipeline as the application’s grids; OData is the only sanctioned external read
Per-module custom OData controllers	One MapGranitODataEndpoints call, one EntitySet<>() per entity
Power BI refresh runs $top=2_000_000 and locks the DB	MaxTop clamp + OData-MaxTop-Applied header for observability
$count=true triggers a full table scan on every refresh	EnableCount() is opt-in per EntitySet
$expand=* exfiltration	Required ExpandWhitelist(...) or explicit DisableExpand()
One tenant saturates the feed	granit-odata rate-limit policy partitioned per tenant
Cross-tenant analytics for finance ops vs per-tenant feed	Two separate mounts (MapGranitODataEndpoints + MapGranitODataHostEndpoints), distinct container, distinct quota

Package structure

• DirectoryGranit.Http.ODataExposure/

  • DirectoryExtensions/

    • ODataExposureServiceCollectionExtensions.cs AddGranitOData()
    • ODataExposureEndpointRouteBuilderExtensions.cs MapGranitODataEndpoints / MapGranitODataHostEndpoints
  • DirectoryInternal/

    • ODataEdmModelBuilder.cs EDM whitelist via EntityDefinition (ADR-050)
    • ODataEntitySetDescriptor.cs
  • DirectoryOptions/

    • ODataExposureOptions.cs Fluent EntitySet<TEntity, TQueryDef>(...) builder
    • ODataHostExposureOptions.cs Cross-tenant host-feed builder
  • DirectoryDiagnostics/

    • ODataExposureMetrics.cs OTel counters (granit.odata.*)

Installation

// Program.cs — DI
builder.Services
    .AddGranitOData()                                    // OData runtime + ODataQueryOptions<T> binding
    .AddGranitQueryEngine()                              // existing QueryEngine registration
    .AddQueryDefinition<Invoice, InvoiceQueryDefinition>();

// Program.cs — routes (after auth middleware)
app.MapGranitODataEndpoints("/api/{version}/odata", opts =>
{
    opts.EntitySet<Invoice, InvoiceQueryDefinition>("Invoices")
        .RequirePermission("OData.Invoicing.Invoices.Read")
        .ExpandWhitelist("Customer");

    opts.EntitySet<Customer, CustomerQueryDefinition>("Customers")
        .RequirePermission("OData.Invoicing.Customers.Read")
        .DisableExpand();
});

Power BI Desktop → Get Data → OData feed → https://your-app/api/v1/odata. The service document advertises Invoices and Customers. The user signs in via OAuth (the same flow as the web UI). Every row returned passes through tenant filtering, soft-delete, and the OData.Invoicing.*.Read permission gate.

EntityDefinition is required (ADR-050)

Every EntitySet must target an entity that has a registered EntityDefinition<TEntity> AND an ExportDefinition<TEntity> referenced via b.Export<TExportDefinition>(). The startup validator throws otherwise — no silent fallback.

public sealed class InvoiceEntityDefinition : EntityDefinition<Invoice>
{
    public override string Name => "Granit.Invoicing.Invoice";

    protected override void Configure(EntityDefinitionBuilder<Invoice> b)
    {
        b.DisplayKey("Entity:Invoice").PermissionGroup("Invoicing.Invoices");
        b.Query<InvoiceQueryDefinition>();
        b.Export<InvoiceExportDefinition>();   // ← drives the OData EDM whitelist
    }
}

The EDM EntityType is built by:

1. Resolving the EntityDefinition for the target entity.
2. Following EntityDefinition.Descriptor.ExportDefinitionType to the matching ExportDefinition.
3. Whitelisting properties from ExportDefinition.GetFields() filtered to IsNavigation == false and PropertyPath containing no . (flat scalar fields for v1; navigation paths land in a follow-up driven by EntityDefinition.Relations).
4. Allowing the navigation properties listed in .ExpandWhitelist(...) on the EntitySet builder.

Everything else is removed from the EDM via EntityTypeConfiguration.RemoveProperty(...) before convention discovery — notably AggregateRoot.DomainEvents and AggregateRoot.IntegrationEvents, which would otherwise leak into $metadata because they’re publicly exposed on every aggregate root to satisfy IDomainEventSource / IIntegrationEventSource.

Note

Pinning the EDM to EntityDefinition instead of ExportDefinition directly aligns the BI surface with the framework’s entity-modeling direction (ADR-050). As more modules ship EntityDefinitions in subsequent phases, their entities become OData-exposable for free.

Request flow

flowchart LR
    BI[Power BI / Excel / Tableau] -->|"GET /odata/Invoices?$filter=...&$top=100"| RT[Route]
    RT --> RL[Rate limit<br/>granit-odata]
    RL --> AU[Auth + Permission]
    AU --> QS[IQueryableSource&lt;Invoice&gt;]
    QS -->|"ApplyGranitConventions<br/>(tenant + soft-delete)"| QE[IQueryEngine.BuildFilteredQuery]
    QE -->|"QueryDefinition pipeline"| OQ[ODataQueryOptions.ApplyTo]
    OQ -->|"$filter / $select / $top / $orderby"| DB[(Database)]

The order is load-bearing: tenant first, framework filters second, user query third. A BI consumer can never widen the result set — only narrow it.

Strict-config validator

Two security-sensitive intents must be declared explicitly per EntitySet, or MapGranitODataEndpoints throws at host startup with a list of every misconfigured set:

1. Permission policy — .RequirePermission(...) (gated) or .AllowAnonymousAccess() (true public reference data, e.g. countries/currencies).
2. $expand policy — .ExpandWhitelist(...) (allow listed navigations) or .DisableExpand() (no expand at all).

// ❌ Throws at startup — both intents missing
opts.EntitySet<Invoice, InvoiceQueryDefinition>("Invoices");

// ✅ Both intents explicit
opts.EntitySet<Invoice, InvoiceQueryDefinition>("Invoices")
    .RequirePermission("OData.Invoicing.Invoices.Read")
    .ExpandWhitelist("Customer", "Lines");

Convention drift toward “unprotected by default” is a top cause of data leaks. The framework refuses to ship a permission-less EntitySet by accident — silent defaults are the equivalent of an architecture test for a config surface that lives inside a closure.

Hardening per EntitySet

opts.EntitySet<Invoice, InvoiceQueryDefinition>("Invoices")
    .RequirePermission("OData.Invoicing.Invoices.Read")  // strict-config: required
    .ExpandWhitelist("Customer")                         // strict-config: required (or DisableExpand())
    .MaxTop(2500)                                        // default 5000 — silently clamps user $top
    .PageSize(500)                                       // default 1000 — used when caller omits $top
    .EnableCount()                                       // default disabled — opt in for cheap-to-count tables
    .MaxExpansionDepth(2);                               // default 1 — flat expand only

Behaviour	Trigger	Response
$top clamped	Caller sends $top above MaxTop	Result trimmed + header OData-MaxTop-Applied: <cap>
$count rejected	Caller sends $count=true without .EnableCount()	400 Bad Request
$expand rejected	Property not in ExpandWhitelist, or no whitelist call	400 Bad Request
Depth rejected	$expand depth above MaxExpansionDepth	400 Bad Request
OTel counters	Every event	granit.odata.query.rejected{entity_set, reason, tenant_id}, granit.odata.query.top_clamped

Rate limiting per tenant

Every OData route is gated by the granit-odata policy from Granit.RateLimiting. Hosts must configure quotas:

{
  "RateLimiting": {
    "Policies": {
      "granit-odata": {
        "Algorithm": "SlidingWindow",
        "PartitionBy": "Tenant",
        "PermitLimit": 60,
        "Window": "00:01:00"
      }
    }
  }
}

One bucket per tenant. When empty, the route returns 429 Too Many Requests with Retry-After. Accepted requests carry X-RateLimit-Limit and X-RateLimit-Remaining so BI tools can show the budget left.

Recommended defaults: 60 req/min for interactive users, 600 req/min for service accounts (plumbing via Granit.Features plan-based quotas if the host has it wired). Reduce for huge datasets where each request can trigger a long-running query.

Host-feed — cross-tenant BI for host operators

The standard MapGranitODataEndpoints is per-tenant: an analyst sees only their tenant’s rows. For legitimate cross-tenant analytics — finance ops (MRR/ARR across all tenants), compliance audit, capacity planning — mount a separate host-feed:

app.MapGranitODataHostEndpoints("/api/{version}/odata/host", opts =>
{
    opts.EntitySet<Tenant, TenantQueryDefinition>("Tenants")
        .RequirePermission("OData.Host.Platform.Tenants.Read")  // MUST be MultiTenancySides.Host
        .DisableExpand();

    opts.EntitySet<Invoice, InvoiceQueryDefinition>("InvoicesAllTenants")
        .RequirePermission("OData.Host.Invoicing.Invoices.Read")
        .AcknowledgeCrossTenantExposure(q =>
            q.IgnoreQueryFilters([GranitFilterNames.MultiTenant]))    // explicit per-query bypass
        .ExpandWhitelist("Customer");
});

Three strict-config gates layered on top of the tenant-feed validator:

1. Permission must be MultiTenancySides.Host. The validator resolves the permission through IPermissionDefinitionManager at startup and refuses Tenant or Both. A permission without an IPermissionDefinitionProvider declaration is also refused.
2. No anonymous access. The host builder doesn’t expose AllowAnonymousAccess() — every host-feed set is gated.
3. AcknowledgeCrossTenantExposure(...) mandatory for IMultiTenant entities. The host writes the per-query bypass lambda at the call site. Without it, the framework filter tenantId == currentTenant.Id returns no rows for a tenantless caller — fail-closed by default.

Operational distinctions:

	Tenant feed	Host feed
Mount	MapGranitODataEndpoints	MapGranitODataHostEndpoints
OData container	Container	HostContainer (schema mismatch surfaces immediately if mixed)
Rate-limit policy	granit-odata (default PartitionBy: Tenant)	granit-odata-host (recommended PartitionBy: User)
Telemetry tag	feed_kind=tenant	feed_kind=host
Audit	Standard	ISO 27001 A.12.4 — every cross-tenant access trackable distinctly

Host-feed vs Granit.Analytics

Need	Use
Cross-tenant aggregate KPI for a host dashboard	Granit.Analytics (MetricDefinition)
Cross-tenant tabular data for Power BI / Excel / Tableau	Host feed (this module)
Per-tenant tabular data for tenant admins	Tenant feed (this module)
Per-tenant aggregate KPI inside an admin grid	Granit.Analytics

Rule of thumb: one or a few aggregated numbers per call → Analytics. Full tabular data + $filter / $select / $top composition → OData.

BI tool connection

sequenceDiagram
    participant U as User
    participant PB as Power BI Desktop
    participant App as App (OData)
    participant DB as Database

    U->>PB: Get Data → OData feed → URL
    PB->>App: GET /odata
    App->>PB: Service document (Invoices, Customers)
    PB->>App: GET /odata/$metadata
    App->>PB: CSDL XML (EDM schema)
    PB->>App: GET /odata/Invoices?$select=Number,Total&$top=1000
    Note over App: auth + permission + tenant filter
    App->>DB: SELECT … WHERE tenant_id = X
    DB->>App: rows
    App->>PB: OData JSON payload

A sample .pbids (Power BI connection file) ships with the package — copy it, substitute the URL, and analysts can connect with one click. The OAuth flow is the same one the user logs in with on the web UI.

Caution

Microsoft.AspNetCore.OpenApi does not produce a complete OData schema (upstream issue #1381). The OData metadata document at /{prefix}/$metadata (CSDL XML) is the authoritative discovery surface — Power BI / Excel / Tableau consume it natively.

Limits (v1)

• Read-only. No POST / PATCH / DELETE per EntitySet — BI tools should not write back.
• Collection access only. GET /Invoices works; GET /Invoices(<id>) (single entity by key) is deferred — see OData/AspNetCoreOData#1567.
• Flat scalar fields. Navigation paths in ExportDefinition (PropertyPath containing .) are not exposed in v1; a follow-up will derive them from EntityDefinition.Relations.

See also

• QueryEngine — the QueryDefinition<TEntity> that powers OData feeds
• Granit.Entities — ADR-050 on EDM whitelist via EntityDefinition
• Granit.Analytics — aggregate KPIs (the BI-feed sibling)
• Rate Limiting — the engine behind granit-odata and granit-odata-host
• Multi-tenancy — the tenant filter inherited by every OData query
• Webhooks — push-style integration when polling isn’t enough