The Aggregate That Censors Itself

The ordinal permission model answers a yes/no question about a single access. The version that makes it useful is applying that answer inside an aggregate — so that the same query returns different numbers to different people, and neither of them has to remember to filter. That is GetFilteredAnalyticalData: an aggregate that censors itself according to who is asking. This post is how it works, the persona demos that show it off, and an honest account of where the filtering actually lives — because it is not where the measure formulas claim.

Rows that self-select

The function takes an employee and a level, and returns only the analytical rows that employee is cleared to see:

CREATE OR REPLACE FUNCTION GetFilteredAnalyticalData(
    p_EmployeeId INT,
    p_HierarchyLevel VARCHAR
)
RETURNS TABLE (...) AS $$
BEGIN
    RETURN QUERY
    SELECT af.ReferenceId, af.HierarchyLevel, mtl.MeasureName,
           af.FactValue, af.DataQualityScore
    FROM AnalyticalFact af
    JOIN HierarchyLevels hl ON af.HierarchyLevel = hl.LevelName
    WHERE hl.LevelOrder <= (SELECT MAX(LevelOrder) FROM ... p_EmployeeId ...)
      AND CheckPermission(p_EmployeeId, 'VIEW_ANALYTICS', af.HierarchyLevel);
END;
$$ LANGUAGE plpgsql;

The magic is in the WHERE. Every candidate row is tested against the caller's clearance two ways: its level must be at or below the employee's ordinal, and CheckPermission must say yes for that specific level. Rows that fail either test simply do not appear. The row self-selects — it includes itself in the result only if the caller is allowed to see it. Call it as a high-clearance manager and thousands of rows come back; call it as an individual contributor and you get your own handful. Same function, same arguments shape, radically different result sets, and the caller never wrote a filter.

SELECT ReferenceId, HierarchyLevel, MeasureName, FactValue, DataQualityScore
FROM GetFilteredAnalyticalData(1, 'Employee')
ORDER BY ReferenceId, HierarchyLevel, MeasureName;

That is the whole idea of permission-aware analytics in one call: the aggregate is not a fixed thing you then restrict — it is the restriction, computed per caller.

Two personas, one query

The persona demos in the usage examples make the censorship vivid. The setup gives four employees different clearances: Employee 1 is a sales manager who sees everything; Employee 2 is a sales rep restricted to the 'Online' channel; Employee 3 sees 'Online' but only at office level; Employee 4 is a regional manager. Then the same analytical question is asked as each of them, and the numbers differ.

Here is the shape of a channel-restricted persona's view, filtering delivered revenue and commission to the one sale type they are cleared for:

SELECT 'Employee 2 (Online Only)' AS EmployeeType,
   SUM(CASE WHEN s.SaleType = 'Online' THEN es.DeliveredAmount ELSE 0 END) AS TotalDelivered,
   SUM(CASE WHEN s.SaleType = 'Online' THEN es.CommissionAmount ELSE 0 END) AS TotalCommission
FROM EmployeeSales es
JOIN Sales s ON es.ExternalSalesId = s.Id
WHERE es.EmployeeId = 2;

Run the manager's version of this and the CASE opens up to all sale types; run the rep's and it clamps to 'Online'. The two employees ask the same business question — “what did we deliver, and what commission did it earn?” — and get two honestly different answers, each correct for that person's clearance. The aggregate censored itself down to what each viewer is permitted to know.

Where the filtering really lives

Now the honest part, because it matters. V6 declares measures 19 and 20 — PermissionBasedTotalSale, PermissionBasedNetRevenue — whose formula string is the pseudo-SQL 'SUM(TotalSale) WITH PERMISSION FILTER'. Reading that, you might think the permission filtering is a property of the measure — that the fact-creation engine bakes clearance into stored values.

It does not. The filtering lives in the query functions, not in the stored facts. GetFilteredAnalyticalData filters at read time by calling CheckPermission in its WHERE. The persona demos filter with hand-written CASE WHEN s.SaleType = 'Online' expressions. The stored AnalyticalFact rows are the same for everyone; what differs is which rows and columns each caller's query is permitted to surface. Measures 19 and 20 are a label for that behaviour, not an implementation of it — the WITH PERMISSION FILTER formula is intent, and the filtering functions are where the intent is actually met.

I think read-time filtering is the right choice here, and it is worth saying why rather than treating the formula gap as an embarrassment. Baking permissions into stored facts would mean recomputing and re-storing facts whenever anyone's clearance changed — a grant expiring would invalidate stored data. Filtering at read time keeps the facts canonical and neutral, and applies clearance fresh on every query, so a revoked permission takes effect immediately with no recomputation. The cost is that every read pays the CheckPermission calls, which is exactly the cost V9's permission cache later exists to reduce.

The trade, and the honesty of it

Permission-aware aggregates are a genuinely nice pattern: the same query serves everyone and each person sees only their slice, with no application-layer filtering to forget. The row self-selection means there is no way to accidentally leak a row you were not cleared for by writing the query slightly wrong — the clearance test is inside the function, not bolted on by each caller.

The wrinkle I will not airbrush is the gap between the declared measures and the real mechanism. A CalculationFormula that reads 'SUM(TotalSale) WITH PERMISSION FILTER' is aspirational; the filtering is procedural. That gap is small here, but it is the same species of drift that gets genuinely expensive one post from now — because the V6 CHANGELOG documents an entire security system, tables and triggers and all, that exists nowhere in the code. Measures 19 and 20 are where the drift starts; the changelog is where it becomes a real problem for anyone trying to understand V6 from the docs.