Query tables

Analytics Plus offers a way to create tables by combining one or more tables in a workspace or creating specific data views using the standard SQL SELECT queries. These data views are similar to tables and let you create reports, share the data, 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.

General

1. What is a query table?
2. Is there any specific consideration for query table creation?
3. What is the SQL SELECT command, and how is it used in query tables?
4. What are the SQL dialects supported in query tables?

Creating a query table

1. How can I create a query table?
2. What are the standard SQL functions supported in Analytics Plus?
3. How can I modify an existing query in the query table?
4. How can I rename columns in the query table?
5. Can I merge data sets using a query table?
6. Can I join one or more tables using a query table?
7. Can I link two query tables using a lookup column?
8. Is it possible to create a query table over a query table?
9. Is it possible to create a query table over a query table?
10. What is the list of operations performed over a query table?

Troubleshooting Tips

1. Why does the query table keep loading or being timed out when I access it?
2. How to resolve the error that occurs when removing a column from an existing query table?
3. How to transform each unique row value in a dimension column with multiple repetitive values into a separate column in a new table?
4. Can I merge all the regional sales present in different columns into a single column?

General

1. What is a query table?

Query tables enable you to create tables by combining one or more tables in a workspace or creating specific data views using the standard SQL SELECT queries.

2. Is there any specific consideration for query table creation?

Query tables facilitate advanced reporting, but improper usage might affect SQL performance.

Follow these best practices when working with query tables:

• Keep your queries simple; avoid complex formats.
• Refrain from using SELECT * to copy entire tables. Select only the required columns.
• When creating query tables, ensure proper join conditions are in place. Duplicate values in join columns can lead to inaccurate results and degrade server performance.
• Avoid using Cartesian joins (n*n). Instead, aim for 1*1, 1*n, or n*1 relationships while joining the tables.
• A GROUP BY clause should only be used for non-aggregate columns.
• Apply the GROUP BY clause to the non-aggregate columns in your query whenever aggregate functions (e.g., min(), max(), sum(), count()) are used on metric columns.
• Avoid using too many columns in a GROUP BY clause (e.g., 30 or 40 columns). Select only the necessary columns. If you need more columns, use a subquery for metric calculation, so the computation is done only on the required data and the rest is appended to the corresponding row without impacting performance.
• Alias names cannot be used in a HAVING clause.
• Apply necessary filters in the initial query to retrieve only the required data before computing aggregate values. For example, if you have 10 years of data but need analysis based on the current year, filter the current year's data to avoid calculations over the entire dataset.
• Generally, using DISTINCT on a large dataset can slow down performance. Use it only when necessary.
• Avoid using both GROUP BY and DISTINCT in a single SELECT query statement.
• It's recommended to use the UNION ALL operator instead of UNION, as UNION implicitly applies a DISTINCT operation.
• Perform computations on a smaller number of records.
  • Aggregate the dataset first, then apply the join clause.
  • Avoid using the UNION clause for referring to the same tables; use CASE WHEN statements instead.
• Prefer the IN clause over multiple OR conditions for expressions with the same left-side expression.
  • For example, instead of writing f(column) = '1' OR f(column) = '2' OR f(column) = '3', use f(column) IN ('1', '2', '3').

Avoid creating query tables for the following scenarios:

• Simple Calculations: Instead of creating a query table for simple calculations, especially over a single table, use formulas or calculations directly.
• Joining Tables: Do not create a query table solely for joining data. Analytics Plus offers an Auto-Join feature for establishing relational data models. Use a query table only when necessary.
• Filtering Data: Instead of creating multiple query tables to share specific data for different users, apply dynamic share filter criteria.
• Multiple Use Cases: Avoid creating separate query tables for each use case. Consolidate where possible; for instance, calculate multiple metrics across the same dimensions within a single query table. Having too many query tables in a workspace can impact system performance.
• Nested Query Tables: While Analytics Plus supports creating query tables over other query tables at multiple levels, refrain from doing this unnecessarily.
• Cleanup Unused Tables: Periodically clean up unused query tables to prevent unnecessary constraints on system resources.

3. What is the SQL SELECT command, and how is it used in query tables?

SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases (e.g., Oracle, SQL Server, MySQL, etc.).

Analytics Plus uses the SQL SELECT statement to select data from different tables and create a query table. Click here to learn more about SQL SELECT queries.

4. What are the SQL dialects supported in query tables?

Analytics Plus allows you to create a query table using the following SQL dialects: Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix, PostgreSQL, and ANSI SQL.

