A Combined Query is a way to have multiple queries work in conjunction to return a single data-set. There are three relationships that these combinations can have:
Union Queries takes all of the information from every query, eliminates duplicates, and returns what remains. This can be visualized by a A⋃B venn-diagram.
Intersection Queries pull the overlapping information between the two queries, and discards information unique in each query. This can be visualized with the A∩B venn-diagram.
Minus Queries shows the difference between two queries by showing the rows of the first query minus the rows of the second query. This can be visualized by the AΔB venn-diagram.
Query 1: Apples; Oranges; Bananas
Query 2: Apples; Limes
Union Query: Apples; Oranges; Bananas; Limes
Intersection Query: Apples
Minus Query: Oranges; Bananas
These systems all have different use-cases and strengths, but functionally they all serve the purpose to produce a single set of information pulled from a relationship of multiple queries.
When to use a Combined Query?
You may be reading the above and thinking Combined Queries are the answer to all of life's problems, but beware: Combined Queries can be precarious and one small slip up could send your report down hill.
First, Combined Queries can have a severe performance impact in ETO Results, especially when several queries are combined, or the combination query is accompanied by several other individual queries, as this can exponentially increase the processing required to run the report.
Secondly, Combined Queries are only helpful in specific use-cases. Often times, using a different data type (such as unflattened), a different reporting universe, or implementing a merged dimension may be the more appropriate option. The decision of whether or not to use a combined query should be made on a case by case basis.
How to Build a Combined Query?
- Within the Query panel of ETO Results, select the "Add a Combined Query" button at the top of the panel
2. A new window should pop up in the lower left corner. Double clicking on "Union" will cycle the type of combination between union, intersection, and minus. Selecting each combined query element will show the objects and filters that exist in that query. Each will look identical to start out.
3. Customize the Result Objects to pull the data you are looking to compare. Ensure that both queries have the same query filters.
Tips and Best Practices
-- A Common use-case for union queries will be to compile information from two Touchpoints with overlapping questions. In this case, ensure that each results object and query filter tab look identical in order and element, with only the form type being different. For Example:
Combined Query 1: Date Taken_3885; Meeting Location_3885; Instructor_3885
Combined Query 2: Date Taken_4223; Meeting Location_4223; Instructor_4223