Analytical queries

With analytical queries, you can combine data from multiple queries from the same or differing data sources into one result set.

In some situations, you might need to draw data from several different sets of data, some of which might be stored in different data sources. Querying each set of data one at a time can be time-consuming and ultimately adds unnecessary time and confusion to your query management process. Using analytical queries, you can combine query results from multiple queries that span different data sources into one query result set. You can add any number of queries in any number of combinations to your analytical query, allowing you to author one comprehensive result set that retrieves exactly the data that you need.

You can incorporate the following query types into an analytical query structure tree:

Append Query
Append queries combine query results from two queries into one query result set. You can specify how the columns are paired across the two result sets.
Join Query
Join queries join the result sets from two queries into one unified result set by joining one or more columns together, much like an SQL join operation.
Crosstab Query
Crosstab queries provide a cross-tabulation of one or more columns, such as a sum, average, minimum, or maximum, computed over two or more tabulation columns. For example, tabulating the sum of product sales across the region and month.
Normalize Query
Normalized queries normalize the text that is returned in a query result set. Normalizing query text facilitates query processing, such that executions of the same query with different parameters can be easily compared with one another and aggregated together.
Conditional Grouping Query
With Conditional Grouping queries, you can create aggregated summaries of result set data based on conditional expressions.
Column Filter Query
With Column filter queries, you can add a calculated column before or after existing columns and enter filtering functions.
The following screen captures show the different views of the Analytical Query editor:
Figure 1. Append query type
View of the editor for the Append query type.
Figure 2. Join query type
View of the editor for the Join query type
Figure 3. Crosstab query type
View of the editor for the Crosstab query type
Figure 4. Normalize query type
View of the editor for the Normalize query type
Figure 5. Conditional grouping query type
View of the editor for the Conditional grouping query type
Figure 6. Column filtering query type
View of the editor for the Column filtering query type

Multiple queries and tables can be added into the analytical query structure tree to generate a comprehensive result set that encompasses data from many different sources. The different options of each query type allow you to customize how this data is displayed.