Relational Data Modeling
A nifty feature in Zoho Reports is the way it supports relational data modeling. You can create or import a 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 Reports, this is done using the Lookup Column feature where a column in one table points to a column in another table.
With a feature like Lookup Columns you can
- Organize your data in a normalized model avoiding duplication of information across tables
- Segregate as measures (numeric data columns which you could aggregate) and facts/dimensions (data columns which you use for grouping in reports).
- Define familiar models like Star Schema & Snow-flake Schema which are optimized for reporting and analysis.
Once you relate two tables using lookup columns, Zoho Reports will use this information to enable you create reports by combining columns from these tables, seamlessly without no additional effort. This is discussed in more detail in the section Joining Tables with Auto-join
of this document
Relating tables with Lookup columns
Let us try to explain this by creating a sample Employee database. Say the Employee DB 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 the cell in the Lookup Column field, lists the different tables & their columns in a drop-down box. In our example, we choose the Department table's Department ID.
Now a dialog box will come up as shown below with the options to handle errors during conversion and to maintain data integrity.
Here's is a rundown of the options available in the Design Modification Settings dialog box.
If error occurs on conversion?
Changing a column to a Lookup could result in failures. This option lists down the action to be taken in case an error occurs during Lookup column conversion. The options are:
- Set empty value for the column in the corresponding record: For every row that fails during conversion the corresponding lookup column value will be set to empty (NULL), if this option is selected.
- Stop conversion and rollback the changes: Choosing this option, if an error occurs during conversion the entire conversion will be aborted and the changes done will be rolled back. This way the table will retain the existing design & values without any lookup column being created and no changes done.
On Deleting the values in the Lookup (parent) column:
This section provides options on the action to be taken in the child table (table in which you define the lookup) when a record is deleted in the Parent table (the table which you are looking up). The options are discussed in detail under the next topic Enforce Data Integrity.
On clicking OK, Zoho Reports will create the Lookup column relationship between the tables.
When a new row is added in a table that looks up a column from another table, all the values get listed automatically. Let's say a new Employee is to be added. The Department IDs get listed in the Department column and you can choose the appropriate department the new Employee belongs to.
To ensure that the integrity of the data is maintained when data rows get deleted from related tables, Zoho Reports supports the following features.
- Retain Child table Data
- Set Null on Delete
These options basically specifies the action to be taken in the child table (table in which you define the lookup) when a record is deleted in the Parent table (the table which you are looking up).
Retain Child Table Data
This feature will be available when you select No action on this (child) table option in the Design Modification settings
dialog that opens while relating tables with lookup column. Selecting
this option will retain the data in the child table even when the corresponding data is deleted in the parent table. This option is highly recommended as it provides high performance in data uploads (add/modify/delete records) into this table.
This feature will be available when you select Delete the Corresponding rows in the(child) table
in the Design Modification settings
dialog box that appears while relating tables with lookup column. Selecting this option will make Zoho Reports to delete all the corresponding rows in the child table when a row
gets deleted in the parent table (Parent and Child tables are related in Zoho Reports using the Lookup column feature discussed above). This option basically ensures that there will not be any row in the child table which does not have a corresponding related row in the parent table. This is called enforcing data integrity in relational modeling.
Continuing 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). Suppose, let's say the Accounts department (row) is deleted.
This will result in the corresponding Employee rows getting deleted.
Set NULL on Delete
This feature will be available when you select Set empty (Null) to the Corresponding values in the(child) table
option in the Design Modification settings
dialog box , when defining a lookup column. If this option is chosen and a row in the parent table is deleted, then for the corresponding rows in the child table the lookup column value will be set to NULL (i.e. empty). What this means is, you are allowing the existence of rows in the child table even though there are no related row(s) in the parent table.
In the example above,
if we delete Accounts department (row) with this option enabled, then all the corresponding rows in the Employee
table will be set to NULL as shown below.
Joining Tables using Lookup columns
Lookup columns can be used to join tables where you need to retrieve data from two or more tables in a reporting database. In Zoho Reports, tables with lookup relationship can be joined in the following two ways:
While creating reports, Zoho Reports provides you with a special feature called Auto-Join
which automatically joins tables connected using lookup column. After selecting a table you want to create the report on, Auto-Join
feature attempts to determine if it has any lookup relationship with other tables in the database. If it has, Auto-Join will list all the columns of the related tables in Column List
panel in Report Designer
. Once listed, you can drag and drop the required columns from the list in to respective shelves to create the reports. Zoho Reports will join the tables while creating reports with out requiring you to create a query table to join the tables. (Refer to the topic Joining Tables with Query Table
discussed below for more information)Following example shows Department wise Employee count Pivot Table created over the Employee and Department tables using Auto-Join feature.
In the above example, Auto-Join feature detects the lookup relationship created between Department and Employee tables (Refer to the example model discussed under the topic Relating Tables with Lookup Columns
) using the common Department ID (Lookup) column present in both the tables. Based on this relationship it lists the columns from both the table under Column List panel in the Report Designer as shown in the above screen-shot.
In the Pivot table created above, we have used the Department Name column from the Department table (dropped in the Rows shelf) and Employee ID from Employee table (dropped in the Data shelf with "Sum" function applied). On generating the Pivot Table, the Auto-join feature will automatically join the data from both the Department and Employee table using the Lookup column Department ID and provide the report.
In Zoho Reports you can combine the data in one or more tables using common columns, by creating a Query Table which contains a SQL Select Join query. For example, a query combining the Employee and Department tables can be made as shown below.
The example query above joins the Employee & Department tables, getting the department name mapped to each employee. Over the query table that you have created by joining the necessary tables, Zoho Reports allows you to create any type of reports for analysis and visualization.