Zoho Analytics supports relational data modelling, allowing you to connect multiple tables using lookup columns and relationship definitions. This approach mirrors concepts from database design such as the Star Schema and Snowflake Schema, enabling more powerful cross-table reporting without the need to manually merge data.

Why Use Relational Modelling?

Storing data in separate, normalised tables reduces duplication and keeps your workspace organised. By linking tables through defined relationships, you can build reports that span multiple tables whilst keeping each dataset clean and focused on a single subject area.

Creating Lookup Columns

A lookup column links a field in one table to a matching field in another. To create one:

1. Open the table where you want to add the lookup.
2. Click Add Column and select Lookup Column.
3. Choose the related table and the column to match on.
4. Optionally, select additional columns from the related table to pull through as reference fields.

Once a lookup column is defined, Zoho Analytics can use it to automatically join the tables when building reports.

Relationship Types

Zoho Analytics recognises three cardinality types when defining table relationships:

• One-to-One: Each record in Table A corresponds to exactly one record in Table B.
• One-to-Many: A single record in Table A relates to multiple records in Table B (the most common type, for example a customer with multiple orders).
• Many-to-Many: Records in both tables can relate to multiple records in the other. This typically requires a bridging table to resolve cleanly.

Star Schema and Snowflake Schema

These are common data warehouse patterns that Zoho Analytics supports:

• Star Schema: A central fact table (such as Sales Transactions) is linked directly to several dimension tables (such as Products, Customers, and Regions). Queries are fast and the structure is straightforward to understand.
• Snowflake Schema: Dimension tables are further normalised into sub-dimension tables. This reduces redundancy further but adds more joins, which can slightly increase query complexity.

Auto-Join in Reports

When you add columns from multiple related tables to a report, Zoho Analytics automatically applies the appropriate join based on the lookup relationships you have defined. This means you do not need to write SQL manually. The platform handles the join logic transparently, making cross-table reporting accessible to non-technical users.

Using Query Tables for Complex Joins

For more advanced join scenarios, including custom WHERE conditions or many-to-many resolution, you can use Query Tables. These let you write SQL SELECT statements that combine data from multiple tables. The resulting query table can then be used as a source for reports just like any standard table.