Creating a query table

1. How can I create a query table?

2. What are the standard SQL functions supported in Analytics Plus?

Analytics Plus enables you to use all the functions listed under the SQL Functions tab to create query tables. Analytics Plus supports Logical, Aggregate, Tabular, String, Mathematical, Date, Date Arithmetic, Date Conversion, Current Date, Business Date, Duration, and other General functions.

3. How can I modify an existing query in the query table?

Analytics Plus allows you to edit the existing query used in the query tables. To do this, follow the steps below:

• Open the required query table in Edit Design mode.

  

• Make the required changes in the existing SQL query, or click the Clear Query button to clear the entire query and type a new one.
• Click Execute Query. The resulting table will be displayed in the Expected Result tab.
• Click Save to save your query table.

  

Note: You cannot edit columns that are used in generating a report in the workspace.

4. How can I rename columns in the query table?

Analytics Plus allows you to rename any column in the query table using an alias name even if the columns are referenced by other views in the workspace.

For example, let's consider a scenario where we use the query table to segregate help desk tickets based on their status, such as incoming and closed, and add alias names for better readability.

5. Can I merge data sets using a query table?

Yes. Analytics Plus lets you merge the common data sets available in different tables using the UNION function. For example, the below query table combines the Employee ID and Employee Name from the Projects Given table with the Employee ID and Employee Name from the Projects Submitted Date table.

6. Can I join one or more tables using a query table?

Yes. Analytics Plus allows you to join tables using one of the following joints:

• Left Join
• Right Join
• Inner Join

The following images show the data combined using all three join types.

7. Can I link two query tables using a lookup column?

Yes. Analytics Plus lets you link two query tables using a lookup column. Click here to learn more about lookup columns. To connect query tables using the lookup column, follow the steps below.

• Open the required query table and select a column to be changed to a lookup column.
• Right-click and choose Change to Lookup Column.

  

• In the dialog that appears, select a column to look up from the list of tables available in the workspace.

  

• Click Save & Close.

8. Is it possible to create a query table over a query table?

Yes. Analytics Plus allows you to create a maximum of 3 levels of queries over the existing query table. Follow the steps below to create a new query table from an existing query table.

• Open the required workspace.
• Click the + Create button from the side panel and choose Query Table.

  

• Use the required columns from the existing query table and other tables in the workspace.

  ​

• Click Execute Query and Save to create a new query table.

9. Is it possible to create a query table over a query table?

Yes. Analytics Plus enables you to create an aggregate formula for query tables. Click here to learn more about creating aggregate formulas.

10. What is the list of operations performed over a query table?

Query tables work just like any other table in the workspace. Analytics Plus enables you to perform all the operations performed over a table created manually or by importing data. Refer the documentation to learn more about table operations.

Troubleshooting Tips

1. Why does the query table keep loading or being timed out when I access it?

The performance of the query table depends on the number of rows, the types of joins used, the functions used, etc., in the table. Please ensure you adhere to the points mentioned while creating a query table.

If the issue persists, try restructuring the query used in the query table, or you can also write to us at analyticsplus-support@manageengine.com.

2. How to resolve the error that occurs when removing a column from an existing query table?

Analytics Plus does not allow you to remove a column when there are dependent reports created using that column.

Before deleting a column in the query table, Analytics Plus performs a dependency check. If dependent views are found, Analytics Plus aborts the deletion process and displays the dependent items for you to review. You can review and delete the dependent items to resolve the issue.

​

3. How to transform each unique row value in a dimension column with multiple repetitive values into a separate column in a new table?

Analytics Plus enables you to split the distinct values in a column and transform them into multiple columns using the PIVOT clause.

The PIVOT keyword rotates rows into columns and transforms each unique value in a chosen column in the input table to a separate column in the output table.

For example, let us split the Sales data across the single column Product Category.

​

The unique value in the Product Category column is split into multiple columns, such as Grocery, Furniture, and Stationery, and the Sales data is displayed under the corresponding Product Category column.

4. Can I merge all the regional sales present in different columns into a single column?

Yes. Analytics Plus allows you to merge the data from multiple columns into a single column using the UNPIVOT clause.

The UNPIVOT keyword rotates columns into rows and transforms the chosen columns in the input table into single column row values in the output table.

For example, let's merge the Sales data from multiple Region columns in the below table.

The columns East, West, South, and Central are combined into a column named Region, and the Sales data from multiple Region columns is displayed under the corresponding row of Sales column.

​