Relational Data Modeling | Zoho Analytics On-Premise

Relational Data Modeling


A nifty feature in Zoho Analytics 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 Analytics, 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 Analytics 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.

You can also create a lookup column 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 a 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 into 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 Column List panel in the Report Designer as shown in the above screen-shot.

In this Pivot table, 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 "Count" 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.

Note on configuring the Join type:

By default, Zoho Analytics will join tables using 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 here to learn more about query tables.


    Access your files securely from anywhere

      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


              Zoho Sign now offers specialized one-on-one training for both administrators and developers.

              BOOK A SESSION









                                            You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                                Manage your brands on social media

                                                  Zoho Desk Resources

                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                    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 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

                                                                                                              • Import data from files

                                                                                                                If you have data stored in local drives in tabular file formats such as CSV, Excel, JSON, etc., you can easily import them into Zoho Analytics. Zoho Analytics offers easy import and copy-paste options of such data to jump start your reporting and ...
                                                                                                              • Import data from Feeds

                                                                                                                If you have data stored in web URLs in tabular file formats such as CSV, Excel, JSON, etc., you can easily import them into Zoho Analytics. Zoho Analytics offers easy import and copy-paste options of such data to jump start your reporting and ...
                                                                                                              • Import 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 Google Drive, Dropbox, Box and OneDrive, for advanced reporting and analysis. You can also schedule ...
                                                                                                              • Data Snapshots

                                                                                                                The Data Snapshot feature in Zoho Analytics allows you to capture and maintain a timeline of changes to a specific metric or a set of metrics. It periodically records changes in your data, and stores it in a table to facilitate historical report ...
                                                                                                              • Data Alerts

                                                                                                                In business, keeping track of changes in your KPIs and metrics is vital to stay ahead of the curve. Data alerts in Zoho Analytics notify you of key business events that you cannot afford to miss. This helps you quickly make informed business ...
                                                                                                                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