A technical breakdown of Level of Detail (LOD) execution pipelines, stateful set-action machines, schema-drift engineering in legacy CRM migrations, and multi-node Tableau Server cluster topology.
Translating calculation expressions into execution plans
The defining difference between a casual dashboard builder and an enterprise BI architect is understanding what happens beneath the canvas when an LOD calculation executes.
Tableau does not compute calculations in memory sequentially; it compiles VizQL expressions into live SQL queries or Hyper column-store scan instructions. The three LOD types represent fundamentally different relational join mechanics:
| LOD Type | Relational Mechanism | Filter Pipeline Order | Query Plan Impact |
|---|---|---|---|
FIXED |
Independent correlated subquery or standalone temporary table. Aggregates at specified dimensions regardless of view granularity. | Evaluated BEFORE Dimension Filters (only Context Filters affect it). | Can cause full table scans if unindexed; joins back to main query on fixed dimension keys. |
INCLUDE |
Appends specified dimension to the view's GROUP BY clause, computes inner aggregation, then outer-aggregates to the viz level. |
Evaluated AFTER Dimension Filters. | Executes as a nested aggregation (e.g. AVG(SUM(x))). Lightweight if inner cardinality is bounded. |
EXCLUDE |
Removes dimension from the view's GROUP BY clause. Window-partitions across the remaining dimensions. |
Evaluated AFTER Dimension Filters. | Translates to SQL analytical window functions (e.g. SUM(x) OVER (PARTITION BY ...)). Excellent for percent-of-total calculations. |
Because FIXED LODs evaluate before normal Dimension Filters, placing a non-context filter on a workbook does not reduce the rows scanned by the FIXED subquery!
On a 10-million row extract, an unpromoted dimension filter forces Tableau to compute the fixed aggregate across the entire 10M rows before filtering the visualization, destroying sub-second render SLAs. Promoting the dimension to Context Filter forces Tableau to create a partitioned temporary table, accelerating the query by orders of magnitude.
Transforming static reporting into stateful web applications
Upwork and enterprise clients consistently demand drill-through interactivity: category expansion, linked breadcrumbs, and cross-filtering that allows executives to investigate anomalies without filing ticket requests.
Traditional Tableau dashboards rely on Quick Filters, which re-query and drop context. In contrast, modern high-performance architecture utilizes Set Actions to implement asymmetric drilling:
// Asymmetric Drill Calculation via Set Action
// When a department is in [Selected Department Set], reveal its work groups; else keep folded
IF [Selected Department Set] THEN
[Work Group]
ELSE
"+" + [Department]
ENDThis avoids full dashboard reloads and keeps the rendering memory footprint bounded. The companion deliverable KC 311 Taxonomy Explorer demonstrates this exact state machine: users drill from Department down to Priority Detail with continuous breadcrumbs, mimicking a full-fledged web application.
Conforming legacy cutovers without parallel runs
In major enterprise system transitions (e.g., Peoplesoft to Workday, Netezza to Snowflake, or PeopleSoft to MyKCMO), management frequently expects continuous longitudinal reporting. However, real-world cutovers almost never provide clean parallel runs:
exceeded_est_timeframe) were completely omitted in the new schema.$select=count(*).Infrastructure architecture for high-concurrency environments
Supporting 1,400 concurrent viewers and 300 active publishers across enterprise branches requires isolating background extraction workloads from interactive VizQL rendering pipelines:
| Node Role | Target Topology | Process Allocation | Architectural Rationale |
|---|---|---|---|
| Primary / Gateway Nodes | 2 Nodes (Active/Passive HA) | Gateway, Cluster Controller, Repository | Terminates SSL, balances incoming user requests, manages active repository state and failover. |
| Interactive VizQL Worker Nodes | 6 Nodes (Compute-Optimized) | VizQL Server (4 per node), Data Engine, Cache Server | Zero background extract tasks allowed. 100% of CPU/RAM dedicated to compiling VizQL queries and serving sub-second dashboard clicks. |
| Dedicated Backgrounder Nodes | 4 Nodes (Batch-Optimized) | Backgrounder (8 per node), Data Engine | Heavy extract refreshes, subscription emails, and flow runs are quarantined to prevent interactive UI latency spikes. |
Contract clients readily pay $85–125/hr for architects who can diagnose why an extract refresh is locking the VizQL cache, tune Snowflake/Synapse query pushdowns, and rebuild broken schemas. Dashboard authoring is commoditized; enterprise infrastructure and schema governance are not.
Translating technical architecture into the technical deliverable
The companion deliverable What the City Stopped Being Able to Measure showcases this engineering discipline in action: