Picture this…
You’re an analyst or data engineer at a B2B SaaS analytics company. Your product serves clients or of all industries, with each client’s data containing unique custom fields.
You’re anticipating this request: different customers want to add their own custom fields (e.g. product categories, industry-specific sales metrics, workflow flags), and then build reports on those fields. Or perhaps, your company has a wide range of internal business units that all require different fields, and you want to restrict access to custom fields within each unit.
Whether it’s external or internal, you want custom fields to be visible only to the individual tenants they are created for.
On the back end, a smart engineer has stored all fields in the data warehouse in Entity-Attribute-Value (EAV) format. It’s a data model that stores data as key-value pairs rather than using fixed columns for each field. It looks like this:
Fig 1. Example of EAV data. Taken from Russ’s article.
Since this article will focus on how data is structured and reshaped, it’s helpful to visually abstract the data architecture. You can imagine the “long” shape of EAV data like:
Fig 2. Data stored in “long” format (EAV). Green and red attributes are shared between company A and B. Blue and pink attributes are custom to each company. For visual simplicity, there are exactly two data points per attribute.
This is a reasonable design for storing data that is custom to individual tenants because it requires no schema migration and it accounts for all the different data types the custom field might store. Additionally, since not every field is relevant to each customer, EAV obviates the need for mostly sparse custom field columns.
However, in order to build client- or department-specific reports, their specific data must be denormalized to the familiar one-row-per-record, one-column-per-field format. The data from above would be reshaped into a “wide” format, like this:
Fig 3. The same data from Fig. 2, reshaped to wide format for reporting. Each company gets a separate table.
The challenge with EAV
As your company scales, so does your client list, along with their list of fields. Reshaping the data from EAV to report-ready requires pivot logic, which needs to be rewritten every time you add a new field.
Fig 4. Company A adds a new custom field, purple. Pivot logic is needed to reshape the data into reporting format.
The challenges are compounding:
-
The data needs to be reshaped and rejoined
-
Reshaping and rejoining is manual and tedious because there’s no pre-defined, fixed schema
-
Everything in the table is text, so every field also needs a cast, and the cast logic differs for each data type
For generating a single report, this is not a huge issue. However, doing this data transformation, on demand, at scale, for thousands of customers, each with a different set of actively evolving custom fields, is a moving target.
Even if your data is stored in JSON blobs, which addresses some manual reshaping challenges, the question is still, “how do I make it easy to present custom fields to end users?
The core problem is structural: you need a way to store the schema, and encode how they should be permissed, cast, and presented.
Different AI business intelligence tools have different architectural approaches to addressing custom fields, data reshaping, security, and governance. For this article, I’ll compare Omni and Sigma.
Omni vs Sigma: EAV and custom fields
The tl;dr
Omni solves the custom fields problem with modeling while Sigma does so with filtering.
With Omni’s API, a script can monitor for new custom fields and write each customer’s fields into their own dedicated file, which is a lightweight extension that combines their custom fields with a foundation shared by all customers. Each customer’s fields are defined, governed, and cast upstream of reporting.
Sigma does not have an equivalent modeling solution. Instead, one shared setup filters the rows that belong to each customer (including their custom fields), such that each customer gets their own specific view of the same underlying table. The pivot logic needs to be rebuilt manually every time at reporting and by default, surfaces raw ungoverned values.
Put plainly: Omni generates the column list per client and keeps it in sync automatically. Sigma pivots against a fixed column list you maintain yourself.
Omni: underneath the hood
Omni’s data model is structured in layers. Extending outwards from the data warehouse:
-
The schema model mirrors the raw database and any modeling done upstream of Omni (e.g. dbt)
-
The shared model that is a governed source of truth for shared fields/metrics/datasets. Data modeling done here affects all tenants. The shared model includes structured fields for AI context, which allows synonyms, sample values, field descriptions, and sample queries to be centrally defined.
- Within this layer, shared model extensions can store custom fields/metrics/datasets for individual tenants.
-
An optional branch for isolating and testing changes, which allows you to safely deploy without impacting end users
-
The workbook model is where reports are built. This front end modeling can ultimately be promoted back upstream.
Fig 5. Omni’s data model, structured in layers. The shared extensions model lives inside the shared model layer.
Inside the shared model extensions, every tenant gets their own extension file, which is composed of a core model holding attributes shared by all tenants, plus custom attributes dynamically defined by an API. The core model defines shared fields and casting logic once. Then for each customer, a script can use Omni’s API to run a metadata query against the EAV database to find each customer’s specific field names/types, and write the corresponding casting logic. The pseudocode looks like this (actual code can be found in Russ’s repo):
connect to Omni API; run metadata query → {company_id: {field_name: data_type}}
load hub model "eav" and any existing per-company extension models
for each company:
get or create extension model "<company_id>"
build a measure per field (SQL chosen by data type, filtered to that field_name)
build a flattened query view with one dimension per field, bound to the user's company
write both YAML files to the extension model
add the company's mapping to the hub model's dynamic_shared_extensions
As a result, the pivot logic–a base pivot template that auto-expands per custom field–is predefined in each company’s extension, before a report is even generated.
Fig 6. How Omni takes raw data into a report when there are tenant-specific custom fields
By programmatically generating the pivot logic upstream, it evolves dynamically with each customer’s set of custom attributes. No manual maintenance necessary.
An additional security benefit also comes from this structure: changes to the core (“parent”) model propagate to every extension automatically, while changes made inside an extension stay local. This gives you a governed core plus tenant-specific offshoots without needing to duplicate the whole model for each customer, or configuring security to hide one company’s fields from another.
Sigma: underneath the hood
Sigma does not have an equivalent hub-and-spoke model aimed at the custom attributes challenge. Any data model has the default assumption that the schema stays consistent across tenants.
Instead, Sigma offers various mechanisms that each address a piece of the multitenancy problem.
The central mechanism is row-level security (RLS), which restricts the rows visible to a specific user, i.e. Company A only sees the EAV rows associated with Company A–this includes both rows that hold fields shared with other companies and those that are custom to Company A. Every workbook inherits this filter before the data is reshaped for reporting.
CurrentUserAttributeText("company_id") = [company_id]
The pivot occurs at the workbook level, i.e., it is neither modeled nor inherited upstream. That means each report requires the pivoting logic to be built from scratch.
Fig 7. How Sigma takes raw data into a report when there are tenant-specific custom fields
Because all tenants are a part of the shared data model, any new named columns are visible by default to all tenants. The team must proactively manage their metadata (e.g. hide the new named column) to prevent noise from other tenants using the same Model.
Even if custom fields are rolled-up into a JSON field instead of EAV, users still need to manually extract columns.
The age of AI: Additional considerations for governance
Multitenancy and custom fields already bring about challenges for governance in traditional reporting. The introduction of AI analytics layers on additional complexity for governance.
On the security governance end, there needs to be a safe way for customers to ask questions about their own custom fields without risking exposure to other tenants. Multitenancy must be strictly enforced so people aren’t accidentally exposed to data that doesn’t belong to them.
On the data governance end, custom fields should have a single source of truth upstream. Since AI is inherently nondeterministic, you risk it using a different definition each time. Things get even messier when thinking about change management. If there is no central upstream definition for fields, whenever a formula needs to be rewritten, you have to rewrite the formula for each individual report. Notably, this is a challenge regardless of whether AI is baked into your BI tool.
Both Omni and Sigma offer strong solutions with respect to security-related AI governance. However, only Omni has a shared central model that is optimized for semantic governance and change management.
Recommendation: Omni or Sigma?
Omni’s and Sigma’s processes of taking multitenant EAV data to a customer- or department-specific report can be summarized by the following figure.
Fig 8. Side-by-side comparison of the process that reshapes multitenant data to tenant-specific report
Both Omni and Sigma offer core BI and embedded analytics, the ability to explore data with natural language, and a mechanism for handling multitenancy.
The better choice depends on how different your clients’ fields are from one another and whether they will keep growing and diverging.
Omni asks for more setup upfront for a model that will automatically stay in sync with custom fields. As a result, Omni streamlines the complex security, governance, and change management associated with scaling custom fields. It’s the best option if your clients’ data fields will evolve and diverge over time. Sigma has a simpler spreadsheet-first setup. It works well if your clients share a mostly fixed schema and you are okay to occasionally do manual work to extract a known set of custom fields.