Relational Data Modeling
A nifty feature in ManageEngine Analytics Plus 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 ManageEngine Analytics Plus, 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, ManageEngine Analytics Plus 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
Let us try to explain using the Sales workspace. Say the Sales workspace consists of two tables - Customer and Sales. The Customer table has two columns - Customer ID and Customer Name.
The Sales table has various columns with sales details, including a Customer ID column.
We define the Customer ID in the Customer table as the column that is being looked up by the Customer ID column of the Sales table. Click on the Edit Design button in the Sales 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 Customer table's Customer ID.
You can also create a lookup column using the column header at the top.
- Right click on the Column Properties and choose 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 ManageEngine Analytics Plus, tables with lookup relationship can be joined in the following two ways:
- Joining Tables with Auto-Join
- Joining Tables with Query Table
Joining Tables with Auto-Join
While creating reports, ManageEngine Analytics Plus 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 workspace. 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. ManageEngine Analytics Plus 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 Customer based Sales created over the Customer and Sales tables using Auto-Join feature.
In the above example, Auto-Join feature detects the lookup relationship created between Customer and Sales tables (Refer to the example model discussed under the topic Relating Tables with Lookup Columns using the common Customer ID (Lookup) column present in both the tables). Based on this relationship it lists the columns from both the tables under Column List panel in the Report Designer as shown in the above screen-shot.
In this chart, we have used the Customer Name column from the Customer table and Sales from Sales table. On generating the chart, the Auto-join feature will automatically join the data from both the Customer and Sales table using the Lookup column CID and provide the report.
Note on configuring the Join type:
By default ManageEngine Analytics Plus 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 paths icon in the report designer while creating reports by joining tables. Click this, the Paths Used dialog will open. Select the type of join and click Apply.
Joining Tables with Query Table
In ManageEngine Analytics Plus 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 Customer and Sales tables can be made as shown below.
The example query above joins the Customer & Sales tables, getting the product mapped to each customer . Over the query table that you have created by joining the necessary tables, ManageEngine Analytics Plus allows you to create any type of reports for analysis and visualization.