Creating query sets

A query subject can be defined using the set operations of union, intersect, or except. You define a query set to merge, compare, or equate similar data from different data sources. Query sets are useful when modeling data from disparate systems.

There are many reasons for creating a query set. A query set may be needed to create a conformed dimension across disparate data sources. Or, you may want to compare the contents of two queries to determine whether the queries contain the same data; this is common in test environments. Or, you may want to compare queries that return nulls. Or, you want to handle a fact-to-fact relationship that is truly a one-to-one relationship. (If it is not truly a one-to-one relationship, create a multiple-grain query What are multi-fact, multi-grain queries.)

A query set can consist of only two query subjects. You can create a query set that merges two other query sets together. A query set can contain

• All the rows of two query subjects (union operation).

  For example, your company recently acquired another company and you need a complete list of all customers.

• Only the rows that are shared between the query subjects (intersect operation).

  For example, you want to find out which staff members are also managers.

• Only the rows that exist in the first query subject and not in the second query subject in the query set (except operation).

  For example, you want to highlight the differences between where your products were sold this year and ten years ago.

The names of the items in the projection list default to the items assigned to the first query subject in the set operation.

Relationships between the two query subjects in the query set and other query subjects are not included in the query set.

Reports show different results depending on which operator is used. For example, you have two query subjects with the names of various employees.

The first query subject contains these rows:

The second query subject contains these rows:

You create a query set. You see different results depending on the operator you use.