Relational Data Modeling in Zoho Analytics

A nifty feature in Zoho Analytics is the way it supports relational data modeling. You can create or import a reporting database consisting of multiple tables which have relationships defined between them as in a relational database.

In relational databases world, tables are related using Foreign Key relationships. In Zoho Analytics, this is done using the Lookup Column feature where a column in one table points to a column in another table.

Relating tables with Lookup columns

Let me explain this by creating a sample Employee database. This workspace consists of two tables Department and Employee. The Department table has two columns – Department Name and Department ID.

The Employee table has the Employee names and their respective Employee IDs. The Department to which each Employee belongs to is referred by the third column Department ID

We define the Department ID in the Department table as the column that is being looked up by the Department ID column of the Employee table.

Click on the Edit Design button in the Employee table to define this relationship. Double-clicking on a cell in the Lookup Column lists the different tables & their columns. In our example, we choose the Department table’s Department ID.

Joining Tables with Query Table

Database tables which are connected to each other through such relationships allow you to fetch data combining the related tables. In Zoho Analytics you can combine the data in such related tables by creating a Query Table which contains a SQL Select Join query. .
Enforces Data Integrity using Cascade-on-Delete

To ensure that the integrity of the data is maintained when data rows get deleted from related tables, Zoho Analytics supports Cascade-on-Delete feature. That is, when rows are deleted in a parent table, then all the corresponding rows in the child table will be deleted automatically. In the above example, if any department gets deleted in the Department table (parent table), then all the employees will get deleted automatically from the Employee table (child table). For example, let’s say the Accounts department (row) is deleted.

Hope you find these relational modeling features in Zoho Analytics useful. Give it a try and let us know your feedback in the comments or mail us at support at zohoreports dot com. We have planned for a lot more enhancements on relational modeling in the future. Stay tuned!