Analytical queries and associated transformations

You use analytical queries to combine multiple data sets from the same or differing data sources into one result set.

In analytical queries, you apply transformations to configure how result sets from multiple queries or tables are transformed into one query result set. There are several principle transformation types that you can incorporate into an analytical query structure:

  • Append - to combine query results from two queries or tables into one query result set in such a way that the result set includes all values from both the queries or tables. You can specify how the columns will be paired up across the two result sets.
  • Union - to combine query results from two queries or tables into one query result set in such a way that the result set includes unique values from the queries or tables. That is, to exclude duplicate values, use Union. You can specify how the columns will be paired up across the two result sets.
  • Except - to combine query results from two queries or tables into one query result set in such a way that the result set includes all values from the first query or table and does not include any values from the second query or table. That is, to exclude all values from the second query or table, even if the values exist in the first query or table, use Except. You can specify how the columns will be paired up across the two result sets.
  • Intersect - to combine query results from two queries or tables into one query result set in such a way that the result set includes common values that exist in both the queries or tables. That is, to only include the values that exist in both the queries or tables, use Intersect. You can specify how the columns will be paired up across the two result sets.
  • Join - to join the result sets from two queries or tables into one unified result set by joining one or more columns together, much like a join operation in SQL.
  • Crosstab - to perform a cross-tabulation of one or more columns, such as a sum, average, min, max, computed over two or more tabulation columns. For example, tabulating the sum of product sales across the region and month.
  • Normalize - to reduce the number of columns in a table by returning data from eliminated columns in more rows. Normalizing query data facilitates query processing so that executions of the same query with different parameters can be easily compared with one another and aggregated together.
  • Conditional Grouping - to create aggregated summaries of result set data based on conditional expressions.
  • Column Filter - to add a calculated column before or after existing columns and enter filtering functions.
To create an analytical query, you perform the following procedures:
  1. Building an analytical query structure. You select transformations that you want to apply in your analytical query.
  2. Populating analytical query transformations. You add tables or queries that provide data for the transformations that you selected.
  3. Configuring analytical query transformations. You configure the columns from the source data sets for the analytical query final result set.