Relational Data Modeling l Zoho Analytics Help

Relational Data Modeling

You might have a database consisting of multiple data tables related to each other. You would want to import these related tables into Zoho Analytics and create reports and dashboards combining data from these tables. 

When these tables are imported as separate entities into a workspace, you cannot create reports and dashboards using data from these tables. You need to join the tables using a lookup relationship to perform cross-functional analytics.

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 Analytics will use this information to enable you create reports by combining columns from these tables, seamlessly without no additional effort.

Relating Tables with Lookup Columns

In a relational database, the related columns from multiple tables are joined using a Foreign key. In Zoho Analytics, the related columns from multiple tables are joined using a Lookup column relationship using cardinality functions. When we join two tables in Zoho Analytics, a Parent-Child relationship will be created between the two tables.

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.

What is Cardinality?
Cardinality refers to the type of lookup relationship that can be created between two tables. Any two tables can be related using either of the following relationships:
  • One-to-One Relationship: In a one-to-one relationship, the tables that we are trying to connect will have distant values and only one possible relationship can be formed between the two tables.  This type of relationship is not widespread.
  • Many-to-Many Relationship: Many-to-Many Relationship or M-N relationship is where both the columns used to create a relationship have multiple values. Each element in the "M" attribute (Parent Table) can have multiple matching rows in the "N" attribute (Child Table) and each element in the "N" attribute (Child Table) can have multiple matching rows in the "M" attribute (Parent Table).
  • One-to-Many/Many-to-One Relationship: This is the most common type of relationship. Each element in the "1" attribute corresponds to one or more elements in the "N" attribute, and each "N" attribute corresponds to one and only one element in the "1" attribute. In a One-to-Many relationship, the parent table will have unique values and the child table will have duplicate values. In a Many-to-One relationship, the parent table will have unique values and the child table will contain duplicate values.
Note: When you manually create a lookup relationship, Zoho Analytics does not check the data columns if the data values adhere to the mentioned cardinality type.

You can also create a lookup coloumn by following the below steps:

  • Open the corresponding table, right click the column header and select Change to Lookup Column.
  • In the Change to Lookup Column dialog that opens, select the column to look up.

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 Workspace. In Zoho Analytics, tables with lookup relationship can be joined in the following two ways:

Joining Tables with Auto-Join

While creating reports, Zoho Analytics 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 the selected table has any lookup relationship defined 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 corresponding tables and fetch the required data to generate the report.  (You can also join tables using Query Tables. Refer to the topic Joining Tables with Query Table).

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. Based on this relationship, it lists the columns from both the table under the Column List panel in the Report Designer as shown in the above screenshot.

Note on configuring the Join type:

By default, Zoho Analytics will join tables using the Left Join type. Possible Join types are:

  • Left Join - Report will be computed with all the rows from the child table (left) and only the matching rows from the parent table (right). Matching is done based on lookup columns defined between child & parent tables. This will be the default join type.
  • Right Join - Report will be computed with all the rows from the parent table (right) and only the matching rows from the child table (left). Matching is done based on lookup columns defined between parent & child tables.

You can choose to change the join type. You will find a view relationship  icon in the report designer while creating reports by joining tables. Click this, the View Relationship Used dialog will open. Select the type of join and click Apply.

Joining Tables with Query Table

Query Table is a feature that enables you to prepare data for easy reporting and analysis. You can combine data from one or more tables in a Workspace and create specific data views using the standard SQL SELECT queries. These data views are similar to tables and you can perform operations such as report creation, sharing, and even create another Query Table over an existing Query Table.

You can create Query Tables for filtering datasets, batching datasets together (union), transforming data, applying SQL query functions, joining datasets and more.

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 Analytics allows you to create any type of reports for analysis and visualization.

Click to learn more.


    Zoho CRM Training Programs

    Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

    Zoho CRM Training
      Redefine the way you work
      with Zoho Workplace

        Zoho DataPrep Personalized Demo

        If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

        Zoho CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                      Still can't find what you're looking for?

                      Write to us: support@zohoforms.com


                            




                            

                        Zoho Marketing Automation

                          Zoho Sheet Resources

                           




                              Zoho Forms Resources


                                Secure your business
                                communication with Zoho Mail


                                Mail on the move with
                                Zoho Mail mobile application

                                  Stay on top of your schedule
                                  at all times


                                  Carry your calendar with you
                                  Anytime, anywhere




                                        Zoho Sign Resources

                                          Sign, Paperless!

                                          Sign and send business documents on the go!

                                          Get Started Now


                                              Zoho SalesIQ Resources



                                                  Zoho TeamInbox Resources



                                                          Zoho DataPrep Resources



                                                            Zoho DataPrep Demo

                                                            Get a personalized demo or POC

                                                            REGISTER NOW


                                                              Design. Discuss. Deliver.

                                                              Create visually engaging stories with Zoho Show.

                                                              Get Started Now











                                                                                    • Related Articles

                                                                                    • Does Zoho Analytics support Relational modeling?

                                                                                      Yes, Zoho Analytics supports relational modeling of your database. There are some limitations currently, which would be addressed incrementally to provide a powerful relational modeling capability in Zoho Analytics. The following are the important ...
                                                                                    • Importing Data from Files

                                                                                      Often you would already have data locally stored in tabular file formats like CSV, XLS (Excel), JSON, Statistical file, and HTML files in your local drive or FTP.  Zoho Analytics offers easy import and copy-paste options of such data for ...
                                                                                    • Importing Data from Zoho Analytics Workspace

                                                                                      Creating reports from tables/data spread across multiple workspaces is now made easy with the Zoho Analytics Workspace import option. You might sometimes have relevant data spread across separate workspaces. However, you cannot create reports from ...
                                                                                    • Importing Data from Cloud Drive

                                                                                      Zoho Analytics allows you to import data from CSV, Excel (XLS and XLSX), JSON, HTML and zipped files stored on different Cloud Drive/Storage such as Zoho WorkDrive, Google Drive, Dropbox, Box, Microsoft OneDrive, and Amazon S3 for advanced reporting ...
                                                                                    • Importing Data from Web Feeds

                                                                                      Often you would have data locally stored in tabular file formats like CSV, XLSX (Excel), JSON, Statistical file and HTML files. The data in such formats could also be available as a URL or Web feed or even some application generated. Zoho Analytics ...
                                                                                    Wherever you are is as good as
                                                                                    your workplace

                                                                                      Resources

                                                                                      Videos

                                                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                      eBooks

                                                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                      Webinars

                                                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                      CRM Tips

                                                                                      Make the most of Zoho CRM with these useful tips.



                                                                                        Zoho Show Resources